Saltar al contenido

Artículos, tutoriales, trucos, curiosidades, reflexiones y links sobre programación web ASP.NET Core, MVC, Blazor, SignalR, Entity Framework, C#, Azure, Javascript... y lo que venga ;)

17 años online

el blog de José M. Aguilar

Inicio El autor Contactar

Artículos, tutoriales, trucos, curiosidades, reflexiones y links sobre programación web
ASP.NET Core, MVC, Blazor, SignalR, Entity Framework, C#, Azure, Javascript...

¡Microsoft MVP!
martes, 5 de marzo de 2013
Anónimo
Venga, lo confieso: yo también he generado desde mis aplicaciones contenidos HTML y los he enviado al cliente en un archivo con extensión XLS, incluso modificando el content-type, para que pareciera un documento de hoja de cálculo. Durante años. Y también le he dicho a mis clientes que el molesto mensaje que aparece al abrirlo desde Excel, el que indica que el contenido del archivo no coincide con la extensión del mismo, es algo normal.

Pero esto se acabó desde que descubrí ClosedXML, un magnífico componente para .NET basado en el estándar OpenXML que permite la generación de archivos Excel “de verdad”, con formato, estilos, fórmulas, rangos, filtros, y casi todo lo que se nos pueda ocurrir.

ClosedXMLClosedXML, proyecto iniciado por Manuel de León y distribuido bajo licencia MIT, se aleja de la verbosidad y amplitud de alcance del Open XML SDK de Microsoft, ofreciendo un API mucho más natural e intuitivo exclusivamente diseñado para crear y manipular documentos Excel. De hecho, el nombre ClosedXML lo eligió después de conocer el SDK oficial y pensar “si es así como se trabaja con Open XML, preferiría utilizar algo que estuviera cerrado”, en referencia a la complejidad que el primero supone.

Dado que se basa en Open XML, para abrir los archivos generados se necesita Excel 2007 o una versión posterior, aunque creo que ocho años después de su aparición ya podríamos considerar que es un mínimo bastante razonable ;-)

Instalación del componente

Como de costumbre, la instalación de ClosedXML la vamos a realizar a través de Nuget:
PM> Install-Package ClosedXML
Attempting to resolve dependency 'DocumentFormat.OpenXml (≥ 1.0)'.
Successfully installed 'ClosedXML 0.68.1'.
Successfully added 'DocumentFormat.OpenXml 1.0' to ClosedXmlDemo.Model.
Successfully added 'ClosedXML 0.68.1' to ClosedXmlDemo.Model.
No me canso de repetirlo: Nuget, ¿dónde has estado todos estos años? ;-)

Creación y salvado de un documento Excel básico (Desktop, Webforms, MVC)

La creación y salvado a disco de un documento Excel es absolutamente trivial. Basta con instanciar un objeto de la clase XLWorkBook, añadir una nueva hoja su colección de Worksheets, e introducir valores en ella a través de su propiedad Cell, como podemos observar en el código genérico mostrado a continuación:
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sheet 1");
worksheet.Cell(1, 1).Value = "Hello, world!";
workbook.SaveAs("c:\\temp\\excel.xlsx");
De esta forma tan simple, en c:\temp\excel.xlsx tendremos lo siguiente:
Hoja de cálculo generada con ClosedXML
Si estamos desarrollando una aplicación web, cuando generamos un archivo Excel lo habitual es que lo enviemos al usuario como documento adjunto para que lo descargue y guarde en su equipo, lo que implica modificar el content-type y añadir un encabezado content-disposition.

En este caso de usar WebForms, el código a emplear es, poco más o menos, el siguiente:
Response.Clear();
Response.ContentType =
     "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"Demo.xlsx\"");

using (var memoryStream = new MemoryStream())
{
    workbook.SaveAs(memoryStream);
    memoryStream.WriteTo(Response.OutputStream);
}
Response.End();
Observad que no estamos salvando el workbook  sobre un MemoryStream, que luego volcamos al stream de salida para que viaje al cliente. Este doble paso, en cualquier caso bastante sencillo de implementar, se debe a que ClosedXML requiere para guardar el archivo que el stream de salida sea de avance y retroceso, y es algo que OutputStream no cumple.

En caso de tratarse de ASP.NET MVC, el código es prácticamente el mismo, aunque lo correcto sería implementarlo en un ActionResult personalizado:
public class ExcelResult: ActionResult
{
    private readonly XLWorkbook _workbook;
    private readonly string _fileName;

