0005 Excel – PostgreSQL III

Para este artículo se van a usar las tres librerías para el manejo de Excel desde python, estas librerías son: xlrd, xlwt, xlutils.
El siguiente diagrama explica lo que se quiere conseguir con el procedimiento almacenado:

• Hoja de Excel "original.xls", (Crear este libro antes de ejecutar el procedimiento almacenado):
  • Crear la hoja de Excel con los siguientes campos:

	A	B	C	D
1	David	100	0	100
2	Jorge	100	1	99
3	Arturo	100	2	98
4	Sylvia	100	3	97
5	Carolina	100	4	96
6	Graciela	100	5	95
7	Fernando	100	6	94
8	Elena	100	7	93
9	Carlos	100	8	92
10	Daniela	100	9	91

• Crear la tabla "excel" en la base de datos:

 CREATE TABLE excel  
 (  
 campo1 character varying(80),  
 campo2 integer  
 )  
 WITH (  
 OIDS=FALSE  
 );  
 --Sin registros…  

• Procedimiento almacenado:

 CREATE OR REPLACE FUNCTION f_proceso_completo(nombre character varying, copiar character varying, tabla character varying, actualizar smallint)  
   
 RETURNS integer AS  
   
 $BODY$  
   
 #***************************************************************************************  
   
 #Creado por:  David A. Lastra N.  
   
 #Alias:    xdrtas  
   
 #Fecha:    10/05/2010  
   
 #Objetivo:    Leer celdas desde libro de excel, guardar los datos en la tabla y actualizar el libro de excel...  
   
 #***************************************************************************************  
   
   
   
 #Llamar a las librerías que entran en juego para porder realizar el proceso...  
   
 from xlrd import open_workbook  
   
 from xlwt import Formula, XFStyle  
   
 from xlutils.copy import copy  
   
   
   
 #Abrir el libro para leer las celdas...  
   
 lb = open_workbook(nombre)  #lb = leer libro...  Abrir el libro...  
   
 lh = lb.sheet_by_index(0)  #lh = leer hoja...  para leer la primera hoja...  
   
 lc = copy(lb)      #lc = libro copia...  copiar el libro...  
   
 eh = lc.get_sheet(0)    #eh = escribir hoja...  escribir en la primera hoja...  
   
   
   
 #Borrar los datos anteriores de la tabla...  
   
 plpy.execute("DELETE FROM "+tabla+";")  
   
   
   
 #Leer el libro para guardar los datos de las celdas en la tabla "excel"...  
   
 for fila_ID in range(lh.nrows):  
   
 uno = lh.cell(fila_ID,0).value    #El valor de la celda fila_ID, columna 0 o "A"...  
   
 dos = int(lh.cell(fila_ID,2).value)  #El valor de la celda fila_ID, columna 2 o "C"...  
   
 sql = "INSERT INTO %s(campo1, campo2) VALUES ('%s', %d::int);" % (tabla, uno, dos)  
   
 plpy.execute(sql)      #Insertar los valores de las celdas en la tabla "nombre"...  
   
   
   
 #Formato de celda numérico...  
   
 formato = XFStyle()  
   
 formato.num_format_str = '0'  
   
   
   
 #Leer los datos de la tabla y grabar los datos en las celdas correspondientes...  
   
 datos = plpy.execute("SELECT campo1, campo2 FROM "+tabla+";")  
   
 fila = 0  
   
 for campo in datos:          #Escribir en todas las filas de la columna 0 o "A" y columna 2 o "C"...  
   
 eh.write(fila,0,campo['campo1'])    #Escribir el valor de campo1 en la celda "A"...  
   
 eh.write(fila,2,int(campo['campo2']),formato)  #Escribir el valor del campo2 en la celda "C"...  
   
 fila = fila + 1          #Pasar a la siguiente fila...  
   
   
   
 #Guardar en las celdas de la columna 1 o "B" la formula: "=Cn+Dn" donde "n" es el valor que va desde 1 hasta n...  
   
 for i, celda in enumerate(lh.col(1)):          #Escribir en todas las filas de la columna 1 o "B"...  
   
 eh.write(i,1,Formula('C'+str(i+1)+'+D'+str(i+1)),formato)  #Escribir la formula de (Cn+ Dn) en la columna 1 o "B"...  
   
   
   
 #Guardar en las celdas de la columna 3 o "D" los valores de las celdas del libro abierto...  
   
 for i, celda in enumerate(lh.col(3)):    #Escribir en todas las filas de la columna 3 o "D"...  
   
 eh.write(i,3,celda.value,formato)  #Los datos se obtienen de las celdas del libro "lb" hoja "lh"...  
   
   
   
 #Actualizar el libro y crear copia, de lo contrario, sólo crear copia...  
   
 if actualizar == 1:  
   
 lc.save(nombre)  
   
 lc.save(copiar)  
   
 else:  
   
 lc.save(copiar)  
   
   
   
 return 1;  
   
 $BODY$  
   
 LANGUAGE 'plpythonu' VOLATILE  
   
 COST 100;  

1. El procedimiento recibe cuatro valores:
   1. Ruta del archivo a copiar/Actualizar.
   2. Ruta del archivo que será la copia del original.
   3. Nombre de la tabla donde se van a guardar/recuperar los datos.
   4. Valor para indicar si se quiere actualizar el archivo o si sólo se quiere crear una copia.
