Formatting in excel failing with C Sharp

T K

Conditional row formatting is failing in excel sheet through my C# Code. I have 4 conditions based on which row color is applied in excel.

Condition One applies Blue color
Condition two applies Pink color
Condition three applies Green color
Condition four applies Yellow color

See attached image:

When the color in Condition 4 is applied, it changes the last row color of Condition 3. Kindly advise.
Note: I am using Excel Interop version- 1.7.0.0.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

T K11y ago
Will try and update you. Thanks
Keyur Patel11y ago
https://msdn.microsoft.com/en-us/library/bb404903(v=office.12).aspx

https://www.snip2code.com/Snippet/613/How-to-set-a-conditional-formatting-in-a

http://stackoverflow.com/questions/15216047/programatically-adding-conditional-formatting-in-excel-2007
T K11y ago
But there is no CSharp code in the links that you posted which would explain how to use it?
Keyur Patel11y ago
Hello,

We can use conditionalFormat in excel. You can check from following links.

https://support.office.com/en-au/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

http://www.wikihow.com/Apply-Conditional-Formatting-in-Excel
T K11y ago
Issue is in the rows and not columns, removing the "Remove Count -1 from loop." throws error !! Can we use something like ConditionalFormat in excel
Keyur Patel11y ago
Hello,

Please try with following loop. Remove Count -1 from loop.

for (int j = 0; j <= datatable2.Rows.Count; j++)
{

}
T K11y ago
I have begun formatting from Row no 3 till column no 7, Below is my source code-

for (int j = 0; j <= datatable2.Rows.Count - 1; j++)
{
startCellIndex = "A" + (j + 2).ToString();
dr = datatable2.Rows[j];

formatRange = worksheet.get_Range(startCellIndex, "G7"); //Create a Range object

if (Condition1 == true)
{
formatRange.Interior.Color = Color.Blue;
}
else if (Condition2 == true)
{
formatRange.Interior.Color = Color.Pink;
}
else if (Condition3 == true)
{
formatRange.Interior.Color = Color.Green;
}
else if (Condition4 == true)
{
formatRange.Interior.Color = Color.Yellow;
}
for (int i = 0; i <= datatable2.Columns.Count - 1; i++) /*Setting cell content*/
{
worksheet.Cells[j + 2, i + 1] = dr[i];
}
}
workbook.SaveAs(spath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
Keyur Patel11y ago
Hello,

I am not sure,whether it's correct or not but i think, something problem in your condition loop.

Excel cell start with row 1 , column 1. try to start with your loop row 1 instead of 0.

Please check in debug mode, cell address of last condition 3.

If you are still having an issue than please share your code.