    public ExcelResult(XLWorkbook workbook, string fileName)
    {
        _workbook = workbook;
        _fileName = fileName;
    }

    public override void ExecuteResult(ControllerContext context)
    {
        var response = context.HttpContext.Response;
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument." 
                             + "spreadsheetml.sheet";
        response.AddHeader("content-disposition", 
                           "attachment;filename=\""+ _fileName +".xlsx\"");

        using (var memoryStream = new MemoryStream())
        {
            _workbook.SaveAs(memoryStream);
            memoryStream.WriteTo(response.OutputStream);
        }
        response.End();
    }
}
Y ya podríamos usarlo directamente desde un controlador:
public ActionResult GenerateExcel()
{
    // Generate the workbook...
    var workbook = ClosedXmlDemoGenerator.GenerateWorkBook();

    // ... and return it to the client
    return new ExcelResult(workbook, "demo");
} 

Establecer valores en las celdas

Las celdas podemos referenciarlas utilizando la propiedad Cell de los objetos IXLWorksheet, y podemos hacerlo indicando su número de fila y columna o mediante el nombre usado normalmente en el mismo Excel:
worksheet.Cell(1, 1).Value = "Hello, world!";
worksheet.Cell("A2").Value = "How are you?";

Establecer valores de celdas con ClosedXML
Por supuesto, los valores pueden ser de todo tipo, de hecho la propiedad Value que establecemos es de tipo object, aunque obviamente sólo serán reconocidos los tipos habituales de Excel:
worksheet.Cell("A2").Value = "Text";
worksheet.Cell("B2").Value = "Hi!!";
worksheet.Cell("A3").Value = "Integer";
worksheet.Cell("B3").Value = 3;
worksheet.Cell("A4").Value = "Decimal";
worksheet.Cell("B4").Value = 3.5;
worksheet.Cell("A5").Value = "Boolean";
worksheet.Cell("B5").Value = true;
worksheet.Cell("A6").Value = "DateTime";
worksheet.Cell("B6").Value = DateTime.Now;
worksheet.Cell("A7").Value = "Object";
worksheet.Cell("B7").Value = new InvoiceDetails();
Tipos de datos en ClosedXML
También podemos crear rangos y establecerles valores de forma directa:
worksheet.Range("A1:D5").Value = "Hi!";
Valores de rangos en ClosedXML
E incluso podemos asignar directamente colecciones de datos como objetos de tipo DataTable o IEnumerable<T>:
worksheet.Cell("A1").Value = new[]
             {
                  new { Id=1, Name="John", Age = 42},
                  new { Id=2, Name="Peter", Age = 23},
                  new { Id=3, Name="Mary", Age = 32},
             };
Uso de conjuntos de datos en ClosedXML

Formato de celdas

ClosedXML pone a nuestra disposición un rico conjunto de propiedades y métodos para dar formato a las celdas o a rangos de ellas. La sintaxis fluida que podemos utilizar facilita mucho el descubrimiento de las posibilidades de formateo, y la implementación de un código muy limpio y comprensible:
worksheet.Range("B2:E5")
         .SetValue("Hi!")
         .Style.Border.SetOutsideBorder(XLBorderStyleValues.Dotted);

worksheet.Range("B3:E5").Style
         .Font.SetFontSize(10)
         .Font.SetFontColor(XLColor.Gray)
         .Font.SetItalic(true);

worksheet.Range("B2:E2").Style
         .Font.SetFontSize(13)
         .Font.SetBold(true)
         .Font.SetFontColor(XLColor.White)
         .Fill.SetBackgroundColor(XLColor.Gray);
Formateo de celdas con ClosedXML
También podemos, por supuesto, establecer la alineación de celdas o rangos, unir celdas, o establecer el formato de visualización de sus valores. Vemos también, de paso, una forma más fluida de establecer los valores, usando SetValue():
worksheet.Cell("A1")
         .SetValue(1234.56789)
         .Style.NumberFormat.SetFormat("#,##0.#0");

worksheet.Cell("A2")
         .SetValue(DateTime.Now)
         .Style.DateFormat.SetFormat("dd-mm-yyyy")
               .Alignment.SetVertical(XLAlignmentVerticalValues.Center);

worksheet.Range("A3:B4")
         .Merge()
         .SetValue("Merged cells")
         .Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
               .Alignment.TextRotation = 15; // Degrees
Formateo de celdas con ClosedXML

Uso de fórmulas

