InsertRow is breaking formulas

  <pre class="source"><table border="0" cellspacing="0" width="100%"><tr><td width="15"></td><td bgcolor="lightgrey" width="15"></td><td bgcolor="lightgrey"><br><font face="Lucida Console, Courier" size="2"> Dim excel As Excel = New Excel excel.Open("C:\Documents and Settings\awaters\Desktop\StratTemplate_v218.xls") Dim sheet As Worksheet = excel.Worksheets("LastPayment") sheet.Cells(9, 2).PutValue(5) sheet.Cells(10, 2).PutValue(8) sheet.Cells(11, 2).PutValue(12) 'sheet.Cells.InsertRow(12) excel.Save("C:\Documents and Settings\awaters\Desktop\NewStratReport.xls")<br/></font><br> </td></tr></table><br /><br />We are using a template and inserting values into fields and occassionally an entire row. Whenever a row is inserted it breaks the formula, pointing it to the wrong cell.<br /><br />The formula is a simple percentage, D#=C#(cell to the left)/C71 )<br /> also, C71=sum(C10:C70)<br /><br />When a row is inserted, the formula changes to, D#=C#(cell to the left)/C81 )<br /> and the C81 increments, so the next line is D#=C#(cell to the left)/C82 )<br /></pre>
    Dim excel As Excel = New Excel<br /> <br />         excel.Open("C:\Documents and Settings\awaters\Desktop\StratTemplate_v218.xls")<br /> <br />         Dim sheet As Worksheet = excel.Worksheets("LastPayment")<br /> <br />         sheet.Cells(9, 2).PutValue(5)<br />         sheet.Cells(10, 2).PutValue(8)<br />         sheet.Cells(11, 2).PutValue(12)<br /> <br />         '  sheet.Cells.InsertRow(12)<br /> <br />         excel.Save("C:\Documents and Settings\awaters\Desktop\NewStratReport.xls")<br /> <br /> This should look a bit better, for code.<br />

Could you please post your template file here? Thank you.

The file name is StratTemplate, that’s different than the code above, just as a warning. Thanks for the help.

Please try this attached fix.

The denominator has stopped the incrementing and has stopped randomly adding 10 to it’s self down the worksheet. The one problem remaining is when a row is incremented it isn’t adding the number of rows added to that denominator.

So the denominator is pointing to where is used to be, farther up the sheet.

The denominator is C$71, right? It’s absolute reference so it should not be incremented while inserting rows. You can verify it in MS Excel. To increment it, please change the denominator to C71.

Yes you are correct. I’m sorry, let me try to re-explain the problem. When a row is inserted at row 15, the denominator(C$71) is going to be pushed down by 1 row.

When using MSExcel and right clicking the row numbers, then selecting insert row, will update the formulas to point to C$72. This is where the new total is, since the insert caused everything to shift down.

Using your InsertRow function the formulas do not update. They continue to point to C$71, which is now above the Total and usually does not contain a value.

Thank you for the explanation. I fixed this issue. Please try the attached fix.

@awaters,
Aspose.Cells has replaced Aspose.Excel that is no more available and discontinued now. This new product has all the features of its predecessor as well as the latest features of different versions of MS Excel. Aspose.Cells updates functions while inserting/deleting the rows and columns as demonstrated in the following sample code:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].Formula = "=B1+C1";
cells["B1"].Value = 5;
cells["C1"].Value = 10;
workbook.Save("outputBeforeInsertRow.xlsx");
cells.InsertRow(0);
cells["A1"].Value = "Inserted row here";
workbook.Save("outputAfterInsertRow.xlsx");

For more information on inserting/deleting the rows and columns, refer to the following article.
Inserting and Deleting Rows and Columns

Download the free trial version of this product here:
Aspose.Cells for .NET (Latest Version)

For testing this product download a runnable solution here.