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.

Wed Jun 11, 2014 7:03 pm

I have a couple questions about pivot tables.

1) Is there away to autofit all columns generated by the pivot table.
2) How do you set the number format of a DataField. i.e) I want to format this column to be a currency.
pt.DataFields.Add(pt.PivotFields("Amount"), "Total", Spire.Xls.SubtotalTypes.Sum)

uwsgibbons
 
Posts: 3
Joined: Thu May 15, 2014 2:45 pm

Thu Jun 12, 2014 7:30 am

Hello,

Thanks for your inquiry.
1) Is there away to autofit all columns generated by the pivot table.

Sorry that currently our product doesn't support the feature.

2) How do you set the number format of a DataField. i.e) I want to format this column to be a currency.
pt.DataFields.Add(pt.PivotFields("Amount"), "Total", Spire.Xls.SubtotalTypes.Sum)

Please try the following method.
Code: Select all
pt.PivotFields("Amount").NumberFormat = "$#,##0.00";


Sincerely,
Gary
E-iceblue support team
User avatar

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

Tue Jul 08, 2014 9:01 am

Hello,

The feature to autofit all columns generated by the pivot table has been added, and the newest hotfix(Spire.XLS Pack Hotfix Version:7.5.3 ) has been released. You can download and test the following code.
Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile(inputFile);
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet2 = workbook.CreateEmptySheet("Pivot Table");
CellRange dataRange = sheet.AllocatedRange;
PivotCache cache = workbook.PivotCaches.Add(dataRange);
Spire.Xls.PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
pt.PivotFields["Rollup1"].Axis = AxisTypes.Row;
pt.Options.RowHeaderCaption = "Rollup1";
pt.PivotFields["Rollup2"].Axis = AxisTypes.Row;
pt.PivotFields["Rollup3"].Axis = AxisTypes.Row;
pt.PivotFields["JAN"].NumberFormat = "$#,##0.00";
pt.DataFields.Add(pt.PivotFields["JAN"], "JAN", SubtotalTypes.Sum);
pt.CalculateData();//necessary
sheet2.AutoFitColumn(1);
sheet2.AutoFitColumn(2);
workbook.SaveToFile(outputFile, ExcelVersion.Version2007);

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

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

Return to Spire.XLS