Aspose.Cells :: There is not enough memory available to create the PivotTable report

Hello aspose.cells Forums,

I have a multi-part question/inquiry...

I was using Aspose.Cells version 4.1.2.1 with an ASP.NET web app, and I have users who are using both Excel 2003 and 2007.

I am creating a PivotTable (see code below), and when the user saves the file, then opens it they receive the error message 'There is not enough memory to create the PivotTable Report.'.

I have since upgraded to the vesion 4.4.0.0, and now do not receive the above error message any longer, but the PivotTable does not even get created.

I have updated the save call to the below:

workbook.Save(fileName, FileFormatType.Excel2007Xlsx, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);

Here is my code for creating the workbook, PivotTable, etc.

------------------------------------------------------------

Aspose.Cells.License license = new License();

license.SetLicense("Aspose.Cells.Lic");

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Name = rptName + "_DATA";

worksheet.Cells.ImportDataTable(ds.Tables["TC_DATA"], true, 0, 0);

ds.Dispose();

Column col = worksheet.Cells.Columns[4];

col.Style.Custom = "mm/dd/yyyy";

workbook.Worksheets.Add();

worksheet = workbook.Worksheets[1];

worksheet.Name = rptName;

worksheet.Move(0);

Cells cells = worksheet.Cells;

Cell cell = cells["A1"];

cell.PutValue("Testing");

PivotTables pivotTables = worksheet.PivotTables;

int rowCount = ds.Tables["TC_DATA"].Rows.Count + 1;

int colCount = ds.Tables["TC_DATA"].Columns.Count + 1;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=" + rptName + "_DATA" + "!A1:P" + rowCount.ToString(), "A1", "pvtProjDetlRpt");

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

//Unshowing grand totals for rows.

pivotTable.RowGrand = false;

//Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Data, 13);

pivotTable.DataFields[0].Number = 4;

pivotTable.AddFieldToArea(PivotFieldType.Page, 2);

pivotTable.AddFieldToArea(PivotFieldType.Page, 3);

pivotTable.AddFieldToArea(PivotFieldType.Page, 6);

//Draging the first field to the row area.

pivotTable.AddFieldToArea(PivotFieldType.Row, 9);

// AutoSort the Field...

pivotTable.RowFields[0].IsAutoSort = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, 10);

pivotTable.RowFields[1].IsAutoSort = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, 1);

pivotTable.RowFields[1].IsAutoSort = true;

MembershipUser usr = Membership.GetUser();

string fileName = "PR_Mgr_ProjDetl_Emp_" + usr.UserName.ToUpper() + "_" + DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Day.ToString() + ".xlsx";

workbook.Save(fileName, FileFormatType.Excel2007Xlsx, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);

----------------------------------------------------------------

Your help is greatly appreciated, as this is causing a 'down-time' until I get a resolution to the problem.

Regards,

Mephisto.

Hi,

Thanks for explaining the issue and code,

Well, we conducted some tests, there seems to have some sort of problem when creating pivot tables in Asp.Net web solutions for xls files.

We will look into it and figure it out soon.

Thank you.

Hi Mephisto,

1,Do you get the error when you save file and open file with Excel 2003? Please post your create file,it will help us to check this issue.

2,Aspose.Cells do not support pivot table with Excel2007.

Hi Warren,

In regards to your bullet-point #1

OK, here is what I have done...

I have created two environments; one Dev and one Prod.

Dev is running the 4.4.0.0 version and Prod is running the 4.1.2.1 version.

When I open the file from Prod with Excel 2003; everything works as expected; PivotTable is created and working as coded.

When I open the file from Prod with Excel 2007; I get the attached error message (aspose.cells.Error_2007.jpg), and the PivotTable never gets created.

When I open the file from Dev with Excel 2003; I get prompted to convert the xlsx file (plug-in for Excel/Office), and the report opens. No PivotTable is created, and no errors are shown.

When I open the file from Dev with Excel 2007; the report opens with no errors, but there is not a PivotTable in the report.

I have attached four files in total, as explained below:

  1. aspose.cells.Error_2007.jpg (is the error message I get when I open the 4.1.2.1 version with Excel 2007)
  2. PR_Mgr_ProjDetl_Emp_TAPPS_2007912.xls (is the file created from the 4.1.2.1 version)
  3. PR_Mgr_ProjDetl_Emp_TAPPS_2007912_Excel2007.xlsx (is the file created from the 4.4.0.0 version that has not been opened (repaired))
  4. PR_Mgr_ProjDetl_Emp_TAPPS_2007912_Excel2007_Repaired.xlsx (is the file created from the 4.4.0.0 version that has been opened and repaired)

---------------------------------------------------------------------

In regards to your bullet-point #2

  1. Why does Aspose.Cells not support PivotTables for 2007?
  2. Will Aspose.Cells support PivotTables for Excel 2007?
    1. If so, when will Aspose.Cells support Excel 2007?

I cannot find anywhere on the site or through the search functionality where it is stated that Excel 2007 PivotTables are not supported; can you direct me to the page that does state this?

---------------------------------------------------------------------

Regards,

Mephisto.

Hi,

Thanks for the info with template files.

We will figure out the issue related pivot table soon.

And we will implement pivot table support for MS Excel 2007 .xlsx file soon.

Thank you.

Hi,

We are looking into this issue. Thanks for your patience.

Hi,

Please try this fix.

Hi Warren,

There still appears to be a problem, as explained below...

I replaced the 4.4.0.0 dll with the new 4.4.0.3 dll.

I ran the specific page (code provided in a prior posting) through debug with two different scenarios, changing the FileFormatType as follows:

  1. workbook.Save(fileName, FileFormatType.Excel2007Xlsx, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);

  2. workbook.Save(fileName, FileFormatType.Excel2003, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);

When I used bullet-point one above, the file I saved is called 'PR_Mgr_ProjDetl_Emp_TAPPS_2007914_Excel2003', but the file was returned and saved with the extension 'xlsx', and not 'xls'.

Then I opened this file, and received the error message (attached) called 'PR_Mgr_ProjDetl_Emp_TAPPS_2007914_Excel2003 - Opened.jpg'.

The file never did open, so Iwas never able to see the result of the spreadsheet/PivotTable.

------------------------------------------------------

When I used bullet-point two above, the file I saved is called 'PR_Mgr_ProjDetl_Emp_TAPPS_2007914_Excel2007Xlsx' abd the file extension is 'xlsx'.

When I open this file, I do not receive an error or anything. The file openes, but there is no PivotTable in the file.

------------------------------------------------------

That said, I am still having the same fundamental problem that the PivotTable is not being created, and now it appears that the FileFormatType of Excel2003 is returning an 'xlsx' file, and not an 'xls' file.

I have attached the files for your review.

Regards,

Mephisto.

Hi,

Thanks for the posting files.

As Warren told you that we do not support pivot tables for Excel 2007 .xlsx files, we will support it future vesions. And related pivot table support for .xls file, we found the problem, we will figure out the things soon. The problem happens only in asp.net solutions.

Thank you.

Hi,

You have to add the file extension by yourself. See following codes:

workbook.Save("dest.xls", FileFormatType.Excel2003, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);

And please try the attached fix.We have fixed another bug : Excel2007 will throw an Exception if the dest cell name is A1 and drags some fields to page area.

@mephisto,

We supported the feature (create, manipulate and refresh Pivot Tables in Excel 2007 - 2019 XLSX) with enhancements in newer versions. We recommend you to kindly give it a try to latest version of Aspose.Cells. Also, see the document for your reference:
Create Pivot Tables and Pivot Charts