2

I'm having issues making an actual table in my excel file. The file is created and all the data is there, but when I open the file the table definition doesn't exist. I'm using this method to try and turn the data range into a table.

    private static Table CreateTable(string tableRange, IEnumerable<string> headers)
    {
        var table = new Table {
            Id = 1,
            Name = "Table 1",
            DisplayName = "Table 1",
            Reference = tableRange,
            TotalsRowShown = false,
            HeaderRowFormatId = 0
        };
        table.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

        UInt32Value i = 1;

        var columns = new TableColumns();
        foreach (var header in headers) {
            columns.Append(new TableColumn {
                Id = i++,
                Name = header
            });
        }

        var style = new TableStyleInfo {
            Name = "TableStyleMedium2",
            ShowFirstColumn = false,
            ShowLastColumn = false,
            ShowRowStripes = true,
            ShowColumnStripes = false
        };

        table.Append(new AutoFilter { Reference = tableRange });
        table.Append(columns);
        table.Append(style);

        return table;
    }

The tableRange variable is a simple excel range string, like "A1:D27". I'm trying to add it to the worksheet like so:

var tableDefinitionPart = worksheetPart.AddNewPart<TableDefinitionPart>();
tableDefinitionPart.Table = CreateTable(tableRange, headers);

I'm not getting any errors when I open the file.

If I open the file with the XML Productivity tool and click on the "x:table (Table)" part within the worksheet I see this:

<x:table xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table 1" displayName="Table 1" ref="A1:Z2" totalsRowShown="0" headerRowDxfId="0">
  <x:autoFilter ref="A1:Z2" />
  <x:tableColumns>
    <x:tableColumn id="1" name="Lab Name" />
    ....
    <x:tableColumn id="26" name="Description" />
  </x:tableColumns>
  <x:tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
</x:table>

So it seems like I'm putting something properly into the file. It's just not an actual "table" as far as Excel is concerned.

Gargoyle
  • 9,590
  • 16
  • 80
  • 145
  • Create what you want in Excel, as a user, then view that in the Open XML SDK Productivity Tool. Compare what it generates with what you have. Or start with your result - without a table; add the table manually and save as a new workbook, then compare the first with the second and look at the code the Tool generates for creating the second from the first. – Cindy Meister Jun 24 '18 at 15:51
  • Did that. That's how I got this far :) If I open up my generated file that doesn't actually show the table and reflect on it I see the exact same definition for `GenerateTableDefinitionPart1Content` that appears if I then re-apply the table settings and save it. – Gargoyle Jun 25 '18 at 18:09

0 Answers0