Hello!
I’ve noticed an issue with inserting columns and changing formulas that cause other cell’s formulas to be wiped. I will include steps to reproduce the bug and a workbook/code snippet for you to reproduce the issue as well.
- Given two cells with formulas, call one of the cells “badCell” and the other cell “victimCell”. They can both have different formulas that have nothing to do with eachother. Lets say our “badCell” is cell BD18, and the victimCell is cell AS17.
- Now insert a new column at column BD, which is column 55. Thus, our “badCell” has gone from column BD to column BE, and has now become BE18.
- If you now change the formula of the badCell at BE18, the victimCell’s formula gets deleted.
However, if after step 1 above, you change the badCell’s formula to anything (including its original formula), and you continue with the steps, you will see that the victimCell does not get its formula deleted.
Please find the code snippet and attached zipped workbook below to help you reproduce the issue. You can comment/uncomment the line “oldBadCell.Formula = “=+BD$316”;” to see the issue happen/not happen.
Also please replace the filePath with wherever you store the sample file.
Please let me know if you require any more information. Thank you for your assistance!
string filePath = "";
Workbook workbook = new Workbook(filePath);
int newlyInsertedColumnNumber = 55;
Worksheet modelWorksheet = workbook.Worksheets["Model"];
Cell victimCell = modelWorksheet.Cells["AS17"];
Cell oldBadCell = modelWorksheet.Cells["BD18"];
// If the line below (which sets the oldBadCell.Formula to itself) is commented out, the
// victimCell's formula will be wiped. If it isn't commented out, the victimCell's formlua won't be wiped.
// oldBadCell.Formula = "=+BD$316";
Console.WriteLine("Did not insert column yet. The victimCell's formula is "
+ victimCell.Formula.ToString()
+ Environment.NewLine);
modelWorksheet.Cells.InsertColumns(newlyInsertedColumnNumber, 1, true);
Console.WriteLine("Column Inserted" + Environment.NewLine);
// Because a new column was inserted at column 55 which is column "BD", that column has now been
// shifted one over, becoming column 56, or column BE.
Cell newBadCell1 = modelWorksheet.Cells["BE18"];
newBadCell1.Formula = "=+B12";
Console.WriteLine("BadCell's formula changed. Now the victimCell's formula is " + victimCell.Formula.ToString());
WMT US - new.zip (133.6 KB)