This project has moved. For the latest updates, please go here.

can I import xml and create xlsx?

Jul 11, 2014 at 7:41 PM
Edited Jul 11, 2014 at 7:41 PM
can I import xml and create xlsx using ClosedXML and viceaversa?
Coordinator
Jul 11, 2014 at 7:50 PM
There's no way to guess how you want an xml file to be loaded into a workbook so no, you have to parse the xml and dump the data where you want it to appear.
Jul 11, 2014 at 8:04 PM
Thanks!
Can I use closedxml to import comma delimited text file and import into Excel and vice a versa?

I loved closedXML so far. So much easier to use than open xml.
Coordinator
Jul 11, 2014 at 8:14 PM
Again no for similar reasons. ClosedXML doesn't have access to disk so it would effectively be a "load string" function. That is, you would have to load the entire file into memory and then give it to a function. Then ClosedXML would then have to deal with field and string delimiters, encoding, and what nots.
Jul 11, 2014 at 8:17 PM
Thanks!
Jul 14, 2014 at 3:23 PM
I suggest you define a class that represents a row in the file.
Let's say you call that class MyExcelRow.
For each row define a property to store each column value where the name of the property is the name of the column.
Make the order of the properties the same as the order you want the columns to be in the Excel file.

Use an XML reader to read the XML file and create a MyExcelRow object for each row, and add them to a generic List<MyExcelRow> collection.
Once you have read in the entire file, all you have to do is call InsertTable() with your list and voila!
ClosedXML will fill the worksheet with your data automatically.
It will also automatically add the column headings

See this the documentation here https://closedxml.codeplex.com/wikipage?title=Inserting%20Tables

Here is some sample code that assumes that GetXMLData returns a list of MyExcelRow objects that were read from the XML file.
// Read XML Data
List<MyExcelRow> myData = GetXMLData();
// Create an Excel Workbook
XLWorkbook workbook = new XLWorkbook();
// Add the worksheet
IXLWorksheet sheet = workbook.Worksheets.Add("My Data");
// Add the XML data to the Excel sheet beginning in cell A1
sheet.Cell(1, 1).InsertTable(myData);
You can then save the Excel file to disk, or return it as a memory stream to your browser.
That's all there is to it.