Saving Spreadsheet Data to Simple XML

You can save the data in the spreadsheet (on an individual sheet) to an XML file or stream if you need to process the data further and want the data in a structured format. This does not save the entire spreadsheet nor does it save the formatting information or presentation-related settings. Only the values in the cells are saved to XML. THe header cells are not saved out to XML; only the data area cells.

To save the data to XML, use the SaveXml methods of the SheetView class.

You can save the data to one file (or stream) and the XML schema to another file (or stream). An example output is shown here. The top level (or root) element is the sheet (SheetName) and within the sheet element are row elements (Row1, Row2, etc.) and within each row are the column elements (Column1, Column2, etc.) and within each column element is the data. The sheet element uses the name of the sheet. The rows are all generic rows and are not given unique names. The columns are each given a unique name and columns without data are ignored.

This method only saves the data for an individual sheet, not on an entire hierarchy which consists of several sheets.

This method only saves the data. For cell type data, see Understanding How Cell Types Display Data.

This example code saves the data for a sheet named "EastCoastSales" to an XML file and the schema to another file.

Example

C#	Copy Code
fpSpread1.ActiveSheet.SaveXml("C:\\testfiles\\FPSpread-SheetToXML2.xml", "C:\\testfiles\\FPSpread-SchemaForXML2.xml");

VB	Copy Code
FpSpread1.ActiveSheet.SaveXml("C:\testfiles\FPSpread-SheetToXML2.xml", "C:\testfiles\FPSpread-SchemaForXML2.xml")

This gives the following result:

                  
<EastCoastSales>

   <Row>

      <Column1>Aerosmith</Column1>

      <Column2>http://www.aerosmith.com/detect.html</Column2>

      <Column3>0</Column3>

   </Row>

   <Row>

      <Column1>Foreigner</Column1>

      <Column2>http://www.foreigneronline.com/</Column2>

      <Column3>1</Column3>

   </Row>

   .

   .

   .

   <Row>

      <Column1>The Who</Column1>

      <Column2>http://www.thewho.net/</Column2>

      <Column3>4</Column3>

   </Row>

</EastCoastSales>

The corresponding schema would be something like this:

                  
<?xml version="1.0" encoding="utf-16"?><xs:schema
id="Sheet1" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element
name="EastCoastSales" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">  <xs:complexType>   <xs:choice
minOccurs="0" maxOccurs="unbounded">    <xs:element name="Row">      <xs:complexType>      <xs:sequence>        <xs:element
name="Column1" type="xs:string" minOccurs="0" />        <xs:element
name="Column2" type="xs:string" minOccurs="0" />        <xs:element
name="Column3" type="xs:string" minOccurs="0" />      </xs:sequence>     </xs:complexType>    </xs:element>   </xs:choice>  </xs:complexType> </xs:element></xs:schema>