Convert Excel (xlsx) to XML with C# and BizTalk

Question

I have looked at most of the topics on this forum regarding similar questions but haven't found exactly what I am looking for.

I am trying to write a pipeline component for BizTalk 2013 R2 using C# to simply convert an incoming Excel 2010 .xlsx file to it's bare/base XML representation.

I do not want to run any templates against it or XLST transform it or anything like that. I simply just want to return the underlying XML representation of said spreadsheet as is.

It seems like this should be a very easy task but I can't figure out how to do it at all.

Everything I've found requires working with DataTables and looping through rows and cells (via OpenXML) to output a specific XML representation that is more human readable but that isn't what I want.

I want the actual Microsoft XML representation of that spreadsheet.

Any help would be greatly appreciated.

Answer 1

OK, figured it out without having to do any unzipping of the file.

If you use the SAX approach to loading the worksheet into an OpenXmlReader found here:

https://msdn.microsoft.com/en-us/library/office/gg575571(v=office.15).aspx

You can then use the reader to get the OuterXml like so:

using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filepath, false))
{
    WorkbookPart wbPart = spreadSheetDocument.WorkbookPart;

    OpenXmlReader reader = OpenXmlReader.Create(wbPart);

    while (reader.Read())
    {
        if (reader.ElementType == typeof(Sheet))
        {
            Sheet sheet = (Sheet)reader.LoadCurrentElement();

            WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));

            OpenXmlReader wsReader = OpenXmlReader.Create(wsPart);
            while (wsReader.Read())
            {
                if(wsReader.ElementType == typeof(Worksheet))
                {
                    Worksheet wsPartXml = (Worksheet)wsReader.LoadCurrentElement();
                    Console.WriteLine(wsPartXml.OuterXml + "\n");
                }
            }
        }
    }
    Console.ReadKey();
}