Shared formula issue

Aspose.Cells Product Family
1

Hi,

I want to use the method set formula dynamically to a range, if i add cells reference other sheets to formula, the cell in other sheets cannot be changed dynamically.

a sample code:

Cell cell = worksheets.get("Sheet1").getCells().get("B1");
cell.setSharedFormula("=A1+'Sheet2'!A1", 11, 1);

I hope the cell from other sheets also can be changed like the result as below:

B1: =A1+Sheet2!A1

B2: =A2+Sheet2!A2 ...

but after setSharedFormula, the formula in B2 cell is =A2+Sheet2!A1

I attached the related excel files.

Thanks.

2

Hi Sissi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. The cells that refers to other sheet inside the shared formula do not change.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40804.

Java


String filePath = “F:\Shak-Data-RW\Downloads\no+formula.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“B1”);

cell.setSharedFormula("=A1+‘Sheet2’!A1", 11, 1);


workbook.save(filePath + “.out.xlsx”);

3

Hi,

We have evaluated your issue further. Well, it's the limitation of MS Excel that when shared formula is referred to other sheets, the range would not be changed/ extended dynamically. So, it is not an issue with the product.

Thank you.

4

Thanks for your update.

I tried in MS Excel, the formula which referenced to other sheets can be changed dynamically.

Steps is:
1. add formula ‘=A1+Sheet2!A1’ in B1 cell in sheet1.
2. pull down from B1, the formula will be filled automatically.
3. check the cells from B2 to B11, the range which on other sheets changed dynamically.

Attached the steps, please help to investigate,thanks.
5

Hi Sissi,

Thanks for your screenshots and MS-Excel steps and using Aspose.Cells.

We have logged your comment in our database for further investigation and consideration of this issue. We will evaluate it and let you know if such a feature could be implemented by Aspose.Cells as it behaves in MS-Excel.

Once, there is some fix or other update for you, we will let you know asap.

6

Hi,

Thanks for using Aspose.Cells.

Please save the file after 3 steps, and unzip the generated xlsx file,
you will find there is no shared formula in sheet1.xml. MS Excel copies the
formula to each cells.

Please try the following code:

C#


var book = new Workbook(@“D:\Filetemp\no+formula.xlsx”);

book.Worksheets[0].Cells[“B1”].Formula = “=A1 + Sheet2!A1”;

Range range = book.Worksheets[0].Cells.CreateRange(“B1”);

Range range2 = book.Worksheets[0].Cells.CreateRange(“B2:B11”);

range2.Copy(range);

book.Save(@“D:\Filetemp\dest.xlsx”);