Sugerencia: Lectura de datos de hojas de cálculo directamente a través de XML desde aplicaciones externas

Lo mejor de ambos mundos en la presentación de informes de cuentas

Al llevar las cuentas, a los contadores, por lo general, les gusta gestionar datos dinámicos utilizando hojas de cálculo y producir reportes estáticos con una aplicación diferente. Sin embargo, permitirle al programa de informes estáticos leer directamente de la hoja de cálculos puede ser problemático. Con Gnumeric como hoja de cálculo y PHP como aplicación de informes, este artículo muestra cómo datos de hoja de cálculo almacenados como XML, con el manejo adecuado de espacios de nombre, permiten la lectura de datos directamente de la hoja de cálculo. Se ahorra tiempo, se aumenta la precisión y se evita copiar y pegar, y otros errores similares.

Colin Beckingham, Writer and Researcher, Freelance

Colin Beckingham es investigador independiente, escritor y programador que vive en el este de Ontario, Canadá. Con títulos de las Universidades Queen, Kingston, y la Universidad de Windsor, ha trabajado en una gran variedad de campos incluyendo la banca, horticultura, carreras de caballos, enseñanza, administración pública, comercio minorista y viajes y turismo. Autor de aplicaciones de bases de datos y varios artículos en periódicos, revistas y publicaciones online, sus intereses de investigación incluyen la programación de código abierto, VoIP y aplicaciones controladas por voz en Linux. Puede contactar a Colin escribiendo a colbec@start.ca.



14-01-2013

La dinámica y las estadísticas en la contabilidad

Los programadores financieros y los usuarios encuentran con frecuencia que un formato de hoja de cálculo es bueno para algunos aspectos de datos contables básicos; los cambios se reflejan inmediatamente y es posible explorar escenarios de prueba sin hacer cambios a los registros fundamentales de contabilidad.

Desarrolle habilidades de este tema

Este contenido es parte de un knowledge path progresivo para avanzar en sus habilidades. Vea Compresión de datos y XML

Sin embargo, esto puede presentar un pequeño problema. Los reportes estadísticos como los ingresos y los gastos, balance de comprobación y el balance general pueden ser producidos por una aplicación diferente que necesite acceso a los datos dinámicos para completar el informe. Copiar y pegar de una aplicación a la otra toma tiempo y está sujeto a errores, y transferir la información utilizando valores separados por comas y otras técnicas es pesado como mínimo.

Cuando la hoja de cálculo almacena los datos como XML, un programa de presentación de informes preparado para XML puede leer los datos de manera directa. Este ejemplo utiliza Gnumeric, que almacena los datos en formato XML, como la hoja de cálculo y PHP, el cual puede leer el XML directamente, como una aplicación de presentación de informes.

Ejemplo: Amortizaciones

Un ejemplo típico tiene que ver con las amortizaciones o amortizaciones de costo de capital (CCA). Una máquina con vida útil de muchos años que se utiliza en el proceso de producción se desgasta con el tiempo y al final de su vida útil debe ser reemplazada. Los contadores liquidan un cierto porcentaje del valor amortizado cada año. Los entes recaudadores podrán permitir una cierta deducción de porcentaje máximo en cualquier año, pero depende del negocio decidir qué monto, menos o igual al máximo permitido, le conviene al negocio reclamar durante el período de informe actual.

Una hoja de cálculo dinámica puede desplegar múltiples años, presentando la historia de las deducciones en años previos y aquellos de futuros años. Solver y otras herramientas estadísticas asisten al dueño del negocio para decidir qué deducciones tomar en el año en curso. Cuando se toma esa decisión, el valor necesita ser expuesto en la aplicación de presentación de informes. Hay muchas maneras de hacer esto, algunas más efectivas que otras.

La Ilustración 1 muestra un ejemplo simple. (Vea una versión textual de la Ilustración 1).

Ilustración 1 Una hoja de cálculo de amortización simple o CCA
Screen capture of spreadsheet showing depreciation or CCA example from 2005-2014

Se adquirió una máquina a principios del 2005 por USD 30.000 El ente recaudador permite una amortización máxima del 20% anual en este tipo de máquina. En los primeros dos años, el negocio decidió utilizar el 10%. Ahora, en el 2010 el dueño piensa utilizar el 15%. Cambiar ese valor en la hoja de cálculo (celda C8) actualiza inmediatamente las entradas de los años siguientes en la hoja de cálculo.

Los datos primarios de la hoja de cálculo

