Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Format Excel 2013 Range as Table using OpenXML

Tags:

c#

excel

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

like image 992
Themos Avatar asked Mar 09 '23 22:03

Themos


1 Answers

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 };
like image 132
Jack Miller Avatar answered Mar 19 '23 08:03

Jack Miller