C# .xml to .xlsx how?

Question

I want to convert a complete XML file to XLSX but I'm not sure how I can do it. I searched at Google for a Solutions but the most time I only find the way into the other direction for example XLSX to XML. On the Microsoft Page I found a xmlconvertclass, but I'm not sure how I can work with the class.

Did someone do something like this in the past and can help me?

How about the [XslCompiledTransform](http://msdn.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform.aspx) class? — rene, Jul 15 '13 at 08:37
this link may be helpful http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp — Vano Maisuradze, Jul 15 '13 at 08:56

score 5 · Accepted Answer · answered Jul 15 '13 at 09:11

5

Try the Below code in which i converted XML into DataSet and later exported DataSet into Excel

        DataSet ds = new DataSet();

        //Convert the XML into Dataset
        ds.ReadXml(@"E:\movie.xml");

        //Retrieve the table fron Dataset
        DataTable dt = ds.Tables[0];

        // Create an Excel object
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        //Create workbook object
        string str = @"E:\test.xlsx";
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(Filename: str);

        //Create worksheet object
        Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;

        // Column Headings
        int iColumn = 0;

        foreach (DataColumn c in dt.Columns)
        {
            iColumn++;
            excel.Cells[1, iColumn] = c.ColumnName;
        }

        // Row Data
        int iRow = worksheet.UsedRange.Rows.Count - 1;

        foreach (DataRow dr in dt.Rows)
        {
            iRow++;

            // Row's Cell Data
            iColumn = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iColumn++;
                excel.Cells[iRow + 1, iColumn] = dr[c.ColumnName];
            }
        }

        ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();

        //Save the workbook
        workbook.Save();

        //Close the Workbook
        workbook.Close();

        // Finally Quit the Application
        ((Microsoft.Office.Interop.Excel._Application)excel).Quit();

answered Jul 15 '13 at 09:11

Gun

1,400
1
10
16

wow, create class. And the class is working but i get only the first row and first column, why? – Fridolin Jul 15 '13 at 09:19
This should work for all rows. can you check the DataTable is filled properly or not by debugging? – Gun Jul 15 '13 at 09:35
Is filled with the first Row and Column, hms. But i have 30 Columns and 5 rows at the test XML. – Fridolin Jul 15 '13 at 09:43
Can you post your XML? – Gun Jul 15 '13 at 09:47
I dont know, how i can post data files here... :/? – Fridolin Jul 15 '13 at 09:54
Edit your question and append your XML format to your question – Gun Jul 15 '13 at 09:56
didnt found the append function here is the link http://www.dateiupload.net/download.php?file=0117454de4e3334066b60eeb27223f37 – Fridolin Jul 15 '13 at 11:21
you are getting more than 1 table from dataset so you are supposed to loop all data tables – Gun Jul 16 '13 at 06:25

Steve B · Answer 2 · 2013-07-15T08:59:51.837

2

A far more simpler solution: use data sources in Excel.

Create a "template" Xslx that matches your need.
For each xml data files, add a data connection to the xml file.
Set up, if you want, the data connection to refresh each time the file is opened.

This works out-of-the box, with no code at all (excluding of course the xml generation).

Optionally, you may publish your Xml through an ASP.Net application (dynamically or not), and set up the data connection to gather the data from this asp.net app.

edited Jul 15 '13 at 08:59

answered Jul 15 '13 at 08:53

Steve B

36,818
21
101
174

Easiest way for me was: Open it with Excel and save as .xlsx but the Problem is, sometimes i have more than 1 XML and have the repeat the These steps... i want a one click solution for this :// – Fridolin Jul 15 '13 at 08:56
Surround my steps with "for each xml source". This will still be valid. The template is generated **once**. Only the xml data is dynamic. – Steve B Jul 15 '13 at 08:58

score 1 · Answer 3 · answered Jul 15 '13 at 08:37

You will need to read the schema for the XLSX file format and write an XSLT file to transform your custom XML file.

.NET has very good XML support so something like this should be fairly trivial, it will be actual mapping from your XML format to XSLT where the real effort is needed.

Gun · Answer 4 · 2013-07-16T07:19:35.210

