And I'd like to get this from the transformation (XSLT) result:
name : Shock
language : Ruby
owner : Brian May
state : New
Date : 31/10/2008 0:00:00
Does anyone know the XSLT to achieve this? I'm using .net in case that matters. I don't have knowledge on XSLT. Can you help me to prepare XSLT to excel.
Does anyone know the XSLT to achieve this? I'm using .net in case that matters. I don't have knowledge on XSLT. Can you help me to prepare XSLT to excel.
According to your description, I couldn't understand your requirement clearly.
Do you mean you want to change your xml file to excel like below format:
Or below format:
Besides, I suggest you could refer to follow link to know how to using xslt format your xml and show in the excel:
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Thanks for prompt reply. Second one is the Correct. If the Project is repeated once then we need to generate in another excel file. Not in another excel sheet.
Thanks for prompt reply. Second one is the Correct. If the Project is repeated once then we need to generate in another excel file. Not in another excel sheet.
As far as I know, xslt couldn't generate two excel file.
In my opinion, one xml file means one file.
We couldn't generate the two excel file.
I suggest you could use C# select the different nodes and load different xml.
Then you could use XslCompiledTransform.Transform method to format the xml by using xslt file.
XmlDocument xdoc = new XmlDocument();
xdoc.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\example.xml");
XmlNodeList nodes = xdoc.SelectNodes("/projects/project");
int i = 0;
foreach (XmlNode node in nodes)
{
i++;
XmlDocument a = new XmlDocument();
a.LoadXml("<?xml version='1.0' encoding='UTF-8'?>" + node.OuterXml);
//XmlDeclaration declaration =a.CreateXmlDeclaration("1.0", "UTF-8", null);
//a.AppendChild(declaration);
XslCompiledTransform xct = new XslCompiledTransform();
xct.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\exap.xslt");
XmlTextWriter writer = new XmlTextWriter(@"c:\users\vxxxx\documents\visual studio 2015\Projects\Error\Error\output" + i + ".xls", null);
writer.WriteProcessingInstruction("xml", "version='1.0'");
xct.Transform(a, null, writer);
writer.Close(); }
Result:
Best Regards,
Brando
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
2 Points
48 Posts
XML to excel (.xls) using XSLT
Nov 05, 2016 03:08 AM|meygnanam|LINK
I have the following XML document:
And I'd like to get this from the transformation (XSLT) result:
Does anyone know the XSLT to achieve this? I'm using .net in case that matters. I don't have knowledge on XSLT. Can you help me to prepare XSLT to excel.
Star
9831 Points
3120 Posts
Re: XML to excel (.xls) using XSLT
Nov 07, 2016 07:57 AM|Brando ZWZ|LINK
Hi meygnanam,
According to your description, I couldn't understand your requirement clearly.
Do you mean you want to change your xml file to excel like below format:
Or below format:
Besides, I suggest you could refer to follow link to know how to using xslt format your xml and show in the excel:
https://blogs.msdn.microsoft.com/brian_jones/2005/06/30/intro-to-excel-xml-part-2-displaying-your-data/
https://blogs.msdn.microsoft.com/brian_jones/2005/06/27/introduction-to-excel-xml-part-1-creating-a-simple-table/
Best Regards,
Brando
Member
2 Points
48 Posts
Re: XML to excel (.xls) using XSLT
Nov 08, 2016 09:55 AM|meygnanam|LINK
Hi,
Thanks for prompt reply. Second one is the Correct. If the Project is repeated once then we need to generate in another excel file. Not in another excel sheet.
Star
9831 Points
3120 Posts
Re: XML to excel (.xls) using XSLT
Nov 10, 2016 11:34 AM|Brando ZWZ|LINK
Hi meygnanam,
As far as I know, xslt couldn't generate two excel file.
In my opinion, one xml file means one file.
We couldn't generate the two excel file.
I suggest you could use C# select the different nodes and load different xml.
Then you could use XslCompiledTransform.Transform method to format the xml by using xslt file.
More details, you could refer to follow codes:
XML:
<?xml version="1.0"?> <projects> <project> <name>Shock</name> <Details> <language>Ruby</language> <owner>Brian May</owner> <state>New</state> <Date>31/10/2008 0:00:00</Date> </Details> </project> <project> <name>Shock</name> <Details> <language>Ruby</language> <owner>Brian May</owner> <state>New</state> <Date>31/10/2008 0:00:00</Date> </Details> </project> </projects>XSLT:
<?xml version="1.0" encoding="utf-8"?> <?mso-application progid="Excel.Sheet"?> <xsl:stylesheet version="1.0" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 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"> <xsl:template match="/"> <Workbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom" /> <Borders /> <Font /> <Interior /> <NumberFormat /> <Protection /> </Style> <Style ss:ID="s21"> <Font ss:Size="22" ss:Bold="1" /> </Style> <Style ss:ID="s22"> <Font ss:Size="14" ss:Bold="1" /> </Style> <Style ss:ID="s23"> <Font ss:Size="12" ss:Bold="1" /> </Style> <Style ss:ID="s24"> <Font ss:Size="10" ss:Bold="1" /> </Style> </Styles> <Worksheet ss:Name="Page1"> <Table> <xsl:call-template name="XMLToXSL" /> </Table> </Worksheet> </Workbook> </xsl:template> <xsl:template name="XMLToXSL"> <Row ss:Index="1" > <Cell> <Data ss:Type="String">name</Data> </Cell> <xsl:for-each select="//project"> <Cell ss:Index="2"> <Data ss:Type="String"> <xsl:value-of select="name" /> </Data> </Cell> </xsl:for-each> </Row> <Row ss:Index="2" > <Cell> <Data ss:Type="String">language</Data> </Cell> <xsl:for-each select="//project"> <Cell ss:Index="2"> <Data ss:Type="String"> <xsl:value-of select="Details/language" /> </Data> </Cell> </xsl:for-each> </Row> <Row ss:Index="3" > <Cell> <Data ss:Type="String">owner</Data> </Cell> <xsl:for-each select="//project"> <Cell ss:Index="2"> <Data ss:Type="String"> <xsl:value-of select="Details/owner" /> </Data> </Cell> </xsl:for-each> </Row> <Row ss:Index="4" > <Cell> <Data ss:Type="String">state</Data> </Cell> <xsl:for-each select="//project"> <Cell ss:Index="2"> <Data ss:Type="String"> <xsl:value-of select="Details/state" /> </Data> </Cell> </xsl:for-each> </Row> <Row ss:Index="5" > <Cell> <Data ss:Type="String">Date</Data> </Cell> <xsl:for-each select="//project"> <Cell ss:Index="2"> <Data ss:Type="String"> <xsl:value-of select="Details/Date" /> </Data> </Cell> </xsl:for-each> </Row> </xsl:template> <xsl:template match="DataList"> </xsl:template> </xsl:stylesheet>C# codes:
XmlDocument xdoc = new XmlDocument(); xdoc.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\example.xml"); XmlNodeList nodes = xdoc.SelectNodes("/projects/project"); int i = 0; foreach (XmlNode node in nodes) { i++; XmlDocument a = new XmlDocument(); a.LoadXml("<?xml version='1.0' encoding='UTF-8'?>" + node.OuterXml); //XmlDeclaration declaration =a.CreateXmlDeclaration("1.0", "UTF-8", null); //a.AppendChild(declaration); XslCompiledTransform xct = new XslCompiledTransform(); xct.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\exap.xslt"); XmlTextWriter writer = new XmlTextWriter(@"c:\users\vxxxx\documents\visual studio 2015\Projects\Error\Error\output" + i + ".xls", null); writer.WriteProcessingInstruction("xml", "version='1.0'"); xct.Transform(a, null, writer); writer.Close();}
Result:
Best Regards,
Brando