How to replace zero's by empty string in pivot table

Using aspose has been very wonderful and i,ve never seen excel reporting easier.Pls the attached file was created with aspose cells. i have 3 sheets. i need to convert the boe sheet to Boeconverted sheet.

all i need is to replace all the zeros in BOE sheet to empty strings in pivot BOEconveted and also to change the font from arial to calibri and add a digital signature to boe converted sheet to be signed off by 6 persons. CaN ASPOSE enable me do that?

Hi,


Sorry for replying you a bit late.

Thanks for the template file. Could you point the option in MS Excel on how to display empty strings for all the zeros other than by manually deleting the zeros in the pivot table, I am not sure about it if MS Excel has this option for pivot tables. We will check it soon.

Also, by the way, you may try to input / replace with empty strings (for 0s) in the source data sheet for the pivot table.

Thank you.

Thanks for the reply.

what about the formating and digital signature.

i tried applying formatting, it worked in another sheet that has data but when i apply it to pivot table . is was n't formatting. is there a way to update the pivot table style to include formatting the data fontstyle

Hi,

Please check the article on how to assign/validate digital signatures:

Assign and Validate Digital Signatures

For formatting pivot table with styles/formatting, you may use advanced built-in Pivot Styles enumeration i.e

PivotTableStyleType

e.g

//For Excel 2007 XLSX file format

//pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;

and

//For Excel 2003 XLS pivot table file format 

pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;

Thank you.

pls i have done this before. all i need is to update the PivotTableStyleType.PivotTableStyleDark1 so that the font will be calibri instead of arial. I I mean is there a way i can update the inbuilt styles

Hi,

We think you try the sample code (given below) to replace zeros by empty string:
PivotTableCollection pivotTables = wb.Worksheets[6].PivotTables;
PivotTable table = pivotTables[0];
table.DataFields[0].NumberFormat = "_ * #,##0.00_ ;_ * -#,##0.00_ ;\"\" ;_ @_ ";

thhanks man, but i have been able to replace using excel options which works perfectly

. all i need is to update the PivotTableStyleType.PivotTableStyleDark1 style so that the font will be calibri instead of arial font. I I mean is there a way i can update the inbuilt styles . i tried applying style and styleflag to the range but it wasnt working and does not throw any exception.

Hi,


I think you may try to use PivotTable.Format() and PivotTable.FormatAll() methods for your need. See the sample code below for your reference:

Sample code:

e.g

var workbook = new Workbook(“e:\test2\aspose.xlsx”);
var pivot = workbook.Worksheets[“Boe”].PivotTables[0];
for (int i = 0; i < pivot.DataFields.Count; i++)
{
pivot.DataFields[i].NumberFormat = “_ * #,##0.00_ ;_ * -#,##0.00_ ;”" ;_ @_ ";
}

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;

Style style = workbook.CreateStyle();
style.Font.Name = “Calibri”;

pivot.FormatAll(style);
workbook.Save(“e:\test2\test2.out.xlsx”);


Thank you.