Hi,
For:
"The values are lost in second file which contains Center Summary. "
The reason is simple, your worksheet “Center Summary” contains a formula that references to the first worksheet in the Excel file, so when you copy that worksheet in another Workbook, this reference would not evaluated fine and you will get #REF! error for the formula value in A4 cell.
Well, you may put the calculated value into that cell before copying this worksheet to other Workbook as a workaround, see the sample code below.
Sample code:
var inputDocument = new Workbook(“e:\test2\Testing_Split_ReferOtherWorksheets.xlsx”);
var outputDocument = new Workbook();
inputDocument.CalculateFormula();
var sourceWorksheet = inputDocument.Worksheets[“Center Summary”];
foreach (Aspose.Cells.Cell cell in sourceWorksheet.Cells)
{
if (cell.IsFormula)
{
cell.PutValue(cell.Value.ToString(), true);
}
}
var destinationWorksheet = outputDocument.Worksheets.Add(“Center Summary”);
destinationWorksheet.Copy(sourceWorksheet);
outputDocument.Save(“e:\test2\output2_Center Summary.xlsx”);
Thank you.
Hi,
Got below questions
1. Doing inputDocument.CalculateFormula(); for the workbooks that doesn't have formulas causes any issues.
2. Is there any worksheet level function that checks and gives boolean value i.e., FormulaExistsInWorksheet ?
3. sourceWorkSheet.Cells checks howmany cells, is it a DataRange bound check?, i.e., if data is there in 5 rows, 5 columns it checks 25 cells only?
4. Instead of checking cell.IsFormula for each cell, is there any method that gets Range bound cells values and copy's to the new worksheet?
Thanks,
Subba
Hi,
1) There is no issue at all for Workbook.CalculateFormula whether there are formulas in the worksheet or there are no formulas in the worksheet.
2) I am afraid, there is no such method in Aspose.Cells for .NET API.
3) Your understanding is correct.
4) You can simply use Range.Copy(Range,PasteOptions) overloaded method to implement your need
See the simple code segment here for your reference:
Cells cells = workbook.Worksheets[0].Cells;
Range range = cells.CreateRange(“B1:G1”);
Range dRange = cells.CreateRange(“A10:F10”);
PasteOptions options = new PasteOptions();
options.PasteType = PasteType.AllExceptBorders;
options.Transpose = true;
dRange.Copy(range, options);
Thank you.
I have followed the suggestion given to use CalculateFormula method.
var inputDocument = new Workbook("e:\\PROBLEM_EXCEL_072811.xlsm");
inputDocument.CalculateFormula();
With the attached Workbook, we are getting outofmemory exception while calling CalculateFormula, Can you see what is the reason for that?
Thanks,
Subba
Hi,
I am currently using 5.1.1.0; after keeping this dll I got one error in my solution
LoadFormat.Excel2003XML didn't found; Can I use LoadFormat.SpreadsheetML in place of Excel2003XML?
Are there any other issues I should understand for upgrading to this dll. The 5.1.1.0 is in PROD right now.
Thanks,
Subba
Hi,
Yes, you can use LoadFormat.SpreadSheetML in place of Excel2003XML.
This issue isn't fixed in release Aspose.Cells for .NET v6.0.1.3.
We are getting System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown with the attached spreadsheet while calling CalculateFormula() method. Please resolve this ASAP as it is effecting in production environment.
Thanks,
SubbaM
Hi,
I have found the problem with the latest version:
Aspose.Cells
for .NET v6.0.1.7
I waited for few minutes and CalculateForumla() did not return. We will look into this problem and resolve it asap.
This spreadsheet contains macros. Are you running them while calling CalculateFormula(). If yes, how to disable running macros on a worksheet.
Thanks,
SubbaM
Hi,
Aspose.Cells for .NET does not support running macros. So I am not running it. Below is my code how I tested your file.
C#
string path = @“F:\Shak-Data-RW\Downloads\ES+Dulles+March+2011.xls”;
Workbook workbook = new Workbook(path);
workbook.CalculateFormula();
Hi,
Iam getting below error while using this dll. Is there a workaround for testing this. Thanks.
The subscription included in this license allows free upgrades until 11 Aug 2011, but this version of the product was released on 24 Aug 2011. Please renew the subscription or use a previous version of the product.
Hi,
so, if we don’t have budget to upgrade the software license, which is several thousand dollars, since we have the total.net suite, are we just stuck with this, even though we had to identify the issue for you and this bug locks up the server? I don’t expect a free upgrade, just a bug fix that works with my version of the code. seems a bit of a steep cost for a bug fix. we aren’t looking for any new features. are there no other options?
Hi,
Please post your question on Aspose.Purchase forum. Aspose Sales Staff members will guide you better and let you know any available and viable options.
The issues you have found earlier (filed as 29461) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
I tried with the V7 dll. Now the calculateformula() isn't throwing exception but while saving the output document it is throwing exception.
I am taking out the worksheets (INSTrialBalance,INSBalance Sheet, INSSummary, INSRooms, INSFood, INSOther) from the attached excel and saving them as separate files. But while saving the document it fails. Can you please investigate what is the root cause for this.
Hi,