Crear un documento Excel con Open XML

¿Cuántas veces hemos necesitado exportar los datos desde una aplicación a un documento Excel? Este es uno de los retos de ayer y de hoy :)
Si bien las opciones son varias y, como no, algunas mejores que otras, he optado por trabajar esta vez con Open XML. Este estándar está basado en archivos XML comprimidos en un ZIP (docx, xlsx, pptx) y es el elegido por Microsoft como formato por defecto a partir de la versión 2007 de Microsoft Office (En la versión 2010 te pregunta cuál es el que quieres utilizar de forma predeterminada). En este post me centraré en las hojas de cálculo aunque también es posible la creación de documentos Word, presentaciones y charts.

DESCARGAS NECESARIAS

Para poder trabajar con Open XML y Visual Studio, necesitamos descargar  Open XML SDK 2.0 for Microsoft Office.
Con este SDK tendremos la capacidad de definir una serie de esquemas XML para la representación de hojas de cálculo, charts, presentaciones y documentos word bajo el estándar ECMA 376 y la aprobación ISO/IEC 29500. Microsoft Office Word 2007/2010, Excel 2007/2010 y PowerPoint 2007/2010 usan Open XML como formato por defecto para sus archivos.

CREACIÓN DE UNA HOJA DE CÁLCULO

Lo primero que necesitamos para comenzar a trabajar con Open XML en Visual Studio es agregar las referencias a DocumentFormat.OpenXML y Windows.Base.

Una hoja de cálculo consta fundamentalmente de tres partes: Un workbook, las hojas y los datos almacenados en cada hoja. La estructura más simple de un documento excel sería algo parecido a esto:

<x:workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheets>
    <x:sheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" name="ReturnGisSheet" sheetId="1" r:id="rId1" />
  </x:sheets>
</x:workbook>

El primer objetivo es crear el documento con los elementos mínimos para que el mismo sea un documento válido.


using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXMLExcel.Models
{
    public class OpenXml
    {
        public static byte[] CreateExcel()
        {
            var memoryStream = new MemoryStream();
            using (var excel = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook, true))
            {
                CreateParts(excel);
            }

            return memoryStream.ToArray();
        }

        public static void CreateParts(SpreadsheetDocument excel)
        {
            //workbook area
            var workbookPart = excel.AddWorkbookPart();
            //workbook content
            CreateWorkbookPartContent(workbookPart);

            //worksheet area
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetContent(worksheetPart);
        }

        private static void CreateWorkbookPartContent(WorkbookPart workbookPart)
        {
            var workbook = new Workbook();

            //Workbook sheets
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "ReturnGisSheet", SheetId = 1, Id = "rId1" };

            sheets.Append(sheet);
            workbook.Append(sheets);

            workbookPart.Workbook = workbook;
        }

        private static void GenerateWorksheetContent(WorksheetPart worksheetPart)
        {
            var worksheet = new Worksheet();

            var sheetData = new SheetData();

            worksheet.Append(sheetData);

            worksheetPart.Worksheet = worksheet;
        }
    }
}

Cada elemento de un archivo Open XML está alojado en un contenedor específicamente diseñado para ese tipo de elemento. De la misma manera, todos ellos deben estar enlazados entre sí ya que, de lo contrario, el documento no se abrirá (En el caso de Office 2003/2007) o bien nos solicitará su reparación, lo cual no es muy elegante.
Si arrancamos el proyecto web y solicitamos el archivo veremos que el nombre de la hoja es ReturnGis y, en el caso de Office 2010, esta aparecerá protegida.

AÑADIR DATOS A LA HOJA DE CÁLCULOS

La parte del documento encargada de contener los datos de una hoja se llama SheetData, la cual está alojada dentro de un WorkSheet y, a su vez, dentro del WorkSheetPart del documento. En este caso alimentaré la hoja de cálculo a través de la base de datos de ejemplo de Adventure Works 2008 R2.


