C# .xml to .xlsx how?
Solution 1
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();
Solution 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.
Solution 3
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.
Fridolin
Updated on July 01, 2022Comments
-
Fridolin almost 2 years
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?
-
rene almost 11 yearsHow about the XslCompiledTransform class?
-
Vano Maisuradze almost 11 yearsthis link may be helpful stackoverflow.com/questions/151005/…
-
Gun almost 11 yearsFridolin: Please verify the recent answer
-
-
Fridolin almost 11 yearsEasiest 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 ://
-
Steve B almost 11 yearsSurround my steps with "for each xml source". This will still be valid. The template is generated once. Only the xml data is dynamic.
-
Fridolin almost 11 yearswow, create class. And the class is working but i get only the first row and first column, why?
-
Gun almost 11 yearsThis should work for all rows. can you check the DataTable is filled properly or not by debugging?
-
Fridolin almost 11 yearsIs filled with the first Row and Column, hms. But i have 30 Columns and 5 rows at the test XML.
-
Gun almost 11 yearsCan you post your XML?
-
Fridolin almost 11 yearsI dont know, how i can post data files here... :/?
-
Gun almost 11 yearsEdit your question and append your XML format to your question
-
Fridolin almost 11 yearsdidnt found the append function here is the link dateiupload.net/…
-
Gun almost 11 yearsyou are getting more than 1 table from dataset so you are supposed to loop all data tables
-
Fridolin almost 11 yearsHey 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 over 10 yearsNo solution finally? :/