How can I color all the related rows and columns- rather than just a subset of them?

Aspose.Cells Product Family
1
I had a related question heretofore, and the answer there that (pretty much) works for me is to use:

pt.ShowInCompactForm();

In a spreadsheet I generate, there are blocks of items that span 5 rows - the first row is a Description, and the four rows below it are "subrows" with detail data for that item (namely, "Total Packages", "Total Purchases", "Sum of Average Price", and "Percentage of Total").

In certain cases, I need to colorize the cells in that region and am able to do this, for the most part, with the following code:

private void ColorizeContractItemBlocks(List contractItemDescs)
{
int FIRST_DESCRIPTION_ROW = 7;
int DESCRIPTION_COL = 0;
int ROWS_BETWEEN_DESCRIPTIONS = 5;
var pivot = pivotTableSheet.PivotTables[0];
var dataBodyRange = pivot.DataBodyRange;
int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
int rowsUsed = dataBodyRange.EndRow;

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

PivotTable pt = pivotTableSheet.PivotTables[0];
var style = workBook.CreateStyle();
// Loop through PivotTable data, colorizing contract items
while (currentRowBeingExamined < rowsUsed)
{
Cell descriptionCell = pivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
String desc = descriptionCell.Value.ToString();
if (contractItemDescs.Contains(desc))
{
style.BackgroundColor = CONTRACT_ITEM_COLOR;
style.Pattern = BackgroundType.Solid;

CellArea columnRange = pt.ColumnRange;
// Using StartColumn-1 instead of StartColumn-1 gives me the "Percentage of Total" data field subrow (but not the others - "Total Packages", "Total Purchases", and "Sum of Average Price")
for (int c = columnRange.StartColumn-1; c <= columnRange.EndColumn; c++)
{
//pt.Format(currentRowBeingExamined-1, c, style); <= Instead of adding the "Description" row, this colors up some unrelated final ("Percentage of Total") data rows
pt.Format(currentRowBeingExamined, c, style);
pt.Format(currentRowBeingExamined + 1, c, style);
pt.Format(currentRowBeingExamined + 2, c, style);
pt.Format(currentRowBeingExamined + 3, c, style);
}

}
currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
}
}

But it only works "for the most part," because I am unable to colorize the "Description" row (such as, "AVOCADOS, HASS 70 CT #2") or any but the last row of column 0/A, - the "Percentage of Total" subrow, as can be seen in the screenshot below.

It may be that the Description row is better left untainted/unpainted, but I think the subrows beneath would be better off colorized, and I don't understand why they are not.

I can prevent ALL of those subrows from being colored by using this:

for (int c = columnRange.StartColumn; c <= columnRange.EndColumn; c++)

(that is to say, starting the loop with "StartColumn" instead of "StartColumn-1" prevents anything in column 0/A from being colorized), but it seems bizarre to me that only that last subrow colors up when I start from one column back (at 0/A).
2

Hi,


Thanks for your posting and using Aspose.Cells.

Please provide us your output excel file and the expected output excel file (which you have fixed manually using Microsoft Excel). In your previous posts, we found, there are some issues in your output excel file probably because of some bugs in Aspose.Cells which do not let you colorize some of the pivot table rows or cells or columns. Once, you will provide us needed files, we will look into this issue and update you asap.

3

Okay, here is the attached file from which I got the screenshot. What it needs to be is all the cells in the rows colored, rather than just a subset of them. Also the “Description” row above those rows which are partially colored.

4

Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. We tried to apply fill color to PivotTable.TableRange1 and found, Cell.SetStyle(), Range.ApplyStyle(), PivotTable.Format() are not working however PivotTable.FormatAll() is working fine.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44973 - Unable to set the fill color of the Pivot Table cells

I have attached the sample excel file used in this code, output excel file generated by it and the expected output excel file for a reference.

C#
Workbook wb = new Workbook(“sample.xlsx”);
Worksheet ws = wb.Worksheets[“PivotTableSheet”];

PivotTable pt = ws.PivotTables[0];

CellArea ca = pt.TableRange1;
int idx1 = ca.ToString().IndexOf("(");
int idx2 = ca.ToString().IndexOf(")");
string address = ca.ToString().Substring(idx1 + 1, idx2 - idx1 - 1);

Style st = wb.CreateStyle();
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = Color.Red;
st.BackgroundColor = Color.GreenYellow;

//Colorizing the PivotTable.RableRange1
for (int r = ca.StartRow; r < ca.EndRow; r++)
{
for (int c = ca.StartColumn; c < ca.EndColumn; c++)
{
Debug.WriteLine(ws.Cells[r, c].Name);
pt.Format(r, c, st); //This works only for some of the cells

}

}

//This works fine
//-----------pt.FormatAll(st);

//Apply via range also does not work
//-----------//Range rng = ws.Cells.CreateRange(address);

//-----------// StyleFlag flag = new StyleFlag();
//-----------// flag.All = true;

//-----------// rng.ApplyStyle(st, flag);

wb.Save(“ouput.xlsx”);


5

@cshannon,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-44973”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

6

@cshannon,

Please try our latest version/fix: Aspose.Cells for .NET v19.7.1 (attached)

Your issue should be fixed in it.

We use the following sample code to test it and it works fine:
e.g
Sample code:

            Workbook wb = new Workbook(filePath + "sample.xlsx");
            Worksheet ws = wb.Worksheets["PivotTableSheet"];

            PivotTable pt = ws.PivotTables[0];
            pt.RefreshDataOnOpeningFile = false;

            CellArea ca = pt.TableRange1;
           int idx1 = ca.ToString().IndexOf("(");
            int idx2 = ca.ToString().IndexOf(")");
            string address = ca.ToString().Substring(idx1 + 1, idx2 - idx1 - 1);           

            Style st = wb.CreateStyle();
            st.Pattern = BackgroundType.Solid;
           st.ForegroundColor = Color.Red;
            st.BackgroundColor = Color.GreenYellow;
            

            //Test 1:Colorizing the PivotTable.TableRange1
            for (int r = ca.StartRow; r <= ca.EndRow; r++)
            {
                for (int c = ca.StartColumn; c <= ca.EndColumn; c++)
                {
                    Debug.WriteLine(ws.Cells[r, c].Name);
                    pt.Format(r, c, st);
                }
            }            
                        
            //Test 2：This works fine
            //pt.FormatAll(st);
            
            //Test 3: Apply via range also works fine
            //Range rng = ws.Cells.CreateRange(address);
            //StyleFlag flag = new StyleFlag();
            //flag.All = true;
            //rng.ApplyStyle(st, flag);

            wb.Save(filePath + "out.xlsx");

Let us know your feedback.
Aspose.Cells19.7.1 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.7.1 For .Net4.0.Zip (4.9 MB)

7

The issues you have found earlier (filed as CELLSNET-44973) have been fixed in Aspose.Cells for .NET v19.8. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi