Aspose.Cells - workbook.CalculateFormula()

Aspose.Cells Product Family
1

Dear All,

I need to to force the workbook to calculate all the formulas before freeze the data, getting just the values, not the formulas.

I am using the code below to freeze the data:

Public Sub FreezeWorsheetData(ByVal sheetName As String)

Dim options As New Aspose.Cells.PasteOptions

options.PasteType = PasteType.Values

Dim range = GetUsedRange(sheetName)

Dim rangeFrom = range

range.Copy(rangeFrom, options)

End Sub

Public Function GetUsedRange(ByVal sheetName) As Aspose.Cells.Range

Dim worksheet As Aspose.Cells.Worksheet = _wb.Worksheets(sheetName)

Dim minRow As Integer = worksheet.Cells.MinRow

Dim maxRow As Integer = worksheet.Cells.MaxRow

Dim minCol As Integer = worksheet.Cells.MinColumn

Dim maxCol As Integer = worksheet.Cells.MaxColumn

If maxCol < 0 Then maxCol = 0

If maxRow < 0 Then maxRow = 0

Dim cellNameTopRange As String = CellsHelper.CellIndexToName(minRow, minCol)

Dim cellNameBottomRange As String = CellsHelper.CellIndexToName(maxRow, maxCol)

Dim range = _wb.Worksheets(sheetName).Cells.CreateRange(cellNameTopRange, cellNameBottomRange)

Return range

End Function

It works fine and complete the task in 600 miliseconds. If I call before it workbook.calculateFormula() it goes in a loop in memmory and finally crash the computer.

Could you please advise.

Regards

Tony Martins

2

Hi,


I think you may just use Cells.RemoveFormulas() method for your needs to replace with calculated values in the worksheet cells.

Now come to your issue regarding Workbook.CalculateFormula(), we cannot confirm your issue unless we have your template file that you are using or if you are not using any template file we would demand a complete sample (runnable) code /sample console application(you may zip it) and attach it here to show the issue on our end. We will check it soon.

Also, please download and try our latest version/fix: Aspose.Cells for .NET v7.3.2.2

3

Dea Amjad

Please find attached the excel file I am using as test for replacing the actual Excelwrapper. I have changed for the cells.RemoveFormulas and it is much clean. The issue with the workbook.calculateFormula() persists.

I will run the new version and see if there is any changes.

Regards

Tony

4

Dear Amjad,

I have tested the latest DLL and it seems that we have the same issue.

Public Sub FreezeWorsheetData(ByVal sheetName As String)

_wb.CalculateFormula()

Dim cells As Aspose.Cells.Cells = _wb.Worksheets(sheetName).Cells

cells.RemoveFormulas()

End Sub

I can follow on the debug mode and it pass Through the _wb.CalculateFormula and go to next statement, when it goes to cells.RemoveFormulas() it does not come back.

Regards Tony

5

Hi,


Well, I tested it with your file. It returns but it take longer time (about 2,3 minutes) to complete the process and re-save the file to disk as your workbook has lots of VLookup formulas.

I think you may add a line to your code (in bold), it will surely enhance performance and it will take lessor time (it took 10-15 seconds on my normal configured pc). I have tested using the line (in bold) with your file and noticed the improvement.

Sample code:

string filePath = @“e:\test2\Template2.xls”;
Workbook _wb = new Workbook(filePath);
_wb.Settings.CreateCalcChain = false;
Worksheet worksheet = _wb.Worksheets[“Sheet1”];
_wb.CalculateFormula();
Cells cells = worksheet.Cells;
cells.RemoveFormulas();
_wb.Save(“e:\test2\outTemplate2.xls”);

Could you try it and let us know the result. Also, please make sure that you are using v7.3.2.2 (I gave the link in my previous reply).

Thank you.
6

Dear Amjad

