Clear all shading/background colors from a worksheet

Aspose.Cells Product Family
1

Is there a quick way to clear the background fill/shading from all cells in a worksheet?

I tried creating a range from 0,0 to MaxRow,MaxCol, but if the user had done a fill while highlighting an entire row or column then all cells outside of the range still show color.

My workaround has been running a loop through all rows and columns and applying a new style with the background color set to empty, but it’s pretty slow and increases the file size quite a bit (doubles normally).

I could possibly check if the row or column has any formatting as I go through the loop, but can’t find an easy method.

2

Hi,

I think you may try the following code:

Workbook objExcel = new Workbook();
objExcel.Open("d:\\test\\test_book.xls");
Worksheet worksheet = objExcel.Worksheets[0];
Cells objCells = worksheet.Cells;
Style style = objExcel.Styles[objExcel.Styles.Add()];
style.ForegroundColor = Color.Empty;
style.Pattern = BackgroundType.None;
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
for (int i = 0; i<= objCells.MaxRow;i++ )
{

if(objCells.Rows[i].Style.ForegroundColor != Color.Empty)
{

objCells.Rows[i].ApplyStyle(style,flag);


}
}

objExcel.Save("d:\\test\\test_clearbgcolor.xls");
Thank you.
3

That loop only handles rows, but regardless, if a user has done the following in Excel:

Highlight column A -> fill with yellow
Highlight row 1 -> fill with red

A loop would be true for every row/column and update every cell, still increasing the file size.

4

any update on this?

5

Hi,

Thanks for keeping in touch,

We will enhance it soon.

Thank you.

6

Any estimate on an update?

7

Hi,

We will add a new method soon i.e., Worksheet.ApplyStyle() which can be used to set / remove style formattings in the whole worksheet. The method is optimized enough and will not increase the file size too much. Hoping, it will suit your requirements.

Thank you.

8

Hi,

Please try this fix in <A href="https://forum.aspose.com/t/92826</FONT></A>.</P> <P>It inlcudes the fix for this bug.We have added a method Cells.ApplyStyle to apply the style to entire worksheet.See following codes:</P> <P>StyleFlag flag = new StyleFlag();<BR> flag.All = true;<BR> cells.ApplyStyle(style, flag);</P>

9

This release does not fix the issue with setting the style on an entire worksheet.

If i run: wb.Worksheets(count).Cells.ApplyStyle(style,flag)
It throws the exception: "Object reference not set to an instance of an object"

What is the fastest method to override the style properties of an entire worksheet?

10

Hi,

Could you post your template file and codes? We will check it soon.

11


Dim styleFlag As StyleFlag = New StyleFlag()
Dim cellStyle As Style
Dim allCells As Cells = wb.Worksheets(sheetCount).Cells

cellStyle = allCells.Start.GetStyle()

cellStyle.Font.Color = System.Drawing.Color.Black
cellStyle.BackgroundColor = System.Drawing.Color.Empty
cellStyle.ForegroundColor = System.Drawing.Color.Empty
cellStyle.Pattern = BackgroundType.None
styleFlag.FontColor = True
styleFlag.CellShading = True

allCells.ApplyStyle(cellStyle, styleFlag)

12

any update on this issue or feedback on my code?

13

Hi，

I tried the following codes. It works fine.

Workbook workbook = new Workbook();

StyleFlag styleFlag = new StyleFlag();
Style cellStyle = null;
Cells allCells = workbook.Worksheets[0].Cells;

cellStyle = allCells["A1"].GetStyle();

cellStyle.Font.Color = System.Drawing.Color.Black;
cellStyle.BackgroundColor = System.Drawing.Color.Empty;
cellStyle.ForegroundColor = System.Drawing.Color.Empty;
cellStyle.Pattern = BackgroundType.None;
styleFlag.FontColor = true;
styleFlag.CellShading = true;

allCells.ApplyStyle(cellStyle, styleFlag);

Could you post your template file?

Please make sure sheetcount is less than Worksheets.Count and Cells.Start is not null.

14

The code throws an exception on every single file I run it on. Attached is a very simple file that produces the error.

In the case of this file, sheetcount is only zero and in all cases the cellstyle and styleflag variables are not null.

Also, i'm using 4.4.1.7

15

Hi,

Thanks for your file.

Please try this fix.