How to Export data from DataTable to Excel

You can find many articles on internet that will provide a solution for exporting data from DataGrid to Excel sheet. i searched many articles on websites and from many articles which provide a solution to this problem but from all those articles the article found at this site.

you may need to change it little according tou your requirements.

private void ExportToExcel(DataTable dt)
Microsoft.Office.Interop.Excel.Application objApp;
Microsoft.Office.Interop.Excel._Workbook objBook;

Microsoft.Office.Interop.Excel.Workbooks objBooks;
Microsoft.Office.Interop.Excel.Sheets objSheets;
Microsoft.Office.Interop.Excel._Worksheet objSheet;
Microsoft.Office.Interop.Excel.Range range;

// Instantiate Excel and start a new workbook.
objApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Type.Missing);
objSheets = objBook.Worksheets;
objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);

//Get the range where the starting cell has the address
//m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range = objSheet.get_Range(“A1”, Type.Missing);
range = range.get_Resize(dt.Rows.Count +1 , dt.Columns.Count +1);

//Create an array.
string[,] saRet = new string[dt.Rows.Count+1 ,dt.Columns.Count +1];

for (int col = 0; col <= dt.Columns.Count – 1; col++)
saRet[0, col] = dt.Columns[col].ColumnName.ToUpper();
bool val = false;
//Fill the array.
for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
//Put the row and column address in the cell.
if (iCol == 0)
saRet[iRow+1, 0] = Convert.ToDateTime(dt.Rows[iRow].ItemArray[0].ToString()).ToShortDateString();

saRet[iRow+1, iCol] = dt.Rows[iRow].ItemArray[iCol].ToString();

//Set the range value to the array.
range.set_Value(Type.Missing, saRet);

//Return control of Excel to the user.
objApp.Visible = true;
objApp.UserControl = true;
objBook.SaveAs(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,       Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
catch (Exception theException)
String errorMessage;
errorMessage = “Error: “;
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, ” Line: “);
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, “Error”);

This code  included to format date

public static bool IsDate(string anyString)
DateTime dummyDate;
if (anyString == null)
anyString = “”;

if (anyString.Length > 0)
dummyDate = DateTime.Parse(anyString);
return false;
return true;
return false;
You can also add other checks to insert values as you want to.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s