c# - How to convert XML file to xls using DocumentFormat.OpenXml in asp.net?

Question

How to convert XML file to xls using DocumentFormat.OpenXml in asp.net?

• open the xml file from fileupload and then convert to.xls.
• Convert very fast even though huge xml file.
• No need to use Interop.Excel.Don't provide links, kindly explain with c# code.

Answer 1

What you ask it's a sort of tutorial on using OpenXMLSdk and it's a bit out of topic i think ;-).

Anyway this example creates a xlsx file from scratch. Don't forget to download Xml sdk productivity tool.

Consider this xml but should work even with larger and more complex:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="TestTable" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="TestTable">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="pk" type="xs:long" minOccurs="0" />
                <xs:element name="Name" type="xs:string" minOccurs="0" />
                <xs:element name="Surname" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <TestTable>
    <pk>1</pk>
    <Name>Name1</Name>
    <Surname>Surname1</Surname>
  </TestTable>
  <TestTable>
    <pk>2</pk>
    <Name>Name2</Name>
    <Surname>Surname2</Surname>
  </TestTable>
  <TestTable>
    <pk>3</pk>
    <Name>Name3</Name>
    <Surname>Surname3</Surname>
  </TestTable>
  <TestTable>
    <pk>4</pk>
    <Name>Name4</Name>
    <Surname>Surname4</Surname>
  </TestTable>
  <TestTable>
    <pk>5</pk>
    <Name>Name5</Name>
    <Surname>Surname5</Surname>
  </TestTable>
  <TestTable>
    <pk>6</pk>
    <Name>Name6</Name>
    <Surname>Surname6</Surname>
  </TestTable>
</NewDataSet> 

Now you just need to call this:

        DataTable dt = new DataTable("TestTable");
        dt.ReadXml("c:\test.xml");

        ExcelNameSpace.ExcelFile xlsxFile = new ExcelNameSpace.ExcelFile(dt);
        xlsxFile.CreatePackage("c:\myFile.xlsx");

The code in the class ExcelNameSpace.ExcelFile at 90% it's reflected by productivity tool in a couple of seconds. You should be able to debug step by step and understand what are your needs. It's about 1000 lines so i can't post here. (http://www.devnmore.com/share/ExcelFromScratch.txt)

Hope it helps.

Francesco