Esto es especialmente interesante, pues no había forma de conseguirlo cuando exportábamos archivos HTML, CSV o similares, en los que sólo importaba el valor de las celdas. Con ClosedXML tenemos total libertad para introducir fórmulas en las celdas; de esta forma, no sólo estaremos enviando al usuario un conjunto estático de datos, sino una completa hoja Excel que puede modificar (si nos interesa, claro) y usar para su trabajo:
worksheet.Cell("B2")
    .SetValue("Number")
    .Style.Font.SetBold(true);

for (int i = 1; i <= 5; i++)
{
    worksheet.Cell(3+i, 2).Value = i;
}

worksheet.Cell("A9").SetValue("Total");
worksheet.Cell("B9")
    .SetFormulaA1("=SUM(B3:B8)")
    .Style.Border.SetTopBorder(XLBorderStyleValues.Medium);

Fórmulas en ClosedXML

Otras características interesantes

ClosedXML soporta muchísimas funcionalidades adicionales a las descritas hasta el momento. Voy a citar algunas más que me han llamado la atención.

Por ejemplo, tenemos la posibilidad de añadir filtros y ordenación por columna a los datos, de manera que el usuario pueda realizar una selección de la información recibida:

worksheet.Cell("A1").Value = "Id";
worksheet.Cell("B1").Value = "Name";
worksheet.Cell("C1").Value = "Age";
worksheet.Cell("A2").Value = new[]
     {
          new { Id=1, Name="John", Age = 42},
          new { Id=2, Name="Peter", Age = 23},
          new { Id=3, Name="Mary", Age = 32},
          new { Id=4, Name="John", Age = 45},
     };
worksheet.RangeUsed().SetAutoFilter();
Filtros de datos con ClosedXML
Otro aspecto que puede ser interesante es la protección de celdas para que el usuario no pueda modificar sus valores. En el siguiente ejemplo, se protege la hoja completa con un password, de forma que no podrá ser editada, excepto las tres primeras columnas de la primera fila, que el usuario podrá editar con total libertad:
worksheet.Protect("1234"); // Locks the worksheet and sets the password
worksheet.Range("A1:C1")
    .Style.Protection.SetLocked(false); // Unlocks the range
Protección de hojas con ClosedXML
También, si nos interesa que el usuario edite valores de las celdas, es posible especificar restricciones en los datos de entrada, como su tipo, rango de valores permitidos, selección de valores desde desde un desplegable, etc.:
worksheet.Cell("A1").Value = "Digit 0-9:";
worksheet.Cell("B1").DataValidation.WholeNumber.Between(0, 9);

worksheet.Cell("A2").Value = "Two:";
worksheet.Cell("B2").DataValidation.WholeNumber.EqualTo(2);

worksheet.Cell("A3").Value = "Date:";
worksheet.Cell("B3").DataValidation.AllowedValues = XLAllowedValues.Date; // Only dates
worksheet.Cell("B3").DataValidation.ErrorMessage = "Only dates, please";
Validación de datos de entrada con ClosedXML
Incluso podemos añadir comentarios a celdas:
worksheet.Cell("B2").SetValue("TOTAL:")
    .Style.Font.SetBold(true)
          .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);

worksheet.Cell("C2").Style.NumberFormat.SetFormat("#,##0.#0");
worksheet.Cell("C2").SetValue(9876543.21)
    .Comment.SetAuthor("jmaguilar")
    .AddText("Danger: this number seems odd!");
Añadir comentarios con ClosedXML

… y vamos a dejarlo aquí ;-)

Bueno, pues tras este largo post espero que más o menos os haya quedado claro el uso y posibilidades de este magnífico componente, y que os hayan entrado muchas ganas de probarlo. Seguro cambiará la forma en que generáis los archivos Excel desde vuestras aplicaciones y abrirá nuevas posibilidades, hasta ahora difícilmente implementables usando otras alternativas.

También, recomendaros que no dejéis de leer la documentación, que es bastante extensa y detallada, donde podréis ver muchas más características que no he comentado para no hacer un post más interminable de lo que ya es ;-)

Podéis descargar un proyecto de prueba desde mi Skydrive, donde veréis en funcionamiento la generación de archivos Excel desde una aplicación de consola, ASP.NET Webforms, y MVC.

Publicado en Variable not found.

35 Comentarios:

Anónimo dijo...

Hasta ahora siempre había utilizado EPPlus. Echaré un vistazo a éste a ver que tal

Txarly dijo...

Muy interesante. ¿Existe algo parecido para generar documentos de Word vía web?

Anónimo dijo...

Hola,

Muy útil el artículo.

