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.
- We have a source workbook with a source worksheet. All the formulas are calculated/working correctly in this source.
- We have a target workbook without the source worksheet. This is the workbook we want to copy the source worksheet to.
- 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.
- Clicking “Calculate” in Excel does not fix these formulas.
- 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.
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
target variables with filepaths to the
target.xlsx workbooks that I’ve attached to this issue.
Also please fill in the target filepath in the last line of code
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
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)