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;

try
{
// 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();
continue;
}

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)
{
try
{
dummyDate = DateTime.Parse(anyString);
}
catch
{
return false;
}
return true;
}
else
{
return false;
}
}
You can also add other checks to insert values as you want to.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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