Come esportare DataTable in Excel

Come posso esportare un DataTable in Excel in C #? Sto usando Windows Forms. DataTable è associato a un controllo DataGridView . Devo esportare i record di DataTable in Excel.

Consiglierei ClosedXML –

È ansible trasformare un DataTable in un foglio di lavoro Excel con un codice molto leggibile:

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

Lo sviluppatore è reattivo e utile. Il progetto è triggersmente sviluppato e la documentazione è superba.

Prova un codice semplice, per convertire DataTable in file Excel come csv:

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

Questo scriverà un nuovo file excel.csv nella “directory di lavoro corrente” che generalmente è dove l’exe è o da dove lo si avvia.

Si noti che l’output mette le virgole ( "," ) tra i dati già contenuti nel dataTable. Poiché non sfugge le virgole nei dati, le virgole nei dati saranno erroneamente interpretate dal programma che legge il file.

Un’opzione elegante sta scrivendo un metodo di estensione (vedi sotto) per la class DataTable di .net framework.

Questo metodo di estensione può essere chiamato come segue:

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

Metodo di estensione per class DataTable:

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

Soluzione basata sull’articolo tuncalik (grazie per l’idea), ma nel caso di grandi tavoli funziona molto più velocemente (ed è un po ‘meno chiaro).

 public static class My_DataTable_Extensions { ///  /// Export DataTable to Excel file ///  /// Source DataTable /// Path to result file name 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); } } } 

Prova questa funzione passa il datatable e il percorso del file dove vuoi esportare

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

Il modo migliore e più semplice

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

È ansible utilizzare EasyXLS che è una libreria per esportare file Excel.

Controlla questo codice:

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

Vedi anche questo esempio su come esportare datatable per eccellere in C # .

NOTA: se stai cercando di passare i valori da una tabella di dati a un object, quindi a Excel, dovresti eseguire anche la gestione degli errori dei tipi di dati. Ad esempio, Guids ucciderà il tuo compito con un’eccezione HRESULT: 0x800A03EC. Uno si aggira senza testare per i tipi di dati è quello di utilizzare “ToString ()” quando si popola il proprio object. Excel convertirà i numeri in formato numerico da solo. FlashTrev come indirizzato al relativo problema di data / ora.

Per quanto riguarda la risposta di tuncalik , che è grandiosa, specialmente se si vuole giocare un po ‘con il codice 🙂 ma sta mettendo le mie date in Excel nel formato americano, ovvero il 2 marzo 2014 nel Regno Unito è il 02/03/2014 ma negli Stati Uniti il ​​03/02/2014 con il mese 1, quindi il giorno dopo la settimana. Ho bisogno di averlo nel formato del Regno Unito, qualche idea per favore?

Ho controllato che sia archiviato nel formato UK nel mio DataTable e anche il mio Excel è impostato nel Regno Unito, ma per qualche motivo quando fa il documento Excel pensa agli Stati Uniti (è perché Microsoft è una società statunitense 🙂

Proverò a sperimentare con i codici cultura ma non sono sicuro di dove metterlo. Ho provato, ma questo non ha avuto alcun effetto.

ps

Ho dovuto cambiare una riga per farlo funzionare aggiungendo un ‘cast’ come sotto

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

Aggiornamento: Ho ottenuto la formattazione del Regno Unito delle date convertendole in formato LongDateTime, ma è solo un lavoro da risolvere.

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

Saluti.

Interop di Excel:

Questo metodo impedisce a Dates di essere capovolto da gg-mm-aaaa a mm-gg-aaaa

 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 dateColIndex = new List(); 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; } 

Se non ti interessa che le date vengano capovolte, usa il link che mostra come popolare tutte le celle nel foglio di calcolo di Excel in una riga di codice:

Interoperabilità di Excel: efficienza e prestazioni

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

Vecchio thread – ma ho pensato di buttare il mio codice qui. Ho scritto una piccola funzione per scrivere una tabella dati su un nuovo foglio Excel in un percorso (posizione) specificato. Inoltre sarà necessario aggiungere un riferimento alla libreria microsoft excel 14.0.

Ho tirato da questa discussione scrivendo qualsiasi cosa per eccellere – Come scrivere alcuni dati nel file excel (.xlsx)

l’ho usato per estrapolare come scrivere un datatable

* nota nelle dichiarazioni catch ho un riferimento alla class statica errorhandler (puoi ignorarle)

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

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. Dovresti averlo, se hai installato Excel, ma questo è qualcosa che devi considerare quando distribuisci l’app.

Questo è l’esempio di chiamare il metodo helper da ExportHelper : ExportHelper.CreateXlsFromDataTable(myDataTable, @"C:\tmp\export.xls");

L’helper per esportare nel file Excel utilizzando 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\";"; ///  /// 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(); } } 

usa la seguente 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); } } } 

}