Entiendo que también sería posible abrir excels ya existentes y modificarlos?

Saludos.

Juan Manuel Fenoll dijo...

Sí, yo también les colaba el mensajito de aviso al bajar la Excel como que era normal :-(((

Muy buena librería.

Graciasss

josé M. Aguilar dijo...

Hola!

@txarly, pues supongo que algo de eso habrá también ;) Como mínimo el SDK de Microsoft seguro que te lo permite, sería cuestión de buscar si hay algo que lo simplifique un poco.

@anónimo, sí, en principio se pueden abrir excels (XML), aunque hasta ahora no he tenido necesidad de hacerlo, por lo que no te lo podría decir con seguridad.

Un saludo!

SJOrrillo dijo...

Interesante gracias a este post, me libre de usar Open XML, para un requerimiento algo complejo... el único problema que tengo es que al generar el archivo excel, las formulas no se ejecutan... tengo que abrir el archivo ir a la celda y presionar F2 + Enter y la formula se ejecuta... Es un problema menor que el usuario final entiende, pero me gustaría solucionarlo

Anónimo dijo...

gracias te pasaste

Miriam dijo...

Hola muy buen post, seria posible que se pudiera exportar a excel sin que se tuviera que guardar, solo que se abra el libro de excel y que se exporten los datos al mismo, ayuda por favor!!!

josé M. Aguilar dijo...

Hola, Miriam.

Me temo que eso depende del usuario... es decir, es él el que decide si guarda o abre cuando retornamos un archivo de esta forma.

Otra posibilidad sería retornar el archivo como "no adjunto"; no lo he probado, pero posiblemente se abriría directamente. Simplemente elimina el "content-disposition", a ver qué tal.

Saludos.

Anónimo dijo...

Hola me gustaría saber si es una librería de uso libre o se debe de realizar algún tipo de pago por su uso.

josé M. Aguilar dijo...

Libre: http://closedxml.codeplex.com/license

José Luis Juárez dijo...

Hola disculpa la molestia, estoy teniendo problemas con el archivo descargado desde el server ya que excel me dice que el archivo está dañado o no se puede abrir, cuando lo genero en mi proyecto local lo abre sin problemas, espero me puedas ayudar,

Saludos

José María Aguilar dijo...

Hola!

Pues la verdad, no lo sé... se me ocurre que quizás algo esté fallando en servidor y la respuesta no sea un PDF, sino la página de error. Prueba a ver dicha respuesta mediante las herramientas de red de tu browser, a ver si te da alguna pista.

Saludos!

DavidIsrael dijo...

¿con esta librería se puede abrir un excel ya existente y modificarlo?

José María Aguilar dijo...

Hola!

Sí, en la documentación del producto aparecen ejemplos de cómo hacerlo.

Saludos!

Anónimo dijo...

¿Con la librería se puede guardar el archivo en pdf?
Gracias.

Anónimo dijo...

disculpa, una pregunta, hay alguna manera de insertar imagenes dentro de una hoja? porque he buscado y todas las soluciones son muy complejas

José María Aguilar dijo...

Hola,

(uff, poned vuestros nombres!!)

@anonimo #1: pues no lo sé, la verdad... he visto que aparece como una feature del producto, pero en un vistazo rápido no he encontrado ejemplos.

@anonimo #2: no he tenido que hacerlo nunca, pero efectivamente, he visto que el soporte de ClosedXML para ello es limitado, y resulta farragoso. Pero poderse se puede, encontrarás algunos ejemplos completos en google de cómo hacerlo (eso sí, requiere bastante código)



Anónimo dijo...

Hola, quisiera saber si hay manera de agregar nuevas hojas al libro de excel?

Saludos!

José María Aguilar dijo...

Hola,

viene en la documentación :)

https://closedxml.codeplex.com/wikipage?title=Creating%20Multiple%20Worksheets&referringTitle=Documentation

Saludos

Anónimo dijo...

Estimado mi nombre es Guillermo, al momento de agregar una configuracion que cambia los margenes no se aplica al momento de guardar. mi version es 0.69.2

worksheet.PageSetup.PrintAreas.Add("A1:X" + co.ToString());
worksheet.PageSetup.Margins.Top = 1;
worksheet.PageSetup.Margins.Bottom = 1;
worksheet.PageSetup.Margins.Left = 1;
worksheet.PageSetup.Margins.Right = 1;


Response.Clear();
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"Demo.xlsx\"");


using (var memoryStream = new MemoryStream())
{
workbook.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
}
Response.End();

