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