How do I add a column to an Excel Table?

Jan 13, 2011 at 5:08 PM

First, thanks to Tim for creating this excellent library and saving us the pain of the Open XML SDK.

My application needs to create a PivotChart from a Table. The first column in the Table is date, the remaining columns represent data series each of which form a line on a line graph (date will be the x-axis). I have a template and existing code which works great when I use the table.Fill method.

Unfortunately I do not know how many data series there are ahead of time.

Is it possible to add columns to a Table? I tried setting TableColumns.Count to the new value, but it didn't work. I expect I need to create a new TableColumnWrapper object and append it to the Table but I could not find the function to do this. If this is possible, I expect I'll also have to alter the PivotChart to include the new series too.

Any help or pointers to the right bit of documentation much appreciated,

Rob.

Coordinator
Jan 13, 2011 at 5:33 PM

Hi Rob,

You're right that you can't simply modify the underling SDK object collections - you need to make any changes via the wrapper objects. In general, whereever you need to add new members to a collection, there is an Add() method to do so.

However, tables are a slightly special case, as a table is not a real data entity, but instead should be considered as an overlay of the worksheet that contains it. Therefore, if you need to expand a table, you do so by expanding the underlying worksheet. When you populate a table using the Fill() method, it inserts rows to the worksheet at the relevant location and the table housekeeping code takes care of re-dimensioning the table at the same time. Similarly, if you need to add columns to a table, you can do so by inserting columns into the worksheet (using the same positioning heuristics that you would use if doing the same thing manually in Excel). If you need a code sample to see how this works, it's worth looking at the unit tests that come with the source code distribution, as all the table row and column insertion and deletion scenarios are covered here.

It's been a long time since I last worked on this functionality, so I am struggling to think whether you will run into any other issues in implementing your scenario. I suggest that you look at the code samples and take a shot at coding what you're trying to achieve; then post again here if you have unresolved issues.

Good luck,

Tim 

Feb 2, 2011 at 9:29 AM

Hi Tim

Thanks for your reply. I tried this:

var part = package.WorkbookPart.WorksheetParts["Data"];
part.Worksheet.InsertColumns(1, 1);

and then did table.Fill() as before. The resultant file gives an error on loading:

Removed Part: /xl/tables/table1.xml part with XML error.  (Table) Load error. Line 1, column 341.

Further investigation in the Productivity Tool showed that the new columns do not have their 'id' attribute set in the table1.xml file. Also, in the /xl/sharedStrings.xml file one of the existing column names had been replaced with 'Column1' when I expected it to create a new entry.

As it happens, I have a workaround using a different template that no longer requires me to add columns, so I'm not going to investigate any further for the moment - however if anyone else is struggling let me know & I'll take another look.

Cheers

Rob.

Dec 21, 2012 at 11:15 AM

Hi,

I've tried using the example from www.extrememl.com/index.php/knowledge-center/manipulating-worksheets/inserting-columns-into-a-worksheet:

public static void InsertColumns()
{
    using (var package = SpreadsheetDocumentWrapper.Open("MyWorkbook.xlsx"))
    {
        var part = package.WorkbookPart.WorksheetParts["Populated"];
        part.Worksheet.InsertColumns(2, 2);
    }
}

However, the resulting spreadsheet causes an error on opening:

Repaired Records: Table from /xl/tables/table1.xml part (Table)

I had a look at the files within the .xlsx zip file and it appears to be because the "sheet1.xml" file is missing information about the added columns. I've tried setting the Name and Id of the columns using the relevant ExtremeML properties but the entries are only represented in the "table1.xml" file but not the "sheet1.xml" file.

Does anyone know how to resolve this?

Many thanks

Simon



Dec 22, 2012 at 3:16 PM

I managed to reproduce error. This happens if insertion point overlaps existing table. The code executes just fine. However when opening file in Microsoft Excel 2007 file recovery gives error:

Removed Part: /xl/tables/table1.xml part with XML error.  (Table) Load error. Line 1, column 363.

Repaired file contains two inserted columns. But table formatting is lost.

Dec 24, 2012 at 10:27 AM

Yes, it's caused by trying to insert an extra column into a table. The problem is that I have a number of columns that are not known until runtime and so I need to insert them dynamically into the template so I can then fill the table.

Does anyone know how to do this?

Thanks, Simon