CODEWITHSUNDEEP
c#excelopenxml
Themos
Themos

Reputation: 460

Format Excel 2013 Range as Table using OpenXML

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.

Excel Format As Table

Keep existing Headers

Upvotes: 4

Views: 4394

Answers (1)

Jack Miller
Jack Miller

Reputation: 325

Create a dummy Excel file with simple data in a table:

enter image description here

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 };

Upvotes: 7

Related Questions