Generazione di un file Excel in ASP.NET

Sto per aggiungere una sezione a un’app ASP.NET (VB.NET codebehind) che consentirà a un utente di ottenere i dati restituiti come un file Excel, che genererò in base ai dati del database. Mentre ci sono diversi modi per farlo, ognuno ha i suoi svantaggi. Come restituiresti i dati? Sto cercando qualcosa che sia il più semplice e pulito ansible.

CSV

Professionisti:

  • Semplice

Contro:

  • Potrebbe non funzionare in altre impostazioni locali o in diverse configurazioni di Excel (ad es. Separatore di elenchi)
  • Imansible applicare la formattazione, le formule, ecc

HTML

Professionisti:

  • Ancora piuttosto semplice
  • Supporta formule e formule semplici

Contro:

  • Devi nominare il file come xls ed Excel potrebbe avvisarti dell’apertura di un file Excel non nativo
  • Un foglio di lavoro per cartella di lavoro

OpenXML (Office 2007 .XLSX)

Professionisti:

  • Formato nativo di Excel
  • Supporta tutte le funzionalità di Excel
  • Non richiedere una copia di installazione di Excel
  • Può generare tabelle pivot
  • Può essere generato utilizzando il progetto open source EPPlus

Contro:

  • Compatibilità limitata al di fuori di Excel 2007 (non dovrebbe essere un problema al giorno d’oggi)
  • Complicato a meno che tu non stia utilizzando un componente di terze parti

SpreadSheetML (formato aperto XML)

Professionisti:

  • Semplice rispetto ai formati nativi di Excel
  • Supporta la maggior parte delle funzionalità di Excel: formatura, stili, formule, fogli multipli per cartella di lavoro
  • Excel non ha bisogno di essere installato per usarlo
  • Non sono necessarie librerie di terze parti: basta scrivere il tuo xml
  • I documenti possono essere aperti con Excel XP / 2003/2007

Contro:

  • Mancanza di buona documentazione
  • Non supportato nelle versioni precedenti di Excel (pre-2000)
  • Solo scrittura, in quanto una volta aperto e modificato da Excel, viene convertito in Excel nativo.

XLS (generato da un componente di terze parti)

Professionisti:

  • Genera file Excel nativo con tutte le formule, formule, ecc.

Contro:

  • Costo
  • Aggiungi dipendenze

Interoperabilità COM

Professionisti:

  • Utilizza librerie Microsoft native
  • Leggi il supporto per i documenti nativi

Contro:

  • Molto lento
  • Problemi di corrispondenza di versione / versione
  • Problemi di concorrenza / integrità dei dati per l’uso del web durante la lettura
  • Molto lento
  • Problemi di ridimensionamento per uso web (diversi dalla concorrenza): è necessario creare molte istanze di app di Excel pesanti sul server
  • Richiede Windows
  • Ho detto che è lento?

È ansible emettere i dati come celle di tabella html, incollare un’estensione .xls o .xlsx e Excel lo aprirà come se fosse un documento nativo. In questo modo, puoi anche eseguire alcuni calcoli di formattazione e formula limitati, quindi è molto più potente di CSV. Inoltre, l’output di una tabella html dovrebbe essere abbastanza facile da fare da una piattaforma web come ASP.Net;)

Se hai bisogno di più fogli di lavoro o fogli di lavoro con nome all’interno della cartella di lavoro di Excel, puoi fare qualcosa di simile tramite uno schema XML chiamato SpreadSheetML . Questo non è il nuovo formato fornito con Office 2007, ma qualcosa di completamente diverso che funziona fin da Excel 2000. Il modo più semplice per spiegare come funziona è con un esempio:

 < ?xml version="1.0"?> < ?mso-application progid="Excel.Sheet"?>   Your_name_here Your_name_here 20080625 ABC Inc 10.2625   6135 8445 240 120 False False       1 2  3 4  5 6  7 8 
A B C D E F G H

se proviene da una tabella di dati.

  public static void DataTabletoXLS(DataTable DT, string fileName) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "utf-16"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250"); HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; string tab = ""; foreach (DataColumn dc in DT.Columns) { HttpContext.Current.Response.Write(tab + dc.ColumnName.Replace("\n", "").Replace("\t", "")); tab = "\t"; } HttpContext.Current.Response.Write("\n"); int i; foreach (DataRow dr in DT.Rows) { tab = ""; for (i = 0; i < DT.Columns.Count; i++) { HttpContext.Current.Response.Write(tab + dr[i].ToString().Replace("\n", "").Replace("\t", "")); tab = "\t"; } HttpContext.Current.Response.Write("\n"); } HttpContext.Current.Response.End(); } 

