Copy worksheet referring values in another worksheet

Aspose.Cells Product Family
1

Hi,

Please refer to the attached spreadsheet. I have one excel file(Testing_Split_ReferOtherWorksheets.xlsx), with two sheets(Pool Summary, Center Summary), Center Summary sheet refers to the values in Pool Summary sheet. I need to create two files one sheet each. The values are lost in second file which contains Center Summary.

I am using Aspose cells version 5.1.1.0 I think the copy does copies the whole sheet as it is. How the values can be copied in place of formulas.

I saw thread http://www.aspose.com/community/forums/171759/copy-paste-special/showthread.aspx#171759

are there any methods added to the product to do it simpler than mentioned in the thread above. I am using below code to get the result

var inputDocument = new Workbook(inputFileName);

var outputDocument = new Workbook();

var sourceWorksheet = inputDocument.Worksheets[name];

var destinationWorksheet = outputDocument.Worksheets.Add(name);

destinationWorksheet.Copy(sourceWorksheet);

Please respond ASAP, because the fix needs to go immediately to PROD environment. I appreciate it.

Thanks,

Subba

2

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.

3

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

4

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.

5

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

6

Hi,


I have tested your issue, it takes very high memory to perform the process. It takes very long time and the memory was going up and up. I waited for some time and then I terminated the process. I have logged an issue with an id: CELLSNET-29461. We will figure it out soon.

Thank you.

7

Hi,

We have fixed this issue.

Please download: Aspose.Cells for .NET v6.0.1.3.

8

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

9

Hi,

Yes, you can use LoadFormat.SpreadSheetML in place of Excel2003XML.

10

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

11

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.

12

This spreadsheet contains macros. Are you running them while calling CalculateFormula(). If yes, how to disable running macros on a worksheet.

Thanks,

SubbaM

13

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();

14

Hi,

Please try this fix:
Aspose.Cells
for .NET v6.0.1.9

15

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.

16

Hi,


You just comment out the licensing lines of code (your license is expired to use this latest fixed version v6.0.1.9) and test the issue if it works fine. If everything is fine, you may upgrade your subscription.

Thank you.
17

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?

18

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.

19

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.
20

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.