How to convert xml to excel file programmatically

Question

I have a xml document holding a small data for my project where I want to convert my xml to an excel file (microsoft office excel 2003 and over)

How can I do this programmatically?

Answer 1

It can be achieved by using Microsoft.Office.Interop.Excel as shown below:

First of all declare these necessary references.

using System;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using Microsoft.Win32;

Than create excel app as shown:

Excel.Application excel2; // Create Excel app
Excel.Workbook DataSource; // Create Workbook
Excel.Worksheet DataSheet; // Create Worksheet
excel2 = new Excel.Application(); // Start an Excel app
DataSource = (Excel.Workbook)excel2.Workbooks.Add(1); // Add a Workbook inside
string tempFolder = System.IO.Path.GetTempPath(); // Get folder 
string FileName = openFileDialog1.FileName.ToString(); // Get xml file name

After that use below code in a loop to ensure all items in xml file are copied

// Open that xml file with excel
DataSource = excel2.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Get items from xml file
DataSheet = DataSource.Worksheets.get_Item(1);
// Create another Excel app as object
Object xl_app;
xl_app = GetObj(1, "Excel.Application");
Excel.Application xlApp = (Excel.Application)xl_app;
// Set previous Excel app (Xml) as ReportPage
Excel.Application ReportPage = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
// Copy items from ReportPage(Xml) to current Excel object
Excel.Workbook Copy_To_Excel = ReportPage.ActiveWorkbook;

Now we have an Excel object named as Copy_To_Excel that contains all items in Xml.. We can save and close the Excel object with the name we want..

Copy_To_Excel.Save("thePath\theFileName");
Copy_To_Excel.Close();

Answer 2

If you have control over the XML generated, just generate an XML Spreadsheet file (the XML file standard for Excel 2002 and 2003).

These open natively in Excel, without having to change the extension. (To open by default in Excel, the file extension XML should be set to open with "XML Editor", which is an Office app that routes the XML file to Excel, Word, PowerPoint, InfoPath, or your external XML editor as needed. This is the default mapping when Office is installed, but it may be out of whack for some users, particularly devs who edit XML files in a text editor.)

Or, use the NPOI library to generate a native (97/2000 BIFF/XLS) Excel file rather than XML.

Answer 3

you can even read the XML file as string and use regular expressions to read the content between the tags and create a CSV file or use xpath expressions to read the XML file data and export to CSV file.

Answer 4

I know of no easy way to do code-based conversion from xml-based spreadsheet to xls/xlsx. But you might look into Microsoft Open Xml SDK which lets you work with xlsx. You might build xlsx spreadsheet and just feed it with your data. On the level of open-xml SDK it's like building xml file.

Answer 5

1.Fill the xml file into dataset,
2.convert dataset to excel by below method in asp.net

These are very simple methods.

  public static void Convert(System.Data.DataSet ds, System.Web.HttpResponse response)
    {
        //first let's clean up the response.object
        response.Clear();
        response.Charset = "";
        //set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel";
        //create a string writer
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();


        //create an htmltextwriter which uses the stringwriter
        System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
        //instantiate a datagrid
        System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
        //set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables[0];
        //bind the datagrid
        dg.DataBind();


        //tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite);
        //all that's left is to output the html
        response.Write(stringWrite.ToString());
        response.End();
    }

Answer 6

For array elements separate with ',' comma and reuse the same column name

1) XML Functions

public static class XMLFunctions
{       
    public static List<Tuple<string, string>> GetXMlTagsAndValues(string xml)
    {
        var xmlList = new List<Tuple<string, string>>();

        var doc = XDocument.Parse(xml);

        foreach (var element in doc.Descendants())
        {
            // we don't care about the parent tags
            if (element.Descendants().Count() > 0)
            {
                continue;
            }

            var path = element.AncestorsAndSelf().Select(e => e.Name.LocalName).Reverse();
            var xPath = string.Join("/", path); 

            xmlList.Add(Tuple.Create(xPath, element.Value));
        }

        return xmlList;
    }

    public static System.Data.DataTable CreateDataTableFromXmlFile(string xmlFilePath)
    {
        System.Data.DataTable Dt = new System.Data.DataTable();
        string input = File.ReadAllText(xmlFilePath);

        var xmlTagsAndValues = GetXMlTagsAndValues(input);
        var columnList = new List<string>();

        foreach(var xml in xmlTagsAndValues)
        {
            if(!columnList.Contains(xml.Item1))
            {
                columnList.Add(xml.Item1);
                Dt.Columns.Add(xml.Item1, typeof(string));
            }                    
        }

        DataRow dtrow = Dt.NewRow();
        var columnList2 = new Dictionary<string, string>(); 

        foreach (var xml in xmlTagsAndValues)
        {
            if (!columnList2.Keys.Contains(xml.Item1))
            {
                dtrow[xml.Item1] = xml.Item2;
                columnList2.Add(xml.Item1, xml.Item2);
            }
            else
            {   // Here we are using the same column but appending the next value
                dtrow[xml.Item1] = columnList2[xml.Item1] + "," + xml.Item2;

                columnList2[xml.Item1] = columnList2[xml.Item1] + "," + xml.Item2;
            }

        }

        Dt.Rows.Add(dtrow);

        return Dt;
    }
}