Da un Gridview

  public static void GridviewtoXLS(GridView gv, string fileName) { int DirtyBit = 0; int PageSize = 0; if (gv.AllowPaging == true) { DirtyBit = 1; PageSize = gv.PageSize; gv.AllowPaging = false; gv.DataBind(); } HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "utf-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250"); HttpContext.Current.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}.xls", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a table to contain the grid Table table = new Table(); // include the gridline settings table.GridLines = gv.GridLines; // add the header row to the table if (gv.HeaderRow != null) { Utilities.Export.PrepareControlForExport(gv.HeaderRow); table.Rows.Add(gv.HeaderRow); } // add each of the data rows to the table foreach (GridViewRow row in gv.Rows) { Utilities.Export.PrepareControlForExport(row); table.Rows.Add(row); } // add the footer row to the table if (gv.FooterRow != null) { Utilities.Export.PrepareControlForExport(gv.FooterRow); table.Rows.Add(gv.FooterRow); } // render the table into the htmlwriter table.RenderControl(htw); // render the htmlwriter into the response HttpContext.Current.Response.Write(sw.ToString().Replace("£", "")); HttpContext.Current.Response.End(); } } if (DirtyBit == 1) { gv.PageSize = PageSize; gv.AllowPaging = true; gv.DataBind(); } } private static void PrepareControlForExport(Control control) { for (int i = 0; i < control.Controls.Count; i++) { Control current = control.Controls[i]; if (current is LinkButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text)); } else if (current is ImageButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText)); } else if (current is HyperLink) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text)); } else if (current is DropDownList) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text)); } else if (current is CheckBox) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False")); } if (current.HasControls()) { Utilities.Export.PrepareControlForExport(current); } } } 

Questo è un wrapper gratuito su SpreadML: funziona alla grande.

http://www.carlosag.net/Tools/ExcelXmlWriter/

In base alle risposte fornite e alla consultazione con i colleghi, sembra che la soluzione migliore sia generare un file XML o tabelle HTML e inserirlo come allegato. L’unico cambiamento consigliato dai miei colleghi è che i dati (cioè le tabelle HTML) possono essere scritti direttamente sull’object Response, eliminando così la necessità di scrivere un file, che può essere problematico a causa di problemi di permessi, I / O contesa e assicurando che si verifichi lo spurgo programmato.

Ecco uno snippet del codice … Non l’ho ancora verificato, e non ho fornito tutto il codice chiamato, ma penso che rappresenti bene l’idea.

  Dim uiTable As HtmlTable = GetUiTable(groupedSumData) Response.Clear() Response.ContentType = "application/vnd.ms-excel" Response.AddHeader("Content-Disposition", String.Format("inline; filename=OSSummery{0:ddmmssf}.xls", DateTime.Now)) Dim writer As New System.IO.StringWriter() Dim htmlWriter As New HtmlTextWriter(writer) uiTable.RenderControl(htmlWriter) Response.Write(writer.ToString) Response.End() 

dal momento che Excel comprende l’HTML, puoi semplicemente scrivere i dati come tabella HTML in un file temporaneo con estensione .xls, ottenere FileInfo per il file e respingerlo usando

 Response.Clear(); Response.AddHeader("Content-Disposition", "attachment; filename=" + fi.Name); Response.AddHeader("Content-Length", fi.Length.ToString()); Response.ContentType = "application/octet-stream"; Response.WriteFile(fi.FullName); Response.End(); 

se si desidera evitare il file temporaneo, è ansible scrivere in un stream in memoria e scrivere i byte anziché utilizzare WriteFile

se l’intestazione della lunghezza del contenuto viene omessa, puoi semplicemente scrivere direttamente il codice HTML, ma questo potrebbe non funzionare sempre in tutti i browser

Personalmente preferisco il metodo XML. Ritornerò i dati dal database in un set di dati, li salverò in XMl, quindi creerò un file xslt che contiene una regola di trasformazione che formatterà un documento appropriato e una semplice trasformazione XML finirà il lavoro. La parte migliore di ciò è la possibilità di formattare celle, eseguire formattazioni condizionali, impostare intestazioni e piè di pagina e persino impostare intervalli di stampa.

L’ho fatto un paio di volte e ogni volta il modo più semplice era semplicemente restituire un file CSV (Comma Separated Value). Excel lo importa perfettamente ed è relativamente veloce da fare.

