Saving Spreadsheet Data to Simple XML

You can save the data from a 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 below. The top level (or root) element is the sheet (SheetName) and within the sheet element are row elements (Row1, Row2, and so on) and within each row are the column elements (Column1, Column2, and so on) 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.

The SaveXml method only saves the data for an individual sheet, not for an entire hierarchy that consists of several sheets.

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

Using Code

Use the SaveXml method to save data to an XML file.

Example

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

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>