Apply alternating background color to rows in Excel worksheet in .NET

Aspose.Cells Product Family
1

hi,

im using this code and i cant find any documentation on how to do alternating color. here's a code snippet...

//Put a string value into a cell

sheet.Cells["A1"].PutValue("Import an object Array");

//the font text is set to bold

sheet.Cells["A1"].Style.Font.IsBold =  **true** ;

//Create an object array and fill it with some values

**object** [] obj = { "Tom", "John", "kelly", 1, 2, 2.8, 5.16,  **true** ,  **false**  };

//Import the object array to the sheet cells

sheet.Cells.ImportObjectArray(obj, 1, 0,  **false** );

//Autofit all the columns in the sheet

sheet.AutoFitColumns();
2

Hi,

I have pasted here a simple sample code to implement your task, hopefully it will help you a bit, kindly check it out. The output file is also attached for your reference.

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Put a string value into a cell
sheet.Cells["A1"].PutValue("Import an object Array");
//the font text is set to bold
sheet.Cells["A1"].Style.Font.IsBold = true;
//Create an object array and fill it with some values
object[] obj = { "Tom", "John", "kelly", 1, 2, 2.8, 5.16, true, false };
//Import the object array to the sheet cells
sheet.Cells.ImportObjectArray(obj, 1, 0, false);
//Autofit all the columns in the sheet
sheet.AutoFitColumns();

//Create first style i.e.., yellow shading color with red font color.
Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.ForegroundColor = Color.Yellow;
style1.Pattern = BackgroundType.Solid;
style1.Font.Color = Color.Red;
StyleFlag flag1 = new StyleFlag();
flag1.CellShading = true;
flag1.FontColor = true;


//Create second style i.e.., green shading color with white text color.
Style style2 = workbook.Styles[workbook.Styles.Add()];
style2.ForegroundColor = Color.Green;
style2.Pattern = BackgroundType.Solid;
style2.Font.Color = Color.White;
StyleFlag flag2 = new StyleFlag();
flag2.CellShading = true;
flag2.FontColor = true;

for (int i = 0; i < 100; i++)
{

if (i % 2 == 0)
{
sheet.Cells.ApplyRowStyle(i, style1, flag1);

}
else {

sheet.Cells.ApplyRowStyle(i, style2, flag2);
}

}


workbook.Save("f:\\test\\alternativerowsstyles.xls");
For your further reference, please check some doc topics:
Thank you.
3

ok the alternating worked fine but i lost the gridlines. how can i keep the gridlines and assigned color to the gridlines? thanks!

also,

Color.LightGray not applying. i tried this in applying color and it doesnt seem to work.

System.Drawing.Color c = System.Drawing.ColorTranslator.FromHtml("#F5F7F8");

4

Hi,

Thank you for considering Aspose.

gandor:

ok the alternating worked fine but i lost the gridlines. how can i keep the gridlines and assigned color to the gridlines?

Well, it is the feature of MS Excel. If you apply the background color to the rows, the grid lines will not be visible. For this you may assign border to your cells to make the gridlines. Please see the under-mentioned code to check how you can set the borders for the cells.

gandor:

Color.LightGray not applying. i tried this in applying color and it doesn’t seem to work.

