How to Apply color for alternative Rows in Excel

Hi Team,


Please find the attached spreadsheet, we need to apply the color as per the attachment. can you please give us an example. Also these rows are dynamic.

Hi,

Thanks for your posting and using Aspose.Cells.

Please check the following code, it applies two colors to first 100 rows alternatively. I have attached the source excel file used in this code and output excel file generated by it for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Style style1 = workbook.CreateStyle();

style1.ForegroundColor = Color.Blue;

style1.Pattern = BackgroundType.Solid;


Style style2 = workbook.CreateStyle();

style2.ForegroundColor = Color.Purple;

style2.Pattern = BackgroundType.Solid;


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

{

Style style = null;


if (i % 2 == 0)

{

style = style1;

}

else

{

style = style2;

}


StyleFlag flag = new StyleFlag();

flag.CellShading = true;


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

}



workbook.Save(filePath + “.out.xls”);

Hi Shakeel,

Thanks for the mail.

But I missing my heading style and also borders. Also, i need that alternative color till the Column M. Can you please help me.

Thanks,
Shiva Alladi

Hi Shakeel,


Please find the attachement and code below.

Aspose.Cells.Style style1 = myWorkbook.CreateStyle();
style1.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#D3D3D3");
style1.Pattern = BackgroundType.None;


Aspose.Cells.Style style2 = myWorkbook.CreateStyle();

style2.Pattern = BackgroundType.Solid;

---------------------------
for (int i = 0; i < myWorksheet.Cells.Rows.Count - 2; i++)
{
if (i % 2 != 0 && i > 1)
{
style = style1;
}
else
{
style = style2;
}
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
myWorksheet.Cells.Rows[i].ApplyStyle(style, flag);
}
--------------------

Hi Shiva,


Thank you for providing the source spreadsheet.

Let me address your concerns one by one.

  • In order to retain/save the formatting of the header rows, please start the loop with row Id beyond the header rows. Such as for (int i = 3; i < 100; i++) starts with 4th row.
  • Please note, Row.ApplyStyle will paint the whole row, and will not stop at column M. One way to achieve your required goal is to paint individual cells but it will require complex logic and much processing. Alternative solution is to apply the style as Shakeel has suggested in his previous response and copy the required range onto a new worksheet with same name while removing the source worksheet from the collection. Please check the following piece of code and it’s resultant spreadsheet as attached.

C#

Workbook workbook = new Workbook(“D:/Inactive+Users+Report+(17).xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Style style1 = workbook.CreateStyle();
style1.Pattern = BackgroundType.None;
Style style2 = workbook.CreateStyle();
style2.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#D3D3D3");
style2.Pattern = BackgroundType.Solid;
for (int i = 3; i < 100; i++)
{
Style style = null;
if (i % 2 == 0)
{
style = style1;
}
else
{
style = style2;
}
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
worksheet.Cells.Rows[i].ApplyStyle(style, flag);
}
string name = worksheet.Name;
Range source = worksheet.Cells.CreateRange(“A1:M” + worksheet.Cells.MaxDataRow + 1);

Worksheet sheet = workbook.Worksheets.Add(“Any Name”);
Range destination = sheet.Cells.CreateRange(source.RefersTo);
destination.Copy(source, new PasteOptions() { PasteType = PasteType.All});
workbook.Worksheets.RemoveAt(name);
sheet.Name = name;
workbook.Save(“D:/out.xls”);

Hi Babar,


Thanks!
I tried in the below way and it worked.

for (int i = 0; i < myWorksheet.Cells.Rows.Count - 2; i++)
{
if (i % 2 != 0 && i > 1)
{
Range range1 = myWorksheet.Cells.CreateRange(“A” + i + “:M” + i);
range1.ApplyStyle(style1, flag);
}
}

I have one query, If i want to apply color for one particular row- say for example- if i have a row with name “AddUser” I want to add the color as green. else Red.
Can you please find the attachement and help me in doing the same.

Hi Shiva,


What I understand from your recently shared scenario is that you wish to find a particular string from a worksheet. In case the specified string is found, you wish to paint the row with Green otherwise Red. If I am correct in my understanding, please try the following piece of code that searches the string “AddUser” and paints the row accordingly.

C#

var myWorkBook = new Workbook(“D:/sample.xls”);
var worksheet = myWorkBook.Worksheets[0];

var maxDisplay = worksheet.Cells.MaxDisplayRange;

//Specify the range where you want to search
var area = CellArea.CreateCellArea(“A1”, CellsHelper.ColumnIndexToName(maxDisplay.ColumnCount) + maxDisplay.RowCount);
//Specify Find options
var opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);

Cell cell = null;
var rowIndices = new ArrayList();
do
{
//Search the cell with value search within range
cell = worksheet.Cells.Find(“AddUser”, cell, opts);

//If no such cell found, then break the loop
if (cell == null)
{
break;
}

rowIndices.Add(cell.Row);
} while (true);

for (int index = 1; index < maxDisplay.RowCount; index++)
{
var style = worksheet.Cells.Rows[index].Style;
if (rowIndices.Contains(index))
{
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Green;
}
else
{
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Red;
}
worksheet.Cells.Rows[index].ApplyStyle(style, new StyleFlag() { CellShading = true });
}
myWorkBook.Save(“D:/output.xlsx”);