Null reference exception in Workbook.Save with pivot table

When I add a pivot table to my workbook and attempt to save it, I get a NullReferenceException. Here's the stack trace:

[NullReferenceException: Object reference not set to an instance of an object.]
Aspose.Cells.x809c66442c41384d.x98131cee83c8d626(Byte[] x5cafa8d49ea71ea1, Int32 x374ea4fe62468d0f, String xeca77d72902a8e0c) +47
Aspose.Cells.x47573edea31ad085.xe7f9fb33d0e00818(Byte[] x5cafa8d49ea71ea1, Int32 x374ea4fe62468d0f, String xeca77d72902a8e0c) +138
Aspose.Cells.x47573edea31ad085.x8c6c2e73976f5058(Byte[] x5cafa8d49ea71ea1, Int32 x374ea4fe62468d0f) +684
Aspose.Cells.xfc686d5bce097028.x26820a7897d4e940(Byte[] x5cafa8d49ea71ea1, Int32 x374ea4fe62468d0f) +99
Aspose.Cells.xfc686d5bce097028.xfe7b9a8b19a25938(MemoryStream x6e0053ba4d6bb1c4) +92
Aspose.Cells.Worksheets.x806178efa0bb6fcf(FileFormatType xab59d5b558cd3dde) +2854
Aspose.Cells.Worksheets.xc26afd5362f5c1ec(Stream xcf18e5243f8d5fd3, FileFormatType xab59d5b558cd3dde) +31
Aspose.Cells.Workbook.Save(Stream stream, FileFormatType fileFormatType) +141
AdShuffle.Reporting.ReportExcelAdapter.RenderReport(Report report) in C:\projects\AdShuffle\AdShuffle.Reporting\ReportExcelAdapter.cs:80
AdShuffle.Reporting.Report.Render(ReportAdapterBase adapter) in C:\projects\AdShuffle\AdShuffle.Reporting\Report.cs:196
AdShuffle.CommandCenter.ReportFilters.btnRunReport_Click(Object sender, EventArgs e) in C:\projects\AdShuffle\AdShuffle.CommandCenter\controls\ReportFilters.ascx.cs:640
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Here's the code that builds the worksheet containing the pivot table:

Worksheet pivotSheet = wb.Worksheets[wb.Worksheets.Add()];

pivotSheet.Name = REPORT_SHEET_NAME;

PivotTables pivotTables = pivotSheet.PivotTables;

int index = pivotTables.Add(DATA_SHEET_NAME + "!" + sourceDataRange, pivotSheet.Cells[2, 2].Name, "PivotTable");

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

pivotTable.AddFieldToArea(PivotFieldType.Data, 0);

//Draging the second field to the column area.

pivotTable.AddFieldToArea(PivotFieldType.Data, 1);

//Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Column, 2);

pivotTable.AddFieldToArea(PivotFieldType.Row, 3);

I get the NullReferenceException on this statement:

wb.Save(Stream, FileFormatType.Excel2003);

If I comment out the code that adds the worksheet that includes the pivot table, my spread is generated correctly. Any tips?

Thanks in advance,

Paul

This problem is caused by that the data in the first row of source range is blank. To create a PivotTable report, you must use data that is organized as a list with labeled columns.

Ok, I've confirmed that I have a table of data included labels, and that the range specified for the pivot table does not extend beyond the table of data, but I'm still getting the error. I'm now putting the pivot table on the same worksheet as my data. If I don't try to programatically add the pivot table, I can open up the generated document and do it in Excel without any errors, so it doesn't seem to be a problem with the data.

Could someone post some sample code that builds a pivot table against the data in this worksheet? It may be a very simple problem I'm experiencing, but the Aspose library isn't returning any kind of useful error message that would let me know exactly what the problem is.

We're definitely going to be licensing one component or another to build Excel spreadsheets, so I hope Aspose can work out for us.

I figured out my problem. I wasn't adding the "=" to the beginning of the first parameter to PivotTables.Add.

Now my code executes without raising an exception, but my pivot table is nowhere to be found!

Worksheet pivotSheet = wb.Worksheets[wb.Worksheets.Add()];

pivotSheet.Name = REPORT_SHEET_NAME;

PivotTables pivotTables = pivotSheet.PivotTables;

int index = pivotTables.Add(sourceDataRange, pivotSheet.Cells[5, 5].Name, "PivotTableFUBAR");

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;

pivotTable.AddFieldToArea(PivotFieldType.Row, 10);

pivotTable.AddFieldToArea(PivotFieldType.Row, 11);

pivotTable.AddFieldToArea(PivotFieldType.Row, 12);

pivotTable.AddFieldToArea(PivotFieldType.Data, 0);

pivotTable.AddFieldToArea(PivotFieldType.Data, 1);

pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

Could you please post your generated file here? Are you saving your file as SpreadsheetML format? Currently SpreadsheetML format doesn’t support PivotTable. Please save it as other native Excel format.

Ok, I discovered that when I was able to add the pivot table to the worksheet and then save the document, I was saving in SpreadsheetML format, which explains why it wasn't crashing and why I wasn't getting a pivot table.

I've attached the spreadsheet that I am able to build when I comment out my pivot table code.

The value of sourceDataRange = "=DataSheet!B51:BE813"
DATA_SHEET_NAME = "DataSheet"

Worksheet pivotSheet = wb.Worksheets[DATA_SHEET_NAME];
PivotTables pivotTables = pivotSheet.PivotTables;
string pivotLocation = pivotSheet.Cells[1, 1].Name;
string pivotTableName = "PivotTableFUBAR";
int index = pivotTables.Add(sourceDataRange, pivotLocation, pivotTableName);
PivotTable pivotTable = pivotTables[index];

