Import xml using XML-mapping in OpenXML

Question

I've been trying to import XML-data to Excel using an xml-mapping in OpenXML SDK.

I have found multiple examples of doing this using Excel Interop, but not using the SDK. As a side note, I have this working for Word-files, but the procedure is not the same in Excel (CustomXmlPart in Word, CustomXmlMappingPart in Excel).

Maybe it's just not possible using the SDK directly. A workaround might be using named ranges, but I find that less practical.

Edit: The scenario is using an Excel-file with an XML-mapping (created as per described https://support.office.com/en-us/article/Import-XML-data-6eca3906-d6c9-4f0d-b911-c736da817fa4?ui=en-US&rs=en-US&ad=US) as a "template" to fill with data. The could be Customer name, current date or likewise.

I have tried something similar to this. In contrast to Word, the mapping is shown in a CustomXmlMappingsPart.

using (var fileStream = File.Open(@"c:\temp\test.xslx", FileMode.Open))
{
   SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(fileStream, true);
   // Only one mapping
   // When doing this for word, there is a document.MainDocumentPart.CustomXmlParts
   var mapping = excelDoc.WorkbookPart.GetPartsOfType<CustomXmlMappingsPart>().FirstOrDefault();
   XNamespace mappingNS = "http://demoimport.org/xmlimport";
   var xmlData = new XElement(mappingNS + "ImportRoot",
                              new XElement(mappingNS + "Customer", "Test Customer Name"));

   XmlReader reader = xmlData.CreateReader();
   reader.MoveToContent();

   using (MemoryStream ms = new MemoryStream())
   {
      xmlData.Save(ms);
      ms.Position = 0;
      // Corrupts the file
      // Probably due to CustomXmlMappingsPart
      mapping.FeedData(ms);
   }   
}

Please can you show what you've tried and what didn't work and then ask a specific question. It's very difficult to understand precisely what you are trying to achieve. Thank you. — Dr Rob Lang
– Dr Rob Lang, Commented Mar 3, 2017 at 14:38

Adrian · Accepted Answer · 2017-03-07 22:36:57Z

Disclaimer - I have no idea if this is the way to accomplish this, but the following works for my limited use case

Instead of working directly with XML-mapping parts (CustomXmlMappingsPart), using using the Open XML Productivity Tool, I discovered that the mapped cells contain XPath for the mapping in a collection of SingleCellTablePart.

It is possible to traverse all SingleCellTablePart to find a matching element in XML file. I found a really useful extensions from https://simpleooxml.codeplex.com/ to write data to the document. It is perfectly possible to achieve only using the OpenXML SDK. But I found setting cell values using the SDK is not a trivial as one would think.

using (var fileStream = File.Open(@"c:\temp\test.xlsx", FileMode.Open))
{
    /* Simple xml-file, 
     * <General>
     *   <Customer>Demo Customer</Customer>
     * </General>
     */
    XDocument doc = XDocument.Load(@"c:\temp\demodata.xml");

    SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(fileStream, true);

    foreach (var workSheet in excelDoc.WorkbookPart.WorksheetParts)
    {
        // Note, multiple mappings could exist. For simplicity I expect only one
        var mappings = workSheet.GetPartsOfType<SingleCellTablePart>().FirstOrDefault();
        if (mappings == null)
            continue;

        foreach (SingleXmlCell cellMapping in mappings.SingleXmlCells)
        {
            if (cellMapping.XmlCellProperties != null &&
                cellMapping.XmlCellProperties.XmlProperties != null)
            {
                // cellMapping.XmlCellProperties.XmlProperties.XPath  = /General/Customer
                var matchingNode = doc.XPathSelectElement(cellMapping.XmlCellProperties.XmlProperties.XPath);
                if(matchingNode != null && !string.IsNullOrEmpty(matchingNode.Value))
                {
                    // If a maching node exist, set text value from XML-file
                    // SpreadsheetReader and WorksheetWriter from https://simpleooxml.codeplex.com/
                    string column = SpreadsheetReader.ColumnFromReference(cellMapping.CellReference.Value);
                    uint row = SpreadsheetReader.RowFromReference(cellMapping.CellReference.Value);
                    WorksheetWriter.PasteText(excelDoc, workSheet, column, row, matchingNode.Value);
                }
            }
        }
        WorksheetWriter.Save(excelDoc, workSheet);
    }
}

Collectives™ on Stack Overflow

Import xml using XML-mapping in OpenXML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related