The line workbook.Settings.CreateCalcChain = false, has greatly improved the perfomance. I received another message informing that if I will use cells.RemoveFormulas( ) I dont need to use workbook.CalculateFormulas(0 as the Cells.removeFormulas( ) calculate it before removing the formula.

Could you please confirm that this is a correct, So I can remove the CalculateFormulas( ) and it will improve even more the performance.

Regards

Tony

7

Hi,

Thanks for your posting

Although, if you remove CalculateFormula() then RemoveFormulas() does not hang at all. But it works only if you do not want to change any value of the cells being used in your formulas.

I have tested it again using the simple source xlsx file and changed the values of some cells and used the RemoveFormulas() method and found that it is not working.

So, I am afraid, you will have to use the Workbook.CalculateFormula() method before calling RemoveFormulas() to get correct results.

8

Thank Shakeel,

I will carry on using the CalculateFormula( ). I was just informing my manager how good is the support provided by your team. Thanks again.

Regards

Tony Martins

9

HI Shakeel,

In excel I can just pass a 2 dimension array to the range:

range.Value(Excel.XlRangeValueDataType.xlRangeValueDefault) = value

When I try to use the same in Aspose Range it complains that is i not Array was not a one-dimensional array.

I am Using Range.Value = value.

DO I need to iterate through the range and put the value on teh respecticve cells?

Regards

Tony

10
Tmartins007:

HI Shakeel,

In excel I can just pass a 2 dimension array to the range:

range.Value(Excel.XlRangeValueDataType.xlRangeValueDefault) = value

When I try to use the same in Aspose Range it complains that is i not Array was not a one-dimensional array.

I am Using Range.Value = value.

DO I need to iterate through the range and put the value on teh respecticve cells?

Regards

Tony

Hi,

I have checked your issue. I am afraid, you will have to set the value of the range cells one by one. I was not able to set multidimensional array on a range object and I get the same exception.

You can use the following properties to find out where the range starts and ends

  1. Range.FirstColumn
  2. Range.FirstRow
  3. Range.ColumnCount
  4. Range.RowCount

After that you can access cell in a loop and put values e.g

worksheet.Cells[rowIndex, colIndex].PutValue(4);
11

HI Shakeel

Using your advice I am populating the range with a loop, but I could see that in old forum that on version 5.3.1.1 the range was able to receive as value a 2 dimension array.

"Please try the new fix Aspose.Cells for .NET v5.3.1.1. We have added Range.Value property.
It only supports simple values or a two-dimension Array object data. 03-21-2011"
https://forum.aspose.com/t/130117

The Excel Wrapper I am replacing with Aspose use this feature a lot,

range.Value = value (2 dimension Array)

I am using this at the moment

Dim x = 0

Dim y = 0

For i = range.FirstRow To (range.FirstRow + range.RowCount) - 1

For j = range.FirstColumn To (range.FirstColumn + range.ColumnCount) - 1

worksheet.Cells(i, j).PutValue(value(x, y))

If y < value.GetUpperBound(1) Then y = y + 1

Next

If x < value.GetUpperBound(0) Then x = x + 1

Next

but I can see my manager questioning this. Can you have a look please why the feature was dropped or if it will be fixed in the near future.

Regards,

Tony Martins

12

Hi,

Thanks for using Aspose.Cell.

I have looked into this issue and I think, this feature is not supported or needs to be fixed.

So I have added a New Feature Request for this issue in our database with the issue id: CELLSNET-41127

We will look into it and implement it. Once there is some update for you or fix is available against this issue, we will let you know asap.

Below is the sample code that I have used to test this feature but it throws the exception

C#


Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];


int[,] twoDarray={{2,4},{5,3},{6,7}};


Range range = worksheet.Cells.CreateRange(“A1:B3”);


range.Value = twoDarray;


workbook.Save(“output.xlsx”);

Exception:
An unhandled exception of type 'System.ArgumentException' occurred in mscorlib.dll

Additional information: Array was not a one-dimensional array.
13

HI Shakeel, I have tested the worksheet.cells.

ImportArray(stringArray(,) As String, firstRow As Integer, firstColumn As Integer)

It works for String and Doubles but I need for Dates and Objects, maybe this is another route.

With regards, Tony

14

Hi,


We have ImportObjectArray and ImportTwoDimensionArray methods of Cells collection that you may try them for your needs.

See an example below.

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

//Put a string value into a cell
sheet.Cells[“A1”].PutValue(“Import an object Array”);

//Create an object array and fill it with some values
object[] obj = { “Tom”, “John”, “kelly”, 1, 2, 2.8, 5.16, true, false };

//Import the object array to the sheet cells
sheet.Cells.ImportObjectArray(obj, 1, 0, false);

//…

See the document for complete reference on how/which different sources/objects can be imported by Aspose.Cells for .NET APIs:
Note: We don’t provide any method regarding importing Dates Array. For your information, Dates are stored in numeric notations in MS Excel, so once you import your date values, you may specify your desired formatting accordingly, see the topic for your reference:

Thank you.

15

Thanks Amjad,

I am able to to use the importArray2Dimension and importArrayObject to replace the excel.range.Value.

Regards, Tony

16

Hi,


Good to know that you got it working with the two methods for your needs.

Thanks and have a good day!
17

Hi,

We have fixed this issue. i.e Assigning 2D array to Range.Value

Please download and try this fix: Aspose.Cells for .NET v7.3.2.4 and let us know your feedback.

Please see the test code below. I have attached the output xlsx file and screenshot for your reference.

C#


Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];


int[,] twoDarray = { { 2, 4 }, { 5, 3 }, { 6, 7 }, {8, 9}, {11, 12}};


Range range = worksheet.Cells.CreateRange(“A1:B3”);


range.Value = twoDarray;


workbook.Save(“output.xlsx”);


Screenshot:
18

The issues you have found earlier (filed as CELLSNET-41127) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.