C # XML to excel

Source: Internet
Author: User
Tags xsl xsl file

Cited
From Internet

 

 

Use ASP. net and a generic XSL file to convert any XML data into an Excel spreadsheet. this generic XSL can be used to present XML reports to users in Excel via their Web browser. the XML data actually gets converted to an Excel XML spreadsheet, and for that reason Excel 2002/2003 is required on the client. once the data has been converted it can be saved, on the client, as an Excel spreadsheet or opened in the client's Web browser.

This is an example of the XML file that is stored in the download. The XML file is required to be in this format to work with the XSL.

<? XML version = "1.0"?>
<Newdataset>
<Authors>
<Au_id> 172-32-1176 </au_id>
<Au_lname> White </au_lname>
<Au_fname> Johnson </au_fname>
<Phone> 408 496-7223 </phone>
<Address> 10932 bigge RD. </address>
<City> Menlo Park </city>
<State> Ca </State>
<Zip> 94025 </zip>
<Contract> true </contract>
</Authors>
<Authors>
<Au_id> 213-46-8915 </au_id>
<Au_lname> green </au_lname>
<Au_fname> Marjorie </au_fname>
<Phone> 415 986-7020 </phone>
<Address> 309 63rd st. #411 </address>
<City> Oakland </city>
<State> Ca </State>
<Zip> 94618 </zip>
<Contract> true </contract>
</Authors>
<Authors>
& Lt; au_id & gt; 238-95-7766 & lt;/au_id & gt;
<Au_lname> Carson </au_lname>
<Au_fname> Cheryl </au_fname>
<Phone> 415 548-7723 </phone>
<Address> 589 Darwin ln. </address>
<City> Berkeley </city>
<State> Ca </State>
<Zip> 94705 </zip>
<Contract> true </contract>
</Authors>
</Newdataset>

The XSL file gets the element local names and uses them for the header row in the Excel spreadsheet. this way you don't have to have the column names in the XSL. and you can resuse the XSL with any XML file that is in the proper format. this generic XSL will work if you have one column or your columns. in addition, the XSL gets the second level element local name and uses it for the worksheet name.

<XSL: stylesheetversion = "1.0"
Xmlns = "urn: Schemas-Microsoft-com: Office: spreadsheet"
Xmlns: XSL = "http://www.w3.org/1999/XSL/Transform"
Xmlns: msxsl = "urn: Schemas-Microsoft-com: XSLT"
Xmlns: User = "urn: My-scripts"
Xmlns: O = "urn: Schemas-Microsoft-com: Office: office"
Xmlns: x = "urn: Schemas-Microsoft-com: Office: excel"
Xmlns: Ss = "urn: Schemas-Microsoft-com: Office: spreadsheet">
 
<XSL: template match = "/">
<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">
<XSL: Apply-templates/>
</Workbook>
</XSL: Template>

<XSL: template match = "/*">
<Worksheet>
<XSL: attribute name = "SS: Name">
<XSL: value-of select = "Local-Name (/*)"/>
</XSL: attribute>
<Table X: fullcolumns = "1" X: fullrows = "1">
<Row>
<XSL: For-each select = "* [position () = 1]/*">
<Cell> <data SS: TYPE = "string">
<XSL: value-of select = "Local-Name ()"/>
</Data> </cell>
</XSL: For-each>
</Row>
<XSL: Apply-templates/>
</Table>
</Worksheet>
</XSL: Template>

<XSL: template match = "/*">
<Row>
<XSL: Apply-templates/>
</Row>
</XSL: Template>

<XSL: template match = "/*">
<Cell> <data SS: TYPE = "string">
<XSL: value-of select = "."/>
</Data> </cell>
</XSL: Template>

</XSL: stylesheet>

The first thing the ASP. NET page does is set the content type to MS excel. it then opens the XML file as an XML data document, transforms it with XSL, and sends it to the client's browser.

<% @ Page Language = "C #" %>
<% @ Import namespace = "system. Data" %>
<% @ Import namespace = "system. xml" %>
<% @ Import namespace = "system. xml. XSL" %>
<Script language = "C #" runat = "server">

Public void page_load (Object sender, eventargs e ){
Response. contenttype = "application/vnd. MS-excel ";
Response. charset = "";
Dataset DS = new dataset ();
DS. readxml (server. mappath ("authors. xml "));
Xmldatadocument xdd = new xmldatadocument (DS );
Transform transform XT = new transform ();
XT. Load (server. mappath ("Excel. XSL "));
XT. Transform (xdd, null, response. outputstream );
Response. End ();
}

</SCRIPT>

Hopefully, I have demonstrated how easy it is to convert XML to an Excel spreadsheet using XSL. but, there is more you can do with this same XSL file. for example, you can convert data from various database types to excel. just use a one-Table dataset in place of the XML file. one thing's for sure -- if your users want their reports in Excel, you're going to save a lot of time.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.