In this sample, I'm not even adding any fields to the report, but I have the same problem if I do add fields to the report. I can step through the above code, and it is getting a reference to a worksheet and accessing the PivotTables collection of the worksheet. When I use the above code to add a pivot table to the spreadsheet. It's not until this line:

wb.Save(Stream, FileFormatType.Excel2003);

that I get the NullReferenceException. As you can see, the souce data range for the pivot table matches a range of cells including column headers in the worksheet, so I'm not sure what the null reference exception means or how I can fix it.

Paul

Hi Paul,

We will figure out the issue and response you soon.

Thank you.

Regards

Amjad Sahi

Aspose Nanjing Team

Dear Paul,

I figure out a bit and checked your template file. I think, the problem may relate to some blank cells in your pivot table source data. Could you try to fill all the cells in your labeled fields of your source range? I fill all the fields with data and its working fine.

Thank you.

Amjad, I really appreciate all of the assistance!

Your last suggestion was absolutely right. I'm now populating all cells in the data source with values, and I can save the worksheet after defining a pivot table.

Here's my current problem. When the browser attempts to open the spreadsheet, I get the following message:

PivotTable report 'PivotTableFUBAR' on '[Client_Summary_Report_4(1).xls]DataSheet' was discarded due to integrity problems.

I've tried different combinations of pivot fields in different parts of the report and I haven't been able to get a pivot table to display. It even happens when I don't add any fields to the pivot table.

I did a search on this site for discarded integrity and didn't get any matches.

Hi,

I generate your pivot table report and open into my browser without any difficulty what so ever. I think the problem may concern to https/ssl issue. I think the problem is with your browser. So, we have to change the http response headers to save and open the generated file into the browser.

Could you please check and try:

HttpsSsl

Kindly let us know if every thing is fine now.

Thank you.

Ok, here's a little more information on what happens when I add the pivot table:

1. I get a Microsoft Excel dialog that says "Unable to open file"

2. I get an Excel dialog that says "[random_file_name].xls could not be found. Check the spelling of the file name and verify that the file location is correct.

3. Another "Unable to open file" dialog.

4. A third "Unable to open file' dialog.

5. Then I get to a dialog that opened initially, titled "Repairs to [actual_name_of_spreadsheet].xls". It reads "PivotTable report 'PivotTableFUBAR' on [actual_name_of_spreadsheet].xls was discarded due to integrity problems.

If I comment out the part of my code that adds the pivot table to the workbook, the above events do not occur, and the spreadsheet is displayed in the browser as expected.

Would you expect this behavior under the https scenario you described?

Hi,

Thanks for the information.

Are you accessing your .aspx page remotely on the client machine or implementing on the server.

We will figure out the issue and response you soon.

Thank you.

Hi Paul,

1. I get a Microsoft Excel dialog that says "Unable to open file"

2. I get an Excel dialog that says "[random_file_name].xls could not be found. Check the spelling of the file name and verify that the file location is correct.

3. Another "Unable to open file" dialog.

4. A third "Unable to open file' dialog.

5. Then I get to a dialog that opened initially, titled "Repairs to [actual_name_of_spreadsheet].xls". It reads "PivotTable report 'PivotTableFUBAR' on [actual_name_of_spreadsheet].xls was discarded due to integrity problems.

Could you please tell us what was the end result after your above described sequence of operations? ( I mean the generated worbook is blank, partially generated or ok or something else). Could you also post us here your generated excel file.

We really appreciate your workaround.

Thank you.

I'm debugging locally with Visual Studio 2005, so I think it's hitting IIS or the Visual Studio web server.

Here's the spreadsheet that's being created (attached). When I open it, after dismissing all of the error dialog boxes, I get essentially the same spreadsheet I did when I didn't try to add the pivot table. It contains all of the data, but no pivot table.

Thank you,
Paul

Hi Paul,

Thanks for the file and info.

Well, We have to re-generate the file to verify the problem, Could you please create a sample project and post us here so that we may resolve the issue soon.

And by the way, could you check the link to create the pivot table report:

Creating a Simple PivotTable

Thank you.

Hi Paul,

We have worked out your problem a bit.

We will release a new fix in the end of this week, hopefully it will solve your problem.

Thanks for your patience.

I've done a little more experimentation myself and I noticed that if I try to manually create a pivot table in Excel using the table that I am building programatically, I get the same error message. So I think that narrows it down to the code I'm using to generate the table containing the raw data.

Could you run into a problem like this if you are setting the Cell.Style property incorrectly?

Paul

Hi Paul,

Thanks for your workout.

As we already told you that your problem will be resolved in our next release in the end of this week or so.

Thank you.

Hi Paul,

Could you please create a sample console project which can create the corrupted output file for reproducing your problem for confirmation and post us here. So that we can resolve the issue more precisely.

Thank you.

While I'm working on building a project that's not dependent on our database, I wanted to post the segment of code that's actually putting the values into the cells, based on the datatype:

switch (colStyle.XmlDataType)

{

case "String":

cell.Style.Number = 0;

if (value == "")

{

cell.PutValue(UNKNOWN_VALUE);

}

else

{

cell.PutValue(value);

}

break;

case "Date":

cell.Style.Number = 22;

DateTime dateTime = DateTime.Parse(value);

cell.PutValue(dateTime);

break;

case "Integer":

int intVal = 0;

Int32.TryParse(value, out intVal);

cell.Style.Number = 1;

cell.PutValue(intVal);

break;

case "Numeric":

case "Number":

cell.Style.Number = 2;

if (value == "")

{

cell.PutValue(0);

}

else

{

cell.PutValue(Convert.ToDecimal(value));

}

break;

default:

cell.Style.Number = 0;

if (value == "")

{

cell.PutValue(UNKNOWN_VALUE);

}

else

{

cell.PutValue(value);

}

break;

}