how to set back ground color of excel sheet cell

Question

0.00/5 (No votes)

See more:

I want to set bgcolor of excel sheet cell in c# and saveas option also.
I am using com reference

C#

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;
          
            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
              oXL.Visible = true;

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                //Add table headers going cell by cell.
                int i = 0;
              
                for (i=0; i <= DateTime.DaysInMonth(Convert.ToDateTime(datestring).Year, Convert.ToDateTime(datestring).Month); i++)
                {
                    if (i == 0)
                    {
                        oSheet.Cells[1, i+1] = "UserName";
                    }
                    else
                    {
                        
                        oSheet.Cells[1, i + 1] = Convert.ToString(i);
                    }
                }oSheet.get_Range("A1", "AF1").Font.Bold = true;
   oRng = oSheet.get_Range("A1", "AF1");
                oRng.EntireColumn.AutoFit();
oSheet.get_Range("A", "D").Font.Background = true;

                
                oWB.Save();

Posted 18-Jun-12 19:02pm

Member 7909353

Updated 9-Jan-19 23:15pm

Add a Solution

4 solutions

Solution 3

This worked perfect for me.

xlsWorkSheet.Cells(row, column).interior.color = Color.Green

Posted 24-Oct-14 3:19am

jwrule

Solution 4

For Coloring The Excel Cell Range:

worksheet.Cells["A2:AZ"].interior.color= System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

For Removing Color from Excel Cell Range:

xlWorkSheet1.Range["A2:AZ"].Interior.ColorIndex = 0;

Posted 9-Jan-19 23:15pm

Imran AS Shaikh

Comments

Maciej Los 10-Jan-19 5:19am

In what aspect your answer is better than accepted solution?

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sunny_Kumar_ · Accepted Answer · 2012-06-18T19:23:00

Solution 1

Hi Member 7909353,

To set the background color of a cell in excel sheet, you need to set It's Interior properties: see the example below:

C#

Range rng = NewWorkSheet.get_Range("A1:Z1", Missing.Value);
rng.Interior.Color = XlRgbColor.rgbLightSteelBlue;

Here above in the example, "XlRgbColor" enum provides the available color codes for an excel sheet.

To save as, use below given syntx:

C#

NewWorkSheet.SaveAs("SaveAsFileName.xlsx", XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Hope this helps you to achieve the purpose. Reply for any clarification if you need.

Happy Coding :)
Sunny_K

Posted 18-Jun-12 19:23pm

Sunny_Kumar_

Comments

Member 7909353 19-Jun-12 1:29am

In saveas method it gives error
The name 'XlFileFormat' does not exist in the current context

Member 7909353 19-Jun-12 1:31am

it gives error
The name 'XlRgbColor' does not exist in the current context

Sunny_Kumar_ 19-Jun-12 1:49am

the problem must be due to your reference. I assume this as "using Microsoft.Office.Interop.Excel;" while you're using "Excel = Microsoft.Office.Interop.Excel;"
try this or update the references as "Excel.XlRgbColor" ... and all that.

Sunny_Kumar_ 19-Jun-12 2:02am

please don't forget to vote and accept as answer if it helped you.

Member 7909353 19-Jun-12 2:06am

Excel.XlRgbColor.rgbLightBlue; is working but I want fill a cell where content is 5

Sunny_Kumar_ 19-Jun-12 2:14am

the use like this:
if(((Range)NewWorkSheet.Cells[i, j]).Value=="5")
{
((Range)NewWorkSheet.Cells[i,j]).Interior.Color = XlRgbColor.rgbLightBlue;
}

you need to check cell's value.

Member 14680349 6-Dec-19 3:13am

This could help to change cell background color only when excel file is closed. But cannot do anything when excel file is opening. Any better solution?

Member 7909353 · Accepted Answer · 2012-06-19T00:19:00

C#

string addrr = GetCellAddress(rownumber, j + 1);
                           oRng = oSheet.get_Range(addrr, addrr);
                           oRng.Interior.Color = Excel.XlRgbColor.rgbLightBlue;

Call this function

C#

public string GetCellAddress(int row, int col)
        {
            StringBuilder sb = new StringBuilder();
            col--;
            if (col < 26)
            {
                sb.Insert(0, (char)('A' + (col % 26)));
                sb.Append(row);
            }
            else
            {
                sb.Append('A');
                sb.Insert(1, (char)('A' + (col % 26)));
                sb.Append(row);
            }
            return sb.ToString();
        }

this will give address of cell

how to set back ground color of excel sheet cell

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

how to set back ground color of excel sheet cell

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us