Generando un archivo de Excel en ASP.NET

Estoy a punto de agregar una sección a una aplicación ASP.NET (código subyacente de VB.NET) que permitirá al usuario obtener los datos que se le devuelven como un archivo de Excel, que generaré en base a los datos de la base de datos. Si bien hay varias formas de hacerlo, cada una tiene sus propios inconvenientes. ¿Cómo devolverías los datos? Estoy buscando algo que sea lo más limpio y directo posible.

CSV

Pros:

  • Sencillo

Contras:

  • Es posible que no funcione en otras configuraciones regionales o en diferentes configuraciones de Excel (es decir, separador de listas)
  • No se puede aplicar formato, fórmulas, etc.

HTML

Pros:

  • Aún bastante simple
  • Admite formatos simples y fórmulas

Contras:

  • Debe nombrar el archivo como xls y Excel puede advertirle sobre la apertura de un archivo Excel no nativo
  • Una hoja de trabajo por libro de trabajo

OpenXML (Office 2007 .XLSX)

Pros:

  • Formato nativo de Excel
  • Admite todas las características de Excel
  • No requiere una copia de instalación de Excel
  • Puede generar tablas pivote
  • Se puede generar utilizando el proyecto de código abierto EPPlus

Contras:

  • Compatibilidad limitada fuera de Excel 2007 (no debería ser un problema hoy en día)
  • Complicado a menos que esté usando un componente de un tercero

SpreadSheetML (formato XML abierto)

Pros:

  • Simple en comparación con los formatos nativos de Excel
  • Admite la mayoría de las funciones de Excel: formateado, estilos, fórmulas, varias hojas por libro de trabajo
  • Excel no necesita ser instalado para usarlo
  • No se necesitan bibliotecas de terceros, simplemente escriba su xml
  • Los documentos pueden ser abiertos por Excel XP / 2003/2007

Contras:

  • Falta de buena documentación
  • No admitido en versiones anteriores de Excel (anteriores a 2000)
  • Solo escritura, ya que una vez que lo abre y realiza cambios desde Excel, se convierte a Excel nativo.

XLS (generado por un componente de terceros)

Pros:

  • Genere archivos nativos de Excel con todos los formatos, fórmulas, etc.

Contras:

  • Cuesta dinero
  • Agregar dependencias

COM Interop

Pros:

  • Utiliza bibliotecas nativas de Microsoft
  • Soporte de lectura para documentos nativos

Contras:

  • Muy lento
  • Dependencia / problemas de coincidencia de versiones
  • Problemas de concurrencia / integridad de datos para uso web al leer
  • Muy lento
  • Problemas de escalado para uso web (a diferencia de la simultaneidad): necesita crear muchas instancias de la aplicación de Excel pesada en el servidor
  • Requiere Windows
  • ¿Mencioné que es lento?

Puede generar los datos como celdas de tabla html, .xlsx extensión .xls o .xlsx y Excel lo abrirá como si fuera un documento nativo. Incluso puede hacer algunos cálculos de fórmula y formato limitados de esta manera, por lo que es mucho más poderoso que CSV. Además, generar una tabla html debería ser bastante fácil de hacer desde una plataforma web como ASP.Net;)

Si necesita varias hojas de trabajo u hojas de trabajo con nombre dentro de su Libro de Excel, puede hacer algo similar a través de un esquema XML llamado SpreadSheetML . Este no es el nuevo formato incluido con Office 2007, sino algo completamente diferente que funciona desde Excel 2000. La forma más fácil de explicar cómo funciona es con un ejemplo:

 < ?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

si proviene de una tabla de datos.

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

Desde una vista de Grid

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

Este es un contenedor gratuito de SpreadML, funciona muy bien.

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

Según las respuestas dadas y la consulta con los compañeros de trabajo, parece que la mejor solución es generar un archivo XML o tablas HTML y presionarlo como un archivo adjunto. El único cambio recomendado por mis compañeros de trabajo es que los datos (es decir, las tablas HTML) se pueden escribir directamente en el objeto Response, eliminando así la necesidad de escribir un archivo, lo que puede ser problemático debido a problemas de permisos, E / S contención y asegurando que ocurra la purga progtwigda.

Aquí hay un fragmento del código … No lo he comprobado todavía, y no he suministrado todo el código llamado, pero creo que representa bien la 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() 

Dado que Excel entiende HTML, puede simplemente escribir los datos como una tabla HTML en un archivo temporal con una extensión .xls, obtener la información de archivo para el archivo y volver a utilizarlo.

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

si desea evitar el archivo temporal, puede escribir en una secuencia en la memoria y escribir los bytes en lugar de utilizar WriteFile

si se omite el encabezado de longitud del contenido, puede escribir directamente el html, pero puede que esto no funcione correctamente todo el tiempo en todos los navegadores.

Yo personalmente prefiero el método XML. Devolveré los datos de la base de datos en un Dataset, lo guardaré en XMl, luego crearé un archivo xslt que contenga una regla de transformación que formateará un documento apropiado, y una simple transformación XML terminará el trabajo. La mejor parte de esto es que puede formatear celdas, formatear condicionalmente, configurar encabezados y pies de página, e incluso establecer rangos de impresión.

He hecho esto un par de veces y cada vez la forma más fácil era simplemente devolver un archivo CSV (valor separado por comas). Excel lo importa perfectamente, y es relativamente rápido de hacer.

Exportamos datos desde una cuadrícula de datos para excel todo el tiempo. Convirtiéndolo en HTML luego escribiendo en un archivo de 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() 

El único problema con este método fue que muchas de nuestras cuadrículas tenían botones o enlaces en ellas, por lo que también necesita esto:

 '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 

Encontré eso en alguna parte, funciona bien.

Recomiendo liberty opensource excel generation libruary que se basa en OpenXML

