We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

pivot.Format

i want to use the pivot.Format() method to format the pivot table. But i want to apply all the rows for the style with <!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>ZH-CN</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><span style=“font-size:11.0pt;font-family:“Calibri”,sans-serif;
mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-bidi-font-family:
“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:ZH-CN;
mso-bidi-language:AR-SA”>all alternating grey and white (one line white, one line dark, one line white again). How to do that?<!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman",serif;}

<![endif]–>

Hi,


Thanks for your query.

Well, you got to iterate through Row/Column and Data (body) ranges in the Pivot Table and apply your desired formatting to each items (in the report) accordingly. I have attached a template file which contains a simple DataTable. I have written the following sample code to accomplish the task (e.g set fill color/ shading color alternative way, i.e., one row dark gray and one row light gray for the Pivot report data). Please refer to the sample code using the template file and update/write your own code by yourself for your custom requirements accordingly.
e.g
Sample code:

var workbook = new Workbook(“e:\test2\PivotSample1.xlsx”);
var pivot = workbook.Worksheets[1].PivotTables[0];

pivot.RefreshData();
pivot.CalculateData();
pivot.CalculateRange();

Style style1 = workbook.CreateStyle();
style1.ForegroundColor = Color.LightGray;
style1.Pattern = BackgroundType.Solid;

Style style2 = workbook.CreateStyle();
style2.ForegroundColor = Color.Gray;
style2.Pattern = BackgroundType.Solid;


CellArea area = pivot.RowRange; //A5:A8
CellArea area2 = pivot.DataBodyRange;//B5:C8

int start = area.StartColumn;

for (int i = area.StartRow; i <= area.EndRow; i++)
{
if (i % 2 == 0)
{
pivot.Format(i, 0, style1);
}
else
{
pivot.Format(i, 0, style2);
}
}

for (int r = area2.StartRow; r <= area2.EndRow; r++)
{
for (int c = area2.StartColumn; c <= area2.EndColumn; c++)
{
if (r % 2 == 0)
{
pivot.Format(r, c, style1);
}
else
{
pivot.Format(r, c, style2);
}
}
}

workbook.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.

Thanks for your reply. when i used your code, i got below error:

Additional information: Index was out of range. Must be non-negative and less than the size of the collection.

can you let me know the reason?

Hi Jimmy,


Thank you for writing back. Please note, the provided code snippet is for demonstration purposes and you should amend it to suit your application requirements. Moreover, the code snippet works fine if used against the spreadsheet shared in our previous post and the latest version of Aspose.Cells for .NET 8.7.1.4 (attached). If you are getting the said exception using the aforementioned spreadsheet then please give a try to the latest version. In case the problem persists, please share your sample spreadsheet for further investigation.