Exportar a Excel 2007 desde Listas y Tablas usando OLEDB
1. Requerimiento
- Se desea exportar a Excel 2007 varias hojas desde tablas en un DataSet.
- Se desea exportar a Excel 2007 varias hojas desde listas de objetos de un objeto contenedor.
2. Técnicas para Crear un Archivo de Excel
Existen muchas técnicas pero vamos a describir brevemente las 3 principales:
2.1 Automatización ActiveX o COM
Consiste en abrir una instancia de Excel desde otra aplicación (en este caso en .NET) y llenar las celdas de cada hoja una por una, luego grabar el archivo y cerrar la instancia de Excel abierta.
Hay 2 inconvenientes: el primero es que tenemos que tener instalado Excel donde se va a ejecutar la aplicación y si es web sería en el servidor, el segundo es que si es una aplicación web hay muchos problemas desde dar permisos al COM de Excel para que se ejecute hasta cerrar la instancia que siempre se queda abierta.
Esta técnica es la peor, pero quizás la más popular (por ser la más fácil), si es una aplicación Windows no hay muchos problemas por que cada aplicación corre en diferentes máquinas, pero si es web, les aconsejaría que cambien de técnica (jamás se debería usar en ASP.NET).
2.2. Usando OLEDB
Esta técnica consiste en crear un archivo de texto separados por comas o algún caracter delimitador y luego usando OLEDB ejecutar un comando que convierta el archivo de texto creado previamente en un archivo de Excel 2007.
También tenemos 2 grandes inconvenientes: el primero es que hay que tener instalado el "Microsoft Access Database Engine 2010 Redistributable", el segundo es que se presentarán problemas de codificación (Encoding), formatos y estilos, por ejemplo códigos que son cadenas con ceros a la izquierda que se convierten en numéricos (sin ceros a la izquierda), ajuste de decimales, formato de fechas y horas, campos memos, colores de fondo, colores de texto, tamaños, etc.
En este post veremos esta técnica sin considerar los problemas que se pueden presentar por el tipo de datos a exportar, el cual lo veremos en otro post que será de mucha ayuda para cientos de programadores que pasan por estos problemas.
2.3. Usado Archivos
Esta técnica es la más optima y natural, consiste en crear archivos XML con los formatos establecidos por Open XML y luego comprimirlos en un archivo de Excel 2007 (xlsx).
Si el programador conoce la especificación y formato de cada archivo xml necesario y sabe manejo de archivos para crearlos y comprimirlos, entonces lo más recomendable es que lo haga el mismo, sino, puede usar una librería de terceros que lo haga por él, tales como: SpreedSheet (DevExpress), NPOI, EPPlus, etc.
En otros post enseñaré como crear archivos de Excel simples (datos sin formato) y complejos (con imágenes, formatos, etc).
3. Crear una Aplicación Windows Forms en C#
Abrir Visual Studio y crear una aplicación Windows Forms en C# llamada "Excel_Crear_OLEDB", 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_OLEDB
{
public class beCategoria
{
public int IdCategoria { get; set; }
public string Nombre { get; set; }
}
}
Archivo de Clase: beProducto.cs
namespace Excel_Crear_OLEDB
{
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_OLEDB
{
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_OLEDB
{
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 de Texto
La tercera parte a codificar es crear una clase que permita crear archivos de texto desde Listas de Objetos o desde Tablas para lo cual usamos IO (FileStream y StreamWriter) y Reflection (PropertyInfo). Adicionalmente para las listas se usa los tipos Genéricos (<T>).
Archivo de Clase: Texto.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
namespace Excel_Crear_OLEDB
{
public class Texto
{
public static void ExportarDeTabla(DataTable tabla, string archivo, char separador)
{
using (FileStream fs = new FileStream(archivo, FileMode.Create,
FileAccess.Write, FileShare.Write))
{
using (StreamWriter sw = new StreamWriter(fs, Encoding.Default))
{
for (int i = 0; i < tabla.Columns.Count; i++)
{
sw.Write(tabla.Columns[i].ColumnName);
if (i < tabla.Columns.Count - 1) sw.Write(separador);
}
sw.WriteLine();
for (int j = 0; j < tabla.Rows.Count; j++)
{
for (int i = 0; i < tabla.Columns.Count; i++)
{
sw.Write(tabla.Rows[j][i].ToString());
if (i < tabla.Columns.Count - 1) sw.Write(separador);
}
sw.WriteLine();
}
}
}
}
public static void ExportarDeLista<T>(List<T> lista, string archivo, char separador)
{
using (FileStream fs = new FileStream(archivo, FileMode.Create,
FileAccess.Write, FileShare.Write))
{
using (StreamWriter sw = new StreamWriter(fs, Encoding.Default))
{
PropertyInfo[] propiedades = lista[0].GetType().GetProperties();
for (int i = 0; i < propiedades.Length; i++)
{
sw.Write(propiedades[i].Name);
if (i < propiedades.Length - 1) sw.Write(separador);
}
sw.WriteLine();
for (int j = 0; j < lista.Count; j++)
{
propiedades = lista[j].GetType().GetProperties();
for (int i = 0; i < propiedades.Length; i++)
{
sw.Write(propiedades[i].GetValue(lista[j], null).ToString());
if (i < propiedades.Length - 1) sw.Write(separador);
}
sw.WriteLine();
}
}
}
}
}
}
6. Crear una Clase que permita Crear Archivos de Excel 2007
La cuarta parte a codificar y quizás la mas importante, es crear una clase que use OLEDB para crear archivos de Excel 2007 a partir del archivo de texto previamente creado.
Archivo de Clase: Excel.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
namespace Excel_Crear_OLEDB
{
public class Excel
{
public static void ExportarDeLista<T>(List<T> lista, string archivoXlsx, string hoja)
{
string ruta = Path.GetDirectoryName(archivoXlsx);
string archivoTxt = String.Format("{0}\\{1}.txt", ruta, hoja);
Texto.ExportarDeLista<T>(lista, archivoTxt, ',');
crearExcel2007(archivoXlsx, hoja, true);
File.Delete(archivoTxt);
}
public static void ExportarDeTabla(DataTable tabla, string archivoXlsx, string hoja)
{
string ruta = Path.GetDirectoryName(archivoXlsx);
string archivoTxt = String.Format("{0}\\{1}.txt", ruta, hoja);
Texto.ExportarDeTabla(tabla, archivoTxt, ',');
crearExcel2007(archivoXlsx, hoja, true);
File.Delete(archivoTxt);
}
static void crearExcel2007(string archivoXlsx, string hoja, bool eliminar)
{
string ruta = Path.GetDirectoryName(archivoXlsx);
using (OleDbConnection con = new OleDbConnection
("provider=Microsoft.Ace.oledb.12.0; data source=" + ruta + ";
extended properties=Text;"))
{
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * Into " + hoja +
" In ''[Excel 12.0 xml;Database=" + archivoXlsx + "]From " + hoja + "#TXT", con);
cmd.ExecuteNonQuery();
}
}
static void crearExcel2003(string archivoXls, string hoja, bool eliminar)
{
string ruta = Path.GetDirectoryName(archivoXls);
using (OleDbConnection con = new OleDbConnection
("provider=Microsoft.Jet.oledb.4.0;data source=" + ruta + ";
extended properties=Text;"))
{
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * Into " + hoja +
" In ''[Excel 8.0;Database=" + archivoXls + "]From " + hoja + "#TXT", con);
cmd.ExecuteNonQuery();
}
}
}
}
Nota: Solo se está usando la función crearExcel2007 que genera archivos xlsx, pero he incluido adicionalmente la función crearExcel2003 que genera archivos xls (por si alguno desea crear el formato antiguo).
7. 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_OLEDB
{
public partial class frmExcel : Form
{
private beCategoriaProducto obeCatPro;
private DataSet dstCatPro;
private string archivoXlsx;
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;
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()
{
if (File.Exists(archivoXlsx)) File.Delete(archivoXlsx);
Excel.ExportarDeLista(obeCatPro.ListaCategoria, archivoXlsx, "Categorias");
Excel.ExportarDeLista(obeCatPro.ListaProducto, archivoXlsx, "Productos");
}
private void exportarDesdeTablas()
{
if (File.Exists(archivoXlsx)) File.Delete(archivoXlsx);
Excel.ExportarDeTabla(dstCatPro.Tables[0], archivoXlsx, "Categorias");
Excel.ExportarDeTabla(dstCatPro.Tables[1], archivoXlsx, "Productos");
}
private void exportarListasExcel(object sender, EventArgs e)
{
guardarExcel(exportarDesdeListas);
}
private void exportarTablasExcel(object sender, EventArgs e)
{
guardarExcel(exportarDesdeTablas);
}
}
}
Nota: Se usa la clase Stopwatch para medir el tiempo que demora en procesar cada técnica, el cual será similar ya que la demora en ambas es la primera vez que se establece conexión con Oledb.
8. 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 "Listas.xlsx" y "Guardar" e inmediatamente se mostrará un cuadro de diálogo con el tiempo que demoró la operación.
Nota: Como ya lo mencioné antes, la primera vez que se realiza una conexión con un origen de datos demora mas del doble, el resto de veces es más rápido, para cualquiera de los 2 orígenes de datos.
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 "Tablas.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.
9. Comentario Final
En este post hemos explicado detalladamente una de las técnicas que hay para crear un archivo XLSX de Excel 2007 (también hemos incluido la función para crear archivos XLS de Excel 2003) usando archivos de texto y OLEDB.
Como ya lo mencioné para datos simples como textos y números no hay muchos inconvenientes pero si queremos formatos en números, fechas y hora, usar campos memo, etc. se presentarán complicaciones que se resuelven usando un archivo de configuración (Schema.ini) el cual será motivo de otro post.
Pero otra desventaja de esta técnica es que si queremos incluir gráficos, imágenes u otros objetos, no es posible usando OLEDB, es solo para datos simples (No objetos), para lo cual mejor usamos la técnica de archivos XML comprimidos en un XLSX.
Finalmente, creo que este post les será de mucha utilidad a muchas personas que diariamente exportan a Excel listas de registros desde DataTables algunos y otros desde Listas de Objetos usando una instancia de Excel, lo cual no debe hacerse y aunque sea hay que reemplazarla por esta técnica.
10. Descarga la Aplicación Windows
