We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How can I set Row Style (color) By Cell Value

Hi Aspose Teams.
Please help me solve my problem. I want to insert table to workbook sheet, then, set color of row depend on cell value. Example:

Cell1.0|Cell 1.1|Cell 1.2| 1
Cell2.0|Cell 2.1|Cell 2.2| 1
Cell3.0|Cell 3.1|Cell 3.2| 2
Cell4.0|Cell 4.1|Cell 4.2| 2
Cell5.0|Cell 5.1|Cell 5.2| 1

I want to set every row with cell (i,3) = 1 to red, every row with cell (i,3) = 2 to blue.
Thank for your help.

Hi,


I think you may use conditional formatting to apply to your area of cells/range of cells, you may specify your desired formatting condition accordingly as per your needs. I have written a sample code for your needs for your reference, you may refer to the code snippet and create your own for your requirements, see the sample code below:

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

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

if (i % 2 == 0)
{
sheet.Cells[i, 3].PutValue(2);

}
else
{
sheet.Cells[i, 3].PutValue(1);
}


}

//Add conditional formattings
int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Create a cell area D1:D10
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 9;
ca.StartColumn = 3;
ca.EndColumn = 3;

fcs.AddArea(ca);

//Adds conditions.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “1”, “”);
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “2”, “”);
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;

FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Blue;
workbook.Save(“e:\test2\outputconditionform1.xlsx”);

Also, for a complete reference, please see the document:
http://www.aspose.com/docs/display/cellsnet/Conditional+Formatting

Let us know if you have any issue or other query.

Thank you.

Hi Amjad. Thank for your quick reply
But I want to set color for all row (not for only cell 3).
How can I do?
Thanks

Hi,


Well, you just need to specify or extend your CellArea accordingly, e.g see the updated code segment here.

Sample code:

//Create a cell area based on the whole data sheet.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = sheet.Cells.MaxDataRow;
ca.StartColumn = 0;
ca.EndColumn = sheet.Cells.MaxDataColumn;

fcs.AddArea(ca);

Hi,
I used your code snippet, but only columm has value be set color, pls help me to set color for all cell of row. Thanks
Code Snippet :

            Workbook workbook1 = new Workbook();
Worksheet sheet1 = workbook1.Worksheets[0];
        <span style="color:blue;">for</span> (<span style="color:blue;">int</span> i = 0; i < 10; i++)
        {

            <span style="color:blue;">if</span> (i % 2 == 0)
            {
                sheet1.Cells[i, 3].PutValue(2);

            }
            <span style="color:blue;">else</span>
            {
                sheet1.Cells[i, 3].PutValue(1);
            }



        }

        <span style="color:green;">//Add conditional formattings</span>
        <span style="color:blue;">int</span> index = sheet1.ConditionalFormattings.Add();

        <span style="color:#2b91af;">FormatConditionCollection</span> fcs = sheet1.ConditionalFormattings[index];

        <span style="color:#2b91af;">CellArea</span> ca = <span style="color:blue;">new</span> <span style="color:#2b91af;">CellArea</span>();
        ca.StartRow = 0;
        ca.EndRow = sheet.Cells.MaxDataRow;
        ca.StartColumn = 0;
        ca.EndColumn = sheet.Cells.MaxDataColumn;

        fcs.AddArea(ca);

        <span style="color:green;">//Adds conditions.</span>
        <span style="color:blue;">int</span> conditionIndex = fcs.AddCondition(<span style="color:#2b91af;">FormatConditionType</span>.CellValue, <span style="color:#2b91af;">OperatorType</span>.Equal, <span style="color:#a31515;">"1"</span>, <span style="color:#a31515;">""</span>);
        <span style="color:blue;">int</span> conditionIndex2 = fcs.AddCondition(<span style="color:#2b91af;">FormatConditionType</span>.CellValue, <span style="color:#2b91af;">OperatorType</span>.Equal, <span style="color:#a31515;">"2"</span>, <span style="color:#a31515;">""</span>);

        <span style="color:green;">//Sets the background color.</span>
        <span style="color:#2b91af;">FormatCondition</span> fc = fcs[conditionIndex];
        fc.Style.BackgroundColor = <span style="color:#2b91af;">Color</span>.Red;

        <span style="color:#2b91af;">FormatCondition</span> fc2 = fcs[conditionIndex2];
        fc2.Style.BackgroundColor = <span style="color:#2b91af;">Color</span>.Blue;

        workbook1.Save(<span style="color:#a31515;">"e:\\outputconditionform1.xlsx"</span>);</pre><br>

Hi,


I have updated my code segment a bit now accordingly. Now if a cell is blank in the given range i.e…, “A1:D10”, it will be filled with “Yellow” color, I have added some more formatting conditions accordingly, see the update code below. Please refer to it and you may create your own code accordingly for your needs.

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

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

if (i % 2 == 0)
{
sheet.Cells[i, 3].PutValue(2);

}
else
{
sheet.Cells[i, 3].PutValue(1);
}


}

//Add conditional formattings
int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Create a cell area A1:D10
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 9;
ca.StartColumn = 0;
ca.EndColumn = 3;

fcs.AddArea(ca);

//Adds conditions.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “1”, “”);
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “2”, “”);
int conditionIndex3 = fcs.AddCondition(FormatConditionType.ContainsBlanks);
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;

FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Blue;

FormatCondition fc3 = fcs[conditionIndex3];
fc3.Style.BackgroundColor = Color.Yellow;
workbook.Save(“e:\test2\outputconditionform2.xlsx”);