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.