2. from xlrd import open_workbook: Abrir el archivo para la lectura de las celdas.
   
3. from xlwt import Formula, XFStyle: Preparar la escritura para añadir fórmulas y formatos a las celdas.
   
4. from xlutils.copy import copy: Crear una copia virtual del archivo para actualizar los datos de las celdas.
   
5. lb = open_workbook(nombre): Abrir el libro para leer las celdas.
   
6. lh = lb.sheet_by_index(0): Leer las celdas de la primera hoja.
   
7. lc = copy(lb): Crear una copia del libro abierto para actualizar las celdas.
   
8. eh = lc.get_sheet(0): Obtener acceso a la primera hoja para escribir en las celdas.
   
9. plpy.execute("DELETE FROM "+tabla+";"): Borrar los datos de la tabla "excel".
   
10. for fila_ID in range(lh.nrows):: Iniciar bucle for para cada uno de las filas.
    
11. uno = lh.cell(fila_ID,0).value: Obtener los datos de las celdas de la columna "A".
    
12. dos = int(lh.cell(fila_ID,2).value): Obtener los datos de las celdas de la columna "C".
    
13. sql = "INSERT INTO %s(campo1, campo2) VALUES ('%s', %d::int);" % (tabla, uno, dos): Cadena SQL para insertar los datos en la tabla "excel".
    
14. plpy.execute(sql): Ejecutar la consulta, aquí finaliza los comandos internos de este bucle.
    
15. formato = XFStyle(): Para establecer el formato de las celdas a numérico sin decimales, en este caso, para las celdas de las columnas "B", "C" y "D", ya que "A" contiene caracteres y por eso el formato de ésta columna no se modifica.
    
16. formato.num_format_str = '0': Formato de las celdas numérico sin decimales.
    
17. datos = plpy.execute("SELECT campo1, campo2 FROM "+tabla+";"): Leer los datos de la tabla "excel".
    
18. fila = 0: Para ir moviendo el cursor entre las filas de las columnas.
    
19. for campo in datos:: Bucle para leer los datos obtenidos de la consulta a la tabla "excel".
    
20. eh.write(fila,0,campo['campo1']): Escribir los datos en las celdas de la columna "A".
    
21. eh.write(fila,2,int(campo['campo2']),formato): Escribir los datos en las celdas de la columna "C" con el formato numérico sin decimal.
    
22. fila = fila + 1: Avanzar a la siguiente fila.
    
23. for i, celda in enumerate(lh.col(1)):: Bucle para escribir en cada una de las celdas de la columna "B".
    
24. eh.write(i,1,Formula('C'+str(i+1)+'+D'+str(i+1)),formato): Escribir en cada celda la fórmula "Cn+Dn" con formato numérico sin decimal.
    
25. for i, celda in enumerate(lh.col(3)):: Bucle para escribir en cada una de las celdas de la columna "D".
    
26. eh.write(i,3,celda.value,formato): Escribir en cada celda los valores tomados del libro "original.xls".
    
27. if actualizar == 1:: Si Actualizar es igual a 1, entonces.
    
28. lc.save(nombre): Actualizar el libro "original.xls".
    
29. lc.save(copiar): Crear una copia del libro "original.xls" con el nombre de "copia.xls".
    
30. else:: Si actualizar es distinto de 1, entonces.
    
31. lc.save(copiar): Crear una copia del libro "original.xls" con el nombre de "copia.xls".
    
32. return 1;: Fin del procedimiento.
    

• Ejecutar el procedimiento almacenado:

 SELECT f_proceso_completo('C:/Users/xdrtas/Documents/Documentos/test/original.xls','C:/Users/xdrtas/Documents/Documentos/test/copia.xls','excel',1::smallint);  

• Puntos a destacar en este procedimiento almacenado:
  • El procedimiento está habilitado para la lectura del libro de Excel.
  • El procedimiento está habilitado para la escritura del libro de Excel.
  • Hay tres fuentes de datos:
    • Datos obtenidos por consulta a la tabla "excel", (líneas 20 y 21).
    • Datos escritos directamente, (línea 24), el programador puede quitar la fórmula e introducir los datos que desee.
    • Datos obtenidos desde el libro abierto, (línea26).
  • El procedimiento está habilitado para el cambio de formato de las celdas mediante el módulo XFStyle().

Como se puede observar, en este procedimiento se hacen todas las operaciones disponibles que permiten las librerías xlrd, xlwt, xlutils.

• Las operaciones de lectura de celdas viene dado por la librería xlrd.
• Las operaciones de escritura de celdas viene dado por la librería xlwt.
• Librería xlutils, para habilitar la actualización del libro mediante una copia virtual del libro abierto para lectura.

• Links de artículos anteriores:
  • 0003 excel-postgresql
  • 0004 excel-postgresql II
• Links de las librerías:
  • xlrd-0.7.1.win32 – web: xlrd
  • xlwt-0.7.2.win32 – web: xlwt
  • xlutils-1.4.1.win32 – web: xlutils

Importante: Es muy importante tener los permisos de lectura y escritura en la ruta donde se va a trabajar con los documentos de Excel, si los permisos no están habilitados para el usuario de postgres que se encarga de la ejecución del/los procedimiento/s entonces el/los procedimiento/s van a fallar.

Un cordial saludo.

Firma: XDRTAS

---