System.Drawing.Color c = System.Drawing.ColorTranslator.FromHtml(“#F5F7F8”);

Well, your mentioned color is not in the default palette of Excel 2003, so you have to add this color to the palette before using it. Please see the following code in this regard,

Sample Code:

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

//Adding color to the palette at 55th index

System.Drawing.Color c = System.Drawing.ColorTranslator.FromHtml("#F5F7F8");

workbook.ChangePalette(c, 55);

//Put a string value into a cell

sheet.Cells["A1"].PutValue("Import an object Array");

//the font text is set to bold

sheet.Cells["A1"].Style.Font.IsBold = true;

//Create an object array and fill it with some values

object[] obj = { "Tom", "John", "kelly", 1, 2, 2.8, 5.16, true, false };

//Import the object array to the sheet cells

sheet.Cells.ImportObjectArray(obj, 1, 0, false);

//Autofit all the columns in the sheet

sheet.AutoFitColumns();

//Create first style i.e.., yellow shading color with red font color.

Style style1 = workbook.Styles[workbook.Styles.Add()];

style1.ForegroundColor = Color.Yellow;

style1.Pattern = BackgroundType.Solid;

style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

style1.Borders[BorderType.TopBorder].Color = c;

style1.Borders[BorderType.BottomBorder].Color = c;

style1.Borders[BorderType.LeftBorder].Color = c;

style1.Borders[BorderType.RightBorder].Color = c;

style1.Font.Color = Color.Red;

StyleFlag flag1 = new StyleFlag();

flag1.CellShading = true;

flag1.FontColor = true;

flag1.Borders = true;

//Create second style i.e.., green shading color with white text color.

Style style2 = workbook.Styles[workbook.Styles.Add()];

style2.ForegroundColor = Color.Green;

style2.Pattern = BackgroundType.Solid;

style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

style2.Borders[BorderType.TopBorder].Color = c;

style2.Borders[BorderType.BottomBorder].Color = c;

style2.Borders[BorderType.LeftBorder].Color = c;

style2.Borders[BorderType.RightBorder].Color = c;

style2.Font.Color = Color.White;

StyleFlag flag2 = new StyleFlag();

flag2.CellShading = true;

flag2.FontColor = true;

flag2.Borders = true;

for (int i = 0; i < 100; i++)

{

if (i % 2 == 0)

{

sheet.Cells.ApplyRowStyle(i, style1, flag1);

}

else

{

sheet.Cells.ApplyRowStyle(i, style2, flag2);

}

}

workbook.Save("c:\\alternativerowsstyles.xls");

Also, please see the following online document regarding colors and palettes,

https://docs.aspose.com/display/cellsnet/Cells+Formatting

Thank You & Best Regards,

5

perfect! one last thing how do you merge two cells like A1 and A2 columns? i know how to merge A1 and B1 but not from top to bottom. thanks much!

6

Hi,

Thank you for considering Aspose.

Well, you can use Cells.Merge() method to merge the cells. Please see the following sample code which will merge cell A1 & A2.

Sample Code:


Workbook workbook = new Workbook();

//Create a Worksheet and get the first sheet.

Worksheet worksheet = workbook.Worksheets[0];

//Create a Cells object ot fetch all the cells.

Aspose.Cells.Cells cells = worksheet.Cells;

//Merge some Cells (A1:A2) into a single A1 Cell.

cells.Merge(0, 0, 2, 1);

//Input data into A1 Cell.

worksheet.Cells[0,0].PutValue("Merged Cell");

workbook.Save("C:\\book1.xls");

Also, Please see the following documentation link regarding Merging / UnMerging of Cells for your reference,

Thank You & Best Regards,

7

thanks again! worked perfectly. now im trying to add an image on a cell and so far im successful in doing so but i can't figure out how to put the image at the middle of the cell. thanks in advance.

heres the code im using...

string ImageUrl = System.Web.HttpContext.Current.Server.MapPath("~/image/icon.gif");

int pictureIndex = sheet.Pictures.Add(3, 11, ImageUrl);

Picture picture = sheet.Pictures[pictureIndex];

8

Hi,

Thank you for considering Aspose.

Well, MS Excel does not provide any property/attribute to set the picture / image in the center to the cell. You may add the image / picture and try to specify the Left position of it to place it in the center of the cell. Please see the following code for your reference,

//Instantiate a new Workbook.

Workbook excelbook = new Workbook();

excelbook.Worksheets[0].Cells.SetColumnWidth(5, 40);

excelbook.Worksheets[0].Cells.SetRowHeight(5, 40);

//Add a picture to the first worksheet in the workbook.

int index = excelbook.Worksheets[0].Pictures.Add(5,5,"C:\\Icon.jpg");

//Get the picture object.

Aspose.Cells.Picture picture = excelbook.Worksheets[0].Pictures[index];

picture.Placement = PlacementType.MoveAndSize;

//Set the left position

picture.Left = 20;

//Save the excel file.

excelbook.Save("C:\\Picture_test.xls");

Thank You & Best Regards,

9

it looks like this would work if we have a fixed images sizes. anyway, i have found one other issues in regards to breaking a line in a cell. i'm using
to break the lines on my grid control and on export i tried "\r\n" but it is not working. so basically my question is how can i break lines in a cell. thanks!

10

Hi,

Thank you for considering Aspose.

You can use "\n" to break the line and please make sure that Cell.Style.IsTextWrapped is set to true. Please see the following documentation topic as a reference,

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/line-break-and-text-wrapping.html

Thank You & Best Regards,

11

the line break "\n" seem to work when i click on the cell i can see on the value text box at the top that the lines indeed have breaks but just looking on the cells their are no breaks. basically there's no text wrapping taking place. what i really want is from row 3 to the end of the data rows i need the column 3 to be autofitcolumn(rowheight be adjusted to show all the data that was broken into multiple lines) here's the code snippet im using...

sheet.Cells.Columns[3].Style.IsTextWrapped = true;

//Set the width of the column

sheet.Cells.SetColumnWidth(3, 50);

sheet.Cells.SetColumnWidth(0, 5);

sheet.Cells.SetRowHeight(2, 50);

12

ok i finally figured out how to make the text wrapping but another issue came up…how can i make the rowheight adjust dynamically to the size of the text on a cell.

13

Hi,

Thank you for considering Aspose.

Well, you can use the Worksheet.AutofitRow(rowIndex) API of Aspose.Cells to achieve your desired results. Please see the following documentation link for further details,

AutoFit Rows and Columns (.NET)
AutoFit Rows and Columns (Java)

Also, you can check our online demo regarding this functionality,

Thank You & Best Regards,

14

perfect! if i insert an icon image on a column, can i use that as a value? basically what i mean is if i have an “active” column and i put an icon image when the row is active and i dont put an icon image when it is not active then at the last row i have a total count of all the column that have an icon image(means “active”). is this possible?

15

Hi,

Thank you for considering Aspose.

Well, I am not very clear about your requirement here. Please create an excel file using MS Excel and post it here to explain your requirement. This way we will be able to provide you with a proper solution to your requirement.

Thank You & Best Regards,

16

here you go. so basically if a user who exported the file to his local machine and wanted to put the total number of active(“x”) rows on C6 cell, will he able to create a formula by using the icon “x” image?

17

Hi,

Thank you for considering Aspose.

Well, I am afraid, MS Excel does not support to count the images in the cells. So, please input a value to the cell as well, when you insert an image to the cell to get your desired result. Please see the following codes for your reference,

Sample Code:


Workbook workbook = new Workbook();

Cells cells = workbook.Worksheets[0].Cells;

Style style = workbook.Styles[workbook.Styles.Add()];

style.HorizontalAlignment = TextAlignmentType.Left;

StyleFlag flag = new StyleFlag();

flag.HorizontalAlignment = true;

cells.ApplyColumnStyle(2, style, flag);

Worksheet sheet = workbook.Worksheets[0];

cells["C2"].PutValue(1);

sheet.Pictures.Add(1, 2, @"f:\filetemp\x.jpg");

cells["C4"].PutValue(1);

sheet.Pictures.Add(3, 2, @"f:\filetemp\x.jpg");

cells["C6"].PutValue(1);

sheet.Pictures.Add(5, 2, @"f:\filetemp\x.jpg");

cells["C7"].Formula = "=COUNT(C2:C6)";

workbook.Save(@"f:\filetemp\dest.xls");

Thank You & Best Regards,