make.tol de Xlsx.Reader

Librería que permite construir lectores de ficheros Excel Xlx, de extensión xlsx, de Office de Microsoft. El objetivo de este fichero make.tol es sólo probar las diferentes funcionalidades de esta librería que se implementan en el fichero xlx.tol y que puede ser incluida desde otros programas Tol. Los actuales ficheros Xlsx de Microsoft Excel son ficheros comprimidos y se pueden abrir con WinZip o 7Zip. En especial, este código, usa 7Zip, desde la línea de mandatos, para extraer la hoja de datos requerida. Las hojas dentro de este fichero comprimido se numeran como 1, 2, 3,... independiéntemente de cual sea el nombre de la hoja.

Xls.Reader utiliza un mandato 7Xip para extraer el contenido, por ejemplo, para extraer la hoja 1 el mandato es: 7z.exe e excel.xlsx xl\worksheets\sheet1.xml -so > _temporal_.xml. Este mandato extrae la primera hoja (sheet1.xml) del fichero llamado excel.xlsx y la guarda por volcado de la salida estándar con el nombre de fichero _temporal_.xml. La hoja que se extrae de esta forma es un fichero Xml con los tags <sheetData>, <row> y <v> (value) que determinan: a) el primero <sheetData> todos los datos del fichero Excel, b) el segundo <row> cada una de las filas, que son un conjunto horizontal de celdas, y c) el tercero <v> cada uno de los valores de cada celda. Por lo que este fichero Xml puede convertirse en un conjunto de conjunto de textos, esto es, en una tabla Tol.

El código de esta librería Xls.Reader, por razones de simplicidad, no realiza especiales comprobaciones: a) ni de mayúsculas ni minúsculas en las etiquetas Xml, b) ni de celdas Excel combinadas, c) omite las celdas vacías que no se guarden, por lo que el resultado puede no ser perféctamente rectangular, d) tampoco extrae fórmulas, ni formatos y e) todos los campos los retorna en formato de texto, asi, por ejemplo, las fechas se retornan como un número (identificador de la fecha) en formato de texto y, f) dependiendo de la variable de control XlxCpm, retorna todos los textos compactados o no, de forma independiente a las directrices internas de Excel-Xml del tipo xml:space = preserve.

A partir de las funciones de la librería Xls.Reader se pueden programar lectores de de Excel Xlx más avanzados en lenguaje Tol, pudiendo ser una buena base de partida. Esta libraría utiliza un único fichero temporal, que usa y cuando deja de ser útil no se borra pues su contenido puede facilitar la comprobación de los resultados. Por esta razón no puede emplearse en extracciones en paralelo, para ello, habría que generar nombres aleatorios de ficheros y borrarlos después. El funcionamiento de este librería ha sido probado con las versiones de Tol 1.1.1, 1.1.5, 1.1.6 y 2.0.1.

Dentro de esta librería Xls.Reader hay 2 tipos de funciones: a) la que retorna directamente los contenidos de las celdas y b) la que si el contenido de la celda no es un valor sino es el índice a la tabla de shared strings realiza la búsqueda en esa tabla y retorna los contenidos reales. Para ocupar menos espacio en Excel Xlx los datos de tipo texto, en vez de guardarse junto con el resto, se guardan en un fichero externo, de forma que si un texto aparece en más de una celda todas lo comparten. De esta forma en la celda de Excel hay números, fechas,... pero cuando se trata de un texto lo que hay es un apuntador a la posición a una lista de textos compartidos (shared strings). Para entender el uso de la tabla de sharedStrings en Excel Xlsx puede consultarse el sitio web: http://www.sadev.co.za/content en reading-and-writing-excel-2007-or-excel-2010-c-series-index. La lectura de un Excel con shared strings es, naturalmente debido a este doble acceso, más lenta que si no los tiene.

Inclusiones

• Set allInc
  Inclusion de las funciones comunes.

Pruebas

• Real timIni
  Para controlar tiempos.
• Real basTst
  Leer un Excel Xlsx sencillo, con celdas vacias, lo que implica que el resultado no es rectangular.
• Real intTst
  Pruebas de integridad de ficheros Excel en extension y contenido.
• Real tru001
  Leer de un Excel Xlsx una hoja interna con mas datos que los visibles.
• Real gmeTst
  Leer de un Excel Xlsx con shared strings.
• Real tru002
  Leer uno de los Excel Xlsx anteriores con shared strings.

