locked
Creating Word from XML XSLT RRS feed

  • Question

  • User-1100884601 posted

    Hi,

    I am writing a program to generate word file on runtime.  Data i am  getting from XML and for Style i am using XSLT.  I wanted to create a BAR Chart on run-time and that chart should show on Word document  which is dynamically creating. 

    If anyone have similar type of code  please share. 

    Thanks

    Udal

    Friday, May 24, 2019 3:00 PM

All replies

  • User-893317190 posted

    Hi udal_cse,

    Not sure about the structure of your xml.

    Assume my xml is

    <?xml version="1.0" encoding="utf-8" ?>
    <countries>
      <country>
        <name>Cuba</name>
        <Jun>3300</Jun>
        <Jul>7500</Jul>
        <Aug>7700</Aug>
        <Sep>8000</Sep>
      </country>
        
                   
      <country>
        <name>Mexico</name>
           <Jun>2300</Jun>
        <Jul>2900</Jul>
        <Aug>6900</Aug>
        <Sep>7200</Sep>
      </country>
      <country>
        <name>France</name>
        <Jun>4300</Jun>
        <Jul>2300</Jul>
        <Aug>8400</Aug>
        <Sep>8200</Sep>
      </country>
        <country>
        <name>German</name>
                    <Jun>6700</Jun>
          <Jul>4200</Jul>
          <Aug>4200</Aug>
          <Sep>5600</Sep>
      </country>
    </countries>
    

    The I could use xslt to convert the xml.

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html" doctype-system="about:legacy-compat" encoding="UTF-8" indent="yes" />
    <xsl:template match="/"><table>
               
                     
                            <xsl:for-each select="countries/country">
                        
                              <row>
                                              
                                  <name>
                      <xsl:value-of select="name"/>
                    </name>
                                  <Jun>
                      <xsl:value-of select="Jun"/>
                    </Jun>
                         
                         
                               <Jul>
                      <xsl:value-of select="Jul"/>
                    </Jul>
                              
                              
                               <Aug>
                      <xsl:value-of select="Aug"/>
                    </Aug>
      
    
                               <Sep>
                      <xsl:value-of select="Sep"/>
                    </Sep>
                  </row>
                             
                </xsl:for-each>
                         
    </table></xsl:template>
    
    </xsl:stylesheet>
    
    

    The result of converted xml.

    <table>
     
                              <row>
                        
                                  <name>Cuba</name>
                                  <Jun>3300</Jun>
                         
                         
                               <Jul>7500</Jul><Aug>7700</Aug><Sep>8000</Sep></row>
           
                              <row>
          
                                  <name>Mexico</name>
                                  <Jun>2300</Jun>
                         
                         
                               <Jul>2900</Jul><Aug>6900</Aug><Sep>7200</Sep></row>
                             
                
                        
                              <row>
                                 
                  
                  
                                  <name>France</name>
                                  <Jun>4300</Jun>
                         
                         
                               <Jul>2300</Jul><Aug>8400</Aug><Sep>8200</Sep></row>
    
                              <row>
                                 
                  
                  
                                  <name>German</name>
                                  <Jun>6700</Jun>
                         
                         
                               <Jul>4200</Jul><Aug>4200</Aug><Sep>5600</Sep></row>
                             
                
                         
    </table>

    To convert xml using xslt using c#, you could , originXml is your originXml's path, xslt is your xslt's path , result is the path where you want to save converted xml.

       public void parse(string originXml, string xslt, string result)
            {
                XsltSettings settings = new XsltSettings(true, true);
    
                XPathDocument xPath = new XPathDocument(originXml);
    
                XslCompiledTransform xslCompiledTransform = new XslCompiledTransform();
    
                xslCompiledTransform.Load(xslt, settings, new XmlUrlResolver());
                using (XmlTextWriter writer = new XmlTextWriter(result, null))
                {
                    xslCompiledTransform.Transform(xPath, null, writer);
                } 
    
                
                
            }

    To  convert xml to chart in  word.

    You could use  Spire.Office or FreeSpire.Office.

    First convert xml to DataTable.

     private DataTable LoadData()
            {
                DataSet dataSet = new DataSet();
                dataSet.ReadXml(Server.MapPath("/xmldemo/xml/result.xml"));
                return dataSet.Tables[0];
            }

    Write the datatable to worksheet.

      private void CreateChartData(Worksheet sheet)
            {
               DataTable table =  LoadData();
    
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    sheet[1, j + 1].Value = table.Columns[j].ColumnName;
                }
    
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        sheet[i + 2, j + 1].Value = table.Rows[i][j].ToString();
                    }
                }
                
    
               sheet.Range["B2:D5"].Style.NumberFormat = "\"$\"#,##0";
            }
    

    Then create chart according to the worksheet.

     private void CreatePieChart(Worksheet sheet)
    
            {
               
                sheet.Name = "Chart data";
                sheet.GridLinesVisible = false;
    
                //Writes chart data
                CreateChartData(sheet);
                //Add a new  chart worsheet to workbook
                Spire.Xls.Chart chart = sheet.Charts.Add();
             
                    chart.ChartType = ExcelChartType.BarStacked;
               
    
                //Set region of chart data
                chart.DataRange = sheet.Range["A1:E5"];
    
                //Set position of chart
                chart.LeftColumn = 1;
                chart.TopRow = 6;
                chart.RightColumn = 11;
                chart.BottomRow = 29;
    
    
                //Chart title
                chart.ChartTitle = "Sales market by country";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 12;
    
                chart.PrimaryCategoryAxis.Title = "Month";
                chart.PrimaryCategoryAxis.Font.IsBold = true;
                chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
    
                chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
                chart.PrimaryValueAxis.HasMajorGridLines = false;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
                chart.PrimaryValueAxis.MinValue = 1000;
                chart.PrimaryValueAxis.TitleArea.IsBold = true;
    
                foreach (var cs in chart.Series)
                {
                    cs.Format.Options.IsVaryColor = true;
                    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
    
                  
                       
                }
    
                chart.PlotArea.Fill.Visible = false;
    
                chart.Legend.Position = LegendPositionType.Top;
              
              
            }

    About how to create worksheet, you could

      public System.Drawing.Image ChartToImage()
            {
                
                Workbook workbook = new Workbook();
                workbook.CreateEmptySheets(1);
                Worksheet sheet = workbook.Worksheets[0];
              
                CreatePieChart(sheet);
                System.Drawing.Image image = workbook.SaveChartAsImage(sheet, 0);
                return image;
            }

    Finally  addChartToWord.

    public void AddCharttoWord()
            {
                Document doc = new Document();
                Section section = doc.AddSection();
                Paragraph paragraph = section.AddParagraph();
                System.Drawing.Image image = ChartToImage();
                paragraph.AppendPicture(image);
                doc.SaveToFile(Server.MapPath("Result.docx"), Spire.Doc.FileFormat.Docx);
            }
    

    Below is whole code.

      protected void Page_Load(object sender, EventArgs e)
            {
                parse(Server.MapPath("/xmldemo/xml/message.xml"), Server.MapPath("/xmldemo/xslt/message.xslt"), Server.MapPath("/xmldemo/xml/result.xml"));
               // File.WriteAllText(Server.MapPath("/xmldemo/xml/result.xml"), File.ReadAllText( Server.MapPath("/xmldemo/xml/result.xml")));
                AddCharttoWord();
    
            }
    
            private DataTable LoadData()
            {
                DataSet dataSet = new DataSet();
                dataSet.ReadXml(Server.MapPath("/xmldemo/xml/result.xml"));
                return dataSet.Tables[0];
            }
    
    
            public System.Drawing.Image ChartToImage()
            {
                
                Workbook workbook = new Workbook();
                workbook.CreateEmptySheets(1);
                Worksheet sheet = workbook.Worksheets[0];
              
                CreatePieChart(sheet);
                System.Drawing.Image image = workbook.SaveChartAsImage(sheet, 0);
                return image;
            }
            private void CreatePieChart(Worksheet sheet)
    
            {
               
                sheet.Name = "Chart data";
                sheet.GridLinesVisible = false;
    
                //Writes chart data
                CreateChartData(sheet);
                //Add a new  chart worsheet to workbook
                Spire.Xls.Chart chart = sheet.Charts.Add();
             
                    chart.ChartType = ExcelChartType.BarStacked;
               
    
                //Set region of chart data
                chart.DataRange = sheet.Range["A1:E5"];
    
                //Set position of chart
                chart.LeftColumn = 1;
                chart.TopRow = 6;
                chart.RightColumn = 11;
                chart.BottomRow = 29;
    
    
                //Chart title
                chart.ChartTitle = "Sales market by country";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 12;
    
                chart.PrimaryCategoryAxis.Title = "Month";
                chart.PrimaryCategoryAxis.Font.IsBold = true;
                chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
    
                chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
                chart.PrimaryValueAxis.HasMajorGridLines = false;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
                chart.PrimaryValueAxis.MinValue = 1000;
                chart.PrimaryValueAxis.TitleArea.IsBold = true;
    
                foreach (var cs in chart.Series)
                {
                    cs.Format.Options.IsVaryColor = true;
                    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
    
                  
                       
                }
    
                chart.PlotArea.Fill.Visible = false;
    
                chart.Legend.Position = LegendPositionType.Top;
              
              
            }
            public void parse(string originXml, string xslt, string result)
            {
                XsltSettings settings = new XsltSettings(true, true);
    
                XPathDocument xPath = new XPathDocument(originXml);
    
                XslCompiledTransform xslCompiledTransform = new XslCompiledTransform();
    
                xslCompiledTransform.Load(xslt, settings, new XmlUrlResolver());
                using (XmlTextWriter writer = new XmlTextWriter(result, null))
                {
                    xslCompiledTransform.Transform(xPath, null, writer);
                } 
    
                
                
            }
            public void AddCharttoWord()
            {
                Document doc = new Document();
                Section section = doc.AddSection();
                Paragraph paragraph = section.AddParagraph();
                System.Drawing.Image image = ChartToImage();
                paragraph.AppendPicture(image);
                doc.SaveToFile(Server.MapPath("Result.docx"), Spire.Doc.FileFormat.Docx);
            }
    
            private void CreateChartData(Worksheet sheet)
            {
               DataTable table =  LoadData();
    
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    sheet[1, j + 1].Value = table.Columns[j].ColumnName;
                }
    
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        sheet[i + 2, j + 1].Value = table.Rows[i][j].ToString();
                    }
                }
                
    
               sheet.Range["B2:D5"].Style.NumberFormat = "\"$\"#,##0";
            }
    
    
        }

    The result.

    For more information about spire, you could refer to https://www.e-iceblue.com/Introduce/excel-for-net-introduce/Demo.html#.XOuLmIgzaUk

    Also refer to http://www.dotnetfunda.com/articles/show/3486/generate-excel-chart-from-datatable-and-add-the-chart-to-word-document

    Best regards,

    Ackerly Xu

    Monday, May 27, 2019 7:03 AM