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.

Fri Mar 06, 2015 11:25 pm

hi,
is there any way to tell Spire.XLS to use the classic layout for pivot table?
haw i can say Spire.XLS to adjast column width in pivot table?
what is the command to compress / expand the fields in the pivot table?

I can add a calculated column with the difference between two columns in pivot table?
i try << pt.PivotTables(0).CalculatedFields.Add("Delta %", "Ricavi-Costi") >>
but the function returns the error << Riferimento a un oggetto non impostato su un'istanza di oggetto. >>
Why?

There is a manual for Spire.XLS with all the functions and usage?

thanks in advance

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

Mon Mar 09, 2015 8:38 am

hi,

Thank for your inquiry. Here are the answer for your enquiry as below.
lucagiovanni wrote:is there any way to tell Spire.XLS to use the classic layout for pivot table?

Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile("pt.xlsx");
Worksheet sheet = workbook.Worksheets["Pivot Table"];
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pt = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark9;

lucagiovanni wrote:haw i can say Spire.XLS to adjast column width in pivot table?

Code: Select all
pt.CaculateData();
sheet.AutoFitColumn(1);

lucagiovanni wrote:what is the command to compress / expand the fields in the pivot table?

http://www.e-iceblue.com/Tutorials/Spir ... -in-C.html
lucagiovanni wrote:I can add a calculated column with the difference between two columns in pivot table?
i try << pt.PivotTables(0).CalculatedFields.Add("Delta %", "Ricavi-Costi") >>
but the function returns the error << Riferimento a un oggetto non impostato su un'istanza di oggetto. >>
Why?

Sorry that at present Spire.Xls doesn't support the feature to add the CalculatedFields.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Mar 09, 2015 4:01 pm

but then what is the method "sheetPivot.PivotTables (0) .CalculatedFields.Add"

I saw that if there is a calculated field I can change the formula. I can delete a calculated field already?

plan to implement the method to add calculated fields?

for classic layout I meant if it was possible to automatically activate the option " Layout classico tabella pivot (consente il trascinamento di campi nella griglia" as shown in the attached file

another question? can i update source data of an existing pivot table? how?

best regards
Attachments
OpzioniTabellaPivot.png
OpzioniTabellaPivot.png (22.86 KiB) Viewed 5104 times

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

Tue Mar 10, 2015 8:43 am

Hello,

Although there is the method "sheetPivot.PivotTables (0) .CalculatedFields.Add", at present Spire.Xls doesn't support to add or delete the calculated field, and you can change its formula with the following method.
Code: Select all
pt.CalculatedFields["Field1"].Formula = "1";

In current version it doesn't support the option" Layout classico tabella pivot (consente il trascinamento di campi nella griglia", which will be supported in next version.
Regarding the late issue, do you want to change the source data of an existing pivot table? or else?

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Mar 10, 2015 9:00 am

i have an existing PivoTable based on an Excel NamedRange.

Can I through Spire.XLS update the data and the size of the NamedRange?

thanks

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

Wed Mar 11, 2015 7:59 am

Hello,

Thanks for the information, here are some codes for your reference.
Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile("pt.xlsx");
Worksheet ptSheet = workbook.Worksheets["Pivot Table"];
Worksheet dataSheet = workbook.Worksheets[0];
//replace the source data of name range
workbook.NameRanges["DataSource"].RefersToRange = dataSheet.Range["A1:C218"];
XlsPivotTable pt = ptSheet.PivotTables[0] as XlsPivotTable;
//refresh
pt.Cache.IsRefreshOnLoad=true;
workbook.SaveToFile("Ptresult.xlsx", ExcelVersion.Version2010);

Please contact us if you have any questions or needs.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Mar 12, 2015 3:14 pm

Hello Gary,
I tried your code and updating the NamedRange is correct.
also refresh data is ok.

I found another problem (or bad?) in my excel file template I added a CalculatedField, and I set it to be displayed as "PercentageOfParent" but after saving the new file the option "ShodataAs" was restored to "Normal" and "BASEITEM" to "0".

if I try to change options in code i retrieve an error when i save as show in figure.

There is a fix for this?

Best Regard


My Code:

'***************************************************************
'** LOAD EXIST XLS FILE AND CHANGE DATASOURCE FOR PIVOT TABLE **
'***************************************************************
workbook.LoadFromFile(NomeFileTemplate)
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("Pivot Table")
pt.DataFields(6).BaseItem = 2
pt.DataFields(6).ShowDataAs = PivotFieldFormatType.PercentageOfParent
sheetPivot.PageSetup.Orientation = PageOrientationType.Landscape
sheetPivot.PageSetup.FitToPagesWide = 1
sheetPivot.PageSetup.FitToPagesTall = 1000
workbook.SaveToFile(NomeFile, ExcelVersion.Version2007)
System.Diagnostics.Process.Start(NomeFile)
'***************************************************************
'** LOAD EXIST XLS FILE AND CHANGE DATASOURCE FOR PIVOT TABLE **
'***************************************************************
Attachments
Errore.png
Errore.png (45.11 KiB) Viewed 5057 times

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

Fri Mar 13, 2015 3:25 am

Hello,

Thanks for your response. The error you retrieved is caused by the "PercentageOfParent" option, there is bug in it. I have transferred it to our Dev team, once there are any progress, we will notify you immediately. Sorry for inconvenience.
Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Mar 20, 2015 1:57 am

Hello,

Glad to notify you that now it is supported that setting the classic layout for pivot table, you can download the newest hotfix(Spire.XLS Pack Hotfix Version:7.6.43) and try the following code.
Code: Select all
XlsPivotTable.Options.ShowGridDropZone = true;

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Oct 22, 2018 3:10 am

Hi,

I am using below code for refresh the pivotal table with update datasource.

Workbook workbook = new Workbook();
workbook.LoadFromFile("pt.xlsx");
Worksheet ptSheet = workbook.Worksheets["Pivot Table"];
Worksheet dataSheet = workbook.Worksheets[0];
//replace the source data of name range
workbook.NameRanges["DataSource"].RefersToRange = dataSheet.Range["A1:C218"];
XlsPivotTable pt = ptSheet.PivotTables[0] as XlsPivotTable;
//refresh
pt.Cache.IsRefreshOnLoad=true;
workbook.SaveToFile("Ptresult.xlsx", ExcelVersion.Version2010);

But I am getting issues while executing below this code

//replace the source data of name range
workbook.NameRanges["DataSource"].RefersToRange = dataSheet.Range["A1:C218"];

Error- Object reference not set to an instance of an object

Please help me how can I resolve this issues.

Thanks
Subhankar

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

Mon Oct 22, 2018 6:52 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/Tutorials/Lic ... nsing.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

Return to Spire.XLS