• Publicidad

Problemas con XLSX grandes

¿Ya sabes lo que es una referencia? Has progresado, el nível básico es cosa del pasado y ahora estás listo para el siguiente nivel.

Problemas con XLSX grandes

Notapor audax » 2014-01-21 10:16 @469

Hola a todos,

Bueno es mi primera vez que escribo en este foro, que me parece genial. Pido disculpas de antemano si este tema ya estaba contestado, pero busqué harto y no encontré una respuesta satisfactoria.

Vamos al grano. Resulta que necesito transformar una hoja de XLSX en txt, nada del otro mundo. Hasta ahora siempre había ocupado Spreadsheet::XLSX sin problemas, pero en esta oportunidad, el xlsx pesa 45M y ocupando esta librería se me cae el script por memoria.

Ahora bien, cambié la librería y probé con my $excel = Win32::OLE->new('Excel.Application'); (necesito trabajarlo bajo Windows) el cual no ocupa memoria pero se demora horas en transformar a txt.

Entonces, mi pregunta es: ¿habrá algún método u otra librería que sea más rápida en leer un XLSX?
audax
Perlero nuevo
Perlero nuevo
 
Mensajes: 56
Registrado: 2013-06-03 13:16 @594

Publicidad

Re: Problemas con XLSX grandes

Notapor explorer » 2014-01-21 22:11 @966

Bienvenido a los foros de Perl en Español, audax.

Por favor, danos detalles del entorno en el que estás: versión de Windows y de Perl (qué distribución Perl), cantidad de memoria y velocidad del procesador, junto con el número de núcleos.

Dinos también si tienes acceso a una máquina Linux. O instalar una máquina Linux virtual en ese Windows.

También sobre el archivo. Si se va a pasar a txt, entonces hemos de suponer de que se trata de hojas de datos, sin más. Si es así, ¿cabe la posibilidad de pasar de formato el libro Excel, de xlsx a xls?

En última instancia... el formato xlsx es un conjunto de archivos xml comprimidos en un solo archivo zip. Ejemplo:
Sintáxis: [ Descargar ] [ Ocultar ]
  1. explorer@joaquinferrero:~> unzip -l /media/500_1/home/explorer/Documentos/Desarrollo/clinicamym/clinicamym.xlsx 
  2. Archive: /media/500_1/home/explorer/Documentos/Desarrollo/clinicamym/clinicamym.xlsx 
  3.  Length   Date  Time  Name 
  4. --------- ---------- -----  ---- 
  5.    1440 1980-01-01 00:00  [Content_Types].xml 
  6.    588 1980-01-01 00:00  _rels/.rels 
  7.    980 1980-01-01 00:00  xl/_rels/workbook.xml.rels 
  8.    839 1980-01-01 00:00  xl/workbook.xml 
  9.    7646 1980-01-01 00:00  xl/theme/theme1.xml 
  10.    1715 1980-01-01 00:00  xl/worksheets/sheet2.xml 
  11.    2413 1980-01-01 00:00  xl/worksheets/sheet3.xml 
  12.    3853 1980-01-01 00:00  xl/sharedStrings.xml 
  13.    5207 1980-01-01 00:00  xl/styles.xml 
  14.    2000 1980-01-01 00:00  xl/worksheets/sheet1.xml 
  15.    604 1980-01-01 00:00  docProps/core.xml 
  16.    866 1980-01-01 00:00  docProps/app.xml 
  17. ---------           ------- 
  18.   28151           12 files 
Si te fijas en el código del módulo Spreadsheet::XLSX, lo que hace es descomprimir el archivo, y luego crear una estructura Spreadsheet::ParseExcel leyendo directamente el contenido de esos xml (incluso sin usar ningún módulo XML de Perl), por medio de expresiones regulares.

Entonces... si tienes recursos limitados, esa puede ser otra opción: descomprimir y acceder directamente a los datos leyendo el xml de la hoja u hojas que quieras pasar a texto, sin tener que pasar por una estructura Spreadsheet::ParseExcel, que seguro que es la causante del agotamiento de la memoria.
JF^D Perl programming & Raku programming. Grupo en Telegram: https://t.me/Perl_ES
Avatar de Usuario
explorer
Administrador
Administrador
 
