Corrupted file after inserting/adding columns

[irvalchev]

I am using EPPlus (v6.0.7 commercial) to edit some complex Excel. An issue occurs when I add/insert columns in one particular table (TblCompFailureRates). So after I use EPPlus to add/insert columns in this table, the next time I open the file Excel tells me it needs to be repaired. The repair seems to work fine, but since we use this for production purposes we would really like to get this working properly.

I am using both table.Columns.Insert and table.Columns.Add methods in my code for the changes and before the change everything works fine.

Is there a fix or a workaround for this?

Attached is a corrupted file you can check

FMEDA-IEC61508_Arduino_1682073208327 - Copy - Copy.xlsx

[JanKallman]

In the worksheet Component Failure Rates you have a formula in column B1, C1 and D1. As this is a column header of the table, so this is not allowed. This causes the header values to be non-unique.

<c r="B1" t="e">
<f>VLOOKUP(TblCompFailureRates[[#This Row],[Component]],TblComponents[],2,FALSE)&""</f>
<v>#VALUE!</v>
</c>
<c r="C1" t="e">
<f>TblCompFailureRates[[#This Row],[λref]]*IF(TblCompFailureRates[[#This Row],[πλ Final]]="",1,TblCompFailureRates[[#This Row],[πλ Final]])</f>
<v>#VALUE!</v>
</c>
<c r="D1" t="e">
<f>SUM(fieldComponentsFitRefLambda)</f>
<v>#VALUE!</v>
</c>
<c r="E1" t="e">
<f>IF(fieldComponentsFitOpConditionsFinal="",1,fieldComponentsFitOpConditionsFinal)</f>
<v>#VALUE!</v>
</c>

This likely happens because these columns are Calculated, but I'm unsure how you end up with this workbook. This is obviously a bug, but I have a hard time to reproduce it without knowing what code you run. Can you provide a small sample?

[irvalchev]

I was able to reproduce it using the code below:

            string folder = @"C:\";
            string path = folder + "test.xlsx";
            using (var package = new ExcelPackage(path))
            {
                var table = package.Workbook.Worksheets
                .SelectMany(x => x.Tables).Single(x => x.Name == "TblCompFailureRates");
                table.Columns.Insert(8);                
                package.SaveAs(folder + "test_updated.xlsx");
            }

I am attaching the source file and the result file.

After executing the insert the Formula for the header cells of the calculated columns is changed.

test.xlsx test_updated.xlsx

[JanKallman]

I have added a fix for this. The formulas of the calculated table column formulas was not correctly set. You can try it using our develop Nuget feed. See...https://github.com/EPPlusSoftware/EPPlus/wiki/Using-our-develop-Nuget-feed

[irvalchev]

For some reason now when I try to change the Excel, the tables are strangely duplicated.

Here the file FMEDA-IEC61508_Arduino_1682683351825.xlsx

I do not experience this issue with the latest stable release

[irvalchev]

So I have checked v6.2.3 and there the original issue seems fixed. Nonetheless, the one from the last comment is still there For example, check the Components sheet in the attached file

FMEDA-IEC61508_VoltageMonitor_1683042494082_v6.2.3.xlsx

[JanKallman]

Can you provide the code to reproduce this?

[irvalchev]

Here is code to reproduce it (not intended to produce meaningful results):

        public void SandboxTest()
        {
            string folder = @"C:\";
            string path = folder + "test.xlsx";
            using (var package = new ExcelPackage(path))
            {
                var table = package.Workbook.Worksheets.SelectMany(x => x.Tables).Single(x => x.Name == "TblComponents");
                List<object[]> tableData = new List<object[]>()
                {
                    new []{ "C1", null, "Ceramic Capacitor", "Ceramic Capacitor FM" },
                    new []{ "C2", null, "Ceramic Capacitor", "Ceramic Capacitor FM" }
                };
                InsertTableRows(table, tableData.ToList(), 1, true);

                package.SaveAs(folder + "test_updated.xlsx");
            }
        }

        static void InsertTableRows(ExcelTable table, List<object[]> data, int insertBeforeRow, bool removeOtherRows)
        {
            // Finding the row in the sheet
            int nextSheetRow = GetSheetRowIndex(table, insertBeforeRow);

            table.InsertRow(insertBeforeRow, data.Count, true);

            // Applying the formulas to the new rows
            if (insertBeforeRow > 0)
            {
                nextSheetRow = GetSheetRowIndex(table, insertBeforeRow + data.Count);
            }

            // Filling the data -> not working
            for (int dataRowIx = 0; dataRowIx < data.Count; dataRowIx++)
            {
                object[] dataRow = data[dataRowIx];
                for (int colIx = 0; colIx < dataRow.Length; colIx++)
                {
                    table.WorkSheet.Cells[nextSheetRow, colIx + 1].Value = dataRow[colIx];
                }
                nextSheetRow++;
            }

            // Deleting the other rows in the table if required
            if (removeOtherRows)
            {
                // Deleting the rows before the newly inserted ones
                if (insertBeforeRow > 0)
                {
                    table.DeleteRow(0, insertBeforeRow);
                }
                // NB: The new rows are now at the start of the sequence, so only the ones after
                // them should be removed
                int endRowsToRemove = (table.Address.End.Row - table.Address.Start.Row) - data.Count;
                if (endRowsToRemove > 0)
                {
                    table.DeleteRow(data.Count, endRowsToRemove);
                }
            }
        }

        static int GetSheetRowIndex(ExcelTable table, int tableRow)
        {
            return table.Address.Start.Row + tableRow + (table.ShowHeader ? 1 : 0);
        }

test.xlsx

[JanKallman]

Thanks, I will provide a fix for this asap. I will try to increase the test coverage, so I'll get back to you when it's ready for test.

[JanKallman]

Try latest develop build and see if it solves this issue.

[irvalchev]

This works. Thank you!