Get Specific Text in a Range of Excel cells using C#

Question

I am working in WPF application with excel . In that application i am having a column name as Bulk. That column contains two options like YES and NO as a text. Now i need to indicate the font as red color for YES option and Normal Black color for NO option. I got the range of that particular column. But i dont know How shall i get the YES options and NO options seperately and also i need to color that options as per the need.

Here I mentioned my code :

foreach(Range Value in range.cells)
{

???????
???????
}

Any one pls tell me the solution of this. How shall i proceed this process. Thanks in Advance.

Possibly you asked this question many times http://stackoverflow.com/questions/2453152/cell-color-change-in-excel-using-conditional-formatting-in-c-sharp, http://stackoverflow.com/questions/2452417/cell-color-changing-in-excel-using-c-sharp — Developer, Nov 08 '11 at 10:23

Developer · Answer 1 · 2011-11-08T10:22:34.280

Try this

string strFileName = "D:\\test1.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelObj = null;
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A11", "G21"); // Your requied range here

foreach (Microsoft.Office.Interop.Excel.Range cell in range.Cells)
{
   if(cell.TextToString()=="Yes")
    {
    }
   if(cell.TextToString()=="No")
    {
    }

}

For more information check here

http://msdn.microsoft.com/en-us/library/4zs9xy29(v=vs.80).aspx

score 1 · Accepted Answer · edited Mar 10 '14 at 12:34

Add this class to your project and change the namespace to yours and start using it to create what you want in coloring settings

as far as your YES/NO detection, you can do the following:

        Microsoft.Office.Interop.Excel.Range range = myworksheet.UsedRange;
        //Start iterating from 1 not zero, 1 is the first row after notation of the current coloumn
        for (int i = 1; i <= range.Rows.Count; i++)
        {
            Microsoft.Office.Interop.Excel.Range myrange = myworksheet.get_Range(/*your column letter, ex: "A" or "B"*/ + i.ToString(), System.Reflection.Missing.Value);
            string temp = myrange.Text;
            if(temp.Contains("YES"))
            {
                //Do your YES logic
            }
            else if(temp.Contains("NO"))
            {
                //Do your No Logic
            }
        }

Shouldn't that iteration run while `i <= range.Rows.Count` ? — Mike Woodhouse, Nov 08 '11 at 09:49

score 0 · Answer 3 · edited Nov 08 '11 at 10:15

As for the Value check on the Cells, you can use the Class that Sherif Mahar Eaid Suggested, When I use VBA to look for excel cells, I use something that looks more like this

' this is VB script though.

if (Range("A1").Value = "DesiredString") Then Code End If

I am almost certain that for cell objects there is a value get method on C#, and for the colors, I think that

YourRange.Font.Color = System.Drawing.Color.Black.ToArgb();

will work...

Get Specific Text in a Range of Excel cells using C#

3 Answers3