Hi,
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.
Hi,
- CELLSNET-44973 - Unable to set the fill color of the Pivot Table cells
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”);
@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.
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)
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