XLSIO Pivot Table Data Field NumberFormat ignored

[Jim Oliver]

I am creating a spreadsheet with XlsIO and immediately converting it to PDF, using 15.3.0.33.

I have a pivot table that sum values from a range. The Pivot table data field has a number format of "0.00".

The pivot table is not applying the number format. If I save the spreadsheet, then open it in excel, the number format gets applied by excel. If I save that spreadsheet with excel and load it with the pdf converter, the number format is correct in the pdf.

I need the number format to be applied when creating the pivot table, or some kind of refresh on the worksheet or pivot table to apply the format.

Thank you.

Code:

string xlsfile = path + "pdftest7.xlsx";

string pdffile = path + "pdftest7.pdf";

ExcelEngine excelEngine = new ExcelEngine();

IApplication application = excelEngine.Excel;

application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook wb = application.Workbooks.Create(2);

IWorksheet ws = wb.Worksheets[1];

IWorksheet wssum = wb.Worksheets[0];

// create test data

ws.PageSetup.PrintTitleRows = "$A$1";

ws.Range["A1"].Value2 = "code";

ws.Range["B1"].Value2 = "total";

int maxrows = 100;

for (int n = 0; n < maxrows; n++)

{

ws.Range[n + 2, 1].Value2 = "ABC - DEF";

ws.Range[n + 2, 2].NumberFormat = "0.00"; // this one seems to work

ws.Range[n + 2, 2].Value2 = (float)(n + 10) / 10;

}

// create pivot table

IRange dataRange = ws.Range[1, 1, maxrows + 1, 2];

IPivotCache cache = wb.PivotCaches.Add(dataRange);

IPivotTable pivotTable = wssum.PivotTables.Add("PivotTable", ws["A1"], cache);

pivotTable.Fields[0].Axis = PivotAxisTypes.Row;

IPivotField field = pivotTable.Fields[1];

field.NumberFormat = "0.00"; // this is ignored

pivotTable.DataFields.Add(field, "Sum by Accounts", PivotSubtotalTypes.Sum);

// test excel save/load

//wb.SaveAs(xlsfile);

// pause here and load and save with excel

//wb = application.Workbooks.Open(xlsfile, ExcelOpenType.Automatic);

//wssum = wb.Worksheets["Sheet1"];

// create pdf

ExcelToPdfConverter converter = new ExcelToPdfConverter(wssum);

Syncfusion.Pdf.PdfDocument pdfout = new PdfDocument();

ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();

settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;

settings.TemplateDocument = pdfout;

settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;

pdfout = converter.Convert(settings);

pdfout.PageSettings.Orientation = PdfPageOrientation.Landscape;

pdfout.Save(pdffile);

pdfout.Close();

converter.Dispose();

wb.Close();

excelEngine.Dispose();

[Mohan Chandran]

Hi Aruno, 

 

Thank you for contacting Syncfusion support. 

  

A support incident to track the status of this query has been created under your account. Please log on to our support website to check for further updates. 

  

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents  

 

Please let me know if you have any other queries. 

 

Regards, 

Mohan Chandran. 

[Jim Oliver]

Patch Fixed the problem, thanks!

[Mohan Chandran]

Hi Aruno, 

 

We are glad that the issue is resolved at your end. Please let us know if you have any other queries. 

 

Regards, 

Mohan Chandran.