How to export DataTable to Excel

Question

How can I export a DataTable to Excel in C #? I am using Windows Forms. The DataTable is associated with the DataGridView control. I need to export DataTable records to Excel.

Answer 1

I would recommend ClosedXML -

You can turn a DataTable into an Excel worksheet with some very readable code:

 XLWorkbook wb = new XLWorkbook(); DataTable dt = GetDataTableOrWhatever(); wb.Worksheets.Add(dt,"WorksheetName"); 

The developer responds and helps. The project is actively developing and the documentation is excellent.

Answer 2

Try simple code to convert a DataTable to an excel file as csv:

 var lines = new List<string>(); string[] columnNames = dataTable.Columns.Cast<DataColumn>(). Select(column => column.ColumnName). ToArray(); var header = string.Join(",", columnNames); lines.Add(header); var valueLines = dataTable.AsEnumerable() .Select(row => string.Join(",", row.ItemArray)); lines.AddRange(valueLines); File.WriteAllLines("excel.csv",lines); 

This will write the new excel.csv file to the "current working directory", which is usually located either where the .exe is located, or where you start it from.

Note that the output places commas ( "," ) between the data already contained in the dataTable. Since it does not skip commas in the data, commas in the data will be misinterpreted by the program reading the file.

Answer 3

An elegant option is to write an extension method (see below) for the DataTable.net framework class.

This extension method can be called as follows:

 using System; using System.Collections.Generic; using System.Linq; using Excel = Microsoft.Office.Interop.Excel; using System.Data; using System.Data.OleDb; DataTable dt; // fill table data in dt here ... // export DataTable to excel // save excel file without ever making it visible if filepath is given // don't save excel file, just make it visible if no filepath is given dt.ExportToExcel(ExcelFilePath); 