Si pudieras ayudarme

José María Aguilar dijo...

Hola!

Me temo que poco puedo hacer al respecto. Antes de nada, prueba a actualizar el componente por si se trata de un bug corregido y, en caso contrario, siempre podrías intentar publicarlo en los foros o issues del proyecto https://closedxml.codeplex.com/discussions

Saludos!

Anónimo dijo...

Hola, mira estoy trabajando con este ensamblado utilizando datatable y dataset, pero necesito poder colocarlos dentro de la misma hoja, porque el dataset lo deja desde hojas distintas.

Por favor, quedo a la espera de tus comentario.

Jaume dijo...

Hola José, soy Jaume (trabajé en Abaden Dentistas con clinicalsession)

Estoy con tu ejemplo, tengo una app html5 de turnos y calendarios, también está en windows, que es donde exporto a Excel. Ahora usaré este código para crear un .xls directamente desde la app web.

http://vacacionalpremium.com/
http://cloud.vacacionalpremium.com/app/

Saludos!!

José María Aguilar dijo...

Hola, me alegro de verte!

Genial, espero que te vaya bien :)

JAIME COHEN dijo...

Excelente Introducción. Muchas gracias!!!

JoseGavilan dijo...

1.000 Gracias!!
Muy útil.

D. C. Q. dijo...

Hola Jose

podras ayudarme a despejar una duda que tengo, estoy importando una tabla de excel, utilizando ClosedXML.Excel impota bastante bien la informacion, sin embargo, me esta estrayendo fecha y hora en la misma columna. solo quiero la fecha.
como puedo condicionar este codigo a que en la columna D, solo sea fecha y la columna F, solo sea hora.
Gracias de antemano.

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Rankiweb_1_0_C_Chard
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
using (XLWorkbook workbook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
IXLWorksheet sheet = workbook.Worksheet(1);
DataTable dt = new DataTable();

bool firstRow = true;
foreach (IXLRow row in sheet.Rows()) //por cada celda, en las filas.
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString()); //agrega la informacion de Columnas
//dt = DataTable
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;

//Este comando lee cuadro por cuadro, la impormacion que exista en la tabla de excel, cuando no encuantra mas informacion, entonces muestra la informacion leida en pantalla a travez del Gridview.
//row=FILA
//var wb = new XLWorkbook();
//var ws = wb.Worksheets.Add("TMES");
//ws.Range("I2").FormulaA1 = "=SUM(A2:A15)";
}
}





GridView1.DataSource = dt;
GridView1.DataBind();


}
}
}

protected void TextBox1_TextChanged(object sender, EventArgs e)
{

}
}
}

José María Aguilar dijo...

Hola!

Creo que se trata simplemente de un problema de formateo. Al añadir a tu datatable el contenido de la celda con un ToString(), estás transformando a cadena la fecha y esto incluye el componente hora.

Lo más simple que podrías hacer es detectar cuándo en la celda hay una fecha (quizás comprobando el tipo de dato o simplemente determinando si te encuentras en la columna "N", que sería donde sabes que hay una fecha) y luego formateando a tu gusto el resultado; por ejemplo, podrías recortar el string por el primer espacio, de forma que sólo pasarías al datatable la parte de la fecha.

Saludos!

Daniel dijo...

Hola buenas tardes, hay alguna forma de instalar ClosedXML, sin el nuget, ya que estoy trabajando con VS 2008 y no existe Nuget para instalar ClosedXML, no se si exista alguna dll

José María Aguilar dijo...

Hola,

lo desconozco, pero en la web de proyecto no parece que distribuyan DLLs. Podrías probar a descargar el proyecto ClosedXML completo y compilarlo en tu equipo para obtener la DLL, aunque con un VS tan antiguo no sé si te funcionará.

Mauro dijo...

Hola, es posible esportar con esta libreria archivos con mas de 1.000.000 de registros?
Muchas gracias por todo los aportes, nos sirve mucho a las personas que estamos conociendo el mundo web

José María Aguilar dijo...

Hola!

No se me ha dado el caso, así que no sabría responderte. Lo mejor para salir de dudas sería probarlo :)

Saludos!

masantizo dijo...

Buena dia, sera posible ocultar el grid de la hoja electronica, esto puede ser posible?

José María Aguilar dijo...

Hola!

No sé exactamente a qué te refieres con ocultar el grid. Si te refieres a que dejen de verse las líneas que separan las celdas, quizás puedas conseguirlo cambiando su color de fondo o alguna otra propiedad de estilo.

Saludos!