Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Mar 10, 2015 10:45 am

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
Attachments
CalculateData_Error.png
CalculateData_Error.png (65.75 KiB) Viewed 3709 times

lucagiovanni
 
Posts: 12
Joined: Thu Aug 14, 2014 6:59 am

Wed Mar 11, 2015 3:28 am

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
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Tue Mar 17, 2015 7:34 am

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
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Tue May 26, 2015 10:57 am

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

sgrech
 
Posts: 5
Joined: Tue May 26, 2015 8:17 am

Mon Oct 22, 2018 4:13 am

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.

pradhan.subhankar@gmail.com
 
Posts: 8
Joined: Fri Oct 12, 2018 12:42 am

Mon Oct 22, 2018 6:58 am

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
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Mar 24, 2020 1:03 pm

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;

dmaciasaviles
 
Posts: 6
Joined: Wed Oct 09, 2019 11:56 am

Wed Mar 25, 2020 3:15 am

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
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Wed Mar 25, 2020 9:58 am

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.

dmaciasaviles
 
Posts: 6
Joined: Wed Oct 09, 2019 11:56 am

Wed Mar 25, 2020 10:31 am

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
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Mon Mar 30, 2020 7:57 am

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
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS