ASP. NET converts xml into Excel and ttxml using xslt

ASP. NET converts xml into Excel and ttxml using xslt

Order:

Recently, when creating a management system for the customer, the customer proposed to export the query results to Excel. I was under pressure to work as an intern, so I found some information. There is an example on the Internet, in which the method is very simple. As a result, I share my methods and experiences with you. OK, Let's go

Step 1:

Create an Excel file (an ordinary Excel file), enter "city" in the first cell, and select "Save as". The save window is displayed. Note: select "XML table (*. xml)" as the persistence format and click Save. Open the Excel file in 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" 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 "> <shortwheight> 11250 </shortwheight> <shortwwidth> 18180 </shortwwidth> <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 = "" 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: 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 important part of converting XML into Excel. In fact, this is the XML format corresponding to Excel. That is to say, write an XML file in this format and open it in Excel. Excel will display the file as Excel.

Step 2:

Add an xslt file to the. net project. Anyone who has learned xslt knows that xslt can convert xml into other formats. Some may not understand what I mean. In fact, we use xslt to convert xml into the "First Step" format, and then save or output it to the client to complete the Excel export function.

We need to make some modifications to the XML code in step 1, because it is automatically generated by Excel, which 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" 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: DefaultColumn Width = "54" ss: DefaultRowHeight = "14.25"> <Row ss:> <Cell> <Data ss: Type = "String"> city <! -- Remember? This is the city --> </Data> </Cell> </Row> </Table> </Worksheet> </Workbook>

So far, this code has no practical use. We copy this code to the created xslt file, and add some xslt syntaxes 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 the xslt file.

Step 3:

Compile the. net background code. I used a button event to trigger export. The Code is as follows:

Private void button#click (object sender, EventArgs e) {XmlDocument xdoc = new XmlDocument (); xdoc. loadXml ("<records> <record> <org> Beijing </org> </record> </records>"); compiledtransform xct = new compiledtransform (); 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. net xslt Conversion Function to output xml in a special format. This format can be automatically generated using Excel software.