Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to get cell background color of excel using C#?

Tags:

c#

excel

I want to retrieve the background color of cell of excel using c# but I just can't find a way to do it

  1. which library shall I use ? microsoft.office.tools.excel.workbook.aspx or microsoft.office.interop.excel ? what's the differents?

  2. I have tried following code but get no luck:

    using Excel = Microsoft.Office.Interop.Excel;
    
    private void button1_Click(object sender, EventArgs e)
    {
        ofd.Filter = "xlsx|*.xlsx|xls|*.xls";
    
        if(ofd.ShowDialog() == DialogResult.OK)
        {
            textBox2.Text = ofd.FileName;
            Excel.Application excel = new Excel.Application();
            Excel.Workbook wb = excel.Workbooks.Open(textBox2.Text);
            Excel.Worksheet ws = wb.Sheets[1];
            Excel.Range xlRange = ws.Cells[0, 0];
            Debug.WriteLine("===" + xlRange.Interior.Color);
        }
    }
    
like image 337
armnotstrong Avatar asked Apr 02 '18 12:04

armnotstrong


2 Answers

There is nice lib called EPPlus, that makes your relationships with excel much easier. You can find it on NuGet.
So use this code to get color:

var x = sheet.Cells[rowIndex, colIndex].Style.Fill.BackgroundColor;

And this to set color:

sheet.Cells[rowIndex, colIndex].Style.Fill.SetCellsColor( Color.Yellow );

like image 81
Sam Sch Avatar answered Oct 10 '22 18:10

Sam Sch


Easiest solution what-so-ever :

 private void Get_Colors()
  {
      Excel.Workbook excel = Globals.ThisAddIn.Application.ActiveWorkbook;
      Excel.Worksheet sheet = null;
      Excel.Range ran = sheet.UsedRange;
      for (int x = 1; x <= ran.Rows.Count; x++)
      {
          for (int y = 1; y <= ran.Columns.Count; y++)
          {
              string CellColor = sheet.Cells[x, y].Interior.Color.ToString(); //Here I go double value which is converted to string.
              if (sheet.Cells[x, y].Value != null && (CellColor == Color.Transparent.ToArgb().ToString() || **CellColor == Excel.XlRgbColor.rgbGold.GetHashCode().ToString()**))
              {
                  sheet.Cells[x, y].Interior.Color = Color.Transparent;
              }
          }
      }
  }
like image 33
Christopher H. Avatar answered Oct 10 '22 18:10

Christopher H.