Esporta DataTable in file Excel

Ho un DataTable con 30+ colonne e 6500+ righe. Devo scaricare tutti i valori DataTable in un file Excel. Qualcuno può aiutare con il codice C #. Ho bisogno che ogni valore di colonna sia in una cella. Per essere precisi, Ho bisogno della copia dall’aspetto esatto di DataTable in un file di Excel. Per favore aiutatemi.

Grazie, Vix

usa questo codice …

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(); 

Questo frammento potrebbe essere più veloce da implementare:

 // 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(); 

Sotto il collegamento viene utilizzato per esportare datatable per eccellere nel codice C #.

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; } } } } 

La risposta più rara in questo lavoro post, tuttavia è il file CSV. Non è un vero file Excel. Pertanto, riceverai un avviso quando stai aprendo un file.

La soluzione migliore che ho trovato sul Web sta usando CloseXML http://closedxml.codeplex.com/. Devi anche aprire 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(); } } 

Credito: http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format- che-specificato-by-the-file-extension.aspx

Uso questo nella pagina

  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(); } } } 
  var lines = new List(); string[] columnNames = dt.Columns.Cast(). 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); 

Qui dt si riferisce al tuo pass DataTable come parametro

Pur non essendo un’implementazione .NET, potresti scoprire che il plug-in TableTools può essere molto efficace a seconda del tuo pubblico. Si basa sul flash che non dovrebbe essere un problema nella maggior parte dei casi in cui è necessario lavorare in profondità e quindi registrare le informazioni tabulari.

L’ultima versione sembra supportare la copia negli appunti, in un file CSV, “.XLS” (in realtà solo un file delimitato da tabulazioni denominato .xls), in un PDF o creare una versione di pagina stampabile con tutte le righe visualizzate e il resto di i contenuti della tua pagina nascosti.

Ho trovato l’estensione sul sito DataTables qui: http://datatables.net/extras/tabletools/

Il download è disponibile nella pagina dei plug-in (extra) qui: http://datatables.net/extras/

Apparentemente è scaricato come parte di DataTables (da qui la frase “Extra inclusi nel pacchetto DataTables”) ma non l’ho trovato nel download che ho usato. Sembra funzionare meravigliosamente!

La maggior parte delle risposte sta effettivamente producendo il CSV che non sempre ho una buona esperienza con l’apertura in Excel.

Un modo per farlo sarebbe anche con il provider OLEDB di ACE (vedi anche stringhe di connessione per Excel ). Ovviamente dovresti avere il provider installato e registrato. Ce l’hai, se hai installato Excel, ma questo è qualcosa che devi considerare durante la distribuzione (ad esempio sul server web).

Sotto il codice di class helper sottostante dovresti chiamare qualcosa come 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\";"; ///  /// Creates the Excel file from items in DataTable and writes them to specified output file. ///  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(); } } ///  /// Generates the insert row command. ///  private static string GenerateInsertRowCommand(DataRow dataRow) { var stringBuilder = new StringBuilder(); var columns = dataRow.Table.Columns.Cast().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(); } ///  /// Adds the parameters with value. ///  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; } } ///  /// Gets the formatted value for the OleDbParameter. ///  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(); } } 

Codice di lavoro per l’esportazione di Excel

  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) { } 

Se si desidera esportare datatable per eccellere con testo di intestazione formattato, provare in questo modo.

 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(); } 

Ho effettuato la conversione DataTable in Excel con il seguente codice. Spero sia molto semplice non c’è bisogno di cambiare di più copia e pest il codice sostituisce la variabile con la variabile, e funzionerà correttamente.

Prima crea una cartella nella tua soluzione Document e crea un file Excel MyTemplate.xlsx . puoi cambiare il nome in base alle tue esigenze. Ma ricorda che devi anche cambiare il nome nel codice.

Si prega di trovare il seguente codice …

  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; } } 

Questo codice è sufficiente per copiare e disinfettare il tuo script e aggiungere lo spazio dei nomi come segue, cambiare anche il nome del file excel come discusso in precedenza.

 using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.SS.Util; 

Si prega di provare questo, questo esporterà i dati della vostra tabella di dati più veloce per eccellere.

Nota: il range “FW”, che ho hard coded, è perché avevo 179 colonne.

 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(); } 

Prova questo per esportare i dati in file Excel come in DataTable e potresti anche personalizzare.

 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); }