Export to Excel with Grid Color in Winforms using C#

T.saravanan
Posted by T.saravanan under C# category on | Points: 40 | Views : 19017
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 :)

Comments or Responses

Posted by: Tusharpatel on: 5/27/2011 Level:Starter | Status: [Member] | Points: 10

Posted by: Harishbansal90 on: 7/18/2011 Level:Starter | Status: [Member] | Points: 10
Hi

how to doing the same thing on Open Office Excel Spreadsheet?
please help.
i don't have Microsoft office excel.

thanks in advance
Posted by: T.saravanan on: 7/27/2011 Level:Silver | Status: [Member] [MVP] | Points: 10
Hi Harishbansal90,

Sorry for the delay...i am not use open office...but i am search in google i got the below links i think its useful to you.

http://www.codeproject.com/KB/cs/TblProc.aspx
http://www.codeproject.com/KB/office/exportxlscsv.aspx?display=PrintAll

Login to post response