esportiamo i dati da un datagrid per eccellere in ogni momento. Convertirlo in HTML e poi scrivere in un file excel

 Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Response.AddHeader("content-disposition", "fileattachment;filename=YOURFILENAME.xls") Me.EnableViewState = False Dim sw As System.IO.StringWriter = New System.IO.StringWriter Dim hw As HtmlTextWriter = New HtmlTextWriter(sw) ClearControls(grid) grid.RenderControl(hw) Response.Write(sw.ToString()) Response.End() 

L’unico modo per ottenere questo metodo era che molte delle nostre griglie avevano pulsanti o collegamenti, quindi è necessario anche questo:

 'needed to export grid to excel to remove link button control and represent as text Private Sub ClearControls(ByVal control As Control) Dim i As Integer For i = control.Controls.Count - 1 To 0 Step -1 ClearControls(control.Controls(i)) Next i If TypeOf control Is System.Web.UI.WebControls.Image Then control.Parent.Controls.Remove(control) End If If (Not TypeOf control Is TableCell) Then If Not (control.GetType().GetProperty("SelectedItem") Is Nothing) Then Dim literal As New LiteralControl control.Parent.Controls.Add(literal) Try literal.Text = CStr(control.GetType().GetProperty("SelectedItem").GetValue(control, Nothing)) Catch End Try control.Parent.Controls.Remove(control) Else If Not (control.GetType().GetProperty("Text") Is Nothing) Then Dim literal As New LiteralControl control.Parent.Controls.Add(literal) literal.Text = CStr(control.GetType().GetProperty("Text").GetValue(control, Nothing)) control.Parent.Controls.Remove(control) End If End If End If Return End Sub 

L’ho trovato da qualche parte, funziona bene.

Raccomando librerie di generazione excel opensource gratuite basate su OpenXML

Mi ha aiutato diversi mesi fa.

Ecco un rapporto che estrae da una stored procedure. I risultati vengono esportati in Excel. Usa ADO invece di ADO.NET e il motivo per cui è questa linea

 oSheet.Cells(2, 1).copyfromrecordset(rst1) 

Fa la maggior parte del lavoro e non è disponibile in ado.net.

 'Calls stored proc in SQL Server 2000 and puts data in Excel and 'formats it Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As ADODB.Connection cnn = New ADODB.Connection cnn.Open("Provider=SQLOLEDB;data source=xxxxxxx;" & _ "database=xxxxxxxx;Trusted_Connection=yes;") Dim cmd As New ADODB.Command cmd.ActiveConnection = cnn cmd.CommandText = "[sp_TomTepley]" cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc cmd.CommandTimeout = 0 cmd.Parameters.Refresh() Dim rst1 As ADODB.Recordset rst1 = New ADODB.Recordset rst1.Open(cmd) Dim oXL As New Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet 'oXL = CreateObject("excel.application") oXL.Visible = True oWB = oXL.Workbooks.Add oSheet = oWB.ActiveSheet Dim Column As Integer Column = 1 Dim fld As ADODB.Field For Each fld In rst1.Fields oXL.Workbooks(1).Worksheets(1).Cells(1, Column).Value = fld.Name oXL.Workbooks(1).Worksheets(1).cells(1, Column).Interior.ColorIndex = 15 Column = Column + 1 Next fld oXL.Workbooks(1).Worksheets(1).name = "Tom Tepley Report" oSheet.Cells(2, 1).copyfromrecordset(rst1) oXL.Workbooks(1).Worksheets(1).Cells.EntireColumn.AutoFit() oXL.Visible = True oXL.UserControl = True rst1 = Nothing cnn.Close() Beep() End Sub 

