So I have a spreadsheet that I am creating, which has styles applied to each column. Basically, I create column ranges, and set styles on each range to set the alignment, formatting, etc per column. Now, what I am trying to do is set the background color on every alternating row to be light gray. So I tried creating a range for each alternating row, and setting each range’s style property to a style that I created called “LightGreyStyle” (which just has foreground color set to light grey).
The color gets set properly, but I am also noticing that my column styles get overriden for particular cells. For example, the first column has horizontal alignment set to left. But now when I set a row to the LightGreyStyle, the alignment for the first cell gets defaulted to the right…seems like the row style overrides any style set on the column for a given cell.
Is there a way to basically “add on” my grey background color to the existing style for a particular cell or range of cells?
Tejash
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
You should use one of the following 2 overloads.
- void Cell.SetStyle(Style style, bool explicitFlag)
- void Cell.SetStyle(Style style, StyleFlag flag)
Using the above methods, you can preserve existing settings while applying new changes to Style objects.
Also, if you are using named styles, you can update them also without affecting their previous state. Please see the following article for your reference.
What about for a range of cells…can I use ApplyStyle? Also, I’m a little confused on how to use the StyleFlags.
Looks like I can’t use ApplyStyle…Im getting an error saying object not set to an instance of an object.
Seems like Style property is null on the range…
Hi,tjpatel403:
What about for a range of cells...can I use ApplyStyle? Also, I'm a little confused on how to use the StyleFlags.
Thanks for your posting and using Aspose.Cells for .NET.
Yes, you are right, if you want to apply some Style to a range of cells, then you should use ApplyStyle method.
Also, StyleFlag(s) are actually used to specify the what part of the Style object should be affected. For example, if you want to affect Fill Color, then you should set StyleFlag.CellShading = true and if you want to affect fonts, then you should set StyleFlag.Font and so forth.
If you will set StyleFlag.All=true, it will completely overwrite your existing styles. It is like if you have used the completely new Style object.
Hi,Looks like I can't use ApplyStyle...Im getting an error saying object not set to an instance of an object.
Seems like Style property is null on the range...
Thanks for your posting and using Aspose.Cells for .NET.
It seems, you are not instantiating the Style and StyleFlag objects. You can create a new Style using workbook.CreateStyle() method, then you can apply its selective parts using the StyleFlag object and ApplyStyle() method.
Please see the following code for complete illustration. It will help you to understand these objects and method and you will then be able to use them as per your needs.
I have also attached the source file used in this code, the output file generated by it and screenshot for your reference.
The code is fully commented so you will not have any trouble understanding it. Please feel free to ask us if you face any trouble.
C#
string path = @"F:\Shak-Data-RW\Downloads\source.xlsx";
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
//Get the maximumn dispaly range that should cover all of your cells
Range rng = worksheet.Cells.MaxDisplayRange;
//Create a new style, change the background color to yellow
//of all the cells without changing their existing Style/Format
Style st = workbook.CreateStyle();
//set the background color to yellow
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = Color.Yellow;
//If you will set StyleFlag.All=true, then it
//will completely overwrite your styles.
//But if you will selectively set its properties,
//then it will affect only that part of the style.
//Since we want to just change the fill color to yellow
//so we will set StyleFlag.CellShading=true
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
rng.ApplyStyle(st, flag);
workbook.Save(path + ".out.xlsx");
Screenshot:Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
//Get the maximumn dispaly range that should cover all of your cells
Range rng = worksheet.Cells.MaxDisplayRange;
//Create a new style, change the background color to yellow
//of all the cells without changing their existing Style/Format
Style st = workbook.CreateStyle();
//set the background color to yellow
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = Color.Yellow;
//If you will set StyleFlag.All=true, then it
//will completely overwrite your styles.
//But if you will selectively set its properties,
//then it will affect only that part of the style.
//Since we want to just change the fill color to yellow
//so we will set StyleFlag.CellShading=true
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
rng.ApplyStyle(st, flag);
workbook.Save(path + ".out.xlsx");