Generar archivos Excel como un señor con ClosedXml

 

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:

?
1
2
3
4
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:tempexcel.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:

?
1
2
3
4
5
6
7
8
9
10
11
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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:

?
1
2
3
4
5
6
7
8
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:

?
1
2
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
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:

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

?
1
2
3
4
5
6
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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():

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

?
1
2
3
4
5
6
7
8
9
10
11
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:

?
1
2
3
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.:

?
1
2
3
4
5
6
7
8
9
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:

?
1
2
3
4
5
6
7
8
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.

2 comentarios sobre “Generar archivos Excel como un señor con ClosedXml”

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *