none
VmlDrawingPart is empty in an OpenXml Document spreadsheet RRS feed

  • Question

  • Hello,

    I am attempting to edit an Excel document via OpenXml. I have almost everything working. <g class="gr_ gr_255 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Punctuation only-ins replaceWithoutSep" data-gr-id="255" id="255">Basically</g> I am inserting data into Cells and inserting rows. 

    I have <g class="gr_ gr_300 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del" data-gr-id="300" id="300">check boxes</g> below the rows I am inserting so I need to update their positioning. I found the part of the vmlDrawing1.xml that I need to update, but when I look at the DOM that part doesn't exist.

    Here is my console app to test what I want to do:

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    
    namespace ConsoleApp1
    {
        public class ExcelDataWriter
        {
            static void Main(string[] args)
            {
                var modifications = new List<ExcelDataModification>
                {
                    new ExcelDataModification()
                    {
                        Row = 4,
                        CellId = "A",
                        CellValue = "2/25/2019",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 4,
                        CellId = "E",
                        CellValue = "1234-1234",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 6,
                        CellId = "A",
                        CellValue = "Customer Name",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 8,
                        CellId = "H",
                        CellValue = "County",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 10,
                        CellId = "A",
                        CellValue = "Address 1, address 2",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 10,
                        CellId = "H",
                        CellValue = "City, MN",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 15,
                        CellId = "B",
                        CellValue = "1",
                        DataType = CellValues.Boolean,
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 16,
                        CellId = "B",
                        CellValue = "0",
                        DataType = CellValues.Boolean,
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 17,
                        CellId = "B",
                        CellValue = "1",
                        DataType = CellValues.Boolean,
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 19,
                        CellId = "A",
                        CellValue = "Chad Clark",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 19,
                        CellId = "D",
                        CellValue = "1234567",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 19,
                        CellId = "E",
                        CellValue = "218-123-4567",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 19,
                        CellId = "H",
                        CellValue = "chad@protech.com",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 21,
                        CellId = "A",
                        //TODO Fix addresses
                        CellValue = "1234 Overthere St, City",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 21,
                        CellId = "H",
                        CellValue = "MN",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 21,
                        CellId = "I",
                        CellValue = "55660",
                    },
    
                    new ExcelDataModification()
                    {
                        Row = 23,
                        CellId = "H",
                        CellValue = "2/24/2019",
                    },
                };
    
                var rowItems = new List<List<ExcelDataModification>>();
                for(int i = 0; i < 10; i++)
                {
                    rowItems.Add(new List<ExcelDataModification>()
                    {
                        new ExcelDataModification()
                        {
                            CellId = "A",
                            CellValue = $"Location{i}",
                        },
    
                        new ExcelDataModification()
                        {
                            CellId = "C",
                            CellValue = $"Super, really long violation description that wraps into the next row, need to make sure it can handle this",
                        },
                    });
                }
    
                new ExcelDataWriter().Create(modifications, rowItems);
    
                /*var workbook = new Spire.Xls.Workbook();
                workbook.LoadFromFile(@"C:\Source\ConsoleApp1\ConsoleApp1\bin\Debug\netcoreapp2.2\InspectionReportTemplate out.xlsx");
                workbook.SaveToFile("sample.pdf", Spire.Xls.FileFormat.PDF);*/
            }
    
            private List<string> sharedStrings = new List<string>();
    
            public void Create(List<ExcelDataModification> modifications, List<List<ExcelDataModification>> rowItemsList)
            {
                modifications = modifications.OrderBy(x => x.Row).ToList();
    
                File.Copy(@"C:\Source\ConsoleApp1\ConsoleApp1\bin\Debug\netcoreapp2.2\InspectionReportTemplate.xlsx",
                    @"C:\Source\ConsoleApp1\ConsoleApp1\bin\Debug\netcoreapp2.2\InspectionReportTemplate out.xlsx", true);
    
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"C:\Source\ConsoleApp1\ConsoleApp1\bin\Debug\netcoreapp2.2\InspectionReportTemplate out.xlsx", true);
                WorksheetPart worksheetPart = spreadsheetDocument.WorkbookPart.Parts.FirstOrDefault(x => x.OpenXmlPart is WorksheetPart).OpenXmlPart as WorksheetPart;
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    
                //TODO spreadsheetDocument.WorkbookPart.WorkbookStylesPart
    
                var sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
                //TODO - come up with something better
                foreach (var ssi in sharedStringTable.Elements<SharedStringItem>())
                {
                    sharedStrings.Add(ssi.Text?.Text);
                }
    
                ApplyModifications(sheetData, modifications);
    
                AddRows(worksheetPart, 10);
    
                InsertData(sheetData, rowItemsList);
    
                for(int i = sharedStringTable.Count(); i < sharedStrings.Count; i++)
                {
                    sharedStringTable.Append(new SharedStringItem(new Text(sharedStrings[i])));
                }
    
                uint diff = sharedStringTable.Count.Value - sharedStringTable.UniqueCount.Value;
                sharedStringTable.Count.Value = (uint)sharedStrings.Count;
                sharedStringTable.UniqueCount.Value = sharedStringTable.Count.Value - diff;
    
                // Close the document.
                spreadsheetDocument.Close();
    
            }
    
            private const int startRowIndex = 13;
    
            private void ApplyModifications(SheetData sheetData, List<ExcelDataModification> modifications)
            {
                int i = 0;
                foreach (var rowElement in sheetData.ChildElements)
                {
                    Row row = rowElement as Row;
                    while (i < modifications.Count && row?.RowIndex == modifications[i].Row)
                    {
                        EditCell(row, modifications[i++]);
                    }
                }
            }
    
            private void InsertData(SheetData sheetData, List<List<ExcelDataModification>> rowItemsList)
            {
                for(int i = 0; i < rowItemsList.Count; i++)
                {
                    Row row = sheetData.Elements<Row>().FirstOrDefault(x => x.RowIndex.Value == (uint)(startRowIndex + i));
                    foreach (var rowItem in rowItemsList[i])
                    {
                        EditCell(row, rowItem);
                    }
                }
            }
    
            private void AddRows(WorksheetPart worksheetPart, uint numberOfRows)
            {
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    
                //Move all of the rows down.
                foreach (var row in sheetData.Elements<Row>())
                {
                    if(row.RowIndex > startRowIndex)
                    {
                        row.RowIndex = new UInt32Value(row.RowIndex.Value + numberOfRows);
                        foreach(var cell in row.Elements<Cell>())
                        {
                            cell.CellReference = cell.CellReference.Value[0] + row.RowIndex.Value.ToString();
                        }
                    }
                }
    
                //Duplicate startingRow into added rows
                Row startingRow = sheetData.Elements<Row>().First(x => x.RowIndex == startRowIndex);
                Row currentRow = startingRow;
                MergeCells mergeCells = worksheetPart.Worksheet.Descendants<MergeCells>().First();
                for(int i = 1; i <= numberOfRows; i++)
                {
                    Row clonedRow = (Row)startingRow.CloneNode(true);
                    clonedRow.RowIndex = (uint)(i + startRowIndex);
                    //Call ToList to get a copy of the enumerator so we aren't modifying it in the middle of the enumeration with the ReplaceChild.
                    foreach (var currentCell in clonedRow.Elements<Cell>().ToList())
                    {
                        var cloneCell = (Cell)currentCell.CloneNode(true);
                        cloneCell.CellReference = cloneCell.CellReference.Value.Replace(startRowIndex.ToString(), clonedRow.RowIndex);
    
                        //TODO add even / odd extension method
                        if (clonedRow.RowIndex.Value % 2 == 0)
                        {
                            //clear cell
                            cloneCell.StyleIndex = 34;
                        }
                        else
                        {
                            //Grey cell
                            cloneCell.StyleIndex = 25;
                        }
    
                        clonedRow.ReplaceChild(cloneCell, currentCell);
                    }
    
                    //Duplicate merged cells
                    foreach (var mergeCell in mergeCells.Elements<MergeCell>().Where(x => x.Reference.Value.Contains(startRowIndex.ToString())))
                    {
                        var clonedMergeCell = (MergeCell)mergeCell.CloneNode(true);
                        clonedMergeCell.Reference.Value = clonedMergeCell.Reference.Value.Replace(startRowIndex.ToString(), clonedRow.RowIndex);
                        mergeCells.Append(clonedMergeCell);
                    }
    
                    currentRow.InsertAfterSelf(clonedRow);
                    currentRow = clonedRow;
                }
    
                //Move the drawing content
                foreach (var markerType in worksheetPart.Worksheet.Descendants<MarkerType>())
                {
                    if (int.Parse(markerType.RowId.Text) >= startRowIndex)
                    {
                        markerType.RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((int.Parse(markerType.RowId.Text) + numberOfRows).ToString());
                    }
                }
    
                foreach (var markerType in worksheetPart?.DrawingsPart?.WorksheetDrawing?.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.MarkerType>())
                {
                    if (int.Parse(markerType.RowId.Text) >= startRowIndex)
                    {
                        markerType.RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((int.Parse(markerType.RowId.InnerText) + numberOfRows).ToString());
                    }
                }
    
                // Anchoe
                // From (upper right coordinate of a rectangle)
                // [0] Left column
                // [1] Left column offset
                // [2] Left row
                // [3] Left row offset
                // To (bottom right coordinate of a rectangle)
                // [4] Right column
                // [5] Right column offset
                // [6] Right row
                // [7] Right row offset
    
                
                foreach (var part in worksheetPart?.VmlDrawingParts)
                {
                    //part is empty, has no parts or children
                    VmlDrawingPart vmlDrawingPart = part as VmlDrawingPart;
                }
    
                foreach (var controlProperty in worksheetPart.ControlPropertiesParts)
                {
                    //TODO - write code to get the integer part and deal with less than starting row index stuff.
                    controlProperty.FormControlProperties.FmlaLink.Value = controlProperty.FormControlProperties.FmlaLink.Value[0] + (int.Parse(controlProperty.FormControlProperties.FmlaLink.Value.Substring(1)) + numberOfRows).ToString();
                }
            }
    
            private void EditCell(Row row, ExcelDataModification modification)
            {
                uint rowIndex = (uint)modification.Row;
                if (modification.Row == 0)
                {
                    rowIndex = row.RowIndex.Value;
                }
    
                Cell cell;
                if (row.FirstOrDefault(x => (x as Cell)?.CellReference == $"{modification.CellId}{rowIndex}") is Cell existingCell)
                {
                    cell = existingCell;
                }
                else
                {
                    cell = new Cell()
                    {
                        CellReference = $"{modification.CellId}{rowIndex}",
                    };
    
                    row.InsertBefore(cell, row.FirstChild);
                }
    
                if (modification.DataType.HasValue)
                {
                    cell.DataType = modification.DataType.Value;
                    cell.CellValue = new CellValue(modification.CellValue);
                }
                else
                {
                    cell.DataType = CellValues.SharedString;
                    int index = sharedStrings.IndexOf(modification.CellValue);
                    if (index == -1)
                    {
                        index = sharedStrings.Count;
                        sharedStrings.Add(modification.CellValue);
                    }
    
                    cell.CellValue = new CellValue(index.ToString());
    
                }
            }
    
            public class ExcelDataModification
            {
                public int Row { get; set; }
                public string CellId { get; set; }
                public string CellValue { get; set; }
                public CellValues? DataType { get; set; }
            }
        }
    }
    


    if you search it for this code: 

                foreach (var part in worksheetPart?.VmlDrawingParts)
                {
                    //part is empty, has no parts or children
                    VmlDrawingPart vmlDrawingPart = part as VmlDrawingPart;
                }


    This is where I would expect to find my data.  <g class="gr_ gr_909 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" data-gr-id="909" id="909">My spreadsheet</g> that I am using can be downloaded from Click here to download

    This is the excerpt from the vmldrawing1.xml

    <xml xmlns:v="urn:schemas-microsoft-com:vml"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel">
      <o:shapelayout v:ext="edit">
        <o:idmap v:ext="edit" data="1"/>
      </o:shapelayout>
      <v:shapetype id="_x0000_t201" coordsize="21600,21600" o:spt="201"
       path="m,l,21600r21600,l21600,xe">
        <v:stroke joinstyle="miter"/>
        <v:path shadowok="f" o:extrusionok="f" strokeok="f" fillok="f" o:connecttype="rect"/>
        <o:lock v:ext="edit" shapetype="t"/>
      </v:shapetype>
      <v:shape id="_x0000_s1029" type="#_x0000_t201" style=';
      margin-left:74pt;margin-top:250pt;width:21.5pt;height:15.5pt;z-index:1;
      mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f"
       strokecolor="windowText [64]" o:insetmode="auto">
        <v:path shadowok="t" strokeok="t" fillok="t"/>
        <o:lock v:ext="edit" rotation="t"/>
        <v:textbox style='mso-direction-alt:auto' o:singleclick="f">
          <div style='text-align:left'></div>
        </v:textbox>
        <x:ClientData ObjectType="Checkbox">
          <x:SizeWithCells/>
          <x:Anchor>
            1, 52, 13, 20, 1, 95, 14, 27
          </x:Anchor>
          <x:AutoFill>False</x:AutoFill>
          <x:AutoLine>False</x:AutoLine>
          <x:TextVAlign>Center</x:TextVAlign>
          <x:FmlaLink>B15</x:FmlaLink>
          <x:NoThreeD/>
        </x:ClientData>
      </v:shape>
      <v:shape id="_x0000_s1030" type="#_x0000_t201" style=';
      margin-left:74pt;margin-top:262pt;width:21.5pt;height:15.5pt;z-index:2;
      mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f"
      strokecolor="windowText [64]" o:insetmode="auto">
        <v:path shadowok="t" strokeok="t" fillok="t"/>
        <o:lock v:ext="edit" rotation="t"/>
        <v:textbox style='mso-direction-alt:auto' o:singleclick="f">
          <div style='text-align:left'></div>
        </v:textbox>
        <x:ClientData ObjectType="Checkbox">
          <x:SizeWithCells/>
          <x:Anchor>
            1, 52, 14, 20, 1, 95, 15, 22
          </x:Anchor>
          <x:AutoFill>False</x:AutoFill>
          <x:AutoLine>False</x:AutoLine>
          <x:TextVAlign>Center</x:TextVAlign>
          <x:FmlaLink>B16</x:FmlaLink>
          <x:NoThreeD/>
        </x:ClientData>
      </v:shape>
      <v:shape id="_x0000_s1031" type="#_x0000_t201" style=';
      margin-left:74pt;margin-top:276.5pt;width:21.5pt;height:15.5pt;z-index:3;
      mso-wrap-style:tight' filled="f" fillcolor="window [65]" stroked="f"
      strokecolor="windowText [64]" o:insetmode="auto">
        <v:path shadowok="t" strokeok="t" fillok="t"/>
        <o:lock v:ext="edit" rotation="t"/>
        <v:textbox style='mso-direction-alt:auto' o:singleclick="f">
          <div style='text-align:left'></div>
        </v:textbox>
        <x:ClientData ObjectType="Checkbox">
          <x:SizeWithCells/>
          <x:Anchor>
            1, 52, 15, 20, 1, 95, 16, 22
          </x:Anchor>
          <x:AutoFill>False</x:AutoFill>
          <x:AutoLine>False</x:AutoLine>
          <x:TextVAlign>Center</x:TextVAlign>
          <x:FmlaLink>B17</x:FmlaLink>
          <x:NoThreeD/>
        </x:ClientData>
      </v:shape>
    </xml>

    I need to update the anchor positioning to increment it for the number of rows I inserted.

    Thank you for any assistance.

    Friday, March 1, 2019 4:31 PM