2) Full Excel class

using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

public class Excel 
{
    string path = "";
    Application excel = new _Excel.Application();
    Workbook wb;
    Worksheet ws;
    public Range xlRange;

    static bool saveChanges = false;
    static int excelRow = 0;
    static List<string> columnHeaders = new List<string>();    

    public Excel(string path, int Sheet = 1)
    {
        this.path = path;
        wb = excel.Workbooks.Open(path);
        ws = wb.Worksheets[Sheet];
        xlRange = ws.UsedRange;
        excelRow = 0;
        columnHeaders = new List<string>();
    }

    public void SaveFile(bool save = true)
    {
        saveChanges = save;
    }

    public void Close()
    {
        wb.Close(saveChanges);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
        excel.Quit();
    }                      

    public void XMLToExcel(string xmlFilePath)
    {
        var dt = XMLFunctions.CreateDataTableFromXmlFile(xmlFilePath);
        AddDataTableToExcel(dt);
    }

    public void AddDataTableToExcel(System.Data.DataTable table)
    {       
        // Creating Header Column In Excel
        for (int i = 0; i < table.Columns.Count; i++)   
        {
            if (!columnHeaders.Contains(table.Columns[i].ColumnName))
            {
                ws.Cells[1, columnHeaders.Count() + 1] = table.Columns[i].ColumnName;
                columnHeaders.Add(table.Columns[i].ColumnName);
            }
        } 

        // Get the rows
        for (int k = 0; k < table.Columns.Count; k++)
        {
            var columnNumber = columnHeaders.FindIndex(x => x.Equals(table.Columns[k].ColumnName));

            ws.Cells[excelRow + 2, columnNumber + 1] = table.Rows[0].ItemArray[k].ToString();
        }

        excelRow++; 
        SaveFile(true);         
    }
}

3) Call it

var excel = new Excel(excelFilename);

foreach (var filePath in files)
{   
    excel.XMLToExcel(filePath); 
}

excel.Close();

For array elements having appended incremented column names (ex. column_2)

Create Data Table From XmlFile Redone

public static System.Data.DataTable CreateDataTableFromXmlFile(string xmlFilePath)
{
    System.Data.DataTable Dt = new System.Data.DataTable();

    string input = File.ReadAllText(xmlFilePath);

    var xmlTagsAndValues = GetXMlTagsAndValues(input);
    var columnList = new List<string>();

    foreach (var xml in xmlTagsAndValues)
    {
        if (!columnList.Contains(xml.Item1))
        {
            columnList.Add(xml.Item1);
            Dt.Columns.Add(xml.Item1, typeof(string));
        }                   
        else 
        {
            var columnName = xml.Item1;

            do
            {
                columnName = columnName.Increment();
            } while (columnList.Contains(columnName));

            columnList.Add(columnName);
            Dt.Columns.Add(columnName, typeof(string));

        }
    }

    DataRow dtrow = Dt.NewRow();
    var columnList2 = new Dictionary<string, string>();  

    foreach (var xml in xmlTagsAndValues)
    {
        if (!columnList2.Keys.Contains(xml.Item1))
        {
            dtrow[xml.Item1] = xml.Item2;
            columnList2.Add(xml.Item1, xml.Item2);
        }
        else
        {
            var columnName = xml.Item1;

            do
            {
                columnName = columnName.Increment();
            } while (columnList2.Keys.Contains(columnName));

            dtrow[columnName] = xml.Item2;
            columnList2[columnName] = xml.Item2;
        }           
    }

    Dt.Rows.Add(dtrow);

    return Dt;
}

String Extensions

public static class StringExtensions
{
    public static string Increment(this string str)
    {
        if (!str.Contains("_"))
        {
            str += "_2";
            return str;
        }

        var number = int.Parse(str.Substring(str.LastIndexOf('_') + 1));            

        var stringBefore = StringFunctions.GetUntilOrEmpty(str, "_");            

        return $"{stringBefore}_{++number}";
    }
}

Using GetUntilOrEmpty

Answer 7

How to open an XML file in Excel 2003.

In short, you can simply open it by using File › Open. Then, when you select an XML file, you are prompted to select one of the following methods to import the XML data:

• As an XML list
• As a read-only workbook
• Use the XML Source task pane

Answer 8

Can't you simply open it in Excel? I thought Excel recognized the suffix XML?