Dado que el archivo Gnumeric sin comprimir es puro XML, es posible dar una idea de la estructura de los datos con un editor de texto básico. Note que Gnumeric puede almacenar los datos de la hoja de cálculo en un formato comprimido. Para visualizar los datos de manera directa, asegúrese de que la hoja de cálculo esté almacenada utilizando cero compresión de las preferencias de Gnumeric.

Una forma mejor de visualizar la estructura global XML del archivo Gnumeric es copiarla en un archivo nuevo con extensión .xml y luego abrirlo con navegador preparado para XML. Esto le permite colapsar o expandir los elementos como se necesite.

Muchos de los datos (importantes en un contexto de hoja de cálculo) son superfluos al intercambio de datos simple, por lo cual necesita centrarse en los datos reales.

El listado 1 es un extracto de una hoja de datos tal y como lo guardó Gnumeric.

Listado 1. Un extracto del archivo de datos
<?xml version="1.0" encoding="UTF-8"?>
<gnm:Workbook xmlns:gnm="http://www.gnumeric.org/v10.dtd" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.gnumeric.org/v9.xsd">
  <gnm:Version Epoch="1" Major="9" Minor="13" Full="1.9.13"/>
  <gnm:Attributes ...>
  ...
  <gnm:Sheets>
    ...
    <gnm:Sheet ... >
      ...
      <gnm:Name>Sheet1</gnm:Name>
      ...
      <gnm:Cells>
        <gnm:Cell Row="0" Col="0" ValueType="60">A depreciation example</gnm:Cell>
        ...

El elemento raíz es <gnm:Workbook ... >, donde gnm es un prefijo e indica espacio de nombres. El elemento del Workbook tiene un número de elementos child, de los cuales uno es Sheets. A su vez, este elemento tiene otro child que incluye elementos Sheet , cada una de las cuales contiene datos en los elementos Cell los que a su vez son hijos de un elemento paraguas Cells . Es decir que se tienen que detallar más los cuatro niveles para obtener los datos. En este listado, se puede observar que la celda en la fila cero y columna cero contiene la cadena de datos Ejemplo de depreciación. La fila cero y columna cero corresponde a la celda A1 en la hoja de cálculo. Note también que el nombre de la hoja, Sheet1, está almacenada en su propio elemento child cuyo parent es Sheet.

Extraiga los datos con PHP

Ahora que sabe dónde se almacenan los datos en el documento, debería ser un asunto sencillo extraerlos con la rutina PHP. Sin embargo, encontrará fácilmente un par de problemas que harán que la operación sea no trivial:

  • Namespaces
  • Contenidos de celda calculado

Namespaces

Namespaces hacen bastante para asegurarse de que el XML se ajuste a un esquema esperado pero que añada la complejidad para la extracción de información. Las funciones SimpleXML en PHP fallan a menos que el espacio de nombres no se tome en cuenta. Llamadas a métodos como children() deben pasar argumentos que describan el prefijo del espacio de nombres e indiquen si está siendo utilizado como prefijo. Además, la extracción de los valores de los atributos asociados con elementos con prefijo de espacio de nombres es un poco diferente.

Contenidos de celda calculado

Las celdas en las hojas de cálculo pueden contener valores fijos o expresiones calculadas. Si se le pide al motor de presentación de informes que busque los contenidos de una expresión calculada, se incurre en un trabajo para recalcular el valor en la misma forma en que lo hace la hoja de cálculo. Entonces, de hecho, ahorra esfuerzos si es posible referir únicamente a celdas que contienen valores fijos.

Un ejemplo de función PHP

El Listado 2 es un ejemplo de función PHP.

Listado 2. Extracción de datos
function read_cca_data() {
  $ccafile = "sscca.gnumeric";
  if (!file_exists($ccafile)) {
    die("Problem: CCA file $ccafile not found");
  } else {
    $xml = simplexml_load_file($ccafile);
    foreach ($xml->children('gnm',TRUE) as $child1) {
      if ($child1->getName() == "Sheets") {
        foreach ($child1->children('gnm',TRUE) as $child2) {
          foreach ($child2->children('gnm',TRUE) as $child3) {
            if ($child3->getName() == "Name") {
              if ($child3 != "Sheet1") break 2;
            }
            if ($child3->getName() == "Cells") {
              foreach ($child3->children('gnm',TRUE) as $child4) {
                // child4 is a cell
                $row = (int) $child4->attributes()->Row;
                $col = (int) $child4->attributes()->Col;
                $val = "$child4"; // quotes evaluate to the type, string or numeric
                $val = (is_numeric($val)) ? round($val,3) : $val;
                $ccaarr[$row][$col] = $val;
              }
            }
          }
        }
      }
    }
  }
  return $ccaarr;
}

