Is their a way to add data to a existing table

[jhoneill]

You can append data to an existing sheet. (export-excel -append)

If that sheet contains a table, this will not extend the table, and if export re-specifies the existing table it will cause an error. Export-Excel should catch this and delete and re-create tables. (I thought I changed this at some point but possibly I never checked in that change )

@dfinke suggest you mark this as a bug. if I get a moment I can do this one , the -movebefore etc from #345 , the pivot filter and pivot data from column from #349 and the URI thing in #348 and check them in together :-) I also need to fix mis-spelling in the merge-multiple... and put in parameter help for -startColumn and -EndColumn.

[jhoneill]

Turns out I did update it - there's a comment in the code that deleting a table causes an error when the file is opened in Excel. I might pop over to eeplus and log an issue with it.
I should still trap the "table name exists" error so it's still a bug :-)

[jhoneill]

I've found a way to do modify the table range via the XML property and it is one of things I've put in my latest check in - which needs more testing before being pulled into the main branch if anyone wants to test it, it is at
https://github.com/jhoneill/ImportExcel/blob/master/Export-Excel.ps1

The full list of things changed is

• Added MoveToStart, MoveBefore and MoveAfter Parameters
• Added support for URI item properties. If a property is of type URI it is created as a hyperlink
• Fixed issue where AutoNamedRange, NamedRange, and TableName do not work when appending to a sheet which already contains the range(s) / table
• Fixed issues when pivottables / charts already exist and an export tries to create them again.
• Allowed PivotFilter and PivotDataToColumn, ChartHeight/width ChartRow/Column, ChartRow/ColumnPixelOffsets, to be passed in -PivotTableDefinition and specified when running New-PivotDefinition.
• Bad column-names specified for Pivots now generate warnings instead of throwing.
• Removed the need to specify a fill type when specifying a title background color
• "flattened out" small "called-once" function , add-title, convert-toNumber and Stop-ExcelProcess.

[jhoneill]

Please note if testing the my unmerged export excel, please use this link https://github.com/jhoneill/ImportExcel/commit/520bb079e5d863931d650e369e7faa9ab7d973d1#diff-2312e8cdbe09401310dc615fad4e8d83 the commit I did this evening needs more changed files.

[DarkLite1]

Stumbled upon this thread during my search for finding a way to update the table range of an already existing table when using Open-ExcelPackage.

The approach of @jhoneill, to update the Xml of the table works fine:

                $targetRange = $ws.Cells[$csr, $csc, $cer, $cec]
                #if the table exists, update it.
                if ($ws.Tables[$TableName]) {
                    $ws.Tables[$TableName].TableXml.table.ref = $targetRange.Address 
                    $ws.Tables[$TableName].TableStyle = $TableStyle
                }

Then I found this post of EPPlus:

From EPPlus 5 you can add rows/cols to an existing table, references in affected cells will be updated/shifted. Here is an example where we add a row to an existing table.

// you can also supply number of new rows as an argument to the AddRow method
var rowRange = table.AddRow();
var newRowIx = rowRange.Start.Row;
sheet.Cells[newRowIx, 1].Value = new DateTimeFormatInfo().GetMonthName(newRowIx);
sheet.Cells[newRowIx, 2].Value = rnd.Next(10000, 100000);
sheet.Cells[newRowIx, 3].Formula = $"B{newRowIx} * 0.25";
sheet.Cells[newRowIx, 4].Formula = $"B{newRowIx} + C{newRowIx}";
rowRange.Style.Numberformat.Format = "€#,##0.00";

Are we using EPPlus 5? Because I tried $table.AddRow(2) but it fails saying the method is unknow.

[dfinke]

Thanks @DarkLite1. Not using EPPlus 5. There were paid licensing requirements at the time I looked at it a while back. Not that I had to pay, and I did not want to go that that rabbit hole. Plus, they fixed EPPlus 5, which introduced many breaking changes for SDKs that used EPPlus 4.

[DarkLite1]

OMG, I really thought this library was open source and free to use. Checking the price list I can fully understand sticking to the older version.

[dfinke]

Those folks did a great job moving EPPlus forward from the early Microsoft days, spent a decade not getting paid for their efforts. Version 5 has integrated excellent new features.

They deserve to be paid for their efforts.