Formatting weirdness when adding rows to a table

[sba923]

Start with an Excel table that looks like this:

If you try to add two more rows to the table using

$newrows = @(
    [PSCustomObject]@{
        A=10
        B=11
        C=12
    },
    [PSCustomObject]@{
        A=20
        B=21
        C=22
    }
)
$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified2.xlsx') -WorksheetName 'Sheet1' -Table 'Table1' -Append

You'll get a new formatting for the extended table:

Removing the -Table 'Table1' parameter preserves the formatting:

Code, input file and output files in:

AddRowsToTable.zip

[dfinke]

took a quick look.

leaving this hear for later review.

ImportExcel/Public/Export-Excel.ps1

Line 161 in dc4a5e9

$existingTable = $ws.Tables.Where({ $_.address.address -eq $ws.dimension.address }, 'First', 1)

[arkoudigr]

Start with an Excel table that looks like this:

If you try to add two more rows to the table using

$newrows = @(
[PSCustomObject]@{
A=10
B=11
C=12
},
[PSCustomObject]@{
A=20
B=21
C=22
}
)
$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified2.xlsx') -WorksheetName 'Sheet1' -Table 'Table1' -Append

You'll get a new formatting for the extended table:

Removing the -Table 'Table1' parameter preserves the formatting:

Code, input file and output files in:

AddRowsToTable.zip

Hello, you need to add -tablestyle medium2 to preserve the same. For some reason the script when you use -table has the pink table style. So your code should look like this:
$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified2.xlsx') -WorksheetName 'Sheet1' -Table 'Table1' -tablestyle Medium2 -Append

[arkoudigr]

Small update -tablestyle Custom works better, it inherits the style that you already have to the test.xlsx. So optimal code would be:
$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified2.xlsx') -WorksheetName 'Sheet1' -Table 'Table1' -tablestyle Custom -Append

[dfinke]

Thank you @arkoudigr. @sba923 does that work for you?

[sba923]

Well, I've tried adding data to two different tables with different formatting, this gets me a corrupted file Excel complains about....

Copy-Item -Path (Join-Path -Path $PSScriptRoot -ChildPath 'test.xlsx') -Destination (Join-Path -Path $PSScriptRoot -ChildPath 'modified.xlsx')


$newrows = @(
    [PSCustomObject]@{
        A=10
        B=11
        C=12
    },
    [PSCustomObject]@{
        A=20
        B=21
        C=22
    }
)

$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified.xlsx') -WorksheetName 'Sheet1' -Table 'Table1' -TableStyle Custom  -Append
$newrows | Export-Excel -Path (Join-Path -Path $PSScriptRoot -ChildPath 'modified.xlsx') -WorksheetName 'Sheet1' -Table 'Table2' -TableStyle Custom  -Append

AddRowsToTable202501212100.zip

[arkoudigr]

Well, the idea you suggest cannot work as it seems. Your excel file gets corrupted because the script add rows and changes the table structure. What I mean is while changing table1 it destroys table 2. Here is something interesting, when I Import test.xlsx to powershell it returns only the first table
`PS C:**\Downloads\AddRowsToTable202501212100> $test = Import-Excel .\test.xlsx

PS C:**\Downloads\AddRowsToTable202501212100> $test

A B C

---

1 2 3
4 5 6`

[sba923]

I was just trying to test how one would modify an existing table by name while preserving its format, so I created a sheet with two distinct tables.

@dfinke the module should never create a corrupted file, don't you think?

[dfinke]

Sure, can you provide a simple repo of that. It could be that the EPPlus lib may have caused it and fixing that is not likely. Also, no reports on that and if it does, folks find work arounds.

[sba923]

Sure, can you provide a simple repo of that. It could be that the EPPlus lib may have caused it and fixing that is not likely. Also, no reports on that and if it does, folks find work arounds.

The repro code in in the zipfile I posted here yesterday. I presume you want me to create a separate issue for that corruption issue, correct?

[dfinke]

not necessarily.

what is the corruption? usually if I see a "corruption" when I open the xlsx and it says it has an issue and it will try to recover.

[sba923]

First

if I choose Yes I get:

the XML repair log reads:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error523200_01.xml</logFileName>
    <summary>
        Errors were detected in file
        'https://d.docs.live.net/6c0f5f708f464f30/CloudOnly/ImportExcel/AddRowsToTable/modified.xlsx'</summary>
    <removedFeatures>
        <removedFeature>Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table)</removedFeature>
        <removedFeature>Removed Feature: Table from /xl/tables/table1.xml part (Table)</removedFeature>
        <removedFeature>Removed Feature: AutoFilter from /xl/tables/table2.xml part (Table)</removedFeature>
        <removedFeature>Removed Feature: Table from /xl/tables/table2.xml part (Table)</removedFeature>
    </removedFeatures>
</recoveryLog>

The file (available in the zipfile I posted yesterday) is very badly damaged:

1. the added data is in the wrong cells
2. all formatting is lost

[dfinke]

Looked at, will at it some more. Not sure I will be able to fix that.