WhiskerBiscuit WhiskerBiscuit -4 years ago 133
Vb.net Question

How do you save an **EXCEL** XML file with linq?

I'm loading and saving an Excel XML file using XElement using the following blurb:

Dim root As XElement = XElement.Load(inFile)
'code to change elements goes here
root.Save(outFile)


The problem is that the Save routine is adding namespace tags as well as who knows what, so that Excel and windows no longer recognizes this as an Excel XML file. In my example, I'm not even manipulating elements. I'm just loading and saving the file. I basically want to use linq to find certain elements in the XML, change those then save the whole file. Am I making this too hard?

inFile XML

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="Datagrid">
<Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="11" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:Index="3" ss:AutoFitHeight="0">
<Cell ecProperty="email_address">
<Data ss:Type="String">email address</Data>
</Cell>
</Row>
<Row ss:Index="4" ss:AutoFitHeight="0">
<Cell ecProperty="synthesis_mode">
<Data ss:Type="String">Ideal Mode</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>


outFile Results

<?xml version="1.0" encoding="utf-8"?>
<ss:Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<ss:Worksheet ss:Name="Datagrid">
<ss:Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="11" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
<ss:Row ss:Index="3" ss:AutoFitHeight="0">
<ss:Cell ecProperty="email_address">
<ss:Data ss:Type="String">email address</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row ss:Index="4" ss:AutoFitHeight="0">
<ss:Cell ecProperty="synthesis_mode">
<ss:Data ss:Type="String">Ideal Mode</ss:Data>
</ss:Cell>
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>

Answer Source

Use XDocument instead of XElement to preserve the full document.

Dim root As XDocument = XDocument.Load(inFile)
'code to change elements goes here 
root.Save(outFile)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download