Asp. NET uses XSLT to transform XML into excel_ practical tips

Order:

Recently in the customer to do a management system, the customer proposed to export the results of the query to Excel. I felt pressured to be an intern, so I found some information. There is an example on the Web, where the method is simple. So suit, the method and experience to share with you. Ok,let ' s Go

First step:

Create an Excel file (just plain Excel), enter "City" in the first cell, and choose Save As to eject the Save window. Note: Select the retention format as an "XML table (*.xml)" and click Save. When you are finished, open the Excel file with Notepad. You will see the following code

<?xml version= "1.0"?> <?mso-application progid= "Excel.Sheet"?> <workbook "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&GT;MC system</author> &LT;LASTAUTHOR&GT;MC system</lastauthor> <Created>2009-05-28T16 :20:57z</created> &LT;COMPANY&GT;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 Body" x:charset= "134" ss:size= "a"/> <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= "si" 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= "si" ss:defaultrowheight= "14.25"/> <worksheetoptions xmlns= "Urn:schemas-microsoft-com:office:excel" > <Unsynced/> <protectobjects>false</ protectobjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </ Worksheet> </Workbook>

This is actually the most important part of translating XML into Excel. In fact, this is the XML format corresponding to Excel. That is, you can write an XML file in this format and then open it in Excel, and Excel will make the file look right in Excel.

Step Two:

Add an XSLT file to a. NET project. Friends who have studied XSLT know that you can transform XML into other formats through XSLT. Maybe some friends haven't understood what I mean. In fact, we use XSLT to convert XML into the "first step" format, and then save or output to the client to complete the export of Excel functionality.

For the first step of the XML code we have to make some changes, because this 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
 "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= "si" ss :D efaultrowheight= "14.25" >
    <row ss:>
      <Cell>

      <data ss:type= "String" >

        City <!--remember? This is the first step entered in the city-->

      </Data>

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

So far, this code has no practical use. We copy this code into the XSLT file we created and add some of the XSLT syntax to the code above, and the final 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-mic" Rosoft-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=" * "&G
    　　 T <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 Three:

Writing. 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 ();

}

Summarize:

In fact, the core of this approach is to use the. NET XSLT transformation feature to export XML in a special format. This format can be automatically generated using Excel software