Problem with pivottable.CalculateData

[lucagiovanni]

hi,
i load an xls file with a pivot table, then update datasource for the pivot table and at the end i want to refresh the data in pivot tabbe.

i try this:

Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("C:\00scambio\TemplateClientiProvinceSize.xlsx")
Dim sheetDati As Worksheet = workbook.Worksheets(1)
Dim sheetPivot As Worksheet = workbook.Worksheets(0)
sheetDati.Clear()
sheetDati.InsertDataTable(ExcelDt, True, 1, 1)
workbook.NameRanges("DataSource").RefersToRange = sheetDati.Range(1, 1, sheetDati.LastRow, sheetDati.LastColumn)
Dim pt As XlsPivotTable = sheetPivot.PivotTables(0)
pt.CalculateData()
workbook.SaveToFile("C:\00scambio\ClientiProvinceSize.xlsx", ExcelVersion.Version2007)
System.Diagnostics.Process.Start("C:\00scambio\ClientiProvinceSize.xlsx")

when excel ope the saved file it return an error as show in the image. Why? where i wrong?

best regards

[Betsy]

Hello,

Thanks for your inquiry.
Please try the following code:
pt.Cache.IsRefreshOnLoad=true;

If the problem still happens, please provide your sample document for our testing.
Thanks.

Best Regards,
Betsy
E-iceblue support team

[Betsy]

Hello,

Have you tried the code I provided? Has your issue been resolved? Could you please give us some feedback at your convenience?

Thanks,
Betsy
E-iceblue support team

[sgrech]

Hello Betsy, I tried the solution you provided and it worked for me.

[pradhan.subhankar@gmail.com]

I am using Free Spire.net. I am using below code to refresh Pivotal table. Facing issue while executing the code.

Worksheet ptsheet = workbook.Worksheets["Quality Summary"];

string rangeValue = "A1:N" + Convert.ToDouble(dsallReportData.Tables["QualityData"].Rows.Count + 1);
CellRange dataRange = sheetQualityData.Range[rangeValue];

workbook.NameRanges["Table2"].RefersToRange = sheetQualityData.Range[1,1, sheetQualityData.LastRow, sheetQualityData.LastColumn];


//Get the PivotTable that was built on the data source
XlsPivotTable pt = ptsheet.PivotTables["ptSummary"] as XlsPivotTable;

//Refresh the data of PivotTable
pt.Cache.IsRefreshOnLoad = true;




While executing this code [ workbook.NameRanges["Table2"].RefersToRange = sheetQualityData.Range[1,1, sheetQualityData.LastRow, sheetQualityData.LastColumn]; ] .

I am getting object reference not set an instance of an object.

Please find the attached file for my template.

[Betsy.jiang]

Dear Subhankar,

Thanks for your email with the input Excel.
Sorry for the inconvenience our free version caused. I have confirmed that the code you mentioned worked well using Spire.XLS Pack(Hotfix) Version:8.10.2. Please download it and try again.
In addition, our sales team has sent you a one-month free license of Spire.XLS to help you remove the warning message and you could have a better evaluation on our product. Here is a guidance on how to apply license.
https://www.e-iceblue.com/Download/down ... t-now.html
If you still have the issue, please share your full code.

Sincerely,
Betsy
E-iceblue support team

[dmaciasaviles]

I am having the same problem. When i open the Excel file i am receiving an error.

This is my code:

Worksheet sheet2 = workbook.CreateEmptySheet();
sheet2.Name = "ACUMULADO";


//CellRange dataRange = sheet.Range[1, 1, sheet.LastRow, sheet.LastColumn];
int row = dt.Rows.Count;
int col = dt.Columns.Count;
string colName = GetExcelColumnName(col);
string range = String.Format("A1:" + "{0}" + row.ToString(), colName);
CellRange dataRange = sheet.Range[range];


PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
pt.EnableDrilldown = true;//Habilitar Mostra detalles

var r1 = pt.PivotFields["EMPLEADO"];
r1.Axis = AxisTypes.Row;

var r2 = pt.PivotFields["DIA_LEIDO"];
r2.Axis = AxisTypes.Column;


pt.Options.RowHeaderCaption = "EMPLEADO";
pt.DataFields.Add(pt.PivotFields["TIPO_COMUNICACION"], "Cuenta de TIPO_COMUNICACION", SubtotalTypes.Count);


pt.CalculateData();//necessary
pt.Cache.IsRefreshOnLoad = true;

[rachel.lei]

Hi David,

Thanks for your inquiry.
I simulated an Excel file and tested your scenario with the latest Spire.XLS Pack(Hotfix) Version:10.3.0, but didn't reproduce your issue. To help us further investigate it, please provide your input file and the value of "range" defined in your code.
You could upload here or send them to us(support@e-iceblue.com) via email. Thanks in advance.

Sincerely,
Rachel
E-iceblue support team

[dmaciasaviles]

Thank you, I work with the version FreeSpire.Office 4.3.1.might this be the issue?

If a five-row-range is generated then code works, however, if a 1000-row-range is created in stead it doesn´t works.

Regards,

David.

[rachel.lei]

Hi David,

Thanks for your response.
Our commercial version contains more bug fixes and is more stable than the free version, we recommend you download the latest commercial version (Spire.XLS Pack(Hotfix) Version:10.3.0) to have a test.
If the issue still happens after trying, since it's difficult to locate the issue without your input file, please share it with us for further investigation. Thanks in advance.

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hi David,

Hope you are doing well.
How is your issue now? Could you please give us some feedback at your convenience?
Thanks in advance.

Sincerely,
Rachel
E-iceblue support team