We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Modifying an existing excel table and preserving formulas

Hello:

I have the following program:
private static void TestTables2()
{
string path = @“C:\temp\spreadsheets\test”;
string fileName = path + “test_table_original2.xlsx”;
Workbook workbook = new Workbook(fileName);
Worksheet worksheet1 = workbook.Worksheets[0];
Worksheet worksheet2 = workbook.Worksheets[1];


// worksheet2.Cells.InsertColumn(0, false);


ListObject listObject = worksheet2.ListObjects[0];
Console.WriteLine("{0}", listObject.StartRow);

worksheet2.Cells[0, 2].Value = “Desc3”;

listObject.Resize(0, 0, listObject.EndRow, listObject.EndColumn + 1, true);

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn(“Desc3”, typeof(String)));
dt.Columns.Add(new DataColumn(“Key”, typeof(Int32)));
dt.Columns.Add(new DataColumn(“Desc”, typeof(String)));

for (int i = 1; i <= 4; i++)
dt.Rows.Add(String.Format(“Desc {0}”, i), i, String.Format(“Desc3 {0}”, i));

worksheet2.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false);

listObject.UpdateColumnName();

workbook.Save(path + “test_table2.xlsx”);

}

Run it against the attached spreadsheet and observe the following:

- in the original spreadsheet in cell A3 the formula is: =Table1[Key] and it’s value is 2.
The values in the Table1 table are:

Key Description
1 Desc 1
2 Desc 2
3 Desc 3
4 Desc 4

- in the spreadsheet produced by the program the formula in cell A3 is changed to =Table1[Desc3]. Table1 is changed to:
Desc3 Key Desc
Desc 1 1 Desc3 1
Desc 2 2 Desc3 2
Desc 3 3 Desc3 3
Desc 4 4 Desc3 4

My questions are:
Why is the formula in A3 changed at all, since it references a column in Table1 by name? Second question is there a way to prevent the formula change in case the columns in the table get shuffled?

Thank you




Hi Costa,

Thanks for your posting and using Aspose.Cells.

It is actually MS-Excel behavior. Whenever you will change the Table’s column names, it will change the formulas too.

For example, the formula =Table1[Key] will become =Table1[Desc3] because you have changed the column Key into column Desc3.

Here Key was column 0 and Desc3 is also column 0.

It means, Excel recognizes the column inside the table with its column indices not column names.