Cross sheet formula update programmatically

Aspose.Cells Product Family
1

I already have a file where there are 2 sheets, sheet 1 & sheet 2

Now in sheet 1 there is a formula which sums all the column entries in sheet 2
i.e. suppose SUM(A2:A4) => Formula in Sheet - 1

I when I insert a new row in sheet 2, suppose at row A2
the formula in sheet 1 should also update to SUM(A2:A5)

This happens natively in excel, my requirement is to achieve is programmatically.

2

@sudiprana

To programmatically update cross-sheet formulas in Aspose.Cells when you insert a new row in another sheet, you can utilize the built-in functionality of the library that automatically updates formulas when rows or columns are inserted or deleted.

Here’s a sample code snippet demonstrating how to achieve this:

using Aspose.Cells;

class Program
{
    static void Main()
    {
        // Create a new workbook
        Workbook workbook = new Workbook();
  // Add a second worksheet
        Worksheet sheet2 = workbook.Worksheets.Add("Sheet2");


        // Access the first worksheet and add a formula that sums a range in the second worksheet
        Worksheet sheet1 = workbook.Worksheets[0];
        sheet1.Cells["A1"].Formula = "=SUM(Sheet2!A2:A4");

      
        // Add some values to the second sheet
        sheet2.Cells["A2"].PutValue(10);
        sheet2.Cells["A3"].PutValue(20);
        sheet2.Cells["A4"].PutValue(30);

        // Calculate the formulas in the workbook
        workbook.CalculateFormula();

        // Print the value of the formula in the first sheet
        Console.WriteLine("Value of A1 before inserting a row: " + sheet1.Cells["A1"].Value);

        // Insert a new row at index 2 in the second sheet
        sheet2.Cells.InsertRow(2);
        sheet2.Cells["A2"].PutValue(40); // Add a new value in the newly inserted row

        // Recalculate the formulas to update the references
        workbook.CalculateFormula();

        // Print the updated value of the formula in the first sheet
        Console.WriteLine("Value of A1 after inserting a row: " + sheet1.Cells["A1"].Value);

        // Save the workbook
        workbook.Save("UpdatedWorkbook.xlsx");
    }
}

Explanation:

  1. Workbook Creation: A new workbook is created, and two worksheets are added.
  2. Formula Setup: A formula is set in the first sheet that sums a range from the second sheet.
  3. Row Insertion: A new row is inserted in the second sheet, which shifts the existing rows down.
  4. Formula Recalculation: After inserting the row, the formulas are recalculated to reflect the updated range.
  5. Output: The values before and after the row insertion are printed to demonstrate the change.

This approach ensures that the formula in the first sheet updates automatically to include the new row in the second sheet, similar to how Excel behaves natively.

For more details on handling formulas and dynamic updates, you can refer to the Aspose.Cells documentation and the Workbook.RefreshDynamicArrayFormulas method for further insights.

Sources:

[1]: Inserting and Deleting Rows and Columns
[2]: Workbook.RefreshDynamicArrayFormulas | Aspose.Cells for .NET API Reference

3

@sudiprana,

Please see the following sample code with template Excel file (attached in the zipped archive) to accomplish your task for your reference. The output Excel file is also included in the zipped archive.
e.g.,
Sample code:

       // Load workbook
       Workbook workbook = new Workbook("d:\\files\\Bk_test1.xlsx");

       Worksheet sheet1 = workbook.getWorksheets().get("Sheet1");
       Worksheet sheet2 = workbook.getWorksheets().get("Sheet2");

       // Set formula in Sheet1!A1 = SUM(Sheet2!A2:A4)
       sheet1.getCells().get("A1").setFormula("=SUM(Sheet2!A1:A4)");

       // Insert a new row at position 2 (i.e., before A2 in Sheet2)
       // Insert 1 row at index 1 (i.e., row 2 in Excel).
       // Also set  updateReference to true,  so formula would be updated accordingly.
       sheet2.getCells().insertRows(1, 1, true);

       // Optionally add a value to the new row for testing
       sheet2.getCells().get("A2").putValue(15);

       // Calculate formulas (optional but good for validation)
       workbook.calculateFormula();

       // Save the workbook
       workbook.save("d:\\files\\output1.xlsx");

       // Debug print updated formula (should now be SUM(Sheet2!A1:A5))
       System.out.println("Updated Formula: " + sheet1.getCells().get("A1").getFormula());
       System.out.println("Calculated Value: " + sheet1.getCells().get("A1").getDoubleValue());

console output:

Updated Formula: =SUM(Sheet2!A1:A5)
Calculated Value: 115.0

files1.zip (13.7 KB)

Hope, this helps a bit.