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:
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”);