'OpenXML to Excel: CellFormat Problem - Open Excel-Template and insert datatable to existing Excel-Table with formatted columns

I am getting some data as JSON via RESTful web api and transforming it into a datatable. Afterwards I want to open an "Excel template" (*.xlsx) with OpenXML and insert the datatable into an Excel table. The Excel spreadsheet contains the headers and a blank row with no data. The columns are formatted (text, number, ... , left-justified, etc.) like this:

+----------+--------------+-----------------+
| Quantity | QuantityUnit | ItemDescription |
+----------+--------------+-----------------+
| number   | Text         | Text            |
+----------+--------------+-----------------+

The content of the "Excel-Template" looks like this:

+----------+--------------+-----------------+
| Quantity | QuantityUnit | ItemDescription |
+----------+--------------+-----------------+
|          |              |                 |
+----------+--------------+-----------------+

My code so far:

 using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, true))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "ImportData").First();
                WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
                //workbookPart.WorkbookStylesPart.Stylesheet = GetStylesheet();
                //workbookPart.WorkbookStylesPart.Stylesheet = GenerateStylesheet();
                workbookPart.WorkbookStylesPart.Stylesheet.Save();
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

                foreach (DataRow item in multiLevelBomDatatable.Rows)
                {
                    Row row = new Row();
                    for (int i = 0; i < item.ItemArray.Length; i++)
                    {
                        Cell cell = new Cell();
                        if (i == 0)
                        {
                            string sValue = item[i].ToString();
                            //double value = double.Parse(item[i].ToString());
                            //decimal dValue = Convert.ToDecimal(sValue);
                            CellValue cellValue = new CellValue
                            {
                                Text = sValue
                            };
                            cell.Append(cellValue);
                            //cell.CellValue = new CellValue(sValue);
                            //cell.DataType = CellValues.String;
                            //cell.StyleIndex = (UInt32Value)2U;
                        }
                        else
                        {
                            cell.CellValue = new CellValue(item[i].ToString());
                            cell.DataType = CellValues.String;
                        }

                        row.Append(cell);
                    }
                    sheetData.Append(row);
                }

                //find the table
                Table table = worksheetPart.TableDefinitionParts.FirstOrDefault(t => t.Table.Name == "ImportData")?.Table;
                //update the reference of the table (11 is 10 data rows and 1 header)
                string maxRow = Convert.ToString(multiLevelBomDatatable.Rows.Count + 1);
                string startRow = "1";
                string tableReference = "A" + startRow + ":AE" + maxRow;
                //table.Reference = "A" + Convert.ToString(startRow) + ":AC" + Convert.ToString(maxRow +1);
                table.Reference = tableReference;
            }

If the table is formatted in the template, the datatable is filled only from third row.

+----------+--------------+-----------------+
| Quantity | QuantityUnit | ItemDescription |
+----------+--------------+-----------------+
|          |              |                 |
+----------+--------------+-----------------+
| 0.421    | m            | Tesa            |
+----------+--------------+-----------------+

The result I want should look like this:

+----------+--------------+-----------------+
| Quantity | QuantityUnit | ItemDescription |
+----------+--------------+-----------------+
| 0.421    | m            | Tesa            |
+----------+--------------+-----------------+

How can I insert data into an already formatted cell with OpenXML?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source