Editing a table using structured references in formulas result in a broken Excel sheet.

[bosma4u]

From EPPlus 7 adding rows to a table from a template using structured references in a table formula (for instance =@[ColumnName1]=@[ColumnName2]) results in a corrupted Excel file. On opening Excel needs to repair the sheet but then the table is broken. If I change the formulatype to explicit (=C1=D1) the table is no longer corrupted, Excel does show warnings in the cells about the cell formula not being equal to the table column formula.

[JanKallman]

I can not reproduce this. I tried with this test with a workbook containing this formula :

"Table1[[#This Row],[a]]=Table1[[#This Row],[b]]"

Test Code...

            using (var p = OpenTemplatePackage("i1314.xlsx"))
            {
                var ws = p.Workbook.Worksheets[0];
                var tbl = ws.Tables[0];
                tbl.InsertRow(1,1);
		tbl.AddRow(1);

		SaveAndCleanup(p);
            }

Are you using EPPlus 7.0.9?

[bosma4u]

Epplus6bug..zip
I stripped our code to demonstrate.

[JanKallman]

Yes, it this seems to happen when you have an array table formula that reference the entire column. If you want to compare the cells per row, use this format instead:

Resultaten[[#This Row],[Gegeven antwoord]]=Resultaten[[#This Row],[Correct antwoord]]
...instead of...
Resultaten[Gegeven antwoord]=Resultaten[Correct antwoord]
.., that compares the entire columns and makes an implicit intersection
We will look at a fix for this in coming versions.

[JanKallman]

That is [@[Gegeven antwoord]]=[@[Correct antwoord]] in Excel