I have a DataTable with 30+ columns and 6500+ rows.I need to dump the whole DataTable values into an Excel file.Can anyone please help with the C# code.I need each column value to be in a cell.To be precise,I need the exact looking copy of DataTable in an Excel File.Please help.
Thanks, Vix
Try this to export the data to Excel file same as in DataTable and could customize also.
dtDataTable1 = ds.Tables[0];
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
for (int i = 1; i > 0; i--)
{
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
//Create Excel sheet
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlWorksheet.Name = "MY FIRST EXCEL FILE";
for (int j = 1; j < dtDataTable1.Columns.Count + 1; j++)
{
ExcelApp.Cells[i, j] = dtDataTable1.Columns[j - 1].ColumnName;
ExcelApp.Cells[1, j].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
ExcelApp.Cells[i, j].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.WhiteSmoke);
}
// for the data of the excel
for (int k = 0; k < dtDataTable1.Rows.Count; k++)
{
for (int l = 0; l < dtDataTable1.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] = dtDataTable1.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
If you to export datatable to excel with formatted header text try like this.
public void ExportFullDetails()
{
Int16 id = Convert.ToInt16(Session["id"]);
DataTable registeredpeople = new DataTable();
registeredpeople = this.dataAccess.ExportDetails(eventid);
string attachment = "attachment; filename=Details.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
registeredpeople.Columns["Reg_id"].ColumnName = "Reg. ID";
registeredpeople.Columns["Name"].ColumnName = "Name";
registeredpeople.Columns["Reg_country"].ColumnName = "Country";
registeredpeople.Columns["Reg_city"].ColumnName = "City";
registeredpeople.Columns["Reg_email"].ColumnName = "Email";
registeredpeople.Columns["Reg_business_phone"].ColumnName = "Business Phone";
registeredpeople.Columns["Reg_mobile"].ColumnName = "Mobile";
registeredpeople.Columns["PositionRole"].ColumnName = "Position";
registeredpeople.Columns["Reg_work_type"].ColumnName = "Work Type";
foreach (DataColumn dc in registeredpeople.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in registeredpeople.Rows)
{
tab = "";
for (i = 0; i < registeredpeople.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
I use This in page.`
public void DTToExcel(DataTable dt)
{
// dosya isimleri ileride ayni anda birden fazla kullanici ayni dosya üzerinde islem yapmak ister düsüncesiyle guid yapildi.
string FileName = Guid.NewGuid().ToString();
FileInfo f = new FileInfo(Server.MapPath("Downloads") + string.Format("\\{0}.xlsx", FileName));
if (f.Exists)
f.Delete(); // delete the file if it already exist.
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ClearHeaders();
response.ClearContent();
response.Charset = Encoding.UTF8.WebName;
response.AddHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
response.AddHeader("Content-Type", "application/Excel");
response.ContentType = "application/vnd.xlsx";
//response.AddHeader("Content-Length", file.Length.ToString());
// create a string writer
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw)) //datatable'a aldigimiz sorguyu bir datagrid'e atayip html'e çevir.
{
// instantiate a datagrid
DataGrid dg = new DataGrid();
dg.DataSource = dt;
dg.DataBind();
dg.RenderControl(htw);
response.Write(sw.ToString());
dg.Dispose();
dt.Dispose();
response.End();
}
}
}
Below link is used to export datatable to excel in C# Code.
http://royalarun.blogspot.in/2012/01/export-datatable-to-excel-in-c-windows.html
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
namespace ExportExcel
{
public partial class ExportDatatabletoExcel : Form
{
public ExportDatatabletoExcel()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
//Add Datacolumn
DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));
dt.Columns.Add("LastName", typeof(String));
dt.Columns.Add("Blog", typeof(String));
dt.Columns.Add("City", typeof(String));
dt.Columns.Add("Country", typeof(String));
//Add in the datarow
DataRow newRow = dt.NewRow();
newRow["firstname"] = "Arun";
newRow["lastname"] = "Prakash";
newRow["Blog"] = "http://royalarun.blogspot.com/";
newRow["city"] = "Coimbatore";
newRow["country"] = "India";
dt.Rows.Add(newRow);
//open file
StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != null)
{
wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
}
else
{
wr.Write("\t");
}
}
//go to next line
wr.WriteLine();
}
//close file
wr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Most answers are actually producing the CSV which I don't always have good experience with, when opening in Excel.
One way of doing it would be also with ACE OLEDB Provider (see also connection strings for Excel). Of course you'd have to have the provider installed and registered. You do have it, if you have Excel installed, but this is something you have to consider when deploying (e.g. on web server).
In below helper class code you'd have to call something like ExportHelper.CreateXlsFromDataTable(dataset.Tables[0], @"C:\tmp\export.xls");
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();
}
}
Please try this, this will export your data table data's faster to excel.
Note: Range "FW", that I have hard coded is because I had 179 columns.
public void UpdateExcelApplication(SqlDataTable dataTable)
{
var objects = new string[dataTable.Rows.Count, dataTable.Columns.Count];
var rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
var colIndex = 0;
foreach (DataColumn column in dataTable.Columns)
{
objects[rowIndex, colIndex++] = Convert.ToString(row[column]);
}
rowIndex++;
}
var range = this.workSheet.Range[$"A3:FW{dataTable.Rows.Count + 2}"];
range.Value = objects;
this.workSheet.Columns.AutoFit();
this.workSheet.Rows.AutoFit();
}
The most rank answer in this post work, however its is CSV file. It is not actual Excel file. Therefore, you will get a warning when you are opening a file.
The best solution I found on the web is using CloseXML https://github.com/closedxml/closedxml You need to Open XML as well.
dt = city.GetAllCity();//your datatable
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
I had a project that I also needed an exact replica of a DataTable in Excel format. Using ClosedXML, the following code did exactly that for me.
using ClosedXML.Excel;
private void DumpDataTableToExcel(DataTable dt, string SavePathAndFilename)
{
try
{
using (XLWorkbook excelWorkbook = new XLWorkbook())
{
excelWorkbook.AddWorksheet(dt);
excelWorkbook.SaveAs(SavePathAndFilename);
}
}
catch
{
//Insert error handling here...
}
}
This method will use your DataTable's column names as the column names in Excel and the DataTable name itself will be the sheet name. Hard to accomplish all of this with fewer lines of code.
Adding ClosedXML to your project is equally simple. There are instructions in the link above as well as directly on nuget.org.
Working code for Excel Export
try
{
DataTable dt = DS.Tables[0];
string attachment = "attachment; filename=log.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
catch (Exception Ex)
{ }
I have done the DataTable to Excel conversion with the following code. Hope it's very easy no need to change more just copy & pest the code replace your variable with your variable, and it will work properly.
First create a folder in your solution Document, and create an Excel file MyTemplate.xlsx. you can change those name according to your requirement. But remember for that you have to change the name in code also.
Please find the following code...
protected void GetExcel_Click(object sender, EventArgs e)
{
ManageTicketBS objManageTicket = new ManageTicketBS();
DataTable DT = objManageTicket.GetAlldataByDate(); //this function will bring the data in DataTable format, you can use your function instate of that.
string DownloadFileName;
string FolderPath;
string FileName = "MyTemplate.xlsx";
DownloadFileName = Path.GetFileNameWithoutExtension(FileName) + new Random().Next(10000, 99999) + Path.GetExtension(FileName);
FolderPath = ".\\" + DownloadFileName;
GetParents(Server.MapPath("~/Document/" + FileName), Server.MapPath("~/Document/" + DownloadFileName), DT);
string path = Server.MapPath("~/Document/" + FolderPath);
FileInfo file = new FileInfo(path);
if (file.Exists)
{
try
{
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ClearContent();
response.ClearHeaders();
response.Buffer = true;
response.ContentType = MimeType(Path.GetExtension(FolderPath));
response.AddHeader("Content-Disposition", "attachment;filename=" + DownloadFileName);
byte[] data = File.ReadAllBytes(path);
response.BinaryWrite(data);
HttpContext.Current.ApplicationInstance.CompleteRequest();
response.End();
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
DeleteOrganisationtoSupplierTemplate(path);
}
}
}
public string GetParents(string FilePath, string TempFilePath, DataTable DTTBL)
{
File.Copy(Path.Combine(FilePath), Path.Combine(TempFilePath), true);
FileInfo file = new FileInfo(TempFilePath);
try
{
DatatableToExcel(DTTBL, TempFilePath, 0);
return TempFilePath;
}
catch (Exception ex)
{
return "";
}
}
public static string MimeType(string Extension)
{
string mime = "application/octetstream";
if (string.IsNullOrEmpty(Extension))
return mime;
string ext = Extension.ToLower();
Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
if (rk != null && rk.GetValue("Content Type") != null)
mime = rk.GetValue("Content Type").ToString();
return mime;
}
static bool DeleteOrganisationtoSupplierTemplate(string filePath)
{
try
{
File.Delete(filePath);
return true;
}
catch (IOException)
{
return false;
}
}
public void DatatableToExcel(DataTable dtable, string pFilePath, int excelSheetIndex=1)
{
try
{
if (dtable != null && dtable.Rows.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(pFilePath, FileMode.Open, FileAccess.ReadWrite))
{
workbook = WorkbookFactory.Create(stream);
worksheet = workbook.GetSheetAt(excelSheetIndex);
int iRow = 1;
foreach (DataRow row in dtable.Rows)
{
IRow file = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in dtable.Columns)
{
ICell cell = null;
object cellValue = row[iCol];
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue)) { cell.SetCellFormula("TRUE()"); }
else { cell.SetCellFormula("FALSE()"); }
//cell.CellStyle = _boolCellStyle;
}
break;
case "System.String":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.String);
cell.SetCellValue(Convert.ToString(cellValue));
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
//cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
//cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
//cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
//cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = file.CreateCell(iCol, CellType.String);
DateTime dateTime = Convert.ToDateTime(cellValue);
cell.SetCellValue(dateTime.ToString("dd/MM/yyyy"));
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
//cell.CellStyle = _dateTimeCellStyle;
}
else
{
// cell.CellStyle = _dateCellStyle;
}
}
break;
default:
break;
}
iCol++;
}
iRow++;
}
using (var WritetoExcelfile = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(WritetoExcelfile);
WritetoExcelfile.Close();
//workbook.Write(stream);
stream.Close();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
This code you just need to copy & pest in your script and add the Namespace as following, Also change the excel file name as previously discussed.
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
While not a .NET implementation, you may find that the plug-in TableTools may be highly effective depending on your audience. It relies upon flash which shouldn't be a problem for most cases of needing to actually work in-depth and then want to record tabular information.
The latest version appears to support copying to clipboard, into a CSV, ".XLS" (really just a tab-delimited file named .xls), to a PDF, or create a printer friendly page version with all rows displayed and the rest of your page's contents hidden.
I found the extension on the DataTables site here: http://datatables.net/extras/tabletools/
The download is available in the plug-ins (extras) page here: http://datatables.net/extras/
It supposedly is downloaded as a part of DataTables (hence the phrase "Extras included in the DataTables package") but I didn't find it in the download I have been using. Seems to work wonderfully!
var lines = new List<string>();
string[] columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
var header = string.Join(",", columnNames);
lines.Add(header);
var valueLines = dt.AsEnumerable()
.Select(row => string.Join(",", row.ItemArray));
lines.AddRange(valueLines);
File.WriteAllLines("excel.csv", lines);
Here dt refers to your DataTable pass as a paramter
This snippet could be faster to implement:
// Example data
DataTable table = new DataTable();
table.Columns.AddRange(new[]{ new DataColumn("Key"), new DataColumn("Value") });
foreach (string name in Request.ServerVariables)
table.Rows.Add(name, Request.ServerVariables[name]);
// This actually makes your HTML output to be downloaded as .xls file
Response.Clear();
Response.ClearContent();
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls");
// Create a dynamic control, populate and render it
GridView excel = new GridView();
excel.DataSource = table;
excel.DataBind();
excel.RenderControl(new HtmlTextWriter(Response.Output));
Response.Flush();
Response.End();
Source: Stackoverflow.com