Copied Worksheet Not Calculating Formulas

Aspose.Cells Product Family
1

Hello,

I’m running into an issue where I copy a worksheet from a source workbook to a target workbook, the worksheet that’s copied in the target workbook won’t have its formulas calculated. Details, code to replicate the issue, and sample workbooks are shown below.

Details

  1. We have a source workbook with a source worksheet. All the formulas are calculated/working correctly in this source.
  2. We have a target workbook without the source worksheet. This is the workbook we want to copy the source worksheet to.
  3. We use Aspose.Cells code to copy the source worksheet from the source workbook to the target workbook. The target workbook now has a copy of the worksheet. However, in the target workbook, a bunch of the formulas in the copied worksheet aren’t calculated.

Observations

  1. Clicking “Calculate” in Excel does not fix these formulas.
  2. The formulas are actually correct, they are just not evaluated for some reason. This can be proved by opening the target workbook in Excel, navigating to the copied worksheet, and using Excel’s shortcut CTRL H, replace “=” with “=”, replace all. And the formulas now calculate correctly.

Code

string source = @"";
string target = @"";

Workbook workbookSource = new Workbook(source);
Workbook workbookTarget = new Workbook(target);

Worksheet sourceWorksheet = workbookSource.Worksheets["DCF (Quarterly)"];
int newWorksheetIndex = workbookTarget.Worksheets.Add();

var targetWorksheet = workbookTarget.Worksheets[newWorksheetIndex];
targetWorksheet.Copy(sourceWorksheet);
workbookTarget.Save(@"");

In the code abolve, please fill in the source and target variables with filepaths to the source.xlsx and target.xlsx workbooks that I’ve attached to this issue.

Also please fill in the target filepath in the last line of code workbookTarget.Save().

If you open up final saved target workbook, you’ll see that the worksheet Sheet6 has some formulas that are filled in and some that aren’t. Specifically, if you look at rows 22 and 23 on the worksheet Sheet6, most of them are populated by formulas but show up as - dashes.

Now if you use Ctrl H, and in "Find what: " type in =, and “Replace with:” type in = as well (replace all equal signs with equal signs) and click on the Replace All button, you’ll find that rows 22 and 23 are now populated by values. Their formulas remain the same but are now calculated.

Thank you and please find the Test workbooks attached in a zip file. Please see the note below from our Legal team regarding these test workbooks:

Canalyst considers the Excel file attached hereto, including the information, data and content contained therein (the “Model”), to be Confidential Information as defined within the Aspose Pty Ltd’s End User License Agreement last revised January 19, 2021. By accessing the Model, Aspose Pty Ltd. acknowledges and agrees that the Model has been provided to it solely for the purposes of resolving the technical issues related thereto and shall not be used by it, its employees, contractors or agents for any other purpose than as stated herein.

Thank you so much and please let me know if you have any questions.

Test Workbooks.zip (1.2 MB)

2

@jasonleecanalyst,
We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48023 - Copied worksheet not calculating formula

Regarding the confidentiality of the data, we strictly follow the legal bindings and do not share data with anyone else except the authorized staff here working on this issue. Once the issue is resolved, data is deleted.

3

@jasonleecanalyst,

To make the copied formulas calculated automatically by MS Excel when opening the output file, please set the property before saving in your code:
e.g.
Sample code:

.....
workbookTarget.Settings.ReCalculateOnOpen = true;

it will works fine.

Let us know your feedback.

4

@Amjad_Sahi

Thank you, that worked! However, we did not have to go through that step before. Is this step necessary for every workbook? Or is there some specific reason why this is happening now?

5

@jasonleecanalyst,
We think there is no visual option available in MS Excel for the underlying feature, it is done internally. When you set this attribute to true, MS Excel always recalculates the formulas when opening the file into it. When you set it to false, MS Excel will skip calculating the formulas in the spreadsheet. The default behavior of MS Excel is that it will calculate all the formulas in the workbook when opening an Excel file into it.

7

So I actually just confirmed that this is not an issue in 21.2 but is an issue in 21.3. So is there some change that happened between the two?

8

A further observation I’ve made is that using both 21.2 and 21.3, before I save and open the workbook, the workbookTarget.Settings.ReCalculateOnOpen is false in both cases. However after saving and opening in 21.2, it is recalculated whereas for 21.3, it isn’t.

9

@jasonleecanalyst,

I have tried the scenario using Aspose.Cells for .NET 21.2, 21.3, and 21.4 but could not observe any difference/issue. All three versions behave according to the ReCalculateOnOpen property value. When I do not set this property, it is false by default and hence formulas are not calculated in Sheet6 of the output file for all the above-mentioned versions. Similalry when this property is set to true before saving and then output files are opened in MS Excel, formulas are calculated as expected.

As the latest version is working fine at your end, you may please use it for appropriate output.