'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 |
---|