Me ayudó hace varios meses.

Aquí hay un informe que extrae de un procedimiento almacenado. Los resultados se exportan a Excel. Utiliza ADO en lugar de ADO.NET y la razón por la cual esta línea

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

Hace la mayor parte del trabajo y no está disponible en 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 

Si llena un GridView con datos, puede usar esta función para obtener los datos con formato HTML, pero indicando que el navegador es un archivo de 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 

Solo evite COM Interop a través del espacio de nombres Microsoft.Office.Interop. Es tan malditamente lento y poco confiable e indestructible. No aplica para masoquistas.

Puede crear fácilmente archivos de Excel con buen formato utilizando esta biblioteca: http://officehelper.codeplex.com/documentation .

¡Microsoft Office no necesita ser instalado en el servidor web!

CSV es la forma más fácil. La mayoría de las veces está vinculado a Excel. De lo contrario, debe usar las API de automatización o el formato XML. Las API y XML no son tan difíciles de usar.

Información sobre la generación de XML para Excel

O voy por la ruta CSV (como se describió anteriormente), o más a menudo en estos días, uso Infragistics NetAdvantage para generar el archivo. (La inmensa mayoría de las veces que Infragistics está en juego, solo exportamos un UltraWebGrid existente, que es esencialmente una solución de un solo LOC a menos que se necesiten ajustes de formato adicionales. También pudimos generar manualmente un archivo Excel / BIFF, pero rara vez es necesario).

hombre, en .net creo que podría tener un componente que podría hacer eso, pero en el ASP clásico ya lo he hecho creando una tabla html y cambiando el tipo de mimo de la página a vnd / msexcel. Supongo que si utilizas una vista de cuadrícula y cambias el tipo de mimo, tal vez debería funcionar, porque la vista de cuadrícula es una tabla html.

La única forma a prueba de evitar el triángulo verde “Parece que estos números se almacenan como texto” es utilizar el formato Open XML. Vale la pena usarlo, solo para evitar los inevitables triangularjs verdes.

El mejor método que he visto para los informes de Excel es escribir los datos en XML con una extensión XML y transmitirlos a los clientes con el tipo de contenido correcto. (aplicación / xls)

Esto funciona para cualquier informe que requiera formateo básico, y le permite comparar con hojas de cálculo existentes mediante el uso de herramientas de comparación de texto.

Suponiendo que se trata de una intranet, donde puede establecer permisos y obligar a IE, puede generar el lado del cliente del libro con JScript / VBScript manejando Excel . Esto le proporciona el formato nativo de Excel, sin la molestia de tratar de automatizar Excel en el servidor.

No estoy seguro de que realmente recomiende este enfoque más, excepto en escenarios bastante específicos, pero fue bastante común durante los días de apogeo ASP clásicos.

Por supuesto, siempre puede elegir componentes de terceros. Personalmente, he tenido una buena experiencia con Spire.XLS http://www.e-iceblue.com/xls/xlsintro.htm

El componente es bastante fácil de usar dentro de su aplicación:

  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 de los problemas que he encontrado al utilizar una de las soluciones sugeridas anteriormente que son similares a esta respuesta es que si extrae el contenido como un archivo adjunto (lo que he encontrado es la solución más limpia para navegadores que no son ms) , luego ábralo en Excel 2000-2003, su tipo es una “Página web de Excel” y no un documento nativo de Excel.

Luego debe explicarles a los usuarios cómo usar “Guardar como tipo” desde Excel para convertirlo a un documento de Excel. Esto es un problema si los usuarios necesitan editar este documento y luego volver a subirlo a su sitio.

Mi recomendación es usar CSV. Es simple y si los usuarios abren desde Excel, Excel al menos les pide que lo guarden en su formato original.

Simplemente crearía un archivo CSV basado en los datos, porque lo veo como el más limpio, y Excel tiene un buen soporte para él. Pero si necesita un formato más flexible, estoy seguro de que hay algunas herramientas de terceros para generar archivos de Excel reales.

Aquí hay una solución que transmite la datatable como un CSV. Rápido, limpio y fácil, y maneja las comas en la entrada.

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

acaba de crear una función para exportar desde un formulario web C # para excel espero que ayude a los demás

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

Si tiene que usar Excel en lugar de un archivo CSV, necesitará usar la automatización OLE en una instancia de Excel, uno en el servidor. La forma más fácil de hacerlo es tener un archivo de plantilla y progtwigrlo programáticamente con los datos. Lo guarda en otro archivo.

Consejos:

  • No lo hagas de forma interactiva. Haga que el usuario inicie el proceso y luego publique una página con el enlace al archivo. Esto mitiga los posibles problemas de rendimiento mientras se genera la hoja de cálculo.
  • Usa la plantilla como describí antes. Hace que sea más fácil modificarlo.
  • Asegúrese de que Excel esté configurado para no mostrar cuadros de diálogo. En un servidor web esto bloqueará la instancia completa de Excel.
  • Mantenga la instancia de Excel en un servidor separado, preferiblemente detrás de un firewall, para que no quede expuesto como un potencial agujero de seguridad.
  • Vigila el uso de los recursos. Generar un spreadhseet sobre la interfaz de automatización OLE (los PIA son simplemente cuñas sobre esto) es un proceso bastante pesado. Si necesita escalar esto a altos volúmenes de datos, es posible que necesite ser un poco listo con su architecture.

Algunos de los enfoques ‘use mime-types to trick excel into opening HTML table’ funcionarían si no le importa que el formato del archivo sea un poco básico. Estos enfoques también encierran el trabajo pesado de la CPU en el cliente. Si desea un control detallado sobre el formato de la hoja de cálculo, probablemente tendrá que usar Excel para generar el archivo como se describe arriba.