Excel Dateien in XML exportieren mit MDAC

Ich hatte ja schon in diesem Blog über den Export von neueren Excel Datei Formaten mit EPPlus geschrieben. EPPlus kann aber leider nur aktuelle Formate von Excel verarbeiten. Alle älteren Formate funktionieren leider nicht.

Es gibt mehrere Möglichkeiten auch ältere Formate zu verarbeiten. Am einfachsten funktionierte für mich aber die MDAC (Microsoft Data Access Components) Varaiante. Hierfür muss man sicherlich erstmal diese etwas in die Jahre gekommene Framwork installieren. Das charmante an dieser Variante ist, dass man mit einer SQL like Abfrage arbeiten kann z.B.:

„Select * from [$Tabelle1]“

Ich hab es mir einfach gemacht und ein Skript gebaut, was alle Daten, aller Worksheets in eine XML Datei schreibt. Damit ist man im Grunde am flexiblesten, egal was man danach noch alles mit den Daten so machen möchte:

#path to the input excel file
$input="c:\temp\excel.xsl"
 
#path to write the xml output to
$output="c:\temp\ex_ppr.xml"
 
#create the db like driver object
$oleDbConn = New-Object System.Data.OleDb.OleDbConnection
$oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
 
#set the connection string
$oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$p_cpf_input;Extended Properties=Excel 12.0;Persist Security Info=False"
 
#open the connection
$oleDbConn.Open()
$oleDbCmd.Connection = $OleDbConn
 
#get the tables/sheet names
$tables = $oleDbConn.GetSchema("Tables")
 
#create the basic xml output nodes
[System.XML.XMLDocument]$xml=New-Object System.XML.XMLDocument
$xml = [xml] "<!--?xml version='1.0' encoding='utf-8'?-->"
[System.XML.XMLElement]$xml_root=$xml.CreateElement("root")
 
 
#init worksheet counter
$ws = 1
 
foreach($table in $tables){
 
	#get the local table name
	$t = $table.TABLE_NAME
 
	#skip all useless tables
	if($t -like "*xlnm*" -or $t -like "*deleted*"){
		continue
	}else{
 
		#tim the ' from the string
		$t = $t.Trim("'")
 
		#reset the db objects
		$oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
		$dataTable = New-Object System.Data.DataTable
 
		#assemble sql statement
		$oleDbCmd.commandtext = "Select * from [$t]"
		$oleDbAdapter.SelectCommand = $OleDbCmd
 
		#execute the command
		$ret=$oleDbAdapter.Fill($dataTable)
 
		#create new worksheet level
		[System.XML.XMLElement]$xml_ws=$xml.CreateElement("worksheet")
		$t = $t.Trim("$")
		$xml_ws.SetAttribute("name", $t)
 
		#init counter
		$row = 1
 
		#per each result in the dataset
		foreach($data in $dataTable){
 
			#create the row node
			[System.XML.XMLElement]$xml_row=$xml.CreateElement("row")
			$xml_row.SetAttribute("id", $row)
 
			#init column counter
			$col = 1
 
			foreach($cell in $data.ItemArray){
 
				[System.XML.XMLElement]$xml_item=$xml.CreateElement("item")
				$xml_item.SetAttribute("column", $col)
				$xml_item.InnerText = $cell
				$xml_row.appendChild($xml_item)
 
				#count up the column
				$col ++
 
			}
 
			#append to the row
			$xml_ws.appendChild($xml_row)
 
			#count up
			$row ++
		}
		#append the worksheet to the xml
		$xml_root.appendChild($xml_ws)
	}
}
 
#append anything to the root
$xml.appendChild($xml_root)
 
#save the xml file and done
$xml.Save($output)

Das skript ignoriert alle möglichen „sinnlosen“ Worksheets. Wie ich seht, überlese ich alles was im Tabellennamen irgendwas mit „delete“ oder „xlnm“. Probiert mal mit ein paar Excel Dateien, ihr werdet sehen dass da aller Hand an Backups und irgendwelcher Datenmüll in den Excel Dateien gespeichert ist. Komischerweise stecken auch in den Tabllennamen hier und da Anführungszeichen….auch diese entferne ich damit man sauber durch alle Zellen aller (wirklichen) Worksheets durchlaufen kann.

http://www.agile-coding.net/excel-dateien-in-xml-exportieren-mit-mdac/