El código comienza definiendo el nombre del archivo Gnumeric, seguido por una interrupción que sale de PHP si el archivo no se encuentra. Si el archivo está presente se carga en un objeto SimpleXML y desglosa cuatro niveles ($child1, $child2, $child3, $child4) para encontrar los datos en las celdas individuales. En varios niveles verifica que tiene el child correcto al verificar el nombre del elemento o el nombre de la hoja. Cada llamada al método de los children() especifica el espacio de nombres y que se utiliza como prefijo. Al nivel celda, obtiene el valor (el número de fila o de columna) almacenado en los atributos Row y Col (por ejemplo, $child4->attributes()->Row), clasifica el valor en un entero y lo almacena para uso futuro. Si el valor hallado en la celda es numérico, el valor se redondea al tercer lugar decimal. El valor, cadena o numérico, es entonces almacenado en una matriz de dos dimensiones, la cual regresa al llamado de rutina luego de que se leyó la hoja completa. El programa de llamada puede seleccionar y escoger los valores de la matriz como se necesite utilizando los subíndices conocidos.

Gnumeric y PHP trabajando juntos

Volviendo al ejemplo de depreciación, la Ilustración 2 muestra una modificación de la Ilustración 1. (Vea una versión textual de la Ilustración 2).

Ilustración 2 Otra hoja de cálculo de amortización simple o CCA
Screen capture of spreadsheet showing depreciation or CCA example extended

EL nuevo rango F3:H12 es un copiado y pegado especial como los valores del rango B3:D12. Dado que se calculan algunos de los valores en el rango original, el nuevo rango es útil porque los valores son fijos y, por ello, directamente legibles. Es posible automatizar fácilmente la operación copiar/pegar en la hoja de cálculo. De manera alternativa, considerando que los datos primarios están disponibles en la matriz, es posible revisar el recálculo del valor necesario. Los número 5, 6 y 7 en la fila 2 son simplemente un recordatorio del número de columna, que comienza desde cero en la columna A. La columna E se deja intencionalmente en blanco como espaciador —recuerde que sigue siendo registrada en el archivo XML como celda, pero sin valor.

Llamando a la función en el Listado 2 de la aplicación de presentación de informes crea una matriz multidimensional —algunos valores de ejemplo son $ccaarr[2][0]=2005 y $ccaarr[2][6]=0,1.

Conclusión

Como podrá notar, la combinación de Gnumeric y PHP comprende lo mejor de dos mundos. La hoja de cálculo Gnumeric acomoda la información alterable y almacena los datos en XML. La aplicación de presentación de informes PHP puede leer el XML de forma directa, de manera que no se prive de datos precisos.

EL archivo testrun.zip (ver Descarga) contiene el archivo Gnumeric utilizado en este artículo junto con una implementación básica de la función PHP descrita en el Listado 2


Descargar

DescripciónNombretamaño
Sample files for this articletestrun.zip4 KB

Recursos

Aprender

Obtener los productos y tecnologías

Comentar

Comentarios

developerWorks: Ingrese

Los campos obligatorios están marcados con un asterisco (*).


¿Necesita un IBM ID?
¿Olvidó su IBM ID?


¿Olvidó su Password?
Cambie su Password

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


La primera vez que inicie sesión en developerWorks, se creará un perfil para usted. La información en su propio perfil (nombre, país/región y nombre de la empresa) se muestra al público y acompañará a cualquier contenido que publique, a menos que opte por la opción de ocultar el nombre de su empresa. Puede actualizar su cuenta de IBM en cualquier momento.

Toda la información enviada es segura.

Elija su nombre para mostrar



La primera vez que inicia sesión en developerWorks se crea un perfil para usted, teniendo que elegir un nombre para mostrar en el mismo. Este nombre acompañará el contenido que usted publique en developerWorks.

Por favor elija un nombre de 3 - 31 caracteres. Su nombre de usuario debe ser único en la comunidad developerWorks y debe ser distinto a su dirección de email por motivos de privacidad.

Los campos obligatorios están marcados con un asterisco (*).

(Por favor elija un nombre de 3 - 31 caracteres.)

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


Toda la información enviada es segura.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=90
Zone=Information mgmt
ArticleID=854761
ArticleTitle=Sugerencia: Lectura de datos de hojas de cálculo directamente a través de XML desde aplicaciones externas
publish-date=01142013