Se si riempie un GridView con i dati è ansible utilizzare questa funzione per ottenere i dati formattati in HTML, ma indicando il browser si tratta di un file excel.

  Public Sub ExportToExcel(ByVal fileName As String, ByVal gv As GridView) HttpContext.Current.Response.Clear() HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName)) HttpContext.Current.Response.ContentType = "application/ms-excel" Dim sw As StringWriter = New StringWriter Dim htw As HtmlTextWriter = New HtmlTextWriter(sw) Dim table As Table = New Table table.GridLines = gv.GridLines If (Not (gv.HeaderRow) Is Nothing) Then PrepareControlForExport(gv.HeaderRow) table.Rows.Add(gv.HeaderRow) End If For Each row As GridViewRow In gv.Rows PrepareControlForExport(row) table.Rows.Add(row) Next If (Not (gv.FooterRow) Is Nothing) Then PrepareControlForExport(gv.FooterRow) table.Rows.Add(gv.FooterRow) End If table.RenderControl(htw) HttpContext.Current.Response.Write(sw.ToString) HttpContext.Current.Response.End() End Sub Private Sub PrepareControlForExport(ByVal control As Control) Dim i As Integer = 0 Do While (i < control.Controls.Count) Dim current As Control = control.Controls(i) If (TypeOf current Is LinkButton) Then control.Controls.Remove(current) control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text)) ElseIf (TypeOf current Is ImageButton) Then control.Controls.Remove(current) control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText)) ElseIf (TypeOf current Is HyperLink) Then control.Controls.Remove(current) control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text)) ElseIf (TypeOf current Is DropDownList) Then control.Controls.Remove(current) control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text)) ElseIf (TypeOf current Is CheckBox) Then control.Controls.Remove(current) control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked)) End If If current.HasControls Then PrepareControlForExport(current) End If i = i + 1 Loop End Sub 

Basta evitare COM Interop tramite lo spazio dei nomi Microsoft.Office.Interop. È così dannatamente lento, inaffidabile e invalicabile. Non applicabile per masochisti.

È ansible creare file Excel ben formattati utilizzando questa libreria, abbastanza facilmente: http://officehelper.codeplex.com/documentation .

Microsoft Office non ha bisogno di essere installato sul server web!

CSV è il modo più semplice. Il più delle volte è collegato a Excel. Altrimenti devi usare le API di automazione o il formato XML. Le API e XML non sono così difficili da usare.

Informazioni sulla generazione di XML per Excel

O vado sul percorso CSV (come descritto sopra), o più spesso in questi giorni, utilizzo Infragistics NetAdvantage per generare il file. (Nella stragrande maggioranza dei casi in cui Infragistics è in gioco, stiamo solo esportando un UltraWebGrid esistente, che è essenzialmente una soluzione LOC-LOC a meno che non siano necessari ulteriori aggiustamenti di formattazione. Potremmo anche generare manualmente un file Excel / BIFF, ma raramente c’è bisogno di.)

man, in .net immagino che potresti avere un componente che potrebbe farlo, ma nel classico asp l’ho già fatto creando una tabella html e cambiando il mime tipe della pagina su vnd / msexcel. Immagino che se usi un gridview e cambi il tipo di mime forse dovrebbe funzionare, perché il gridview è una tabella html.

L’unico modo a prova di proiettile per evitare il “Sembra che questi numeri siano memorizzati come testo” i triangoli verdi è usare il formato Open XML. Vale la pena usarlo, solo per evitare gli inevitabili triangoli verdi.

Il metodo migliore che ho visto per i report Excel è quello di scrivere i dati in XML con un’estensione XML e inviarli in streaming ai client con il tipo di contenuto corretto. (Application / xls)

Questo funziona per qualsiasi rapporto che richiede una formattazione di base e consente di confrontare i fogli di lavoro esistenti utilizzando strumenti di confronto del testo.

Supponendo che questo sia per una intranet, dove puoi impostare permessi e mandare IE, puoi generare il lato client della cartella di lavoro con JScript / VBScript che guida Excel . Questo ti dà la formattazione nativa di Excel, senza il fastidio di cercare di automatizzare Excel sul server.

Non sono sicuro di raccomandare veramente questo approccio eccetto che in scenari abbastanza di nicchia, ma era abbastanza comune durante il classico ASP heydays.

Ovviamente puoi sempre cercare componenti di terze parti. Personalmente, ho avuto una buona esperienza con Spire.XLS http://www.e-iceblue.com/xls/xlsintro.htm

Il componente è abbastanza facile da usare all’interno dell’applicazione:

  Workbook workbook = new Workbook(); //Load workbook from disk. workbook.LoadFromFile(@"Data\EditSheetSample.xls"); //Initailize worksheet Worksheet sheet = workbook.Worksheets[0]; //Writes string sheet.Range["B1"].Text = "Hello,World!"; //Writes number sheet.Range["B2"].NumberValue = 1234.5678; //Writes date sheet.Range["B3"].DateTimeValue = System.DateTime.Now; //Writes formula sheet.Range["B4"].Formula = "=1111*11111"; workbook.SaveToFile("Sample.xls"); 