Mensajes: 14476
Registrado: 2005-07-24 18:12 @800
Ubicación: Valladolid, España

Re: Problemas con XLSX grandes

Notapor audax » 2014-01-22 10:32 @480

Estimado, no tenía idea de cómo eran los XLSX, me dejaste con la boca abierta cuando hice el ejercicio de descomprimir el XLXS y ver cómo aparecían los xml.

Bueno, viendo mi problema, en la máquina que estoy desarrollando tiene las siguientes características:

- Windows 7 de 32 bit, Perl v5.16.3, 3G de RAM, Procesador Intel Core Duo de 1.8GHz, 2 Núcleos.

Lamentablemente no tengo acceso ni puedo usar Linux (antes lo usaba y no tenía problemas de este tipo), pero tengo que dejar este script en otras máquinas con Windows.

La idea es dejarlo funcionando automático, para que le ingresen un xlsx a futuro y hagan la transformación, en base a eso, por ahora, no es posible pasar de xlsx a xls manualmente.

Lo de descomprimir se ve bastante accesible, lo malo que veo en eso, que los datos de la hoja de datos que necesito quedan en 2 xml, sharedStrings.xml y sheet1.xml, viéndose un poco complejo armar el txt a partir de esas xml, pero como última opción haría eso con Expresiones Regulares que sí que facilitan la vida :D ¡je,je,je!

Si tienes más ideas quedo atento a tus comentarios.
Te agradezco un montón por tu ayuda. Gracias y saludos.
audax
Perlero nuevo
Perlero nuevo
 
Mensajes: 56
Registrado: 2013-06-03 13:16 @594

Re: Problemas con XLSX grandes

Notapor explorer » 2014-01-22 12:29 @562

Pues, sí, el cuello de botella es Windows y esa memoria. Es un poco escasa para ese Windows. Necesitarías 1 GB más, como mínimo.

Prueba a usar SimpleXlsx, que es justo un módulo que hace lo mínimo para abrir el contenido de esos archivos, devolviendo toda la información en una estructura Perl.
JF^D Perl programming & Raku programming. Grupo en Telegram: https://t.me/Perl_ES
Avatar de Usuario
explorer
Administrador
Administrador
 
Mensajes: 14476
Registrado: 2005-07-24 18:12 @800
Ubicación: Valladolid, España

Re: Problemas con XLSX grandes

Notapor audax » 2014-01-23 08:37 @401

Gracias por la respuesta, esa librería no la conocía.

Ahora probando SimpleXlsx me está dando un error que no entiendo:

Not an ARRAY reference at C:/Perl/site/lib/SimpleXlsx.pm line 74.
en la linea my $workbook = $parser->parse($xlsx);

Esto es lo que estoy haciendo, donde $xlsx es el xlsx ingresado como parámetro.

Sintáxis: [ Descargar ] [ Ocultar ]
Using perl Syntax Highlighting
  1. use SimpleXlsx;
  2.  
  3. my $parser   = SimpleXlsx->new();
  4. my $workbook = $parser->parse($xlsx);
  5.  
  6. if ( !defined $workbook ) {
  7.     die $parser->error(), ".\n";
  8. }
  9.  
  10. for my $worksheet ( $workbook->worksheets() ) {
  11.     my ( $row_min, $row_max ) = $worksheet->row_range();
  12.     my ( $col_min, $col_max ) = $worksheet->col_range();
  13.     for my $row ( $row_min .. $row_max ) {
  14.         for my $col ( $col_min .. $col_max ) {
  15.             my $cell = $worksheet->get_cell( $row, $col );
  16.             next unless $cell;
  17.             print "Row, Col    = ($row, $col)\n";
  18.             print "Value       = ", $cell->value(),       "\n";
  19.             print "Unformatted = ", $cell->unformatted(), "\n";
  20.             print "\n";
  21.         }
  22.     }
  23. }
Coloreado en 0.005 segundos, usando GeSHi 1.0.8.4


¿Me puedes dar más detalle de qué puede estar pasando? Gracias y disculpa por molestarte tanto.
audax
Perlero nuevo
Perlero nuevo
 
Mensajes: 56
Registrado: 2013-06-03 13:16 @594

Re: Problemas con XLSX grandes

