gambas2: TableView a hoja de cálculo (ODS)

He creado un pequeño código que exporta la data contenida en un TableView o GridView  a una hoja de cálculo, fichero ODS de la fundación Open Document.

Este pequeño modulo es muy simple y se limita solo a pasar la data pero sin conservar el formato de fuente.

Aquí está la parte importante del código, pero también les dejo las fuentes para su facilidad. Cualquier duda aquí andamos :)

' Gambas module file


PRIVATE pathOpendoc AS String = "/tmp/opendocgambas/" ' Ruta temporal donde se crearan los ficheros que componen el ODS


' Funcion que creará los ficheros que componen el ODS, y los empaquetará con el nombre de la variable pathODS
PUBLIC FUNCTION saveODS(controlx AS Object, pathODS AS String)

   

   DIM writer AS XmlWriter ' Creará los ficheros XML necesarios
   DIM filex AS File             ' Creará los ficheros de texto plano necesarios
   DIM iCount AS Integer     ' Índice para las filas del TableView
   DIM jCount AS Integer     ' Índice para las columnas del TableView
   

   ' Ejecutar función solo para GridViews o TableViews
   IF Object.Type(controlx) = "GridView" OR IF Object.Type(controlx) = "TableView" THEN 

   TRY MKDIR pathOpendoc                                             ' Crear directorio temporal
   TRY MKDIR pathOpendoc &/ "Configurations2"   ' Crear otros directorios para el ODS
   TRY MKDIR pathOpendoc &/ "META-INF"

   TRY MKDIR pathOpendoc &/ "Thumbnails"

   ' Crear fichero mimetype
   filex = OPEN pathOpendoc &/ "mimetype" FOR INPUT CREATE 

       PRINT #filex, "application/vnd.oasis.opendocument.spreadsheet"; ' El ";" es para no insertar una terminación de línea
   CLOSE #filex

   ' Crear fichero manifest.xml
   writer = NEW XmlWriter

   WITH writer

   .Open(pathOpendoc &/ "META-INF/manifest.xml", TRUE, "UTF-8")

   .StartElement("manifest:manifest", ["xmlns:manifest", "urn:oasis:names:tc:opendocument:xmlns:manifest:1.0"])

       .StartElement("manifest:file-entry", ["manifest:media-type", "application/vnd.oasis.opendocument.spreadsheet", "manifest:version", "1.2", "manifest:full-path", "/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/statusbar/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/accelerator/current.xml"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/accelerator/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/floater/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/popupmenu/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/progressbar/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/menubar/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/toolbar/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/images/Bitmaps/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Configurations2/images/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", "application/vnd.sun.xml.ui.configuration", "manifest:full-path", "Configurations2/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", "text/xml", "manifest:full-path", "content.xml"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", "text/xml", "manifest:full-path", "styles.xml"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", "text/xml", "manifest:full-path", "meta.xml"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Thumbnails/thumbnail.png"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", " ", "manifest:full-path", "Thumbnails/"])

       .EndElement()

       .StartElement("manifest:file-entry", ["manifest:media-type", "text/xml", "manifest:full-path", "settings.xml"])

       .EndElement()

   .EndElement

   .EndDocument

   END WITH 

   

   ' Crear fichero content.xml. En este fichero se encuentra la data del TableView
   writer = NEW XmlWriter

   writer.Open(pathOpendoc &/ "content.xml", TRUE, "UTF-8")

   writer.StartElement("office:document-content")

     writer.Attribute("xmlns:office", "urn:oasis:names:tc:opendocument:xmlns:office:1.0")

     writer.Attribute("xmlns:style", "urn:oasis:names:tc:opendocument:xmlns:style:1.0")

     writer.Attribute("xmlns:text", "urn:oasis:names:tc:opendocument:xmlns:text:1.0")

     writer.Attribute("xmlns:table", "urn:oasis:names:tc:opendocument:xmlns:table:1.0")

     writer.Attribute("xmlns:number", "urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0")

     writer.Attribute("xmlns:chart", "urn:oasis:names:tc:opendocument:xmlns:chart:1.0")

     writer.Attribute("xmlns:form", "urn:oasis:names:tc:opendocument:xmlns:form:1.0")

     writer.Attribute("xmlns:oooc", "http://openoffice.org/2004/calc")

     writer.Attribute("xmlns:field", "urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0")

     writer.Attribute("xmlns:formx", "urn:openoffice:names:experimental:ooxml-odf-interop:xmlns:form:1.0")

     writer.Attribute("office:version", "1.2")

     

     writer.StartElement("office:body")

         writer.StartElement("office:spreadsheet")

         writer.StartElement("table:table", ["table:name", CStr(controlx.Name), "table:print", "false"])

         ' Si existen headers, también vaciarlos a la hoja de cálculo
         IF controlx.Header = GridView.Horizontal OR IF controlx.Header = GridView.Both THEN 

             writer.StartElement("table:table-row")

             FOR jCount = 0 TO controlx.Columns.Count - 1

                 writer.StartElement("table:table-cell", ["office:value-type", "string"])

                 writer.Element("text:p", controlx.Columns[jCount].Text)

                 writer.EndElement()

             NEXT 

             writer.EndElement()

         ENDIF 

         ' Vaciar la info de las celdas del TableView a la hoja de cálculo
         FOR iCount = 0 TO controlx.Rows.Count - 1 ' Recorremos las filas
             writer.StartElement("table:table-row")

             FOR jCount = 0 TO controlx.Columns.Count - 1   ' Recorremos las columnas
                 writer.StartElement("table:table-cell")

                 ' Indentificar si el dato es una string o un dato numerico
                 IF Str$(Val(controlx[iCount, jCount].Text)) = controlx[iCount, jCount].Text THEN 

                     writer.Attribute("office:value-type", "float")

                     writer.Attribute("office:value", controlx[iCount, jCount].Text)

                 ELSE 

                     writer.Attribute("office:value-type", "string")

                 ENDIF 

                 writer.Element("text:p", controlx[iCount, jCount].Text)

                 writer.EndElement()

             NEXT 

             writer.EndElement()

         NEXT 

         writer.EndElement()

     writer.EndElement()

   writer.EndElement()

   writer.EndDocument()

   

   ' Creamos un script bash para empaquetar los ficheros y directorios del ODS
   filex = OPEN pathOpendoc &/ "pckods" FOR INPUT CREATE 

       PRINT #filex, "#!/bin/bash"

       PRINT #filex, "# Script creado con gambas, comprime ficheros para crear un documento ODS"

       PRINT #filex, "cd $(dirname $0)"

       PRINT #filex, "zip -r $1 Configurations2 META-INF Thumbnails content.xml mimetype"

   CLOSE #filex

   EXEC ["chmod", "+x", pathOpendoc &/ "pckods"] WAIT

   EXEC [pathOpendoc &/ "pckods", pathODS] WAIT ' Ejecutamos el script
   ELSE 

       ERROR "El control no es un GridView o TableView"

   ENDIF 

   

END



' aztk
' Tenochtitlan. Febrero del 2011
' CopyLeft