Excel 2007 (PIVOT TABLE) - Object reference not set to an instance of an object

Aspose.Cells Product Family
1

Hi,

I am using an Excel 2007 - created pivot.xlsx file. See Attached file.

Sheet2 has a PIVOT table who’s data is coming from Sheet1 (Has a TABLE defined)

what i want to do is replace the data in the TABLE in Sheet1 through Aspose Cells.

I execute the following C# code:

DataTable dtable = code to get data from sql…

LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
workbook = new Workbook(@“pivot.xlsx”, loadOptions);

// ref Sheet1 with the data on it
Worksheet worksheet = workbook.Worksheets[“Sheet1”];
worksheet.Clear();

// Add headers for my TABLE
worksheet.Cells.InsertRow(0); <— RUNTIME ERROR AT THIS LINE (“Object reference not set to an instance of an object”)

for (Int32 colIndex = 0; colIndex < dtable.Columns.Count; colIndex++)
{
worksheet.Cells[0, colIndex].PutValue(dtable.Columns[colIndex]);
}

worksheet.Cells.ImportDataTable(dtable, false, 1, 0, true);




I have tried this same code on an .xlsx file WITHOUT a pivot table and all works well.


I am using Aspose Cells v5.1.3.5 (a patch release i was given in another thread to fix a previous issue ref: CELLSNET-20765)

Nitin
Bell Canada









2

Hi Nitin,

We are have worked on the issue mentioned by you. The issue is there if we use the Excel file provided by you. On the other hand, if we use some other Excel file your code execute fine. This conclude that there is some issue with your file.

We will further work on this issue and will update you with our findings.

Test Code Used:
string sInputFileName = @"C:\excelTrash\Sample_1.xlsx";
string sOutputFileName = @"C:\excelTrash\Sample_Rslt.xlsx";

Workbook objWb = new Workbook(sInputFileName, new LoadOptions(LoadFormat.Xlsx)); Worksheet worksheet = objWb.Worksheets["Sheet1"];
worksheet.Cells.Clear();
worksheet.Cells.InsertRow(0);

Thanks,

3

Hi Salman,

Can u send me the file you created that works so i can test it.
Even if there is a work around and you say that i must create my pivot file in a particular way then that would be fine too - for now.

We need to be in production very soon.

Thanks for your help…

Nitin

4

Hi Nitin,

Please use the updated version Aspose.Cells5.1.3.8.zip attached. The issue has been fixed.

Thanks,

5

Hi Salman,

Thanks for the new DLL. I used it and here is what i found:

1. The runtime error at this line of code is gone now …
worksheet.Cells.InsertRow(0); <— NO MORE RUNTIME ERROR AT THIS LINE!!

but now when I open the pivot.XLS file i get the 2 errors (see attached images).

- the sheet with the new data pumped in looks good.
- but the pivot sheet is not good anymore.

Please note: I am attaching the original pivot.xls file BEFORE I run the process so you can try out for yourself.

Also here is the C# code I ran…

======================
DataTable dtable = DataServices.ExcelDataInjectorManager.ExecuteSql(excelJob.connectionString,
excelJob.adhocSqlQuery);

LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
Aspose.Cells.Workbook workbook = new Workbook(“pivot.xlsx”, loadOptions);

// delete Sheet1 which is the TABLE with data used by pivot in Sheet4
workbook.Worksheets.RemoveAt(“Sheet1”);

// Add Sheet1 again …
workbook.Worksheets.Add(“Sheet1”);

worksheet = workbook.Worksheets[“Sheet1”];

// =====================================
// Create Header Row
// =====================================
worksheet.Cells.InsertRow(0); <-- THIS IS FINE NOW - NO MORE ERROR HERE
for (Int32 colIndex = 0; colIndex < dtable.Columns.Count; colIndex++)
{
worksheet.Cells[0, colIndex].PutValue(dtable.Columns[colIndex]);
}

// Import DataTable …
Int32 worksheetInsertStartRow = 1; // NOT 0 because 0 is the header

worksheet.Cells.ImportDataTable(dtable, false, worksheetInsertStartRow, 0, true);

// =====================================================
// *** CREATE DYNAMIC TABLE …
//Adding a new List Object to the worksheet
// =====================================================
string firstCellName = worksheet.Cells.FirstCell.Name; // eg: "A1"
string lastCellName = worksheet.Cells.LastCell.Name; // eg: “F21”;
Aspose.Cells.Tables.ListObject listObject = worksheet.ListObjects[worksheet.ListObjects.Add(firstCellName, lastCellName, true)];


// =====================================================
// Adding Default Style to the table
// =====================================================
listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium10;

// =====================================================
// SAVE FILE …
// =====================================================
OoxmlSaveOptions options = new OoxmlSaveOptions(SaveFormat.Xlsx);
options.ExportCellName = true; // Must set this else does not work

workbook.Save(“pivot.xlsx”, options);



======================

Thanks
Nitin Mistry
Bell Canada

6 
Hi,

After further looking into your issue, actually, currently we did not support a pivot table whose source is some 
table name. Since it is a complex feature/task, so we need some time to implement it. We will work for it in future versions

 /* Style Definitions */
 table.MsoNormalTable
	{mso-style-name:"Table Normal";
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-priority:99;
	mso-style-parent:"";
	mso-padding-alt:0in 5.4pt 0in 5.4pt;
	mso-para-margin:0in;
	mso-para-margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"Times New Roman","serif";}


<![endif]–><span style=“font-size: 10pt; font-family: “Microsoft YaHei”,“serif”;”>.
Once we support it, we will let you know.

Sorry for any inconvenience caused!.
7

Hi Amjad,

I was thinking of a work around like this.

Is it possible to create the whole workbook dynamically?

I know how to create a TABLE dynamically using Aspose Cells,
but how would i create a pivot table dynamically that uses that TABLE?

Thanks

Nitin

8

Hi,

I think you may try to use ListObject.StartRow/StartColumn and other properties to create/find a range for the pivot table, then create your pivot table dynamically based on the range.

Thank you.

9
Amjad Sahi: 
Hi,

After further looking into your issue, actually, currently we did not support a pivot table whose source is some 
table name. Since it is a complex feature/task, so we need some time to implement it. We will work for it in future versions.
Once we support it, we will let you know.

Sorry for any inconvenience caused!.

Has it been implemented in any new version of Aspose?
10
Hi,

Thanks for using Aspose.Cells.

We support a pivot table whose source is some table name. Please download the source excel file used in this code and output excel file generated by it for your reference.

Please try the code with the latest version: Aspose.Cells for .NET (Latest Version) .

The sample code is as follows:

C#
Workbook wb = new Workbook(filePath + "a.xlsx"); PivotTableCollection pivots = wb.Worksheets[0].PivotTables; int pivotIndex = pivots.Add("=Table1", "B10", "PivotTable1"); PivotTable pivot = pivots[pivotIndex]; pivot.AddFieldToArea(PivotFieldType.Row, "aa"); pivot.AddFieldToArea(PivotFieldType.Column, "bb"); pivot.AddFieldToArea(PivotFieldType.Data, "cc"); pivot.RefreshDataFlag = true; pivot.RefreshData(); pivot.CalculateData(); pivot.RefreshDataFlag = false; wb.Save(filePath + "a_out.xlsx");