Free Support Forum - aspose.com

Multiple sheet calculation in other sheet in same workbook

Hi Team,

I am one workbook with four worksheet.

In sheet one contains 10 values like 1,1,1,1,1,1,1,1,1,1
In sheet Two contains 10 values like 2,2,2,2,2,2,2,2,2,2
In sheet Three contains 10 values like 3,3,3,3,3,3,3,3,3,3,3

In sheet Four it should add like 6,6,6,6,6,6,6,6,6,6


Addition is done in a1 sheet one + a1 sheet two + a1 sheet three

1 + 2 + 3 = 6

In above sheet whether dynamically placing values and calculation is possible?

Thanks & Regards,
Saravanan Mani

Hi,


Thanks for providing us some details.

Well, you have to specify the formulas in the sheet Four manually using Aspose.Cells APIs as there is no automatic way or relevant feature either in MS Excel or in Aspose.Cells to calculate first three sheets’ cell values to get results in fourth sheet. See the documents on how to apply and calculate the formulas in the workbook:
http://www.aspose.com/docs/display/cellsjava/Using+Formulas+or+Functions+to+Process+Data
http://www.aspose.com/docs/display/cellsjava/Calculating+Formulas

e.g
Sample code:

Workbook workbook = new Workbook(“Book1.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(3);
Cell cell = worksheet.getCells().get(“A1”);
cell.setFormula("=Sheet1!A1+Sheet2!A1+Sheet3!A1");

workbook.calculateFormula();

System.out.println(cell.getStringValue());


Hope, this helps a bit.

Thank you.