Free Support Forum - aspose.com

Hiding gridlines and changing the background color at Row level of worksheet

I am traversing through the Rows of a worksheet and want to pick up specific rows, changing the Row’s background color and hiding the grid-lines for that particular row only !!
I have coded the following but it wont reflect any change at the sheet level after saving the workbook…

foreach (Row dr in _workbook.Worksheets[1].Cells.Rows)
{

if (!string.IsNullOrWhiteSpace(_workbook.Worksheets[1].Cells[dr.Index, 2].ToString()))
{
Style _style = dr.Style;
StyleFlag _sf = new StyleFlag();
_style.BackgroundColor = System.Drawing.Color.Cyan;
_style.ForegroundColor = System.Drawing.Color.Black;
dr.ApplyStyle(_style, _sf);
}
}
Please help on this… Thanks in Advance…

Hi,

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

You need to set StyleFlag.All property to true or selectively set the properties for which you want to apply your style.

For example, if you want to change the fill color, you will have to set StyleFlag.CellShading = true .

And if you want to change the font color, you will have to set the StyleFlag.FontColor = true .

In order to illustrate this, I have written a code that sets the fill color and font color of first 10 rows. I have also attached the output file generated by the following code and screenshot for your reference.

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)

C#

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Random rand = new Random();
for (int i = 0; i < 10; i++)
{
//Random forground color, it will be a font color
int fred = rand.Next(255);
int fgreen = rand.Next(255);
int fblue = rand.Next(255);
//Random background color, it will be a fill color
int bred = rand.Next(255);
int bgreen = rand.Next(255);
int bblue = rand.Next(255);
//Create a style object to set font and fill color of the cell
Style style = workbook.CreateStyle();
style.Font.Color = Color.FromArgb(fred, fgreen, fblue);
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.FromArgb(bred, bgreen, bblue);
//CellShading means we want to change fill color
//FontColor means we also want to change font color
StyleFlag sf = new StyleFlag();
sf.CellShading = true;
sf.FontColor = true;
worksheet.Cells.Rows[i].ApplyStyle(style, sf);
//Write some text in first cell of each row.
worksheet.Cells[i, 0].PutValue(“Aspose”);
}

Thank you Faiz… That was of great Help to me… in order to change to the background color of the cell i had to change the foreground color of the cell…

 Style _style = _row[2].GetStyle();
StyleFlag _sf = new StyleFlag();
_sf.CellShading = true;
_style.ForegroundColor = System.Drawing.Color.Cyan;
_style.Pattern = BackgroundType.Solid;
_row.ApplyStyle(_style, _sf);



gudguy.deepak:

If in a cell there is a text content which is long enough than the width of the cell then some part of the text content are hidden.. But when i double click on the particular cell then the entire text is visible to me..
I now want to have some implementation similar to Watermark on the cell level.. so that the entire text moves to the background of the grid lines and is visible completely in the cell..
Thanks in Advance..

Hi,

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

You can either merge cells or you can use AutoFitColumns() method to make you cell's width to widen according to its text width.

Please see the following articles for your more help.


If it does not solve your problem, then please provide us your expected output xls/xlsx file which you can create manually using Ms-Excel 2010 and post it here.

We will look into it and help you asap with sample code or workarounds.
gudguy.deepak:
Thank you Faiz.. That was of great Help to me.. in order to change to the background color of the cell i had to change the foreground color of the cell..
 Style _style = _row[2].GetStyle();
StyleFlag _sf = new StyleFlag();
_sf.CellShading = true;
_style.ForegroundColor = System.Drawing.Color.Cyan;
_style.Pattern = BackgroundType.Solid;
_row.ApplyStyle(_style, _sf);
Hi,

Thanks for your feedback.

It's good to know the sample code was helpful for you to resolve this issue.

Often, the background color is mistakenly understood as a Fill Color. The Fill Color is assigned to cell using the Style.ForegroundColor and Style.Pattern properties as you have shown in the code.

Style.BackgroundColor property is used when you are filling the cell with two gradient colors where one color is specified with the ForegroundColor property and the second color is assigned with the BackgroundColor property.
Hi,
For:
gudguy.deepak:
I now want to have some implementation similar to Watermark on the cell level.. so that the entire text moves to the background of the grid lines and is visible completely in the cell..


Please check the document on how to set watermark as the sheet background in the cells:
http://www.aspose.com/docs/display/cellsnet/Add+WordArt+Watermark+to+Worksheet

Thank you.