//////////////////////////////////////////////////////////////////////////////
Set  allInc = Include("tol/inc.tol");
//////////////////////////////////////////////////////////////////////////////
PutDescription("Inclusion de las funciones comunes.", allInc);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real timIni = Copy(Time);
//////////////////////////////////////////////////////////////////////////////
PutDescription("Para controlar tiempos.", timIni);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real basTst = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn(XlxFromLblToEnd("0000sheetData1 2 3 4", "sheetData"));
  Text WriteLn(XlxFromTagToEnd("0000<sheetData>1 2 3", "sheetData"));
  Set  View(XlxSplitByTag("a<v>1</v>b<v>2</v>c<v>3</v>d", "v"), "");

  Text WriteLn("\nExecution\n");
  Real exe001 = Xlx7ZipExtract("dat/test01.xlsx", "1", "dat/test01.xml");

  Text WriteLn("\nReading as text\n");
  Text txtXml = XlxReadText("dat/test01.xlsx", "1");

  Text WriteLn("\nReading as table\n");
  Set  setTab = XlxReadTable("dat/test01.xlsx", "1");
  Set  View(setTab, "");

  TRUE
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer un Excel Xlsx sencillo, con celdas vacias, lo que implica que el
resultado no es rectangular.",
basTst);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real intTst = If(FALSE, // Poner true para realizar este test
{
  Set  View(SetOfReal
  (
    Xlx7ZipTest(   "dat/test01.xlsx"),         // Debe ser cierto
    XlxIsExcelXlsx("dat/test01.xlsx"),         // Debe ser cierto
    XlxIsExcelXlsx("dat/test01.xml"),          // Debe ser falso
    XlxIsExcelXlsx("dat/corrupto.xlsx")        // Debe ser falso
  ), "");

  TRUE
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Pruebas de integridad de ficheros Excel en extension y contenido.",
intTst);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real tru001 = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading as table\n");
  Set  setTab = XlxReadTable("dat/tru.input.xlsx",
                             "xl/externalLinks/externalLink1.xml");
  Set  setExt = Extract(setTab, 2, 3, 6, 7, 9, 15, 18, 24);
  Set  setSel = Select(setExt, Real(Set rowSet)
  { Text venIni = Sub(rowSet[3],1,3); venIni <: [["KOC","SEG","THQ"]] });
  
  Text filNam = "dat/tru.output.csv";
  Text filHea = "PRODUCTO;CODIGO;VENDOR;EAN;COSTE;VENTA;ACUMULADO;STOCK\n";
  Text WriteFile(filNam,filHea);
  Set  wriCic = EvalSet(setSel, Real(Set rowSet)
  {
    Text linTxt = Replace(rowSet[1],"&amp;","&") + ";" + rowSet[2] + ";" +
                  Replace(rowSet[3],"&amp;","&") + ";" + rowSet[4] + ";" +
                  Replace(rowSet[5],".",    ",") + ";" + rowSet[6] + ";" +
                          rowSet[7]              + ";" + rowSet[8] + "\n";
    Text AppendFile(filNam, linTxt);
    TRUE
  });
  SetSum(wriCic)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer de un Excel Xlsx una hoja interna con mas datos que los visibles.",
tru001);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real gmeTst = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading with shared strings\n");

  Set  setTab = XlxReadTable("dat/gmeW16.xlsx", "1"); // Muchos son shared
  Set  View(setTab, "");

  Set  shaStr = XlxReadShared("dat/gmeW16.xlsx"); // Leer la tabla de shared
  Set  View(shaStr, "");

  Set  xlsTab = XlxReadTableShared("dat/gmeW16.xlsx", "1"); // Contenido real
  Set  View(xlsTab, ""); // Los valores directos y los shared localizados

  Card(xlsTab)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription("Leer de un Excel Xlsx con shared strings.", gmeTst);
//////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
Real tru002 = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading with shared strings\n");
  Set  xlsTab = XlxReadTableShared("dat/tru.input.xlsx", "1");
  Set  View(xlsTab, "");

  Card(xlsTab)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer uno de los Excel Xlsx anteriores con shared strings.",
tru002);
//////////////////////////////////////////////////////////////////////////////

Text WriteLn("\nXls.Reader make: end ["+
             FormatReal((Time-timIni)/60,"%.2lf")+" m].");

