Recently I found myself needing to paste a large C# DataTable into Excel, and was excited to come across the SimpleOOXML (https://simpleooxml.codeplex.com) extension's WorksheetWriter.PasteDataTable() function, which did exactly that.
Unfortunately, it did not automagically convert the pasted DataTable as a "Format as Table" with automated column width and filtering capabilities, as one might expect, but instead only allowed me to specify individual styles (e.g. border, background color etc).
It would be amazing if someone knew how to do this using SimpleOOXML (I couldn't find something relevant in their Discussion forum), but in case this is not possible I was wondering if it could be done using traditional OpenXML?
EDIT
To clarify, I checked OpenXML's MSDN Documentation, but couldn't find a way to do the following:
Programmatically use OpenXML to Format a specific Range (G7:I9) As a Table, using the style "Table Style Light 9", and keep existing Headers.
Create a dummy Excel file with simple data in a table:
Opening the Excel file in the open xml productivity tool shows that a Table part needs to be created:
private void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
{
Table table1 = new Table(){ Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:D2", TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "A1:D2" };
TableColumns tableColumns1 = new TableColumns(){ Count = (UInt32Value)4U };
TableColumn tableColumn1 = new TableColumn(){ Id = (UInt32Value)1U, Name = "1" };
TableColumn tableColumn2 = new TableColumn(){ Id = (UInt32Value)2U, Name = "2" };
TableColumn tableColumn3 = new TableColumn(){ Id = (UInt32Value)3U, Name = "3" };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)4U, Name = "4" };
tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
tableColumns1.Append(tableColumn3);
tableColumns1.Append(tableColumn4);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleLight17", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
And then called in main:
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart<TableDefinitionPart>("rId1");
GenerateTableDefinitionPart1Content(tableDefinitionPart1);
To choose the right style for your table change the TableStyleInfo Name property:
TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleLight17", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With