Please Use the below code if you are getting multiple tables from dataset and you should take care of the logic of inserting data tables into excel.

    static void Main(string[] args)
    {
        DataSet ds = new DataSet();

        //Convert the XML into Dataset
        ds.ReadXml(@"E:\movies.xml");

        //Retrieve the table fron Dataset
        //DataTable dt = ds.Tables[0];

        // Create an Excel object
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        //Create workbook object
        string str = @"E:\test.xlsx";
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(Filename: str);

        foreach (DataTable tab in ds.Tables)
        {
            FromDataTableToExcel(tab,excel,workbook);
        }

        //Save the workbook
        workbook.Save();

        //Close the Workbook
        workbook.Close();

        // Finally Quit the Application
        ((Microsoft.Office.Interop.Excel._Application)excel).Quit();

    }

    static void FromDataTableToExcel(DataTable dt, Microsoft.Office.Interop.Excel.Application excel, Microsoft.Office.Interop.Excel.Workbook workbook)
    { 
        //Create worksheet object
        Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;

        // Column Headings
        int iColumn = worksheet.UsedRange.Columns.Count-1;
        int iColumn1 = iColumn;
        int iColumn2 = iColumn;

        foreach (DataColumn c in dt.Columns)
        {
            iColumn++;
            excel.Cells[1, iColumn] = c.ColumnName;
        }

        // Row Data
        int iRow = 0;

        foreach (DataRow dr in dt.Rows)
        {
            iRow++;

            // Row's Cell Data                
            foreach (DataColumn c in dt.Columns)
            {
                iColumn1++;
                excel.Cells[iRow + 1, iColumn1] = dr[c.ColumnName];
            }

            iColumn1 = iColumn2;
        }

        ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();

    }

Hey thanks for this, it works better than the first one but i have one Problem, when Row 1 and 2 have same Content in a column it doesnt Show the Information in the next row/column... — Fridolin, Jul 17 '13 at 09:13

score 0 · Answer 5 · answered Mar 10 '23 at 10:32

In case someone needed to convert an XML (tabular data) file to XLSX without Microsoft Excel. Here below an NPOI + C# solution....

Sample XML (tabular data):

<rows>
    <row>
        <col_1>a</col_1>
        <col_2>b</col_2>
        <col_3>c</col_3>
        <col_4>d</col_4>
        <col_5>e</col_5>
    </row>
    <row>
        <col_1>f</col_1>
        <col_2>h</col_2>
        <col_3>h</col_3>
        <col_4>i</col_4>
        <col_5>j</col_5>
    </row>
    <row>
        <col_1>k</col_1>
        <col_2>l</col_2>
        <col_3>m</col_3>
        <col_4>n</col_4>
        <col_5>o</col_5>
    </row>
</rows>

C# code:

class clsNpoi
{
    public static bool convert_xml_to_xlsx(string xml_path, string row_element_name)
    {
        try
        {
            //load xml
            XElement elem = XElement.Load(xml_path);
            if (elem.XPathSelectElements(row_element_name).Count() == 0)
            {
                return false; // exit if no row elements are found
            }
            else
            {
                //process;
                string xpath_text = "//" + row_element_name;
                XSSFWorkbook wb = new XSSFWorkbook();
                XSSFSheet sh = (XSSFSheet)wb.CreateSheet(Path.GetFileNameWithoutExtension(xml_path));
                //get column heads;
                var heading_items = elem.XPathSelectElements(xpath_text).First().Elements().Select(e => e.Name.LocalName).Distinct().ToArray();
                //get row items;
                var row_elems = elem.XPathSelectElements("//" + row_element_name).ToArray();
                int row_index = 0, col_index = 0;
                foreach (XElement row_elem in row_elems)
                {
                    sh.CreateRow(row_index);
                    if (row_index == 0)
                    {
                        foreach (string heading_item in heading_items)
                        {
                            sh.GetRow(row_index).CreateCell(col_index);
                            sh.GetRow(row_index).GetCell(col_index).SetCellValue(heading_item);
                            sh.AutoSizeColumn(col_index);
                            col_index++;
                        }
                        row_index++; col_index = 0;
                        sh.CreateRow(row_index);
                    }
                    for (col_index = 0; col_index < heading_items.Count(); col_index++)
                    {
                        sh.GetRow(row_index).CreateCell(col_index);
                        //discard blank / null values -- set "-" as default !important step
                        var content_value = (string.IsNullOrEmpty(row_elem.Element(heading_items[col_index]).Value) || string.IsNullOrWhiteSpace(row_elem.Element(heading_items[col_index]).Value)) ? "-" : row_elem.Element(heading_items[col_index]).Value;
                        sh.GetRow(row_index).GetCell(col_index).SetCellValue(content_value);
                    }
                    row_index++; col_index = 0;
                }
                FileStream fs = new FileStream(Path.ChangeExtension(xml_path, ".xlsx"), FileMode.Create, FileAccess.Write);
                wb.Write(fs);
                fs.Close();
                wb.Clear();
                return true;
            }
            
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return false;
        }
    }

Call as clsNpoi.convert_xml_to_xlsx(<xml_path>, "row"); and the result...

For java usage visit Busy Developers' Guide to HSSF and XSSF Features

For c# usage visit nissl-lab / npoi

Hope this helps someone :)

C# .xml to .xlsx how?

5 Answers5

Linked