How to export datatable to excel in c#

Posted by Chaithragm under C# on 1/2/2014 | Points: 10 | Views : 156383 | Status : [Member] | Replies : 6
private void export_to_excel_btn_Click(object sender, EventArgs e)
{
datatable dt;

dt.ExportToExcel(ExcelFilePath)//error
//datatable does not contain definition for ExportToExcel
}

public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");

// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();

// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;

// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}

// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}

// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}





Responses

Posted by: vishalneeraj-24503 on: 1/2/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You can use DataTable WriteXML method,to export datatable rows into excel sheet.
first create file with extention as xls and call WriteXml method and pass file name.

For Example:-

private static void DataTableToXml()

{
try
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath FROM Employees";
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter sda;
DataTable dt;

using (con = new SqlConnection(ConString))
{
cmd = new SqlCommand(CmdString, con);
con.Open();
dt = new DataTable("Employees");
sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
dt.WriteXml("Employees.xls");
con.Close();
}
}
catch (Exception)
{
throw;
}
}


Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 1/2/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You can also try below code:-

private void DataTableToXml()

{
try
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath " +
"FROM Employees FOR XML RAW('Employee'), ROOT('Employees'), ELEMENTS";
SqlConnection con;
SqlCommand cmd;
XmlReader reader;
XmlDocument xmlDoc;

using (con = new SqlConnection(ConString))
{
cmd = new SqlCommand(CmdString, con);
con.Open();
reader = cmd.ExecuteXmlReader();
xmlDoc = new XmlDocument();

while (reader.Read())
{
xmlDoc.Load(reader);
}

xmlDoc.Save("Employees.xls");
}
}
catch (Exception)
{
throw;
}
}


Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: kgovindarao523-21772 on: 1/2/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,
you are using Extension Methods. Right?
Refer the following link to extension method.
http://www.dotnetperls.com/extension

Now solution to your question is:
Write 'ExportToExcel' in static class.

public static class MyClass

{
public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");

// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();

// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;

// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}

// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}

// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
//MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}



Thank you,
Govind

Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ring on: 1/9/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,
here is another easier solution for you,
private void btnRun_Click(object sender, EventArgs e)
{
Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;
worksheet1.StartDataCol = ((System.Byte)(0));
cellExport.Sheets.Add(worksheet1);
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport.SaveToFile("20110223.xls");
}

reference here:
http://spreadsheet.codeplex.com/
hope it helps.


Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Alex.Pulver on: 4/28/2014 [Member] Starter | Points: 25

Up
0
Down
Try also this solution:

DataSet ds = new DataSet();

ds.Tables.Add(dataTable);

ExcelDocument xls = new ExcelDocument();
xls.easy_WriteXLSFile_FromDataSet(ExcelFilePath, ds,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataTable");


See the entire example here:
http://www.easyxls.com/manual/FAQ/export-datatable-to-excel.html

Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sadman on: 6/30/2014 [Member] Starter | Points: 25

Up
0
Down
refer this tutorails ,I export datatable from database to Excel correctly with the guidance
http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

And core code fragment would be as follows, hope this makes sense
private void button1_Click(object sender, EventArgs e)
{
//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls");
System.Diagnostics.Process.Start("insertTableToExcel.xls");
}


Chaithragm, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response