Updating formulas in other sheets

I have a very simple use case that is illustrated with the attached excel files. Aspose-issue1.xlsx is the excel file before I run my code, aspose-issue1-mod.xlsx is after.

I have data in sheet 1 and a cell in sheet 2 that refers to data in sheet 1.

If I insert a row into sheet 1 the formula does not update in sheet 2 (as it would if I did this directly in excel).

The code I use is very straightforward:

Workbook wb = new Workbook(new FileInputStream(new File(“C:\work\source\aspose-issue1.xlsx”)));
WorksheetCollection sheets = wb.getWorksheets();
Worksheet sheet1 = sheets.get(“Sheet1”);
sheet1.getCells().insertRow(0);
wb.save(“C:\work\source\aspose-issue1-mod.xlsx”);

How do I make the formulas update automatically?

Thanks,
John

Hi,


You are missing the CalculateFormula() function call before saving the workbook, as follow:

Workbook wb = new Workbook(new FileInputStream(new File(“C:\work\source\aspose-issue1.xlsx”)));
WorksheetCollection sheets = wb.getWorksheets();
Worksheet sheet1 = sheets.get(“Sheet1”);
sheet1.getCells().insertRow(0);
//Calculates the result of formulas
wb.CalculateFormula();
wb.save(“C:\work\source\aspose-issue1-mod.xlsx”);

I hope this helps you.

Hi,


You may use Cells.insertRange() method with updateReference Boolean parameter on for your needs. See the sample updated code segment for your reference.

Sample code:


Workbook wb = new Workbook(new FileInputStream(new File(“C:\work\source\aspose-issue1.xlsx”)));
WorksheetCollection sheets = wb.getWorksheets();
Worksheet sheet1 = sheets.get(“Sheet1”);
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = ca.StartRow;
ca.StartColumn = 0;
ca.EndColumn = sheet1.getCells().getMaxDataColumn();
sheet1.getCells().insertRange(ca, 1, ShiftType.DOWN, true);

wb.save(“C:\work\source\aspose-issue1-mod.xlsx”);

Thank you.

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use Cells.InsertRows(int rowIndex, int totalRows, bool updateReference) method.

It should fulfill your needs. Let us know your feedback.

Thanks, insertRows(int,int,bool) worked.