Generating large Excel (over 150mb)

Aspose.Cells Product Family
1

Hi,

I’m having an issue with aspose 4.5.1.0 where it’ll save when I try to generate large excel.
I am consistently getting memory-related error when the file size is over 150mb on the workbook.Save() function.

– Tony

2

Is this a windows application (winforms) or windows service?

3

I build it as a dll that’s used by both windows app and windows service. It is failing on both.

4

Hi,

Well, I think if you are generating very large excel file in the range 150mb or more, you require lots of memory for the process (it might be more or less ten times of the size of the generated file). Please make sure that you have sufficient memory for this task.

I have attached the latest fix for your here, in the new versions, we have optimized the component a great deal, it will take/consume less memory for the big tasks.

If you still find the issue, kindly create a sample application and post it here with all the details. Also, give us your environment information, OS, memory, .NET framework any other extension etc. We will check it soon.

Thank you.


5

Thanks for the build. We have an issue with the license.

"The subscription included in this license allows free upgrades until 19 Nov 2009, but this version of the product was released on 16 Dec 2009. Please renew the subscription or use a previous version of the product."

Would you please provide an updated license such that we can test the new build. We’re ready to renew the subscription once we confirmed the new build works.

Please feel free to send the license to my email.
We are also ready to acquire live tech support from you as we’re on a mission critical project and need the fix ASAP.

Thanks,
Tony

6

Hi,

Thank you for considering Aspose.

As your license subscription is expired, so you need to upgrade your license. But for testing purpose, you may comment the license code in your application and test the latest fix and check if it works fine for you or you may contact Aspose.Purchase to get the temporary license for this purpose. Please post your requirement in Aspose.Purchase forum by using the following link and one of our sales team member will contact you soon as per your requirement.

For Live technical support, you may use our Live Chat feature in working days to discuss any issue you face. Also, we try to provide the solutions and fixes in our support forum asap for our customers need.

Thank You & Best Regards,

7

Hi,

I tried the build you provided and I’m experiencing the same out of memory exception issue. I’m running the app on a 8gb machine trying to generate a 250mb file.

Is there anything else that we can try?


8

Hi,

Kindly create a sample application and
post it here with all the details to show the issue. Also, give us your environment
information, OS, memory, .NET framework any other extension if you have etc. We
will check it soon.

9

Hi, I've created a slimed down version of the application that continues to generate the same error. Please provide a private method to send you the application since we do not wish to post source codes on a public forum.

Thanks Tony

10

Hi,

Thank you for considering Aspose.

You may check “Keep this post private” while replying to the post and only you and Aspose Staff will be able to see your posted application. Alternately, you can also send us your sample application via email. Please follow the below mentioned steps to send us the file via email.

1: click the Contact button in the Post.

2: In the drop down list options click "Send nausherwan.aslam an Email”.

3: Zip you application, attached the application and send it.

4: Once you have done it, kindly confirm us on this thread.

Thank You & Best Regards,

11

Hi, please let me know if you received the attachment

12

Hi,

Thank you for considering Aspose.

We have received your sample application via email. We will look into your issue and get back to you soon.

Thank You & Best Regards,

13

Hi,

Thanks for your project.

After checking it, we think you can do some changes according to the following points:

  1. Please use Cell.SetSharedFormula method if the formulas in the range are very similar (See Helper_BuildColumns_3 method).

For the methods Helper_BuildColumns_1, Helper_BuildColumns_2, please set the shared formulas for each column after importing the data.

  1. If you want to make a cell empty, please check whether the cell exists first (See Helper_BuildColumns_3 method).

  2. Please release other objects. For example: please call dtInvestments.Clear() before calling Workbook.Save() method.

  3. If the value is null, could you simply leave the cell empty?

For example:

If drv("ProfitSharingPercent") Is System.DBNull.Value Then

'sheet.Cells(rowindex, 4).PutValue(0)

Else

sheet.Cells(rowindex, 4).PutValue(drv("ProfitSharingPercent"))

End If

  1. If the styles of the cell in the a row or a column are same, please simply set the row style or column style.

  2. Please use the new fix 4.8.1.9.

Private Sub Helper_BuildColumns_3(ByVal rowindex As Integer, ByVal firstsheetname As String, ByVal lastsheetname As String, ByRef sheet As Worksheet)

Dim colLetter As String

''Columns G through FM

'For i As Int32 = 6 To 168

' colLetter = Utility.ConvertNumberToLetter(i + 1)

' sheet.Cells(rowindex, i).Formula = "=ROUND(SUM('" & firstsheetname & ":" & lastsheetname & "'!$" & colLetter & "$" & rowindex + 1 & "),0)"

'Next

'sheet.Cells(rowindex, 143).Formula = ""

'sheet.Cells(rowindex, 144).Formula = ""

'sheet.Cells(rowindex, 145).Formula = ""

'sheet.Cells(rowindex, 146).Formula = ""

'sheet.Cells(rowindex, 147).Formula = ""

'sheet.Cells(rowindex, 148).Formula = ""

colLetter = Utility.ConvertNumberToLetter(6 + 1)

sheet.Cells(rowindex, 6).SetSharedFormula("=ROUND(SUM('" & firstsheetname & ":" & lastsheetname & "'!" & colLetter & "$" & rowindex + 1 & "),0)", 1, 168 - 6 + 1)

'Column EN through ES (special case)

Dim cell As Cell

Dim obj As Object

obj = Nothing

For i As Int32 = 143 To 148

cell = sheet.Cells.GetCellOrNull(rowindex, 143)

If (cell IsNot Nothing) Then

cell.PutValue(obj)

End If

Next


End Sub

And could you post how many worksheets in this workbook and how many rows and columns in each worksheet?

If you still have any problem, please post your changed project. We will check it soon.

Thank you.

14

Hello,

I made the recommended changes but I'm still getting the Out of memory error.

There is 50 sheets and each sheet has 900 rows and 160 columns

15

Hi,

Please share us the changed project. We will check it soon.

And whether the error is thrown before calling Workbook.Save method?

Thank you.

16

Hi,

Please try this fix (attached) and set a saving option.

See following sample code:

workbook.SaveOptions.ClearData = true;

workbook.Save(@“F:\FileTemp\dest.xls”);

Please let us know if you still find the issue.

Thank you.

17

Hello,

Here’s the latest app. I made all the recommended changes and used 4.8.1.11. I’m still getting the same error. Please advise on next steps to try.

Thanks,
Tony

18

Hi,

Thanks for providing us your project.

We will look into your issue and get back to you soon.

Thank you.

19

Hi,

  1. Please use Cell.SetSharedFormula method. You can use Shared formulas to replace the methods:
    Helper_BuildColumns_1,Helper_BuildColumns_2,Helper_BuildColumns_3.

Please check the attached file. We have changed Helper_BuildColumns_1 method.

  1. Please remove all codes i.e…, Cell.Formula = “”. If you call Cells(rowIndex,columnIndex), we have to create a Cell instance in the memory.

If you want to confirm the cell is null, See the following sample code:

Dim cell As Cell

Dim obj As Object

obj = Nothing

cell = sheet.Cells.GetCellOrNull(rowIndex, columnIndex)

If (cell IsNot Nothing) Then

cell.PutValue(obj)

End If
  1. Please do not use Cell.Style property. Please use the following code to replace it.
Style style = cell.GetStyle()

'.....

cell.SetStyle(style)

Hopefully it will help your resolve the issue.

Kindly let us know if you still find some issues, we will check it soon.

Thank you.