Are there any third-party tools which allow importing XML data into Excel?

Question

We are currently working on an application that has been using COM Interop in C# to import data into an Excel workbook. This data is fairly complex and lengthy, so we have been using the Import from XML feature in Excel. Because of the issues with running Excel on a server, we are now looking into moving to a 3rd-party solution for our workbook creation, such as SpreadsheetGear or Aspose.

SpreadsheetGear and Aspose do not support XML Import. Does anyone know of another 3rd-party, server-friendly tool that does?

--Edit-- The data is coming from an DB2 database that we are putting into C# classes and then serializing into XML. We are then importing the XML into around 30 tables and other cells. Then we copy and paste the tables to an output sheet and reuse the originals to import more.

--Edit-- The output is a form with multiple tables and fields on one sheet. Part of what the XML map helps with is keeping track of the locations of the cells, without having to massively hard-code cell-coordinates into the code.

I have just contacted them, but neither product supports this feature. — xdhmoore, Apr 15 '11 at 18:28

James Wiseman · Answer 1 · 2011-04-15T15:42:14.977

1

You could look at SSIS. This has the facility where you can set XML as a datasource and a spreadsheet as a destination. (Or vice-versa).

edited Apr 15 '11 at 15:42

answered Apr 15 '11 at 15:28

James Wiseman

29,946
17
95
158

That is probably overkill for what we are trying to do. We are getting the data from a DB2 database in the first place. – xdhmoore Apr 15 '11 at 15:45

score 1 · Answer 2 · answered Apr 15 '11 at 15:57

1

EPPlus is a pretty good library but i'm not sur you can import XML directly, but you can import csv file directly, there are example in the samples.

It would still be fairly easy to implement.

answered Apr 15 '11 at 15:57

moi_meme

9,180
4
44
63

Thanks, I had not seen EPPlus before. It looks like they are currently working on XML Importing for the next version. Unfortunately, the amount of data is fairly large and complex, so an XML map would be the easiest way to go if it is available. – xdhmoore Apr 15 '11 at 16:52

score 1 · Accepted Answer · answered Apr 20 '11 at 14:13

1

Although I have not found a 3rd party tool that allows this functionality, Aspose.Cells has a "Smart Marker" feature that allows you to designate specific cells in a way similar to an XML map. I believe this may be the solution I have been looking for.

answered Apr 20 '11 at 14:13

xdhmoore

8,935
11
47
90

FWIW Aspose.Cells now has XML Map support (at least for importing). It was introduced in Aspose.Cells for .Net 7.3.0. – Matthew Strawbridge Dec 17 '12 at 15:13

score 0 · Answer 4 · answered Sep 30 '11 at 14:35

Load the XML into a dataset then use EPPlus to take the datatable out of excel and create an excel file from it:

DataSet ds = new DataSet();
            DataTable dt = ds.Tables[0];
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Calls");

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(dt, true);

                Byte[] bin = pck.GetAsByteArray();

                string file = filePath;
                File.WriteAllBytes(file, bin);

            }

Are there any third-party tools which allow importing XML data into Excel?

4 Answers4