Uno dei problemi che ho riscontrato usando una delle soluzioni suggerite sopra che sono simili a questa risposta è che se si spinge il contenuto come allegato (quale ho trovato essere la soluzione più pulita per i browser non ms) , quindi aprirlo in Excel 2000-2003, il suo tipo è una “Pagina Web Excel” e non un documento Excel nativo.

Quindi devi spiegare agli utenti come usare “Salva come tipo” da Excel per convertirlo in un documento Excel. Questo è un problema se gli utenti devono modificare questo documento e quindi ricaricarlo sul tuo sito.

La mia raccomandazione è di usare CSV. È semplice e se gli utenti lo aprono da Excel, Excel li richiede almeno per salvarlo nel suo formato nativo.

Vorrei solo creare un file CSV basato sui dati, perché vedo che è il più pulito, e Excel ha un buon supporto per questo. Ma se hai bisogno di un formato più flessibile, sono sicuro che ci sono alcuni strumenti di terze parti per la generazione di file excel reali.

Ecco una soluzione che trasmette il datatable out come CSV. Veloce, pulito e facile e gestisce le virgole nell’input.

 public static void ExportToExcel(DataTable data, HttpResponse response, string fileName) { response.Charset = "utf-8"; response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250"); response.Cache.SetCacheability(HttpCacheability.NoCache); response.ContentType = "text/csv"; response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); for (int i = 0; i < data.Columns.Count; i++) { response.Write(data.Columns[i].ColumnName); response.Write(i == data.Columns.Count - 1 ? "\n" : ","); } foreach (DataRow row in data.Rows) { for (int i = 0; i < data.Columns.Count; i++) { response.Write(String.Format("\"{0}\"", row[i].ToString())); response.Write(i == data.Columns.Count - 1 ? "\n" : ","); } } response.End(); } 

appena creato una funzione per esportare da un modulo Web C # per eccellere spero che aiuti gli altri

  public void ExportFileFromSPData(string filename, DataTable dt) { HttpResponse response = HttpContext.Current.Response; //clean up the response.object response.Clear(); response.Buffer = true; response.Charset = ""; // set the response mime type for html so you can see what are you printing //response.ContentType = "text/html"; //response.AddHeader("Content-Disposition", "attachment;filename=test.html"); // set the response mime type for excel response.ContentType = "application/vnd.ms-excel"; response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\""); response.ContentEncoding = System.Text.Encoding.UTF8; response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble()); //style to format numbers to string string style = @""; response.Write(style); // create a string writer using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // instantiate a datagrid GridView dg = new GridView(); dg.DataSource = dt; dg.DataBind(); foreach (GridViewRow datarow in dg.Rows) { //format specific cell to be text //to avoid 1.232323+E29 to get 1232312312312312124124 datarow.Cells[0].Attributes.Add("class", "text"); } dg.RenderControl(htw); response.Write(sw.ToString()); response.End(); } } } 

Se devi utilizzare Excel invece di un file CSV, dovrai utilizzare l’automazione OLE su un’istanza di Excel su un server. Il modo più semplice per farlo è quello di avere un file di modello e inserirlo in modo programmatico con i dati. Lo salvi in ​​un altro file.

Suggerimenti:

  • Non farlo in modo interattivo Chiedi all’utente di avviare il processo e quindi di pubblicare una pagina con il link al file. Ciò riduce i potenziali problemi di prestazioni mentre il foglio di calcolo viene generato.
  • Usa il modello come ho descritto prima. Rende più facile modificarlo.
  • Assicurarsi che Excel sia impostato per non visualizzare windows di dialogo. Su un server Web questo si bloccherà l’intera istanza di Excel.
  • Mantieni l’istanza di Excel su un server separato, preferibilmente dietro un firewall, quindi non è esposto come potenziale buco di sicurezza.
  • Tieni d’occhio l’utilizzo delle risorse. La generazione di un spreadhseet tramite l’interfaccia di automazione OLE (i PIA sono solo degli shim sopra) è un processo abbastanza pesante. Se è necessario ridimensionarlo a volumi di dati elevati, potrebbe essere necessario essere un po ‘intelligenti con la propria architettura.

Alcuni degli “usi mime-types per ingannare eccellere nell’aprire gli approcci della tabella HTML” potrebbero funzionare se non ti dispiace che il formato del file sia un po ‘di base. Questi approcci fob anche il pesante lavoro della CPU sul client. Se si desidera il controllo a grana fine sul formato del foglio di calcolo, sarà probabilmente necessario utilizzare Excel stesso per generare il file come descritto sopra.