Notapor explorer » 2014-01-23 09:07 @421

Viendo el código del módulo, y los informes de errores, nos damos cuenta de que el problema está en el propio módulo: al usar el módulo XML::Simple para leer el contenido de los xlsx, algunas veces transforma los datos en un array y otras veces en un hash.

Por eso sale un error dentro del código del módulo: trata a $xfonts como una referencia a un array, pero en realidad es una referencia a un hash.

No hay una solución sencilla. El primer informe de errores ya da una idea de cómo hay que parchear el código.

Me temo que te tocará lidiar con los xml de forma directa (o con alguno de los muchos módulos XML en CPAN).

O mirar el código de Spreadsheet::XLSX, para que veas cómo lo lee.
JF^D Perl programming & Raku programming. Grupo en Telegram: https://t.me/Perl_ES
Avatar de Usuario
explorer
Administrador
Administrador
 
Mensajes: 14476
Registrado: 2005-07-24 18:12 @800
Ubicación: Valladolid, España

Re: Problemas con XLSX grandes

Notapor explorer » 2014-01-23 14:45 @656

Buenas noticias.

He conseguido hacer un parche que arregla el módulo SimpleXlsx. Se lo he comunicado al autor, y ya veremos si lo acepta o no.

De momento, esto es lo que tienes que hacer.

Abres el archivo SimpleXlsx.pm y haces estos cambios:
Sintáxis: [ Descargar ] [ Ocultar ]
Using text Syntax Highlighting
43c43
<   my($tstrings) = $xml->XMLin($sstrings);
---
>   my($tstrings) = $xml->XMLin($sstrings, ForceArray => ['si']);
62,63c62,63
<   $data = $xml->XMLin($data);
<  
---
>   $data = $xml->XMLin($data, ForceArray => ['xf','font','border']);
>
145c145
<  
---
>
156,157c156,157
<     my($data) = $xml->XMLin($contents);
<    
---
>     my($data) = $xml->XMLin($contents, ForceArray => ['row','mergeCell','c']);
Coloreado en 0.000 segundos, usando GeSHi 1.0.8.4
Los números indican los números de línea. Ves que lo que hay que hacer es meter un argumento más a los XMLin(). Eso obliga a XML::Simple a pasar todos los atributos que le indicamos a forma de array, y no hash.

Luego, el programa que habías escrito era para una estructura Spreadsheet::ParseExcel, pero la estructura que devuelve SimpleXlsx es diferente: es solo una referencia a un hash, que contiene toda la información.

Ejemplo de programa:
Sintáxis: [ Descargar ] [ Ocultar ]
Using perl Syntax Highlighting
  1. #!/usr/bin/perl
  2. use v5.16;
  3. use utf8::all;          # por si hay celdas con codificación utf8
  4. use SimpleXlsx;
  5.  
  6. my $xlsx     = 'clinicamym.xlsx';
  7. my $parser   = SimpleXlsx->new();
  8. my $worksheets_ref = $parser->parse($xlsx);
  9.  
  10. #use Data::Dumper;
  11. #say Dumper $worksheets_ref;            # vuelca la estructura
  12.  
  13. # Nombres de las hojas
  14. say "Hojas: ", join " ", @{ $worksheets_ref->{'Worksheets'} };
  15.  
  16. # Acceso a una de las hojas
  17. my $HOJA = 'sheet1';
  18.  
  19. say "Filas: ", join " ", @{ $worksheets_ref->{$HOJA}->{'Rows'} };
  20.  
  21. say "Columnas: ", join " ", @{ $worksheets_ref->{$HOJA}->{'Columns'} };
  22.  
  23. say "Datos: ", join " ", keys %{ $worksheets_ref->{$HOJA}->{'Data'} };
  24.  
  25. # Acceso a una celda
  26. my $fila = 1;
  27. my $columna = 2;
  28.  
  29. say "Celda ($columna, $fila): ", $worksheets_ref->{$HOJA}->{'Data'}->{$fila}->{'Data'}->[$columna-1];
  30.  
  31. # Otra forma, más simple
  32. my $hoja1 = $worksheets_ref->{$HOJA}->{'Data'};
  33. say $hoja1->{$fila}->{'Data'}->[$columna-1];
