Problem using SetSharedFormula when formula references other worksheets

Aspose.Cells Product Family
1

Hi Team,

I create reports using excel workbooks that might contain hundreds of formulas. To retain these formulas in my reports after calling wsheet.Cells.ImportDataTable(), I keep track of all columns that contain formulas. After pasting data using ImportDataTable, I put the formulas back into excel using SetSharedFormula().

This works awesome.

BUT. I ran into an issue today. The above method does not work when your formula contains reference to another worksheet in the same workbook.

Here is a more concrete example:

Column 121 should contain formula like this for all cells in it. Say, for cell(9, 121):
“=‘Holdings I - 1%’!DR10+‘Holdings I - Promote’!DR10”

After using the formula sharing API, The formula should be updated for all rows in that column:
wsheet.Cells(9, 121).SetSharedFormula(formulaString, 56, 1)
Assume that formulaString contains the formula I highlighted in grey, above.

After this step I was expecting the cells from row 10 onwards to contain the following:
wsheet.Cells(15, 121).Formula = “=‘Holdings I - 1%’!DR15+‘Holdings I - Promote’!DR15”

Instead, it contains the following:
wsheet.Cells(15, 121).Formula = “=‘Holdings I - 1%’!A1+‘Holdings I - Promote’!A1”

On going through the forums, I was thinking it could be related to the following post that a colleague of mine opened some time back:

Please advise and/or fix.

thanks,
S


2

Hi,

Thanks for providing us some details.

Please post your template files with sample code to show the issue you have mentioned, we will check your issue very soon.

Thank you.

3

Ok, I am attaching a test project that i created + a sample excel workbook to help you recreate.

All you need to do is to open the project in visual studio, hit run, and press the button in the test application. It will generate the output file with the incorrect formula.

My template xlsx file has the formula: =Bought!C3+Bought!B3
but in the generated file, it becomes: =Bought!A1+Bought!XFD1

thanks,
S
4

Hi,

Thanks for providing us the project.

After an initial test using your project with template file, I am able to reproduce the issue you have mentioned. I have logged your issue into our issue tracking system with an id: CELLSNET-24204. We will soon look into your issue and get back to you.

Thank you.

5

Thanks.


The current aspose library API works fine for references within the same sheet. So, hopefully that will not be affected by your changes.

S
6

Hi,

Thanks for sharing your concerns,

We will keep/retain the existing functionality.

Thank you.

7

can we please expedite this? We have a tight deadline …

8

Hi,

I just want to inform you that our several Chinese developers are on leave these days for their New Year’s celebrations. But, hopefully, we will look into your issue when they come back in the next week from their vacations.

Thanks for your understanding!

9

Hi guys. Do you have an ETA on this?

10

Hi,

As I told you our several Chinese developers
are on leave these days for their New Year’s celebrations. So, we can provide an eta for the issue when the come back. The are due to re-join us in a couple of days from now.

Thank you.

11

Man - this is cutting really close. I guess I will have to code something myself to implement this functionality …

12

Hi,

MS Excel does not support the shared formula which refers to other worksheet.

So if the formula refers to other worksheet, please set the formula one by one.

13

I am not saying that i HAVE to use setSharedFormula. All I am asking for is how to fix the bug that i attached. I even created a sample project for you !!

14

I assume this will not be fixed anytime soon?

15

Hi,

There are two solutions:

1 - Using Cell.Copy() method, please
check the following:

public void LoadDataWithFormula(ref Worksheet wsheet, DataTable dt, int startRow, int startColumn, bool insertRows)

{


int colCount = dt.Columns.Count;

if (insertRows)

{

wsheet.Cells.InsertRows(startRow + 1, dt.Rows.Count, false);

}


//Create a record of all the formula from the current excel

Dictionary<int, string> formulaMap = new Dictionary<int, string>();


for (int i = 0; i <= colCount; i++)

{

if (wsheet.Cells[startRow, startColumn + i].IsFormula)

{

formulaMap.Add(startColumn + i, wsheet.Cells[startRow, startColumn + i].Formula);

}

}


wsheet.Cells.ImportDataTable(dt, false, startRow, startColumn, false);

foreach (int col in formulaMap.Keys)

{

string formulaString = formulaMap[col];

wsheet.Cells[startRow, col].Formula = formulaString;

}

//Now that data has been pasted, lets put the formula back

for (int i = 1; i < dt.Rows.Count; i++)

{

foreach (int col in formulaMap.Keys)

{

wsheet.Cells[startRow + i, col].Copy(wsheet.Cells[startRow, col]);

}

}

}


2 - Please
try Smartmarker. See this document.
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html