C # .xml to .xlsx how?

Question

I want to convert a complete XML file to XLSX, but I'm not sure how to do this. I searched on Google for solutions, but more often than not I only find a way in a different direction, for example XLSX in XML. On the Microsoft page, I found xmlconvertclass, but I'm not sure how I can work with the class.

Has someone done something similar in the past and can help me?

Answer 1

Try the code below, in which I converted the XML to a DataSet and later exported the DataSet to 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 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(); 

Answer 2

A simpler solution: use data sources in Excel.

• Create an Xslx "template" that suits your needs.
• For each xml data file, add a data connection to the XML file.
• Configure, if you want, a data connection for the update each time you open the file.

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

Optionally, you can publish your Xml through an ASP.Net application (dynamically or not) and configure a data connection to collect data from this asp.net application.

Answer 3

You will need to read the schema for the XLSX file format and write the XSLT file to convert your own XML file.

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

Answer 4

Please use the code below if you get multiple tables from a dataset and you have to take care of the logic of inserting data tables in 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 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(); }