ExcelTable LoadFromDataTable method raise calculatedColumnFormula error when open with excel

[minren118]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.4.2.1079

Spreadsheet application

Excel365

Description

When AddRow and DeleteRow are used, the data is not filled, and the formula calculation is correct. After filling the data with ExcelTable LoadFromDataTable, an error is reported after Excel is opened, and the final formula is displayed incorrectly

            using (ExcelPackage excelPackage = new ExcelPackage("testExcelTableLoadFromDataTable.xlsx"))
            {
                var sht = excelPackage.Workbook.Worksheets[0];
                var dataTable = GetChartTable();

                var excelTable = sht.Tables[0];
                var difRowsCount = excelTable.Range.Rows - dataTable.Rows.Count - 1;

                if (difRowsCount > 0)
                {
                    excelTable.DeleteRow(1, difRowsCount);
                }
                else if (difRowsCount < 0)
                {
                    excelTable.AddRow(-difRowsCount);
                }

                var rngData = sht.Cells["A11"].LoadFromDataTable(dataTable, PrintHeaders: true);

                excelPackage.SaveAs("result.xlsx");

            }

testExcelTableLoadFromDataTable.xlsx

[minren118]

is also error using the below code

            using (ExcelPackage excelPackage = new ExcelPackage("testExcelTableLoadFromDataTable.xlsx"))
            {
                var sht = excelPackage.Workbook.Worksheets[0];
                var srcExcelTable = sht.Tables["图表数据源"];
                var dataTable = GetChartTable();

                var rngData = sht.Cells["A21"].LoadFromDataTable(dataTable, PrintHeaders: true, TableStyles.Medium1);
                var excelTable = rngData.GetTable();
                excelTable.Name = "图表数据源1";
                excelTable.Columns.Add(7);

                for (var i = 0; i < 7; i++)
                {
                    excelTable.Columns[i+3].Name = srcExcelTable.Columns[i + 3].Name;
                    excelTable.Columns[i + 3].CalculatedColumnFormula = srcExcelTable.Columns[i + 3].CalculatedColumnFormula.Replace("图表数据源", "图表数据源1");
                }

                excelPackage.SaveAs("result.xlsx);

            }

private static DataTable GetChartTable()
{
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("国家");
    dataTable.Columns.Add("今年值", typeof(double));
    dataTable.Columns.Add("去年值", typeof(double));

    dataTable.Rows.Add(new object[] { "美国", 368, 265 });
    dataTable.Rows.Add(new object[] { "越南", 222, 130 });
    dataTable.Rows.Add(new object[] { "巴西", 220, 180 });
    dataTable.Rows.Add(new object[] { "伊朗", 176, 230 });
    dataTable.Rows.Add(new object[] { "法国", 100, 121 });
    dataTable.Rows.Add(new object[] { "马来西亚", 89, 122 });
    dataTable.Rows.Add(new object[] { "意大利", 102, 97 });
    dataTable.Rows.Add(new object[] { "日本", 91, 78 });
    dataTable.Rows.Add(new object[] { "英国", 90, 87 });
    dataTable.Rows.Add(new object[] { "菲律宾", 78, 43 });
    dataTable.Rows.Add(new object[] { "印度", 53, 45 });
    dataTable.Rows.Add(new object[] { "韩国", 30, 34 });
    dataTable.Rows.Add(new object[] { "德国", 27, 33 });
    dataTable.Rows.Add(new object[] { "澳大利亚", 27, 23 });
    dataTable.Rows.Add(new object[] { "瑞士", 10, 11 });
    dataTable.Rows.Add(new object[] { "加拿大", 7, 6 });

    return dataTable;
}

[swmal]

We will have a look and will get back to you here.

[swmal]

I run the example in your first comment and got no error, see screenshot of the result.xlsx workbook below.

Your second problem is related to the calculated formula you are adding in this line:

excelTable.Columns[i + 3].CalculatedColumnFormula = srcExcelTable.Columns[i + 3].CalculatedColumnFormula.Replace("图表数据源", "图表数据源1");

If you remove this line the workbook will open fine. To investigate exactly why this happens in your workbook is beyond the support we provide here on Github. My advice is that you create a workbook with the intended result in Excel and then renames the workbook from .xlsx to .zip. Then open the /xl/tables/table6.xlm file in the zip library and ensure that the value you add for the calculated column formula with EPPlus has the same value.

[minren118]

sorry i make a mistake by

I made a mistake when I reorganized the sample file and wrote the address to cell A1 instead of A11

I further tested and found that any modification to the contents of the header area of ExcelTable would cause an error to the calculation formula

Comment the following code after return normal sht.SetValue(startRow, startCol + iCol, dataTable.Columns[iCol].ColumnName);

            var sht = excelPackage.Workbook.Worksheets[0];
            var dataTable = GetChartTable();
            var excelTable = sht.Tables["图表数据源"];
            var difRowsCount = excelTable.Range.Rows - dataTable.Rows.Count - 1;

            if (difRowsCount > 0)
            {
                excelTable.DeleteRow(1, difRowsCount);
            }
            else if (difRowsCount < 0)
            {
                excelTable.AddRow(-difRowsCount);
            }

            var startRow = excelTable.Range.Start.Row;
            var startCol = excelTable.Range.Start.Column;

            for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)
            {
                for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)
                {
                    if (iRow == 0)
                    {
                        //标题行
                        //excelTable.Columns[iCol].Name = dataTable.Columns[iCol].ColumnName;
                        sht.SetValue(startRow, startCol + iCol, dataTable.Columns[iCol].ColumnName);
                    }
                    //内容
                    sht.SetValue(startRow + 1 + iRow, startCol + iCol, dataTable.Rows[iRow][iCol]);

                }
            }
           excelTable.Range.Calculate();

An error has been made in the internal calculation of epplus. The following screenshot is the result after modifying the column name of ExcelTable

If i don't change the column name, everything is fine

[minren118]

Your second problem is related to the calculated formula you are adding in this line:

excelTable.Columns[i + 3].CalculatedColumnFormula = srcExcelTable.Columns[i + 3].CalculatedColumnFormula.Replace("图表数据源", "图表数据源1");

In the above code, I just test whether it is normal to create a calculation formula in the new ExcelTable after generating an ExcelTable using LoadFromDataTable. After testing, it is still abnormal.

[minren118]

I found the reason, because after changing the column name, the original calculation formula did not update automatically, because the column name in ExcelTable is a structured reference, unlike the traditional cell address, I think the better effect should be that it can adapt to the change of the column name, the column name of the calculation formula will automatically change with the column name of the table. Similar to the Excel environment.