Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
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
Updated 9-Jan-19 23:15pm

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
 
Share this answer
 
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
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
 
Share this answer
 
This worked perfect for me.

xlsWorkSheet.Cells(row, column).interior.color = Color.Green
 
Share this answer
 
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;
 
Share this answer
 
Comments
Maciej Los 10-Jan-19 5:19am    
In what aspect your answer is better than accepted solution?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900