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