1. Introducción
En el post anterior vimos las 3 técnicas que hay para crear un archivo de Excel 2007 exportando datos desde varias listas de objetos o varias tablas de un DataSet en .NET. En ese post se presentó un Demo de cómo usar OLEDB para crear el archivo Excel.
En este post vamos a ver algo inédito, que no hay en la web, que es como crear un archivo de Excel 2007, creando los archivos XML necesarios y luego comprimiendolo en un xlsx.
2. Ventajas de Crear un Archivo Excel usando XML
Existen muchas ventajas de usar esta técnica entre las 3 mas importantes tenemos:
- Es la más rápida en la creación de archivos Excel, casi 3 veces más rápida que OLEDB y mas de 10 veces más rápida que abrir una instancia de Excel (Automatización COM).
- No necesita tener instalado ni los Componentes de Acceso a Datos del "Microsoft Access Database Engine 2010 Redistributable" ni tampoco una instancia de Excel para crear el archivo.
Nota: Ni siquiera se necesita tener instalado el Excel para leer o ver el archivo ya que se puede hacer usando los Visores de los Demos pasados.
- Se puede crear no solo celdas con datos sino gráficos, dar estilo, formatos, etc, cosa que no se puede al usar OLEDB.
Nota: Si se puede abriendo una instancia de Excel pero se demora demasiado y consume muchos recursos, lo cual está terminantemente prohibido para Aplicaciones Web ASP.NET.
3. Crear una Aplicación Windows Forms en C#
Abrir Visual Studio y crear una aplicación Windows Forms en C# llamada "Excel_Crear_XML", cambiarle de nombre al formulario por "frmExcel" y realizar el diseño similar a la figura mostrada:
Diseño del formulario "frmExcel"
En el diseño hay los siguientes controles:
- Botón para Llenar desde Listas llamado "btnLlenarLista".
- Botón para Llenar desde Tablas llamado "btnLlenarTabla".
- Botón para Exportar Listas a Excel llamado "btnExportarListas". Deshabilitado por defecto.
- Botón para Exportar Tablas a Excel llamado "btnExportarTablas". Deshabilitado por defecto.
- Control Tab llamado "tabExcel" con 2 tabPages: "tabCategorias" y "tabProducto".
- Control DataGridView llamado "dgvCategoria" ubicado en el tab de Categorias.
- Control DataGridView llamado "dgvProducto" ubicado en el tab de Productos.
4. Crear las Clases con las Entidades de Negocio
Lo primero que debemos crear son las clases de entidades para las listas de objetos de categorías y productos:
Archivo de Clase: beCategoria.cs
namespace Excel_Crear_XML
{
public class beCategoria
{
public int IdCategoria { get; set; }
public string Nombre { get; set; }
}
}
Archivo de Clase: beProducto.cs
namespace Excel_Crear_XML
{
public class beProducto
{
public int IdProducto { get; set; }
public string Nombre { get; set; }
public int IdCategoria { get; set; }
public decimal Precio { get; set; }
public short Stock { get; set; }
}
}
Archivo de Clase: beCategoriaProducto.cs
using System.Collections.Generic;
namespace Excel_Crear_XML
{
public class beCategoriaProducto
{
public List<beCategoria> ListaCategoria { get; set; }
public List<beProducto> ListaProducto { get; set; }
}
}
5. Crear una Clase que permita llenar los Datos
Lo segundo que debemos hacer es crear una clase para llenar los datos de las tablas y listas de objetos tanto para categorías como productos.
En una aplicación real esto se reemplazará por la llamada a un componente de reglas del negocio y solo se elegirá uno de las 2 repositorios de datos: DataSet o Objeto con Listas. Lo recomendable es elegir el Objeto con Listas ya que consume menos memoria y procesa más rápido.
Archivo de Clase: Data.cs
using System;
using System.Collections.Generic;
using System.Data;
namespace Excel_Crear_XML
{
public class Data
{
public static List<beCategoria> ListaCategorias()
{
//Llenar la Lista de Categorias
List<beCategoria> lbeCategoria = new List<beCategoria>();
lbeCategoria.Add(new beCategoria
{
IdCategoria = 1,
Nombre = "Bebidas"
});
lbeCategoria.Add(new beCategoria
{
IdCategoria = 2,
Nombre = "Cereales"
});
lbeCategoria.Add(new beCategoria
{
IdCategoria = 3,
Nombre = "Condimentos"
});
lbeCategoria.Add(new beCategoria
{
IdCategoria = 4,
Nombre = "Lacteos"
});
lbeCategoria.Add(new beCategoria
{
IdCategoria = 5,
Nombre = "Pastas"
});
return lbeCategoria;
}
public static List<beProducto> ListaProductos()
{
//Llenar la Lista de Productos
List<beProducto> lbeProducto = new List<beProducto>();
lbeProducto.Add(new beProducto
{
IdProducto = 1,
Nombre = "Pepsi",
IdCategoria = 1,
Precio = 1.5M,
Stock = 100
});
lbeProducto.Add(new beProducto
{
IdProducto = 2,
Nombre = "Avena 3 Ositos",
IdCategoria = 2,
Precio = 2.0M,
Stock = 50
});
lbeProducto.Add(new beProducto
{
IdProducto = 3,
Nombre = "Ajinomoto",
IdCategoria = 3,
Precio = 0.5M,
Stock = 200
});
lbeProducto.Add(new beProducto
{
IdProducto = 4,
Nombre = "Leche Gloria",
IdCategoria = 4,
Precio = 3.5M,
Stock = 300
});
lbeProducto.Add(new beProducto
{
IdProducto = 5,
Nombre = "Fideos Lavaggi",
IdCategoria = 5,
Precio = 3.0M,
Stock = 150
});
lbeProducto.Add(new beProducto
{
IdProducto = 6,
Nombre = "Sprite",
IdCategoria = 1,
Precio = 2.0M,
Stock = 200
});
lbeProducto.Add(new beProducto
{
IdProducto = 7,
Nombre = "Quinua Avena",
IdCategoria = 2,
Precio = 2.5M,
Stock = 150
});
lbeProducto.Add(new beProducto
{
IdProducto = 8,
Nombre = "Tuco Sibarita",
IdCategoria = 3,
Precio = 0.5M,
Stock = 200
});
lbeProducto.Add(new beProducto
{
IdProducto = 9,
Nombre = "Leche Pura Vida",
IdCategoria = 4,
Precio = 3.5M,
Stock = 300
});
lbeProducto.Add(new beProducto
{
IdProducto = 10,
Nombre = "Spagetti Don Victorio",
IdCategoria = 5,
Precio = 2.0M,
Stock = 250
});
return lbeProducto;
}
public static DataTable TablaCategorias()
{
//Llenar la Tabla de Categorias
DataTable tblCategoria = new DataTable();
tblCategoria.Columns.Add("IdCategoria",
Type.GetType("System.Int32"));
tblCategoria.Columns.Add("Nombre",
Type.GetType("System.String"));
DataRow drCat1 = tblCategoria.NewRow();
drCat1[0] = 1;
drCat1[1] = "Bebidas";
tblCategoria.Rows.Add(drCat1);
DataRow drCat2 = tblCategoria.NewRow();
drCat2[0] = 2;
drCat2[1] = "Cereales";
tblCategoria.Rows.Add(drCat2);
DataRow drCat3 = tblCategoria.NewRow();
drCat3[0] = 3;
drCat3[1] = "Condimentos";
tblCategoria.Rows.Add(drCat3);
DataRow drCat4 = tblCategoria.NewRow();
drCat4[0] = 4;
drCat4[1] = "Lacteos";
tblCategoria.Rows.Add(drCat4);
DataRow drCat5 = tblCategoria.NewRow();
drCat5[0] = 5;
drCat5[1] = "Pastas";
tblCategoria.Rows.Add(drCat5);
return tblCategoria;
}
public static DataTable TablaProductos()
{
//Llenar la Tabla de Productos
DataTable tblProducto = new DataTable();
tblProducto.Columns.Add("IdProducto",
Type.GetType("System.Int32"));
tblProducto.Columns.Add("Nombre",
Type.GetType("System.String"));
tblProducto.Columns.Add("IdCategoria",
Type.GetType("System.Int32"));
tblProducto.Columns.Add("Precio",
Type.GetType("System.Decimal"));
tblProducto.Columns.Add("Stock",
Type.GetType("System.Int16"));
DataRow drPro1 = tblProducto.NewRow();
drPro1[0] = 1;
drPro1[1] = "Pepsi";
drPro1[2] = 1;
drPro1[3] = 1.5M;
drPro1[4] = 100;
tblProducto.Rows.Add(drPro1);
DataRow drPro2 = tblProducto.NewRow();
drPro2[0] = 2;
drPro2[1] = "Avena 3 Ositos";
drPro2[2] = 2;
drPro2[3] = 2.0M;
drPro2[4] = 50;
tblProducto.Rows.Add(drPro2);
DataRow drPro3 = tblProducto.NewRow();
drPro3[0] = 3;
drPro3[1] = "Ajinomoto";
drPro3[2] = 3;
drPro3[3] = 0.5M;
drPro3[4] = 200;
tblProducto.Rows.Add(drPro3);
DataRow drPro4 = tblProducto.NewRow();
drPro4[0] = 4;
drPro4[1] = "Leche Gloria";
drPro4[2] = 4;
drPro4[3] = 3.5M;
drPro4[4] = 300;
tblProducto.Rows.Add(drPro4);
DataRow drPro5 = tblProducto.NewRow();
drPro5[0] = 5;
drPro5[1] = "Fideos Lavaggi";
drPro5[2] = 5;
drPro5[3] = 3.0M;
drPro5[4] = 150;
tblProducto.Rows.Add(drPro5);
DataRow drPro6 = tblProducto.NewRow();
drPro6[0] = 6;
drPro6[1] = "Sprite";
drPro6[2] = 1;
drPro6[3] = 2.0M;
drPro6[4] = 200;
tblProducto.Rows.Add(drPro6);
DataRow drPro7 = tblProducto.NewRow();
drPro7[0] = 7;
drPro7[1] = "Quinua Avena";
drPro7[2] = 2;
drPro7[3] = 2.5M;
drPro7[4] = 150;
tblProducto.Rows.Add(drPro7);
DataRow drPro8 = tblProducto.NewRow();
drPro8[0] = 8;
drPro8[1] = "Tuco Sibarita";
drPro8[2] = 3;
drPro8[3] = 0.5M;
drPro8[4] = 200;
tblProducto.Rows.Add(drPro8);
DataRow drPro9 = tblProducto.NewRow();
drPro9[0] = 9;
drPro9[1] = "Leche Pura Vida";
drPro9[2] = 4;
drPro9[3] = 3.5M;
drPro9[4] = 200;
tblProducto.Rows.Add(drPro9);
DataRow drPro10 = tblProducto.NewRow();
drPro10[0] = 10;
drPro10[1] = "Spagetti Don Victorio";
drPro10[2] = 5;
drPro10[3] = 2.0M;
drPro10[4] = 250;
tblProducto.Rows.Add(drPro10);
return tblProducto;
}
}
}
6. Crear una Clase que permita Crear Archivos XML y Comprimirlos
La tercera parte a codificar es crear una clase que permita crear los diferentes archivos XML necesitados y luego comprimirlos en un archivo XLSX.
Archivo de Clase: Excel.cs
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Reflection;
using System.Linq;
using System.Collections.Generic;
using System.IO.Compression;
namespace Excel_Crear_XML
{
public class Excel
{
private enum TipoOrigen {
Listas = 0,
Tablas =1
}
private static string sArchivoXlsx;
private static string[] sHojas;
private static string[] sRango;
private static int nHojas;
private static TipoOrigen tipoOrigen;
private static List<dynamic> data;
public static void ExportarDeLista<T>(string archivoXlsx, string[] hojas, T objData)
{
data = new List<dynamic>();
tipoOrigen = TipoOrigen.Listas;
sArchivoXlsx = archivoXlsx;
sHojas = hojas;
nHojas = hojas.Length;
sRango = new string[nHojas];
PropertyInfo[] listas = objData.GetType().GetProperties();
dynamic lista;
PropertyInfo[] campos;
for (int i = 0; i < listas.Length;i++)
{
lista = listas[i].GetValue(objData, null);
data.Add(lista);
campos = lista[0].GetType().GetProperties();
sRango[i] = String.Format("${0}${1}", (char)(64 + campos.Length), lista.Count + 1);
}
crearDirectoriosArchivos();
}
public static void ExportarDeTabla(string archivoXlsx, string[] hojas, DataSet objData)
{
data = new List<dynamic>();
tipoOrigen = TipoOrigen.Tablas;
sArchivoXlsx = archivoXlsx;
sHojas = hojas;
nHojas = hojas.Length;
sRango = new string[nHojas];
DataTable tabla;
for (int i = 0; i < objData.Tables.Count; i++)
{
tabla = objData.Tables[i];
data.Add(tabla);
sRango[i] = String.Format("${0}${1}",
(char)(64 + tabla.Columns.Count), tabla.Rows.Count + 1);
}
crearDirectoriosArchivos();
}
private static void crearDirectoriosArchivos()
{
//Definir la ruta de los directorios a crear
string sDirectorioRaiz = Path.Combine(Path.GetDirectoryName(sArchivoXlsx),
Path.GetFileNameWithoutExtension(sArchivoXlsx));
string sDirectorioRels = Path.Combine(sDirectorioRaiz, "_rels");
string sDirectorioDocProps = Path.Combine(sDirectorioRaiz, "docProps");
string sDirectorioXl = Path.Combine(sDirectorioRaiz, "xl");
string sDirectorioXlRels = Path.Combine(sDirectorioXl, "_rels");
string sDirectorioXlTheme = Path.Combine(sDirectorioXl, "theme");
string sDirectorioXlWorksheets = Path.Combine(sDirectorioXl, "worksheets");
//Definir la ruta de los archivos a crear
string sArchivoContentTypes = Path.Combine(sDirectorioRaiz, "[Content_Types].xml");
string sArchivoRels = Path.Combine(sDirectorioRels, ".rels");
string sArchivoDocApp = Path.Combine(sDirectorioDocProps, "app.xml");
string sArchivoDocCore = Path.Combine(sDirectorioDocProps, "core.xml");
string sArchivoXlStyles = Path.Combine(sDirectorioXl, "styles.xml");
string sArchivoXlWorkbook = Path.Combine(sDirectorioXl, "workbook.xml");
string sArchivoXlRels = Path.Combine(sDirectorioXlRels, "workbook.xml.rels");
string sArchivoXlTheme = Path.Combine(sDirectorioXlTheme, "theme1.xml");
string[] sArchivoXlSheets = new string[nHojas];
for (var i = 0; i < nHojas;i++) sArchivoXlSheets[i] =
Path.Combine(sDirectorioXlWorksheets, String.Format("sheet{0}.xml",i+1));
//Crear los Directorios definidos
DirectoryInfo oDirectorioRaiz = Directory.CreateDirectory(sDirectorioRaiz);
oDirectorioRaiz.CreateSubdirectory("_rels");
oDirectorioRaiz.CreateSubdirectory("docProps");
DirectoryInfo oDirectorioXl = oDirectorioRaiz.CreateSubdirectory("xl");
oDirectorioXl.CreateSubdirectory("_rels");
oDirectorioXl.CreateSubdirectory("theme");
oDirectorioXl.CreateSubdirectory("worksheets");
//Crear los Archivos definidos
File.WriteAllText(sArchivoContentTypes, getContentTypes());
File.WriteAllText(sArchivoRels, getRels());
File.WriteAllText(sArchivoDocApp, getApp());
File.WriteAllText(sArchivoDocCore, getCore());
File.WriteAllText(sArchivoXlStyles, getXlStyles());
File.WriteAllText(sArchivoXlWorkbook, getXlWorkbook());
File.WriteAllText(sArchivoXlRels, getXlRels());
File.WriteAllText(sArchivoXlTheme, getXlTheme());
for (var i = 0; i < nHojas; i++) File.WriteAllText(sArchivoXlSheets[i], getXlSheet(i));
//Comprimir los archivos en un Xlsx
ZipFile.CreateFromDirectory(sDirectorioRaiz, sArchivoXlsx);
//Borrar todo el directorio con los archivos temporales creados
Directory.Delete(sDirectorioRaiz, true);
}
private static string getContentTypes()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<Types xmlns=\"http://schemas.openxmlformats.org/
package/2006/content-types\">");
sb.Append("<Default Extension=\"rels\" ContentType=
\"application/vnd.openxmlformats-package.relationships+xml\"/>");
//sb.Append("<Default Extension=\"wmf\" ContentType=\"image/x-wmf\"/>");
sb.Append("<Default Extension=\"xml\" ContentType=\"application/xml\"/>");
sb.Append("<Override PartName=\"/xl/workbook.xml\" ContentType=
\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\"/>");
sb.Append("<Override PartName=\"/xl/styles.xml\" ContentType=
\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\"/>");
sb.Append("<Override PartName=\"/xl/theme/theme1.xml\" ContentType=
\"application/vnd.openxmlformats-officedocument.theme+xml\"/>");
sb.Append("<Override PartName=\"/docProps/core.xml\" ContentType=
\"application/vnd.openxmlformats-package.core-properties+xml\"/>");
sb.Append("<Override PartName=\"/docProps/app.xml\" ContentType=
\"application/vnd.openxmlformats-officedocument.extended-properties+xml\"/>");
for (var i = 0; i < nHojas; i++)
{
sb.Append("<Override PartName=\"/xl/worksheets/sheet");
sb.Append(i + 1);
sb.Append(".xml\" ContentType=\"application/vnd.openxmlformats-officedocument.
spreadsheetml.worksheet+xml\"/>");
}
sb.Append("</Types>");
return sb.ToString();
}
private static string getRels()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<Relationships xmlns=\"http://schemas.openxmlformats.org/
package/2006/relationships\">");
sb.Append("<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/
package/2006/relationships/metadata/core-properties\" Target=\"docProps/core.xml\"/>");
//sb.Append("<Relationship Id=\"rId2\" Type=\"http://schemas.openxmlformats.org/
package/2006/relationships/metadata/thumbnail\" Target=\"docProps/thumbnail.wmf\"/>");
sb.Append("<Relationship Id=\"rId2\" Type=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.xml\"/>");
sb.Append("<Relationship Id=\"rId3\" Type=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships/extended-properties\"
Target=\"docProps/app.xml\"/>");
sb.Append("</Relationships>");
return sb.ToString();
}
private static string getApp()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<Properties xmlns=\"http://schemas.openxmlformats.org/
officeDocument/2006/extended-properties\"
xmlns:vt=\"http://schemas.openxmlformats.org/
officeDocument/2006/docPropsVTypes\">");
sb.Append("<Application>Microsoft Access</Application>");
sb.Append("<DocSecurity>0</DocSecurity>");
sb.Append("<ScaleCrop>false</ScaleCrop>");
sb.Append("<HeadingPairs>");
sb.Append("<vt:vector size=\"2\" baseType=\"variant\">");
sb.Append("<vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant>");
sb.Append("<vt:variant><vt:i4>1</vt:i4></vt:variant>");
sb.Append("</vt:vector>");
sb.Append("</HeadingPairs>");
sb.Append("<TitlesOfParts>");
sb.Append("<vt:vector size=\"1\" baseType=\"lpstr\">");
sb.Append("<vt:lpstr>A266FF2A662E84b639DA</vt:lpstr>");
sb.Append("</vt:vector>");
sb.Append("</TitlesOfParts>");
sb.Append("<Company>Microsoft</Company>");
sb.Append("<LinksUpToDate>false</LinksUpToDate>");
sb.Append("<SharedDoc>false</SharedDoc>");
sb.Append("<HyperlinksChanged>false</HyperlinksChanged>");
sb.Append("<AppVersion>12.0000</AppVersion>");
sb.Append("</Properties>");
return sb.ToString();
}
private static string getCore()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<cp:coreProperties ");
sb.Append("xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/
core-properties\" ");
sb.Append("xmlns:dc=\"http://purl.org/dc/elements/1.1/\" ");
sb.Append("xmlns:dcterms=\"http://purl.org/dc/terms/\" ");
sb.Append("xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" ");
sb.Append("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");
sb.Append("<dc:creator>");
sb.Append(Environment.UserName);
sb.Append("</dc:creator>");
sb.Append("<cp:lastModifiedBy>");
sb.Append(Environment.UserName);
sb.Append("</cp:lastModifiedBy>");
sb.Append("<dcterms:created xsi:type=\"dcterms:W3CDTF\">");
sb.Append(DateTime.Now.ToString("s"));
sb.Append("Z</dcterms:created>");
sb.Append("<dcterms:modified xsi:type=\"dcterms:W3CDTF\">");
sb.Append(DateTime.Now.ToString("s"));
sb.Append("Z</dcterms:modified>");
sb.Append("</cp:coreProperties>");
return sb.ToString();
}
private static string getXlStyles()
{
StringBuilder sb = new StringBuilder();
sb.Append("<styleSheet xmlns=\"http://schemas.openxmlformats.org/
spreadsheetml/2006/main\">");
sb.Append("<fonts count=\"1\">");
sb.Append("<font>");
sb.Append("<sz val=\"11\"/>");
sb.Append("<color theme=\"1\"/>");
sb.Append("<name val=\"MS Sans Serif\"/>");
sb.Append("<family val=\"2\"/>");
sb.Append("<scheme val=\"minor\"/>");
sb.Append("</font>");
sb.Append("</fonts>");
sb.Append("<fills count=\"2\">");
sb.Append("<fill><patternFill patternType=\"none\"/></fill>");
sb.Append("<fill><patternFill patternType=\"gray125\"/></fill>");
sb.Append("</fills>");
sb.Append("<borders count=\"1\">");
sb.Append("<border><left/><right/><top/><bottom/><diagonal/></border>");
sb.Append("</borders>");
sb.Append("<cellStyleXfs count=\"1\">");
sb.Append("<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/>");
sb.Append("</cellStyleXfs>");
sb.Append("<cellXfs count=\"1\">");
sb.Append("<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/>");
sb.Append("</cellXfs>");
sb.Append("<cellStyles count=\"1\">");
sb.Append("<cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\"/>");
sb.Append("</cellStyles>");
sb.Append("<dxfs count=\"0\"/>");
sb.Append("<tableStyles count=\"0\" defaultTableStyle=\"TableStyleMedium9\"
defaultPivotStyle=\"PivotStyleLight16\"/>");
sb.Append("</styleSheet>");
return sb.ToString();
}
private static string getXlWorkbook()
{
StringBuilder sb = new StringBuilder();
sb.Append("<workbook xmlns=\"http://schemas.openxmlformats.org/
spreadsheetml/2006/main\" ");
sb.Append("xmlns:r=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships\">");
sb.Append("<fileVersion appName=\"xl\" lastEdited=\"4\" lowestEdited=\"4\"
rupBuild=\"4505\"/>");
sb.Append("<workbookPr defaultThemeVersion=\"124226\"/>");
sb.Append("<bookViews>");
sb.Append("<workbookView xWindow=\"120\" yWindow=\"90\"
windowWidth=\"23895\" windowHeight=\"14535\"/>");
sb.Append("</bookViews>");
sb.Append("<sheets>");
for (var i = 0; i < nHojas; i++)
{
sb.Append("<sheet sheetId=\"");
sb.Append(i + 1);
sb.Append("\" r:id=\"rId");
sb.Append(i + 3);
sb.Append("\" name=\"");
sb.Append(sHojas[i]);
sb.Append("\"/>");
}
sb.Append("</sheets>");
sb.Append("<definedNames>");
for (var i = 0; i < nHojas; i++)
{
sb.Append("<definedName name=\"");
sb.Append(sHojas[i]);
sb.Append("\">'");
sb.Append(sHojas[i]);
sb.Append("'!$A$1:");
sb.Append(sRango[i]);
sb.Append("</definedName>");
}
sb.Append("</definedNames>");
sb.Append("<calcPr calcId=\"125725\" fullCalcOnLoad=\"true\"/>");
sb.Append("</workbook>");
return sb.ToString();
}
private static string getXlRels()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<Relationships xmlns=\"http://schemas.openxmlformats.org/
package/2006/relationships\">");
sb.Append("<Relationship Id=\"rId1\" ");
sb.Append("Type=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships/styles\" ");
sb.Append("Target=\"styles.xml\"/>");
sb.Append("<Relationship Id=\"rId2\" ");
sb.Append("Type=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships/theme\" ");
sb.Append("Target=\"theme/theme1.xml\"/>");
for (var i = 0; i < nHojas; i++)
{
sb.Append("<Relationship Id=\"rId");
sb.Append(i + 3);
sb.Append("\" ");
sb.Append("Type=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships/worksheet\" ");
sb.Append("Target=\"worksheets/sheet");
sb.Append(i + 1);
sb.Append(".xml\"/>");
}
sb.Append("</Relationships>");
return sb.ToString();
}
private static string getXlTheme()
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
sb.Append("<a:theme xmlns:a=\"http://schemas.openxmlformats.org/
drawingml/2006/main\" name=\"Office Theme\">");
sb.Append("<a:themeElements>");
sb.Append("<a:clrScheme name=\"Office\">");
sb.Append("<a:dk1><a:sysClr val=\"windowText\" lastClr=\"000000\"/></a:dk1>");
sb.Append("<a:lt1><a:sysClr val=\"window\" lastClr=\"FFFFFF\"/></a:lt1>");
sb.Append("<a:dk2><a:srgbClr val=\"1F497D\"/></a:dk2>");
sb.Append("<a:lt2><a:srgbClr val=\"EEECE1\"/></a:lt2>");
sb.Append("<a:accent1><a:srgbClr val=\"4F81BD\"/></a:accent1>");
sb.Append("</a:themeElements>");
sb.Append("<a:objectDefaults/>");
sb.Append("<a:extraClrSchemeLst/>");
sb.Append("</a:theme>");
return sb.ToString();
}
private static string getXlSheet(int nHoja)
{
StringBuilder sb = new StringBuilder();
sb.Append("<worksheet xmlns=\"http://schemas.openxmlformats.org/
spreadsheetml/2006/main\" ");
sb.Append("xmlns:r=\"http://schemas.openxmlformats.org/
officeDocument/2006/relationships\">");
sb.Append("<dimension ref=\"A1:");
sb.Append(sRango[nHoja]);
sb.Append("\"/>");
sb.Append("<sheetViews>");
sb.Append("<sheetView tabSelected=\"1\" workbookViewId=\"0\" rightToLeft=\"false\">");
sb.Append("<selection activeCell=\"A1\" sqref=\"A1\"/>");
sb.Append("</sheetView>");
sb.Append("</sheetViews>");
sb.Append("<sheetFormatPr defaultRowHeight=\"15\"/>");
sb.Append("<sheetData>");
if(tipoOrigen.Equals(TipoOrigen.Listas)) sb.Append(getSheetList(nHoja));
else sb.Append(getSheetTable(nHoja));
sb.Append("</sheetData>");
sb.Append("<pageMargins left=\"0.7\" right=\"0.7\" top=\"0.75\"
bottom=\"0.75\" header=\"0.3\" footer=\"0.3\"/>");
sb.Append("</worksheet>");
return sb.ToString();
}
private static string getSheetList(int nHoja)
{
StringBuilder sb = new StringBuilder();
var lista = data[nHoja];
PropertyInfo[] campos = lista[0].GetType().GetProperties();
sb.Append("<row outlineLevel=\"0\" r=\"1\">");
string celda;
string valor;
string tipo;
for (int j = 0; j < campos.Length; j++)
{
celda = String.Format("{0}1", (char)(65 + j));
valor = campos[j].Name;
sb.Append("<c r=\"");
sb.Append(celda);
sb.Append("\" s=\"0\" t=\"inlineStr\"><is><t>");
sb.Append(valor);
sb.Append("</t></is></c>");
}
sb.Append("</row>");
for (int i = 0; i < lista.Count; i++)
{
sb.Append("<row outlineLevel=\"0\" r=\"");
sb.Append(i + 2);
sb.Append("\">");
for (int j = 0; j < campos.Length; j++)
{
celda = String.Format("{0}{1}", (char)(65 + j),i+2);
valor = lista[i].GetType().GetProperty(campos[j].Name).
GetValue(lista[i], null).ToString();
tipo = lista[i].GetType().GetProperty(campos[j].Name).PropertyType.ToString();
sb.Append("<c r=\"");
sb.Append(celda);
sb.Append("\" s=\"0\"");
if (tipo.Contains("String"))
{
sb.Append(" t=\"inlineStr\"><is><t>");
sb.Append(valor);
sb.Append("</t></is>");
}
else
{
sb.Append("><v>");
sb.Append(valor);
sb.Append("</v>");
}
sb.Append("</c>");
}
sb.Append("</row>");
}
return sb.ToString();
}
private static string getSheetTable(int nHoja)
{
StringBuilder sb = new StringBuilder();
var tabla = data[nHoja];
DataColumnCollection campos = tabla.Columns;
sb.Append("<row outlineLevel=\"0\" r=\"1\">");
string celda;
string valor;
string tipo;
for (int j = 0; j < campos.Count ; j++)
{
celda = String.Format("{0}1", (char)(65 + j));
valor = campos[j].ColumnName;
sb.Append("<c r=\"");
sb.Append(celda);
sb.Append("\" s=\"0\" t=\"inlineStr\"><is><t>");
sb.Append(valor);
sb.Append("</t></is></c>");
}
sb.Append("</row>");
for (int i = 0; i < tabla.Rows.Count; i++)
{
sb.Append("<row outlineLevel=\"0\" r=\"");
sb.Append(i + 2);
sb.Append("\">");
for (int j = 0; j < campos.Count; j++)
{
celda = String.Format("{0}{1}", (char)(65 + j), i + 2);
valor = tabla.Rows[i][j].ToString();
tipo = campos[j].DataType.ToString();
sb.Append("<c r=\"");
sb.Append(celda);
sb.Append("\" s=\"0\"");
if (tipo.Contains("String"))
{
sb.Append(" t=\"inlineStr\"><is><t>");
sb.Append(valor);
sb.Append("</t></is>");
}
else
{
sb.Append("><v>");
sb.Append(valor);
sb.Append("</v>");
}
sb.Append("</c>");
}
sb.Append("</row>");
}
return sb.ToString();
}
}
}
7. Explicación Resumida de la Clase Excel
Para cada origen de datos se ha creado un método: "ExportarDeLista" que trabaja con Reflection para extraer los datos de las listas y "ExportarDeTabla" que trabaja con DataTable para extraer los datos de las tablas.
Ambos métodos llaman a una función común y principal llamada "crearDirectoriosArchivos" que primero define las rutas de los directorios y archivos a crear y luego crea los directorios y archivos definidos.
La función "crearDirectoriosArchivos" llama a diferentes funciones una para crear cada archivo XML y para llenar la data de las listas se usa la función "getSheetList" y para llenar la data de las tablas se usa la función "getSheetTable".
Finalmente se comprime los archivos en un solo archivo XLSX y se borra todos los directorios y archivos XML creados temporalmente.
8. Programar el formulario para que use las Clases creadas y Exporte
El último bloque a codificar es el formulario "frmExcel" en el cual debe escribir el siguiente código:
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Diagnostics;
using System.Windows.Forms;
namespace Excel_Crear_XML
{
public partial class frmExcel : Form
{
private beCategoriaProducto obeCatPro;
private DataSet dstCatPro;
private string archivoXlsx;
private string[] hojas;
public frmExcel()
{
InitializeComponent();
}
private void llenarDesdeListas(object sender, EventArgs e)
{
//Simular que se llenan las listas desde la base de datos
obeCatPro = new beCategoriaProducto();
obeCatPro.ListaCategoria = Data.ListaCategorias();
obeCatPro.ListaProducto = Data.ListaProductos();
//Enlazar las listas del objeto principal a las grillas
dgvCategoria.DataSource = obeCatPro.ListaCategoria;
dgvProducto.DataSource = obeCatPro.ListaProducto;
//Habilitar botón de Exportar Listas a Excel y deshabilitar el otro
btnExportarListas.Enabled = true;
btnExportarTablas.Enabled = false;
}
private void llenarDesdeTablas(object sender, EventArgs e)
{
//Simular que se llenan las tablas desde la base de datos
dstCatPro = new DataSet();
dstCatPro.Tables.Add(Data.TablaCategorias());
dstCatPro.Tables.Add(Data.TablaProductos());
//Enlazar las tablas del DataSet a las grillas
dgvCategoria.DataSource = dstCatPro.Tables[0];
dgvProducto.DataSource = dstCatPro.Tables[1];
//Habilitar botón de Exportar Tablas a Excel y deshabilitar el otro
btnExportarListas.Enabled = false;
btnExportarTablas.Enabled = true;
}
private void guardarExcel(Action exportar)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "Selecciona el Directorio e ingresa Nombre del Archivo Excel";
sfd.Filter = "Archivos de Excel 2007|*.xlsx";
if (sfd.ShowDialog().Equals(DialogResult.OK))
{
archivoXlsx = sfd.FileName;
hojas = new string[]{"Categorias", "Productos"};
Stopwatch oCronometro = new Stopwatch();
oCronometro.Start();
exportar();
oCronometro.Stop();
MessageBox.Show(String.Format("Archivo Excel fué creado en {0:n0} msg",
oCronometro.Elapsed.TotalMilliseconds));
}
}
private void exportarDesdeListas()
{
Excel.ExportarDeLista(archivoXlsx, hojas, obeCatPro);
}
private void exportarDesdeTablas()
{
Excel.ExportarDeTabla(archivoXlsx, hojas, dstCatPro);
}
private void exportarListasExcel(object sender, EventArgs e)
{
guardarExcel(exportarDesdeListas);
}
private void exportarTablasExcel(object sender, EventArgs e)
{
guardarExcel(exportarDesdeTablas);
}
}
}
9. Ejecutar y Probar la Aplicación Windows Forms
Grabar la aplicación y pulsar F5 para ejecutarla, se mostrará una ventana similar a la figura:
Ventana de ejecución del formulario "frmExcel"
Luego clic al primer botón "Llenar desde Listas" y se mostrará en las grillas los datos desde las listas de categorías y productos, tal como se muestra en las siguientes figuras:
Ventana con la Ficha de Categorías para Listas
Ventana con la Ficha de Productos para Listas
Se habilitará el botón de "Exportar Listas a Excel" al cual si se da click se mostrará el siguiente diálogo de guardar:
Diálogo de Guardar Excel para Listas
Escribir el nombre del archivo de Excel 2007, por ejemplo "ListasXML.xlsx" y "Guardar" e inmediatamente se mostrará un cuadro de diálogo con el tiempo que demoró la operación.
Continuando con las pruebas click al botón "Llenar desde Tablas" y se mostrará en las grillas los datos desde las tablas de categorías y productos, similar a las presentadas en las figuras de arriba.
Ventana con la Ficha de Categorías para Tablas
Se habilitará el botón de "Exportar Tablas a Excel" al cual si se da click se mostrará el siguiente diálogo de guardar:
Diálogo de Guardar Excel para Tablas
Escribir el nombre del archivo de Excel 2007, por ejemplo "TablasXML.xlsx" y "Guardar" e inmediatamente se mostrará un cuadro de diálogo con el tiempo que demoró la operación.
Finalmente, pueden abrir los 2 archivos de Excel creados que contendrán ambos 2 hojas: Categorías y Productos con los datos de los orígenes.
10. Comentario Final
Personalmente considero este post como uno de mis favoritos ya que no hay información en Internet sobre cómo crear un archivo de Excel 2007 en forma nativa, es decir creando los archivos XML necesarios para luego comprimirlos en un solo archivo XLSX.
Esta técnica es muy rápida, no necesita de instalar nada, y puede crear archivos de Excel con todo tipo de objetos como gráficos, formas, estilos, formatos, etc. Es la mejor forma para exportar datos sobre todo en la Web ya que también se puede hacer por JavaScript nativo como lo veremos en otro post.
Espero que este post ayude a muchas programadores a entender como es la estructura de un archivo Excel y poder crearlo sin necesidad de usar librerías, ya que según mi opinión solo volviendo a "Crear la Rueda" se puede construir una mejor. Los que usen el refrán "Para que Inventar la Rueda" es porque "NO saben como crearla".
Este Blog nació con ese fin, demostrar que con un poco de ingenio y mucha investigación se puede hacer "cualquier cosa". Finalmente, los quiero dejar con otra reflexión: "Fácil es hacer Complejo lo Simple", pero "Difícil es hacer Simple lo Complejo".
Cuando uno aprende hacer lo Complejo Simple estará en condiciones de resolver cualquier problema en corto tiempo y no esperar a que otro lo haga por ti (Frameworks y Librerías) que éste se hace mas fuerte y tu dependiente de él. Además que te pierdes de lo más interesante.
11. Descarga la Aplicación Windows
2016_08_09_Demo_Excel_Crear_XML
