Pivot table row coloring

Hi,


I have attached my sample application where, on click of Generate report., pivot table is generated.
To meet some requirements we are using custom totals instead of using sub-totals property in Aspose.

My requirement is ,
the TOTAL sheet row of pivot table must be colored as shown in Expected Excel output.(Report.xlsx in Asposesamples folder).

In brief,Wherever Total exists that entire row must be colored.

Please do note that data is dynamic, input datasheet(Sample.xlx) may contain, any number of columns and any number of rows.
we purchased latest version of aspose .


thank you

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Well, you will have to find out the range from which cell it starts and to which cell it ends yourself and then apply the fill color to your Total row using the following code.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\AsposeSample\ExpectedReport.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[1];


Style style = workbook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.FromArgb(194, 214, 155);


//Find out the range of the cells that lies in your pivot table

Range range = worksheet.Cells.CreateRange(“B9:H9”);


StyleFlag flag = new StyleFlag();

flag.All = true;


range.ApplyStyle(style, flag);


workbook.Save(filePath + “.out.xlsx”);

Thanks a lot for your reply,

But, how can I find out the cell range? My data in Sheet is Dynamic..

TOTAL cell does not always have predefined starting column and ending column or Starting Row,

I think finding range in pivot table is a bit complicated...

As I provided u a sample, can u work on that and find out the range of TOTAL row dynamically and send back to me....

can u get back me on this...

THANK YOU

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Well, there is no API that could find the range directly so finding range is bit complicated. You will have to search the cell with the sample text e.g Grand Total to find the ending column and the text from which your pivot table starts. In this way, you will find the starting and ending columns of your pivot table. Then you will have to search the rows with text Total to find the row. Once, you will find the starting column, ending column and row number, you can then define a range for ApplyStyle method.

Please see the following document how to search some text in cells using Aspose.Cells.

http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data