c# - datos - Exportar archivo DataTable a Excel
exportar datos de un datatable a excel c# (13)
Tengo una DataTable con más de 30 columnas y más de 6500 filas. Necesito volcar todos los valores de DataTable en un archivo de Excel. Puede ayudarme con el código de C #. Necesito que cada valor de columna esté en una celda. Para ser precisos, Necesito la copia exacta de DataTable en un archivo de Excel. Ayuda.
Gracias, Vix
Código de trabajo para 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)
{ }
El siguiente enlace se usa para exportar datatable para sobresalir en 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;
}
}
}
}
Este fragmento podría ser más rápido de implementar:
// 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();
He realizado la conversión de DataTable a Excel con el siguiente código. Espero que sea muy fácil, no hay necesidad de cambiar más, solo copie y plague el código. Reemplace su variable con su variable, y funcionará correctamente.
Primero crea una carpeta en tu documento de solución y crea un archivo de Excel MyTemplate.xlsx . puedes cambiar esos nombres de acuerdo a tus requerimientos. Pero recuerde que también debe cambiar el nombre en el código.
Encuentra el siguiente código ...
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;
}
}
Este código solo necesita copiar y plagar en su secuencia de comandos y agregar el espacio de nombres de la siguiente manera. También cambie el nombre del archivo de Excel como se discutió anteriormente.
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
Intente esto exportar los datos al archivo de Excel igual que en DataTable y también podría personalizar.
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);
}
La mayoría de las respuestas están produciendo el CSV con el que no siempre tengo buena experiencia, al abrir en Excel.
Una forma de hacerlo sería también con ACE OLEDB Provider (ver también cadenas de conexión para Excel ). Por supuesto, debe tener el proveedor instalado y registrado. Lo tiene, si tiene Excel instalado, pero esto es algo que debe tener en cuenta al implementar (por ejemplo, en el servidor web).
En el siguiente código de clase auxiliar, deberá llamar a algo como 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();
}
}
La respuesta de mayor rango en este trabajo de publicación, sin embargo, es el archivo CSV. No es un archivo de Excel real. Por lo tanto, recibirá una advertencia cuando abra un archivo.
La mejor solución que encontré en la web es usar CloseXML http://closedxml.codeplex.com/ También necesita abrir XML.
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();
}
}
Por favor, intente esto, esto exportará los datos de su tabla de datos más rápido para sobresalir.
Nota: Rango "FW", que he codificado duro es porque tenía 179 columnas.
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();
}
Si bien no es una implementación de .NET, es posible que el plug-in TableTools sea altamente efectivo dependiendo de su audiencia. Se basa en flash que no debería ser un problema para la mayoría de los casos de la necesidad de trabajar en profundidad y luego desea grabar información tabular.
La última versión parece ser compatible con la copia al portapapeles, en un CSV, ".XLS" (en realidad solo un archivo delimitado por tabuladores llamado .xls), a un PDF, o crea una versión de página amigable para la impresora con todas las filas visualizadas y el resto de los contenidos de su página ocultos
Encontré la extensión en el sitio de DataTables aquí: http://datatables.net/extras/tabletools/
La descarga está disponible en la página de complementos (extras) aquí: http://datatables.net/extras/
Supuestamente se descarga como parte de DataTables (de ahí la frase "Extras incluidos en el paquete DataTables") pero no lo encontré en la descarga que he estado usando. Parece que funciona maravillosamente!
Si desea exportar tabla de datos a Excel con texto de encabezado formateado, intente de esta manera.
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();
}
Yo uso esto en la página.
public void DTToExcel(DataTable dt)
{
// dosya isimleri ileride aynı anda birden fazla kullanıcı aynı dosya üzerinde işlem yapmak ister düşüncesiyle guid yapıldı.
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 aldığımız sorguyu bir datagrid''e atayıp 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();
}
}
}
usa este código ...
dt = city.GetAllCity();//your datatable
string attachment = "attachment; filename=city.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();
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);
Aquí dt se refiere a su pase de DataTable como parámetro