using System;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXMLExcel.Models
{
    public class OpenXml
    {
        public static byte[] CreateExcel()
        {
            var memoryStream = new MemoryStream();
            using (var excel = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook, true))
            {
                CreateParts(excel);
            }

            return memoryStream.ToArray();
        }

        public static void CreateParts(SpreadsheetDocument excel)
        {
            //workbook area
            var workbookPart = excel.AddWorkbookPart();
            //workbook content
            CreateWorkbookPartContent(workbookPart);

            //worksheet area
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetContent(worksheetPart);
        }

        private static void CreateWorkbookPartContent(WorkbookPart workbookPart)
        {
            var workbook = new Workbook();

            //Workbook sheets
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "ReturnGisSheet", SheetId = 1, Id = "rId1" };

            sheets.Append(sheet);
            workbook.Append(sheets);

            workbookPart.Workbook = workbook;
        }

        private static void GenerateWorksheetContent(WorksheetPart worksheetPart)
        {
            var worksheet = new Worksheet();

            var sheetData = new SheetData();

            var adventureWorksEntities = new AdventureWorksEntities();
            var creditCardData = adventureWorksEntities.CreditCard.ToList();

            for (var i = 0; i < creditCardData.Count; i++)
            {
                var rowNumber = (i + 1).ToString();
                var row = new Row { RowIndex = UInt32.Parse(rowNumber) };
                var creditCardId = new Cell { CellReference = "A" + rowNumber, CellValue = new CellValue(creditCardData[i].CreditCardID.ToString()) ,DataType = CellValues.Number};
                row.Append(creditCardId);
                var cardType = new Cell { CellReference = "B" + rowNumber, CellValue = new CellValue(creditCardData[i].CardType), DataType = CellValues.String };
                row.Append(cardType);
                var cardNumber = new Cell { CellReference = "C" + rowNumber, CellValue = new CellValue(creditCardData[i].CardNumber), DataType = CellValues.Number };
                row.Append(cardNumber);
                var expMonth = new Cell { CellReference = "D" + rowNumber, CellValue = new CellValue(creditCardData[i].ExpMonth.ToString()), DataType = CellValues.Number };
                row.Append(expMonth);
                var expYear = new Cell { CellReference = "E" + rowNumber, CellValue = new CellValue(creditCardData[i].ExpYear.ToString()), DataType = CellValues.Number };
                row.Append(expYear);
                sheetData.Append(row);
            }
            worksheet.Append(sheetData);

            worksheetPart.Worksheet = worksheet;
        }
    }
}

El resultado sería el siguiente:

OPEN XML SDK 2.0 PRODUCTIVITY TOOL FOR MICROSOFT OFFICE

Existen infinidad de combinaciones a la hora de crear un documento, por lo que es bastante complicado aprender y localizar cada una de las propiedades y la forma de asociación entre los elementos que componen un Open XML. Por ello, cuando instalamos el SDK vimos que en la página de descarga aparecían dos elementos disponibles:

El primero de ellos se trata de una herramienta que nos permite ver de una forma jerárquica documentos ya creados, así como la posibilidad de obtener el código necesario para implementarlo en C#. Puede ser recomendable utilizar esta herramienta como guía pero resulta algo complejo de comprender ya que el código no está optimizado para su uso.

También es posible validar nuestros documentos para comprobar que la estructura es la correcta aunque no siempre es efectivo. El motivo es que dependiendo del error cometido en la creación del mismo la herramienta nos permitirá abrir el documento o no.

La gran ventaja de trabajar con este formato es que no existe la necesidad de tener instalado Office en el servidor y creamos nuestros documentos a través de una programación orientada a objetos.

Facilito el proyecto por si fuera de utilidad.

¡Saludos!

5 comentarios sobre “Crear un documento Excel con Open XML

  1. Desde ya muchas gracias tu post me fue de mucha ayuda, me encontré con un problema al utilizar

    DataType = CellValues.Date

    al abrir el archivo Excel generado Excel 2010 me indicaba que había un error que se generaba por este tipo de datos, buscando por Internet me encontré que esto es un problema general, utilizando el Open XML SDK 2.0 Productivity Tool expongo a continuación que fue lo que me dio resultado.

    inicialmente intente :

    var horafecha = new Cell { CellReference = “J” + rowNumber, CellValue = new CellValue(queryAlarma[i].horafecha.ToString()), DataType = CellValues.Date };
    row.Append(horafecha);

    como les comentaba esto produce un error y no reconoce Excel la fecha,

    Lo que me resulto:

    var horafecha = new Cell { CellReference = “J” + rowNumber, CellValue = new CellValue(queryAlarma[i].horafecha.ToOADate().ToString().Replace(‘,’, ‘.’)), StyleIndex = (UInt32Value)1U };
    row.Append(horafecha);

    Paso a explicar: horafecha es del tipo DateTime utilizando ToOdate() lo que hacemos es convertir la fecha al valor numérico de la fecha lo cual genera un Double luego al convertirlo a string remplazo las “comas” por puntos debido al formato numérico ya que es necesario que sea en punto la separación de decimales por temas de regionalización.

    Luego en vez de utilizar el tipo de dato date ( DataType = CellValues.Date ) lo que haremos sera utilizar un estilo, el estilo en mi caso lo hago mediante la hoja de estilos la cual pueden definir a su gusto.

    Lo relevante de mi hoja de estilo para esto es:

    NumberingFormats numberingFormats1 = new NumberingFormats(){ Count = (UInt32Value)1U };

    NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)164U, FormatCode = “d-m-yy h:mm;@” };

    numberingFormats1.Append(numberingFormat1);

    Esperando que sea de utilidad me despido.

    Saludos a todos!

  2. Que tal se que este post no es reciente pero estoy tratando de usar Open XML para crear un archivo excel pero me manda este error :

    Error 18 El tipo ‘System.IO.Packaging.Package’ está definido en un ensamblado al que no se hace referencia. Debe agregar una referencia al ensamblado ‘WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′.

    De casualidad no sabrias cual es la solucion ?
    Saludos y Gracias

  3. Compartido por Gisela Torres (@0GiS0)

  4. Compartido por @rlbisbe

Deja un comentario