ExcelTable Has no Resize fuction,i use delete and recreate exceltable,but will fail on excel open

[minren118]

EPPlus usage

Personal use

Environment

Windows

Epplus version

7.4

Spreadsheet application

Excel

Description

Because ExcelTable does not have resize method, I want to change its address, try to delete the old one and create a new ExcelTable implementation, but after the change is saved, the Excel will report an error and delete ExcelTable

Here is the sample code

`using OfficeOpenXml;
using OfficeOpenXml.Table;
using System.Data;
using System.IO;

public void LoadDataTableIntoExcelTable(string filePath, string sheetName, string tableName, DataTable dataTable)
{
var fileInfo = new FileInfo(filePath);
using (var package = new ExcelPackage(fileInfo))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var excelTable = worksheet.Tables[tableName];

    // 获取表头位置
    int startRow = excelTable.Address.Start.Row;
    int startColumn = excelTable.Address.Start.Column;
    int endColumn = startColumn + dataTable.Columns.Count - 1;
    int endRow = startRow + dataTable.Rows.Count; // 根据 DataTable 的行数扩展

    // 如果表格已经存在，删除旧的表格
    worksheet.Tables.Delete(excelTable);

    // 定义新的表格区域
    var newRange = worksheet.Cells[startRow, startColumn, endRow, endColumn];

    // 重新创建表格
    var newTable = worksheet.Tables.Add(newRange, tableName);

    // 将 DataTable 的数据加载到新表格中
    worksheet.Cells[startRow + 1, startColumn].LoadFromDataTable(dataTable, false); // 忽略表头

    // 保存 Excel 文件
    package.Save();
}

}
`

[OssianEPPlus]

Your sample code does not create a reproducible error. It works completely as expected without Excel reporting an error and deleting the table on our end when I provide your method with a datatable.

This tells me the problem likely lies in your input. Either in the table name or the data within the data-table is causing the issue. I cannot help you determine the issue without the input data.

If the data is sensitive, commercial customers have access to our Ticket system with Priority Support which includes private file-sharing.

[minren118]

test.zip

I have a problem again. I haven't tested it enough yet. When I reference formulas across sheet, I get an error when I open Excel.

If a formula in sheet2 refers to data in sheet1, an error occurs.

[minren118]

An error is reported even if the scope of the referenced formula is not covered by the ExcelTable new range .

 static void Main(string[] args)
 {
     var filePath = "C:\\Users\\19026\\Desktop\\test.xlsx";
     var shtName = "Sheet1";
     var tableName = "表1";

     LoadDataTableIntoExcelTable(filePath, shtName, tableName);
 }


 public static void LoadDataTableIntoExcelTable(string filePath, string sheetName, string tableName)
 {
     var fileInfo = new FileInfo(filePath);
     ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
     using (var package = new ExcelPackage(fileInfo))
     {
         var worksheet = package.Workbook.Worksheets[sheetName];
         var excelTable = worksheet.Tables[tableName];

         var dataTable = excelTable.ToDataTable();
         //dataTable.Rows.RemoveAt(dataTable.Rows.Count - 1);
         //dataTable.Rows.RemoveAt(dataTable.Rows.Count - 1);
         var newValues = dataTable.Rows[0].ItemArray;
         dataTable.Rows.Add(newValues);
         dataTable.Rows.Add(newValues);
  

         //dataTable.Columns.Add("newCol1");
         //dataTable.Columns.Add("newCol2");
         // 获取表头位置
         int startRow = excelTable.Address.Start.Row;
         int startColumn = excelTable.Address.Start.Column;
         int endColumn = startColumn + dataTable.Columns.Count - 1;
         int endRow = startRow + dataTable.Rows.Count; // 根据 DataTable 的行数扩展

         // 如果表格已经存在，删除旧的表格
         excelTable.Range.SkipRows(1).Clear();
         worksheet.Tables.Delete(excelTable);

         // 定义新的表格区域
         var newRange = worksheet.Cells[startRow, startColumn, endRow, endColumn];

         // 重新创建表格
         var newTable = worksheet.Tables.Add(newRange, tableName);
         newTable.TableStyle = OfficeOpenXml.Table.TableStyles.Medium2;
         // 将 DataTable 的数据加载到新表格中
         worksheet.Cells[startRow + 1, startColumn].LoadFromDataTable(dataTable, false); // 忽略表头

         // 保存 Excel 文件
         var newPath = Path.Combine(Path.GetDirectoryName(filePath), "newfile.xlsx");
         package.SaveAs(newPath);
     }

[OssianEPPlus]

Thank you! We've now been able to reproduce the error and are looking into a fix.
A bug somehow assigns tableId = 0 after removing and adding your table. This is normally handled but something, possibly related to name handling is causing a bug.