Of Stack

ASP. NET converts xml to Excel using xslt

  • 2021-07-22 09:35:13
  • OfStack

Preface:

Recently, when making a management system for the customer, the customer proposed to export the query results to Excel. I was stressed out as an intern, so I found some information. There is an example on the Internet, in which the method 10 points is simple. So I painted a tiger according to a cat and shared my methods and experiences with everyone. OK, Let ` s go

Step 1:

Create an Excel file (that is, ordinary Excel), enter "city" in the first cell, and then select "Save As", and the save window will pop up. Note: Select "XML Table (*. xml)" to keep the format, and click Save. When finished, open the Excel file with Notepad. You will see the following code 


<?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>MC SYSTEM</Author>
<LastAuthor>MC SYSTEM</LastAuthor>
<Created>2009-05-28T16:20:57Z</Created>
<Company>MC SYSTEM</Company>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11250</WindowHeight>
<WindowWidth>18180</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName=" Song Style " x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Row ss:>
<Cell><Data ss:Type="String">org</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

In fact, this is the most critical part of converting XML into Excel. In fact, this is the XML format corresponding to Excel. That is to say, write an XML file according to this format, and then open it with Excel. Excel will correctly realize this file as Excel.

Step 2:

Add an xslt file to the. net project. Friends who have studied xslt know that xml can be converted into other formats through xslt. Maybe some friends haven't understood what I mean. In fact, we convert xml into the format in "Step 1" through xslt, and then save or output it to the client to complete the function of exporting Excel.

We need to make a few changes to the XML code in Step 1, because it is automatically generated by Excel and contains a lot of useless information. The modified code is as follows: 


<?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="Sheet1">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
    <Row ss:>
      <Cell>

      <Data ss:Type="String">

        city <!-- Remember? This is the first 1 Step entered in the city -->

      </Data>

     </Cell>
    </Row>
  </Table>
 </Worksheet>
 </Workbook>

So far, this code has no practical use. We add this code copy to the created xslt file, and add some syntax of xslt to the above code. Finally, the xslt file will look like this: 


<?xml version="1.0"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
   　　 
  <?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="Sheet1">
    <Table>
    　　  <xsl:for-each select="*">
     　　  <Row>
     　　   <Cell>
        <Data ss:Type="String">
          <xsl:value-of select="."/>
        </Data>
       </Cell>
     　　   <Cell>
        <Data ss:Type="String">
           <xsl:value-of select="."/>
        </Data>
       </Cell>
      </Row>
     </xsl:for-each>
    </Table>

  </Worksheet>
  </Workbook>
 
 </xsl:template>
</xsl:stylesheet>

Save this xslt file.

Step 3:

To write. net background code, I triggered the export through a button event. The code is as follows: 


private void button1_Click(object sender, EventArgs e)
{
 XmlDocument xdoc = new XmlDocument();
 xdoc.LoadXml("<records><record><org> Beijing </org></record></records>");
 XslCompiledTransform xct = new XslCompiledTransform();
 xct.Load(Application.StartupPath+"\\excel.xslt");

 

 XmlTextWriter writer = new XmlTextWriter("output.xls", null);
 writer.WriteProcessingInstruction("xml", "version="1.0"");

 xct.Transform(xdoc, null,writer);
 writer.Close();

}

Summary:

In fact, the core of this method is to use the xslt conversion function of. net to output xml in a special format. This format can be automatically generated by Excel software

Related articles: