none
How to get data from xml mapped parts? RRS feed

  • Question

  • Hi there

       Imagine that you've got a spreadsheet which has got xml mapping, is there any simple way to programaticaly retrieve data from mapped cells? Unfortunately I can't do this using Excel automation because it is going to be a ASP.NET server job (Microsoft seriously warns not to use office automation on server side http://support.microsoft.com/kb/257757).

    Thanks for help!

    Thursday, July 8, 2010 7:02 AM

Answers

  • Hi Bartlomiej,

    As such I couldn't find any readymade sample but here are the common steps you can try to achieve this:

    1. Open workbook and get the XML map information. If you unzip a spreadsheet document which has XML maps, you will see a file xmlmaps.xml which contains information about them.

    Here is typical xml:

    ==========
    <Map ID="1" Name="FutureContract_Map" RootElement="FutureContract" SchemaID="Schema1" ShowImportExportValidationErrors="false" AutoFit="true" Append="false" PreserveSortAFLayout="true" PreserveFormat="true" />

      <Map ID="2" Name="FutureContract_Map1" RootElement="FutureContract" SchemaID="Schema2" ShowImportExportValidationErrors="false" AutoFit="true" Append="false" PreserveSortAFLayout="true" PreserveFormat="true" />

      </MapInfo>
    ==========

    Here is the sample code to read this:

    // Open the document for editing.

    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(@"mapbook.xlsx", true))

    {

    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1");

    if (sheets.Count() == 0){

    // The specified worksheet does not exist.

    return;}

    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets.First().Id);

    Worksheet worksheet = worksheetPart.Worksheet;

    CustomXmlMappingsPart custMapp = spreadSheet.WorkbookPart.CustomXmlMappingsPart;

    var mapCol = custMapp.MapInfo.OfType<Map>();

    2. Once you have the Map information (specially map id), you can get the cell information which are mapped to these maps. Basically if you drag the complete XML map to the sheet, it becomes a table or if you drag a node to a cell, it becomes SingleCellTable. Let’s assume you have 2 maps attached to a workbook. If you drag a complete map to worksheet, you will notice that it has created a table part and if you simply drag a node from the second map, it will create the SingleCellTable part. Here is XML you will see in the spreadsheet package :

     

    ==============
    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

    - <table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="5" name="Table5" displayName="Table5" ref="Q7:Y8" tableType="xml" insertRow="1" totalsRowShown="0">

      <autoFilter ref="Q7:Y8" />

    - <tableColumns count="9">

    - <tableColumn id="1" uniqueName="Symbol" name="Symbol">

      <xmlColumnPr mapId="2" xpath="/FutureContract/Meta/Symbol" xmlDataType="string" />

      </tableColumn>

    ===================

    Here is the when you map a node to a single cell

    ===================
    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

    - <singleXmlCells xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

    - <singleXmlCell id="1" r="A3" connectionId="0">

    - <xmlCellPr id="1" uniqueName="Symbol">

      <xmlPr mapId="1" xpath="/FutureContract/Meta/Symbol" xmlDataType="string" />

      </xmlCellPr>

      </singleXmlCell>

    =========

     

    If you look at the first table xml, it gives us the range ref (Q7:Y8) and xpath . Also look at the tableType which says it is XML map table. The second table gives the cell number and xpath. Here is C# sample to get these tables:

     

      var tbldef = worksheetPart.TableDefinitionParts;

                    int intLoop=0;

                    for (intLoop = 0; intLoop < tbldef.Count(); intLoop++)

     

                    {

                        DocumentFormat.OpenXml.Spreadsheet.Table tbl;

                        tbl = tbldef.ElementAt(intLoop).Table;

                        if (tbl.TableType == "xml")

                        {

     

                        }

                        

                    }

                    var single = worksheetPart.SingleCellTablePart;

     

    Once you have the cell reference, you can use the following blog to read the values:

     

    Reading Data from SpreadsheetML

    http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx

     

     Thanks,
    Ankush

     

    • Proposed as answer by Ankush_Bhatia Tuesday, July 13, 2010 6:28 PM
    • Marked as answer by Ankush_Bhatia Wednesday, August 11, 2010 9:29 PM
    Tuesday, July 13, 2010 5:52 PM

All replies