Export to Excel with Grid Color in Winforms using C#

Hi All,

Here i am given a Code Snippet for How to Export to Excel from DataGridView with its Cell Color.

First you can add the reference of Microsoft.Office.Interop.Excel in your project.

Use the following Namespace in your Export to Excel Class...

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

Use the following methods for Export to Excel....

Here sPath --> Its your output path (Ex: C:\\ABC.xls).
Private void CreateExcel(string sPath)
{
   DataTable dt = (DataTable)dgvGridData.DataSource;
   int n = dt.Columns.Count;
   string[] strArr = new string[n];
   objct objValue = System.Reflection.Missing.Value;
   Excel.Application sXLApp = new Excel.Application();
   Excel.Workbooks sXLBooks = (Excel.Workbooks)sXLApp.Workbooks;
   Excel._Workbook sXLBook = (Excel._Workbook)(sXLBooks.Add(objValue));
   Excel.Sheets sXLSheets = (Excel.Sheets)sXLBook.Worksheets;
   Excel._Worksheet sXLWorksheet = (Excel._Worksheet)(sXLSheets.get_Item(1));

   for(int x=0; x < n; x++)   		
   {
      strArr[x] = dt.Columns[x].ColumnName.ToString().Trim(); 			 
   } 	
   object objHeaders = (object)strArr;
   Excel.Range sXLRange = sXLWorksheet.get_Range("A1", "IV1");
   sXLRange.set_Value(objValue, objHeaders);
   Excel.Font sXLFont = sXLRange.Font;
     	
   // To Assign Empty Column Header is null
   for(int y = n+1; y <= sXLRange.Count ; y++)   		
   {
      sXLRange[1,y] = null;		 
   } 	
   
   sXLFont.Bold = true;    // To Assign Header in Bold

   object[,] objData = new object[dt.Rows.Count, dt.Columns.Count];

   for(int nRow = 0; nRow < dt.Rows.Count; nRow++)
   {
      for(int nCol = 0; nCol< dt.Rows.Count; nCol++)
      {
 	objData[nRow, nCol] = dt.Rows[nRow][nCol].ToString().Trim();
      } 	 	 	
   }
   sXLRange = sXLWorkSheet.get_Range("A2", objValue);
   sXLRange = sXLRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
   sXLRange.set_Value(objValue, objData);
   
   //If you need Apply the color into Excel Cell based on Grid Cell	
   for(int c=0; c < dgvGirdData.Columns.Count; c++)
   {
      // To get the Excel Cell Name	
      string sCell = GetExcelCell(c + 1);	

      for(int r=0; r < dgvGridData.Rows.Count; r++)
      {
	  sXLRange = sXLWorksheet.get_range(sCell + (r+2), sCell + (r+2));
	  sXLRange.Interior.Color = ColorTranslator.ToOle(dgvGridData[c, r].Style.BackColor); 
      }			
    }

    sXLApp.Columns.EntireColumn.AutoFit();
    sXLApp.Columns.EntireRow.AutoFit();
    sXlBook.SaveAs(sPath, ObjValue, ObjValue, ObjValue, ObjValue, ObjValue,
			Excel.XlSaveAsAccessMode.xlNoChange, ObjValue, ObjValue, ObjValue, ObjValue, ObjValue);			
    sXLBook.Close(false, ObjValue, ObjValue);
    sXLApp.Quit();
    GC.WaitForPendingFinalizers();
    GC.Collect();				  
    GC.WaitForPendingFinalizers();
    GC.Collect();				      	 	
}

The following method is used to get a Cell Name from Excel based on Column No.
Ex: If column No '5' means its return 'E'

private string GetExcelCell(int nID)
{
  string sCell = string.Empty;
  if(nID < 27)
  {
    switch (nID)
    {
	case 0:
	     sCell = "z";
   	     break; 	
	case 1:
	     sCell = "A";
   	     break; 	
	case 2:
	     sCell = "B";
   	     break; 	
	case 3:
	     sCell = "C";
   	     break; 	

		.
		.
		.
		.
		.
		.
		.
		.
		.
		.
	case 25:
	     sCell = "Y";
   	     break; 	
	case 26:
	     sCell = "Z";
   	     break; 	
	default:
	     sCell= String.Empty;
	     break;						
    }	 	   	
	return sCell;
  }  
  else
  {
      	int nDiv = nID / 26;
 	int nMod = nID % 26;
	if (nMod.Equals(0))
	{
	   nDiv = nDiv - 1;
	}     	
	sCell = GetExcelCell(nDiv);
	sCell = sCell + GetExcelCell(nMod);	
	return sCell;
    } 	
}

Try this code i hope its useful to Export to Excel.This code also suitable to webForms.

Cheers :)