setSharedFormula is not working as Expected when referencing different worksheet

[santhosh666]

hi

I am using setSharedFormula to copy the formula to the other cells.

It is working fine when the formula uses the columns of same subsheet

ex : =E2+AP2

But when the formula Referencing Different Worksheet then setSharedFormula is not working.

One of the formula I am using is

=SUMIFS('sheet1'!H:H,'sheet1'!D:D,sheet2!$C$38,'sheet1'!G:G,sheet2!W)

Note : sheet2 is the current sheet.

My code is like below

for (int i = firstCellNumber; i <= targetCellNumber; i++) {

Cells parameterCells = currentWorkSheet.getCells();

Cell cell = parameterCells.get(1, cellNumber);

if (cell.getFormula() != null) {

cell.setSharedFormula(cell.getFormula(), parameterCells.getMaxDataRow(), 1);

}

}

Note : We found one workaround, If I define the formula in Name manager then it will work fine.

Could you please advise , how to use setSharedFormula when referencing different worksheet ?

-Thanks,

Santhosh.

[Amjad_Sahi]

Hi,

Well, for your case (as you are referencing to different sheets in the formula(s)) Shared Formula feature won't work. For your information, in MS Excel, if a formula refers to other worksheets, it will be copied to each cells and it is not applied/ added to shared formula(s) list. For confirmation, you may unzip the file (you may perform all the steps in MS Excel manually and save the file) and check the source .xml file for the sheet, you will see there is no shared formula with its attribute(s) described there. So, MS Excel actually copies the cell's formula from one cell to other cell but not set shared formulas.

For your situation, you should try to use Range.copy(sourceRange) several times accordingly, see the sample code segment for your reference and write your own code accordingly.

e.g

Sample code:

.............

Cell cell2 = parameterCells.get("A6");

Range range = parameterCells.createRange("A6");

Range range2 = parameterCells.createRange("B6:J6");

for (int r = range2.getFirstRow(); r < range2.getFirstRow() + range.getRowCount(); r++)

{

for (int c = range2.getFirstColumn(); c < range2.getFirstColumn() + range2.getColumnCount(); c++)

{

cell = formulaWorkSheet.getCells().get(r, c);

Range destRange = parameterCells.createRange(cell.getName());

destRange.copy(range);

}

}

............

Hope, it helps a bit.

[santhosh666]

hi,

In our case we will be defining the formula in the template for the first row.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Formula will be changed based on the user requirement.

In the code we just need to write the data to the template and copy the formulae from first row until the row which has data(we won’t be knowing what are the other sheets referenced in the formula it should be dynamic).

setSharedFormula is working fine if we define the same formula with a name using "Name Manager" in excel sheet.

But we don’t want to define it again and again.

Please let us know if there is any other solution.

-Thanks,

Santhosh.

[Amjad_Sahi]

Hi,

I am afraid, as I already told you the limitation when using Shared Formula feature (the limitation is put forth by MS Excel itself) referencing other worksheets. So, you have to use other workaround (we have also provided one way to cope with it already).

Thank you.