//////////////////////////////////////////////////////////////////////////////
// FILE    : make.tol
// AUTHOR  : http://www.asolver.com
// CLASS   : Excel; 7Zip
// VERSION : Tol 1.1.1; Tol 1.1.5; Tol 1.1.6; Tol 2.0.1
// PURPOSE : Librería que permite construir lectores de ficheros Excel Xlx,
// de extensión xlsx, de Office de Microsoft.
// 
// El objetivo de este fichero make.tol es sólo probar las diferentes
// funcionalidades de esta librería que se implementan en el fichero xlx.tol
// y que puede ser incluida desde otros programas Tol.
// 
// Los actuales ficheros Xlsx de Microsoft Excel son ficheros comprimidos y
// se pueden abrir con WinZip o 7Zip.
// 
// En especial, este código, usa 7Zip, desde la línea de mandatos, para
// extraer la hoja de datos requerida. Las hojas dentro de este fichero 
// comprimido se numeran como 1, 2, 3,... independiéntemente de cual sea el
// nombre de la hoja.
// _
// Xls.Reader utiliza un mandato 7Xip para extraer el contenido, por ejemplo,
// para extraer la hoja 1 el mandato es:
// 7z.exe e excel.xlsx xl\worksheets\sheet1.xml -so > _temporal_.xml.
// 
// Este mandato extrae la primera hoja (sheet1.xml) del fichero llamado
// excel.xlsx y la guarda por volcado de la salida estándar con el nombre
// de fichero _temporal_.xml.
// 
// La hoja que se extrae de esta forma es un fichero Xml con los tags
// <sheetData>, <row> y <v> (value) que determinan:
// a) el primero <sheetData> todos los datos del fichero Excel,
// b) el segundo <row> cada una de las filas, que son un conjunto horizontal
//    de celdas, y
// c) el tercero <v> cada uno de los valores de cada celda.
// 
// Por lo que este fichero Xml puede convertirse en un conjunto de conjunto
// de textos, esto es, en una tabla Tol.
// _
// El código de esta librería Xls.Reader, por razones de simplicidad,
// no realiza especiales comprobaciones:
// a) ni de mayúsculas ni minúsculas en las etiquetas Xml,
// b) ni de celdas Excel combinadas,
// c) omite las celdas vacías que no se guarden, por lo que el resultado puede
//    no ser perféctamente rectangular,
// d) tampoco extrae fórmulas, ni formatos y
// e) todos los campos los retorna en formato de texto, asi, por ejemplo,
//    las fechas se retornan como un número (identificador de la fecha) en
//    formato de texto y,
// f) dependiendo de la variable de control XlxCpm, retorna todos los textos
//    compactados o no, de forma independiente a las directrices internas de
//    Excel-Xml del tipo xml:space = preserve.
// _
// A partir de las funciones de la librería Xls.Reader se pueden programar
// lectores de de Excel Xlx más avanzados en lenguaje Tol, pudiendo ser una
// buena base de partida.
// 
// Esta libraría utiliza un único fichero temporal, que usa y cuando deja de
// ser útil no se borra pues su contenido puede facilitar la comprobación de
// los resultados. Por esta razón no puede emplearse en extracciones en
// paralelo, para ello, habría que generar nombres aleatorios de ficheros y
// borrarlos después.
// 
// El funcionamiento de este librería ha sido probado con las versiones de Tol
// 1.1.1, 1.1.5, 1.1.6 y 2.0.1.
// _
// Dentro de esta librería Xls.Reader hay 2 tipos de funciones:
// a) la que retorna directamente los contenidos de las celdas y
// b) la que si el contenido de la celda no es un valor sino es el índice a la
//    tabla de shared strings realiza la búsqueda en esa tabla y retorna los
//    contenidos reales.
// 
// Para ocupar menos espacio en Excel Xlx los datos de tipo texto, en vez de
// guardarse junto con el resto, se guardan en un fichero externo, de forma
// que si un texto aparece en más de una celda todas lo comparten. De esta
// forma en la celda de Excel hay números, fechas,... pero cuando se trata de
// un texto lo que hay es un apuntador a la posición a una lista de textos
// compartidos (shared strings).
// 
// Para entender el uso de la tabla de sharedStrings en Excel Xlsx puede
// consultarse el sitio web: http://www.sadev.co.za/content
// en reading-and-writing-excel-2007-or-excel-2010-c-series-index.
// 
// La lectura de un Excel con shared strings es, naturalmente debido a este 
// doble acceso, más lenta que si no los tiene.
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
// INCLUDE
//////////////////////////////////////////////////////////////////////////////
Text WriteLn("\nXls.Reader make: begin");

//////////////////////////////////////////////////////////////////////////////
Set  allInc = Include("tol/inc.tol");
//////////////////////////////////////////////////////////////////////////////
PutDescription("Inclusion de las funciones comunes.", allInc);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
// TEST
//////////////////////////////////////////////////////////////////////////////
Text WriteLn("\nXlx.Reader make: test");