Questa soluzione sta fondamentalmente spingendo i dati di List in Excel, utilizza DataTable per raggiungere questo objective, ho implementato un metodo di estensione, quindi fondamentalmente ci sono due cose necessarie. 1. Un metodo di estensione.

 public static class ReportHelper { public static string ToExcel(this IList data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); foreach (PropertyDescriptor prop in properties) { //table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null) { table.Columns.Add(GetColumnHeader(prop), Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); } } //So it seems like when there is only one row of data the headers do not appear //so adding a dummy blank row which fixed the issues //Add a blank Row - Issue # 1471 DataRow blankRow = table.NewRow(); table.Rows.Add(blankRow); foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) //row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null) { row[GetColumnHeader(prop)] = prop.GetValue(item) ?? DBNull.Value; } table.Rows.Add(row); } table.TableName = "Results"; var filePath = System.IO.Path.GetTempPath() + "\\" + System.Guid.NewGuid().ToString() + ".xls"; table.WriteXml(filePath); return filePath; } private static string GetColumnHeader(PropertyDescriptor prop) { return ((FGMS.Entity.Extensions.ReportHeaderAttribute)(prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)])).ReportHeaderText; } } 
  1. Decora le tue classi DTO con l’attributo [ReportHeaderAttribute("Column Name")]
 public class UserDTO { public int Id { get; set; } public int SourceId { get; set; } public string SourceName { get; set; } [ReportHeaderAttribute("User Type")] public string UsereType { get; set; } [ReportHeaderAttribute("Address")] public string Address{ get; set; } [ReportHeaderAttribute("Age")] public int Age{ get; set; } public bool IsActive { get; set; } [ReportHeaderAttribute("Active")] public string IsActiveString { get { return IsActive ? "Yes" : "No"; } }} 

Tutto ciò che deve essere una colonna in Excel deve essere decorato con [ReportHeaderAttribute("Column Name")]

Quindi semplicemente

 Var userList = Service.GetUsers() //Returns List of UserDTO; var excelFilePath = userList.ToExcel(); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); var stream = new FileStream(excelFilePath, FileMode.Open); result.Content = new StreamContent(stream); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "UserList.xls" }; return result; 

Per esportare i dati in Excel, è ansible utilizzare la libreria ClosedXML.Report ( https://github.com/ClosedXML/ClosedXML.Report ). Credimi, questa è una biblioteca meravigliosa e facile da usare. La libreria non ha bisogno di Excel Interop. ClosedXML.Report genera un file Excel basato su un modello che è ansible creare in Excel utilizzando qualsiasi formattazione. Per esempio:

  var template = new XLTemplate(@".\Templates\report.xlsx"); using (var db = new DbDemos()) { var cust = db.customers.LoadWith(c => c.Orders).First(); template.AddVariable(cust); template.Generate(); } template.SaveAs(outputFile); 

Basta fare uso della libreria CloseMXL.Excel . È anche facile e abbastanza veloce.

Classe

 private DataTable getAllList() { string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("SELECT EmpId, gender, EmpName, pOnHold FROM Employee WHERE EmpId= '"+ AnyVariable + "' ORDER BY EmpName")) { using (SqlDataAdapter da = new SqlDataAdapter()) { DataTable dt = new DataTable(); cmd.CommandType = CommandType.Text; cmd.Connection = con; da.SelectCommand = cmd; da.Fill(dt); dt.Columns[0].ColumnName = "Employee Id"; dt.Columns[1].ColumnName = "Gender"; dt.Columns[2].ColumnName = "Employee Name"; dt.Columns[3].ColumnName = "On Hold"; return dt; } } } } 

Quindi un altro metodo che ottiene il set di dati

 public DataSet getDataSetExportToExcel() { DataSet ds = new DataSet(); DataTable dtEmp = new DataTable("CLOT List"); dtEmp = getAllList(); ds.Tables.Add(dtEmp); ds.Tables[0].TableName = "Employee"; //If you which to use Mutliple Tabs return ds; } 

Ora fai clic sul pulsante Evento

 protected void btn_Export_Click(object sender, EventArgs e) { DataSet ds = getDataSetExportToExcel(); using (XLWorkbook wb = new XLWorkbook()) { wb.Worksheets.Add(ds); wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wb.Style.Font.Bold = true; Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=EmployeeonHoldList.xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } } 
 Private tmr As System.Windows.Forms.Timer Private Sub TestExcel() Handles Button1.Click '// Initial data: SQL Server table with 6 columns and 293000 rows. '// Data table holding all data Dim dt As New DataTable("F161") '// Create connection Dim conn As New SqlConnection("Server=MYSERVER;Database=Test;Trusted_Connection=Yes;") Dim fAdapter As New SqlDataAdapter With { .SelectCommand = New SqlCommand($"SELECT * FROM dbo.MyTable", conn) } '// Fill DataTable fAdapter.Fill(dt) '// Create Excel application Dim xlApp As New Excel.Application With {.Visible = True} '// Temporarily disable screen updating xlApp.ScreenUpdating = False '// Create brand new workbook Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add() Dim xlSheet As Excel.Worksheet = DirectCast(xlBook.Sheets(1), Excel.Worksheet) '// Get number of rows Dim rows_count = dt.Rows.Count '// Get number of columns Dim cols_count = dt.Columns.Count '// Here 's the core idea: after receiving data '// you need to create an array and transfer it to sheet. '// Why array? '// Because it's the fastest way to transfer data to Excel's sheet. '// So, we have two tasks: '// 1) Create array '// 2) Transfer array to sheet '// ========================================================= '// TASK 1: Create array '// ========================================================= '// In order to create array, we need to know that '// Excel's Range object expects 2-D array whose lower bounds '// of both dimensions start from 1. '// This means you can't use C# array. '// You need to manually create such array. '// Since we already calculated number of rows and columns, '// we can use these numbers in creating array. Dim arr = Array.CreateInstance(GetType(Object), {rows_count, cols_count}, {1, 1}) '// Fill array For r = 0 To rows_count - 1 For c = 0 To cols_count - 1 arr(r + 1, c + 1) = dt.Rows(r)(c) Next Next '// ========================================================= '// TASK 2: Transfer array to sheet '// ========================================================= '// Now we need to transfer array to sheet. '// So, how transfer array to sheet fast? '// '// THE FASTEST WAY TO TRANSFER DATA TO SHEET IS TO ASSIGN ARRAY TO RANGE. '// We could, of course, hard-code values, but Resize property '// makes this work a breeze: xlSheet.Range("A1").Resize.Resize(rows_count, cols_count).Value = arr '// If we decide to dump data by iterating over array, '// it will take LOTS of time. '// For r = 1 To rows_count '// For c = 1 To cols_count '// xlSheet.Cells(r, c) = arr(r, c) '// Next '// Next '// Here are time results: '// 1) Assigning array to Range: 3 seconds '// 2) Iterating over array: 45 minutes '// Turn updating on xlApp.ScreenUpdating = True xlApp = Nothing xlBook = Nothing xlSheet = Nothing '// Here we have another problem: '// creating array took lots of memory (about 150 MB). '// Using 'GC.Collect()', by unknown reason, doesn't help here. '// However, if you run GC.Collect() AFTER this procedure is finished '// (say, by pressing another button and calling another procedure), '// then the memory is cleaned up. '// I was wondering how to avoid creating some extra button to just release memory, '// so I came up with the idea to use timer to call GC. '// After 2 seconds GC collects all generations. '// Do not forget to dispose timer since we need it only once. tmr = New Timer() AddHandler tmr.Tick, Sub() GC.Collect() GC.WaitForPendingFinalizers() GC.WaitForFullGCComplete() tmr.Dispose() End Sub tmr.Interval = TimeSpan.FromSeconds(2).TotalMilliseconds() tmr.Start() End Sub