Extension method for the DataTable class:

 public static class My_DataTable_Extensions { // Export DataTable into an excel file with field names in the header line // - Save excel file without ever making it visible if filepath is given // - Don't save excel file, just make it visible if no filepath is given 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 var excelApp = new Excel.Application(); excelApp.Workbooks.Add(); // single worksheet Excel._Worksheet workSheet = excelApp.ActiveSheet; // column headings for (var i = 0; i < tbl.Columns.Count; i++) { workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName; } // rows for (var i = 0; i < tbl.Rows.Count; i++) { // to do: format datetime values before printing for (var j = 0; j < tbl.Columns.Count; j++) { workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j]; } } // check file path if (!string.IsNullOrEmpty(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 file path is given excelApp.Visible = true; } } catch (Exception ex) { throw new Exception("ExportToExcel: \n" + ex.Message); } } } 

Answer 4

The solution is based on the tuncalik article (thanks for the idea), but in the case of large tables it works much faster (and a little less clear).

 public static class My_DataTable_Extensions { /// <summary> /// Export DataTable to Excel file /// </summary> /// <param name="DataTable">Source DataTable</param> /// <param name="ExcelFilePath">Path to result file name</param> public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null) { try { int ColumnsCount; if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0) throw new Exception("ExportToExcel: Null or empty input table!\n"); // load excel, and create a new workbook Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbooks.Add(); // single worksheet Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet; object[] Header = new object[ColumnsCount]; // column headings for (int i = 0; i < ColumnsCount; i++) Header[i] = DataTable.Columns[i].ColumnName; Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount])); HeaderRange.Value = Header; HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); HeaderRange.Font.Bold = true; // DataCells int RowsCount = DataTable.Rows.Count; object[,] Cells = new object[RowsCount, ColumnsCount]; for (int j = 0; j < RowsCount; j++) for (int i = 0; i < ColumnsCount; i++) Cells[j, i] = DataTable.Rows[j][i]; Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells; // check fielpath if (ExcelFilePath != null && ExcelFilePath != "") { try { Worksheet.SaveAs(ExcelFilePath); Excel.Quit(); System.Windows.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 { Excel.Visible = true; } } catch (Exception ex) { throw new Exception("ExportToExcel: \n" + ex.Message); } } } 

Answer 5

Try this function to pass the path to the data and files where you want to export

 public void CreateCSVFile(ref DataTable dt, string strFilePath) { try { // Create the CSV file to which grid data will be exported. StreamWriter sw = new StreamWriter(strFilePath, false); // First we will write the headers. //DataTable dt = m_dsProducts.Tables[0]; int iColCount = dt.Columns.Count; for (int i = 0; i < iColCount; i++) { sw.Write(dt.Columns[i]); if (i < iColCount - 1) { sw.Write(","); } } sw.Write(sw.NewLine); // Now write all the rows. foreach (DataRow dr in dt.Rows) { for (int i = 0; i < iColCount; i++) { if (!Convert.IsDBNull(dr[i])) { sw.Write(dr[i].ToString()); } if (i < iColCount - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); } catch (Exception ex) { throw ex; } } 

Answer 6

The best and easiest way

 private void exportToExcel(DataTable dt) { /*Set up work book, work sheets, and excel application*/ Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application(); try { string path = AppDomain.CurrentDomain.BaseDirectory; object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet(); // obook.Worksheets.Add(misValue); osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"]; int colIndex = 0; int rowIndex = 1; foreach (DataColumn dc in dt.Columns) { colIndex++; osheet.Cells[1, colIndex] = dc.ColumnName; } foreach (DataRow dr in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn dc in dt.Columns) { colIndex++; osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName]; } } osheet.Columns.AutoFit(); string filepath = "C:\\Temp\\Book1"; //Release and terminate excel obook.SaveAs(filepath); obook.Close(); oexcel.Quit(); releaseObject(osheet); releaseObject(obook); releaseObject(oexcel); GC.Collect(); } catch (Exception ex) { oexcel.Quit(); log.AddToErrorLog(ex, this.Name); } } 

Answer 7

You can use EasyXLS , which is a library for exporting Excel files.

Check this code:

 DataSet ds = new DataSet(); ds.Tables.Add(dataTable); ExcelDocument xls = new ExcelDocument(); xls.easy_WriteXLSFile_FromDataSet("datatable.xls", ds, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataTable"); 

See also this example on how to export datatable to excel in C # .

Answer 8

NOTE. If you are trying to transfer values ​​from a data table to an object and then to Excel, you should also handle error handling of data type. For example, Guides will kill your assignment with the exception HRESULT: 0x800A03EC. One work without data type testing is to use "ToString ()" when populating your object. Excel will convert the numbers back to number format yourself. FlashTrev as a considered date / time issue.

Answer 9

Regarding tuncalik's answer, which is great, especially if you want to play a bit with the code :), but it puts my dates in Excel in American format, i.e. March 2, 2014 in the UK - 03/03/2014, but in the USA - 03 / 02/2014 with the 1st month, then the day of the week. I need to have it in british format, any ideas please?

I checked that it is stored in the British format in my DataTable, and also my Excel is installed in the UK, but for some reason, when he makes an Excel document, he thinks about his USA (this is because Microsoft is a company in the USA: )

I will try to experiment with culture codes, but I don’t know where to do it. I tried, but it had no effect.

ps

I had to change one line to make it work by adding "cast" as shown below

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

Update. I have achieved British date formatting by switching to LongDateTime format, but this is just the job.

 DateTime startDate = Convert.ToDateTime(myList[0].ToString()); string strStartDate = startDate.ToLongDateString(); DateTime endDate = Convert.ToDateTime(myList[myListTotalRows].ToString()); string strEndDate = endDate.ToLongDateString(); 

greetings.

Answer 10

Old thread - but thought I would drop my code here. I wrote a small function to write a data table to a new excel sheet at a specified path (location). You will also need to add a link to the microsoft excel 14.0 library.

I pulled from this thread a letter about writing something to excel - How to write some data to an excel file (.xlsx)

I used this to extrapolate how to write datatable

* note in catch operations. I have a link to the static class of the error handler (you can ignore them)

  using excel = Microsoft.Office.Interop.Excel; using System.IO; using System.Data; using System.Runtime.InteropServices; //class and namespace wrapper is not shown in this example private void WriteToExcel(System.Data.DataTable dt, string location) { //instantiate excel objects (application, workbook, worksheets) excel.Application XlObj = new excel.Application(); XlObj.Visible = false; excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add("")); excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet; //run through datatable and assign cells to values of datatable try { int row = 1; int col = 1; foreach (DataColumn column in dt.Columns) { //adding columns WsObj.Cells[row, col] = column.ColumnName; col++; } //reset column and row variables col = 1; row++; for (int i = 0; i < dt.Rows.Count; i++) { //adding data foreach (var cell in dt.Rows[i].ItemArray) { WsObj.Cells[row, col] = cell; col++; } col = 1; row++; } WbObj.SaveAs(location); } catch (COMException x) { ErrorHandler.Handle(x); } catch (Exception ex) { ErrorHandler.Handle(ex); } finally { WbObj.Close(); } } 

Answer 11

Excel Interop:

This method prevents the dat switch from dd-mm-yyyy to mm-dd-yyyy

 public bool DataTableToExcelFile(DataTable dt, string targetFile) { const bool dontSave = false; bool success = true; //Exit if there is no rows to export if (dt.Rows.Count == 0) return false; object misValue = System.Reflection.Missing.Value; List<int> dateColIndex = new List<int>(); Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(misValue); Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets("sheet1"); //Iterate through the DataTable and populate the Excel work sheet try { for (int i = -1; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { if (i < 0) { //Take special care with Date columns if (dt.Columns(j).DataType is typeof(DateTime)) { excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "d-MMM-yyyy;@"; dateColIndex.Add(j); } //else if ... Feel free to add more Formats else { //Otherwise Format the column as text excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "@"; } excelWorkSheet.Cells(1, j + 1) = dt.Columns(j).Caption; } else if (dateColIndex.IndexOf(j) > -1) { excelWorkSheet.Cells(i + 2, j + 1) = Convert.ToDateTime(dt.Rows(i).ItemArray(j)).ToString("d-MMM-yyyy"); } else { excelWorkSheet.Cells(i + 2, j + 1) = dt.Rows(i).ItemArray(j).ToString(); } } } //Add Autofilters to the Excel work sheet excelWorkSheet.Cells.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); //Autofit columns for neatness excelWorkSheet.Columns.AutoFit(); if (File.Exists(exportFile)) File.Delete(exportFile); excelWorkSheet.SaveAs(exportFile); } catch { success = false; } finally { //Do this irrespective of whether there was an exception or not. excelWorkBook.Close(dontSave); excelApp.Quit(); releaseObject(excelWorkSheet); releaseObject(excelWorkBook); releaseObject(excelApp); } return success; } 

If you don’t like when the dates are upside down, use the link that shows how to fill in all the cells in the Excel spreadsheet in one line of code:

Excel Interop - Efficiency and Performance

CSV:

 public string DataTableToCSV(DataTable dt, bool includeHeader, string rowFilter, string sortFilter, bool useCommaDelimiter = false, bool truncateTimesFromDates = false) { dt.DefaultView.RowFilter = rowFilter; dt.DefaultView.Sort = sortFilter; DataView dv = dt.DefaultView; string csv = DataTableToCSV(dv.ToTable, includeHeader, useCommaDelimiter, truncateTimesFromDates); //reset the Filtering dt.DefaultView.RowFilter = string.Empty; return csv; } public string DataTableToCsv(DataTable dt, bool includeHeader, bool useCommaDelimiter = false, bool truncateTimesFromDates = false) { StringBuilder sb = new StringBuilder(); string delimter = Constants.vbTab; if (useCommaDelimiter) delimter = ","; if (includeHeader) { foreach (DataColumn dc in dt.Columns) { sb.AppendFormat("{0}" + Constants.vbTab, dc.ColumnName); } //remove the last Tab sb.Remove(sb.ToString.Length - 1, 1); sb.Append(Environment.NewLine); } foreach (DataRow dr in dt.Rows) { foreach (DataColumn dc in dt.Columns) { if (Information.IsDate(dr(dc.ColumnName).ToString()) & dr(dc.ColumnName).ToString().Contains(".") == false & truncateTimesFromDates) { sb.AppendFormat("{0}" + delimter, Convert.ToDateTime(dr(dc.ColumnName).ToString()).Date.ToShortDateString()); } else { sb.AppendFormat("{0}" + delimter, CheckDBNull(dr(dc.ColumnName).ToString().Replace(",", ""))); } } //remove the last Tab sb.Remove(sb.ToString.Length - 1, 1); sb.Append(Environment.NewLine); } return sb.ToString; } public enum enumObjectType { StrType = 0, IntType = 1, DblType = 2 } public object CheckDBNull(object obj, enumObjectType ObjectType = enumObjectType.StrType) { object objReturn = null; objReturn = obj; if (ObjectType == enumObjectType.StrType & Information.IsDBNull(obj)) { objReturn = ""; } else if (ObjectType == enumObjectType.IntType & Information.IsDBNull(obj)) { objReturn = 0; } else if (ObjectType == enumObjectType.DblType & Information.IsDBNull(obj)) { objReturn = 0.0; } return objReturn; } 

Answer 12

One way to do this would also be the ACE OLEDB provider (see also connection strings for Excel ). Of course, you will need to install and register a provider. You should have this if you have Excel installed, but this is what you need to consider when deploying the application.

This is an example of calling a helper method from ExportHelper : ExportHelper.CreateXlsFromDataTable(myDataTable, @"C:\tmp\export.xls");

Helper to export to Excel file using ACE OLEDB:

 public class ExportHelper { private const string ExcelOleDbConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";"; /// <summary> /// Creates the Excel file from items in DataTable and writes them to specified output file. /// </summary> public static void CreateXlsFromDataTable(DataTable dataTable, string fullFilePath) { string createTableWithHeaderScript = GenerateCreateTableCommand(dataTable); using (var conn = new OleDbConnection(String.Format(ExcelOleDbConnectionStringTemplate, fullFilePath))) { if (conn.State != ConnectionState.Open) { conn.Open(); } OleDbCommand cmd = new OleDbCommand(createTableWithHeaderScript, conn); cmd.ExecuteNonQuery(); foreach (DataRow dataExportRow in dataTable.Rows) { AddNewRow(conn, dataExportRow); } } } private static void AddNewRow(OleDbConnection conn, DataRow dataRow) { string insertCmd = GenerateInsertRowCommand(dataRow); using (OleDbCommand cmd = new OleDbCommand(insertCmd, conn)) { AddParametersWithValue(cmd, dataRow); cmd.ExecuteNonQuery(); } } /// <summary> /// Generates the insert row command. /// </summary> private static string GenerateInsertRowCommand(DataRow dataRow) { var stringBuilder = new StringBuilder(); var columns = dataRow.Table.Columns.Cast<DataColumn>().ToList(); var columnNamesCommaSeparated = string.Join(",", columns.Select(x => x.Caption)); var questionmarkCommaSeparated = string.Join(",", columns.Select(x => "?")); stringBuilder.AppendFormat("INSERT INTO [{0}] (", dataRow.Table.TableName); stringBuilder.Append(columnNamesCommaSeparated); stringBuilder.Append(") VALUES("); stringBuilder.Append(questionmarkCommaSeparated); stringBuilder.Append(")"); return stringBuilder.ToString(); } /// <summary> /// Adds the parameters with value. /// </summary> private static void AddParametersWithValue(OleDbCommand cmd, DataRow dataRow) { var paramNumber = 1; for (int i = 0; i <= dataRow.Table.Columns.Count - 1; i++) { if (!ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(int)) && !ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(decimal))) { cmd.Parameters.AddWithValue("@p" + paramNumber, dataRow[i].ToString().Replace("'", "''")); } else { object value = GetParameterValue(dataRow[i]); OleDbParameter parameter = cmd.Parameters.AddWithValue("@p" + paramNumber, value); if (value is decimal) { parameter.OleDbType = OleDbType.Currency; } } paramNumber = paramNumber + 1; } } /// <summary> /// Gets the formatted value for the OleDbParameter. /// </summary> private static object GetParameterValue(object value) { if (value is string) { return value.ToString().Replace("'", "''"); } return value; } private static string GenerateCreateTableCommand(DataTable tableDefination) { StringBuilder stringBuilder = new StringBuilder(); bool firstcol = true; stringBuilder.AppendFormat("CREATE TABLE [{0}] (", tableDefination.TableName); foreach (DataColumn tableColumn in tableDefination.Columns) { if (!firstcol) { stringBuilder.Append(", "); } firstcol = false; string columnDataType = "CHAR(255)"; switch (tableColumn.DataType.Name) { case "String": columnDataType = "CHAR(255)"; break; case "Int32": columnDataType = "INTEGER"; break; case "Decimal": // Use currency instead of decimal because of bug described at // http://social.msdn.microsoft.com/Forums/vstudio/en-US/5d6248a5-ef00-4f46-be9d-853207656bcc/localization-trouble-with-oledbparameter-and-decimal?forum=csharpgeneral columnDataType = "CURRENCY"; break; } stringBuilder.AppendFormat("{0} {1}", tableColumn.ColumnName, columnDataType); } stringBuilder.Append(")"); return stringBuilder.ToString(); } } 

Answer 13

use the following class

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using excel = Microsoft.Office.Interop.Excel; using EL = ExcelLibrary.SpreadSheet; using System.Drawing; using System.Collections; using System.Runtime.InteropServices; using System.Windows.Forms; namespace _basic { public class ExcelProcesser { public void WriteToExcel(System.Data.DataTable dt) { excel.Application XlObj = new excel.Application(); XlObj.Visible = false; excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add("")); excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet; object misValue = System.Reflection.Missing.Value; try { int row = 1; int col = 1; foreach (DataColumn column in dt.Columns) { //adding columns WsObj.Cells[row, col] = column.ColumnName; col++; } //reset column and row variables col = 1; row++; for (int i = 0; i < dt.Rows.Count; i++) { //adding data foreach (var cell in dt.Rows[i].ItemArray) { WsObj.Cells[row, col] = cell; col++; } col = 1; row++; } WbObj.SaveAs(fileFullName, excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { WbObj.Close(true, misValue, misValue); } } } 

}