//////////////////////////////////////////////////////////////////////////////
Real timIni = Copy(Time);
//////////////////////////////////////////////////////////////////////////////
PutDescription("Para controlar tiempos.", timIni);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
Real basTst = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn(XlxFromLblToEnd("0000sheetData1 2 3 4", "sheetData"));
  Text WriteLn(XlxFromTagToEnd("0000<sheetData>1 2 3", "sheetData"));
  Set  View(XlxSplitByTag("a<v>1</v>b<v>2</v>c<v>3</v>d", "v"), "");

  Text WriteLn("\nExecution\n");
  Real exe001 = Xlx7ZipExtract("dat/test01.xlsx", "1", "dat/test01.xml");

  Text WriteLn("\nReading as text\n");
  Text txtXml = XlxReadText("dat/test01.xlsx", "1");

  Text WriteLn("\nReading as table\n");
  Set  setTab = XlxReadTable("dat/test01.xlsx", "1");
  Set  View(setTab, "");

  TRUE
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer un Excel Xlsx sencillo, con celdas vacias, lo que implica que el
resultado no es rectangular.",
basTst);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
Real intTst = If(FALSE, // Poner true para realizar este test
{
  Set  View(SetOfReal
  (
    Xlx7ZipTest(   "dat/test01.xlsx"),         // Debe ser cierto
    XlxIsExcelXlsx("dat/test01.xlsx"),         // Debe ser cierto
    XlxIsExcelXlsx("dat/test01.xml"),          // Debe ser falso
    XlxIsExcelXlsx("dat/corrupto.xlsx")        // Debe ser falso
  ), "");

  TRUE
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Pruebas de integridad de ficheros Excel en extension y contenido.",
intTst);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
Real tru001 = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading as table\n");
  Set  setTab = XlxReadTable("dat/tru.input.xlsx",
                             "xl/externalLinks/externalLink1.xml");
  Set  setExt = Extract(setTab, 2, 3, 6, 7, 9, 15, 18, 24);
  Set  setSel = Select(setExt, Real(Set rowSet)
  { Text venIni = Sub(rowSet[3],1,3); venIni <: [["KOC","SEG","THQ"]] });
  
  Text filNam = "dat/tru.output.csv";
  Text filHea = "PRODUCTO;CODIGO;VENDOR;EAN;COSTE;VENTA;ACUMULADO;STOCK\n";
  Text WriteFile(filNam,filHea);
  Set  wriCic = EvalSet(setSel, Real(Set rowSet)
  {
    Text linTxt = Replace(rowSet[1],"&amp;","&") + ";" + rowSet[2] + ";" +
                  Replace(rowSet[3],"&amp;","&") + ";" + rowSet[4] + ";" +
                  Replace(rowSet[5],".",    ",") + ";" + rowSet[6] + ";" +
                          rowSet[7]              + ";" + rowSet[8] + "\n";
    Text AppendFile(filNam, linTxt);
    TRUE
  });
  SetSum(wriCic)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer de un Excel Xlsx una hoja interna con mas datos que los visibles.",
tru001);
//////////////////////////////////////////////////////////////////////////////



//////////////////////////////////////////////////////////////////////////////
Real gmeTst = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading with shared strings\n");

  Set  setTab = XlxReadTable("dat/gmeW16.xlsx", "1"); // Muchos son shared
  Set  View(setTab, "");

  Set  shaStr = XlxReadShared("dat/gmeW16.xlsx"); // Leer la tabla de shared
  Set  View(shaStr, "");

  Set  xlsTab = XlxReadTableShared("dat/gmeW16.xlsx", "1"); // Contenido real
  Set  View(xlsTab, ""); // Los valores directos y los shared localizados

  Card(xlsTab)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription("Leer de un Excel Xlsx con shared strings.", gmeTst);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
Real tru002 = If(FALSE, // Poner true para realizar este test
{
  Text WriteLn("\nReading with shared strings\n");
  Set  xlsTab = XlxReadTableShared("dat/tru.input.xlsx", "1");
  Set  View(xlsTab, "");

  Card(xlsTab)
},FALSE);
//////////////////////////////////////////////////////////////////////////////
PutDescription(
"Leer uno de los Excel Xlsx anteriores con shared strings.",
tru002);
//////////////////////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////
// END
//////////////////////////////////////////////////////////////////////////////
Text WriteLn("\nXls.Reader make: end ["+
             FormatReal((Time-timIni)/60,"%.2lf")+" m].");