Coloreado en 0.002 segundos, usando GeSHi 1.0.8.4

La estructura del hash es así (también la puedes sacar si descomentas las líneas Dumper):
Sintáxis: [ Descargar ] [ Ocultar ]
Using text Syntax Highlighting
$VAR1 = {
    'Total Worksheets' => 3,
    'Worksheets'       => [ 'sheet2', 'sheet3', 'sheet1' ],
    'sheet1'           => {
        'Merge'   => {},
        'Columns' => [ 'A1', 'B1', 'C1' ],
        'Rows'    => [ '1', '2', '3', '4' ],
        'Data'    => {
            '4' => {
                'Style' => [
                    {
                        'Font' => {
                            'Size' => '9',
                            'Bold' => '0',
                            'Name' => 'Verdana'
                        },
                        'fillId' => '0',
                        'xfId'   => '0',
                        'Border' => $VAR1->{'sheet2'}{'Data'}{'1'}{'Style'}[0]{'Border'},
                        'numFmtId' => '0'
                    },
                    $VAR1->{'sheet1'}{'Data'}{'4'}{'Style'}[0]
                ],
                'Data' => [
                    'Curso en Brasil',
                    'En estos dias se estan impartiendo cursos en la Universidad de Brasil'
                ]
            },
        }
    },
};
 
Coloreado en 0.000 segundos, usando GeSHi 1.0.8.4
JF^D Perl programming & Raku programming. Grupo en Telegram: https://t.me/Perl_ES
Avatar de Usuario
explorer
Administrador
Administrador
 
Mensajes: 14476
Registrado: 2005-07-24 18:12 @800
Ubicación: Valladolid, España

Re: Problemas con XLSX grandes

Notapor audax » 2014-01-27 09:50 @451

¿¿¿Modificaste la librería!!??? ¡Uff!, eso sí no lo esperaba, ¡je,je,je! Andaba de vacaciones y estoy recién retomando el tema. Voy a aplicar los cambios y cuento cómo me fue.

Gracias, amigo, por tu dedicación, sigo agradeciéndote eso.

Saludos
audax
Perlero nuevo
Perlero nuevo
 
Mensajes: 56
Registrado: 2013-06-03 13:16 @594

Re: Problemas con XLSX grandes

Notapor audax » 2014-02-12 09:13 @426

Estimado, aquí ando retomando el tema luego de haberlo dejado en espera por unas semanas por otras prioridades...

Bueno, hice lo que me aconsejaste y ¡¡no da el error!!, pero me consume memoria exageradamente hasta que se cae (error: Out of memory!) en la línea:

my $worksheets_ref = $parser->parse($xlsx);

¿Habría algún método que pudiera hacer un SaveAs pasándole el nombre de la hoja y que la guarde directamente como texto tabulado o algo parecido sin necesidad de pasar celda por celda?

Gracias por todo amigo.
Saludos
audax
Perlero nuevo
Perlero nuevo
 
Mensajes: 56
Registrado: 2013-06-03 13:16 @594

Re: Problemas con XLSX grandes

Notapor explorer » 2014-02-12 11:16 @511

Todos los módulos que conozco relativos a Excel, pasan por la fase de lectura de la hoja y de su interpretación para ser convertido a estructura interna de Perl, así que no te libras del problema. Bueno, trabajando en Linux hay una pequeña ventaja, porque la gestión de memoria es mejor, y además de contar con la memoria física, cuentas con la memoria virtual.

Pero... si el problema es que hoja es tremendamente grande, no yo veo más solución que la comentada antes: acceder al archivo XLSX de forma directa.

En ese caso, también tienes limitaciones en el momento de la interpretación de archivos XML. Por fortuna, hay módulos Perl en CPAN que permiten hacer la interpretación del archivo a medida de que se va leyendo (ahora mismo no recuerdo sus nombres).

O más fácil, leer el código de Spreadsheet::XLSX para que veas cómo lo lee él.
JF^D Perl programming & Raku programming. Grupo en Telegram: https://t.me/Perl_ES
Avatar de Usuario
explorer
Administrador
Administrador
 
Mensajes: 14476
Registrado: 2005-07-24 18:12 @800
Ubicación: Valladolid, España

Siguiente

Volver a Intermedio

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 1 invitado

cron