Editing Spreadsheets from gvSIG Scripting for reporting

Following up on last week´s topic about Automate-reporting-in-gvsig-using-scripting, we are going to explain how can we make the same but using a spreadsheet file template (ODS).

In the previous post, we explained how to install jOpendocument library and how to download all the needed material for running the script.

For this example we are going to use the file located at C:\gvsig_informes\plantilla_invoice.ods.  There is no need for having any layer loaded in gvSIG.

If we use Linux or load the file in another place of our disk, we only should change the script path which makes reference to the spreadsheet template: pathTemplate. There are two, one input file plantilla_invoice.ods, and another one for the output file that we can save whenever we want.

The following script uses the jOpendocument library to modify the ODS file, assigning to the cells the required values. These values can be extracted from the information of our layers, as we explained in the previous post.

The template is based on the one used in the documentation of jOpendocument. This script is based on the example about modify an existent spreadsheet, but there is more, for example, create a new spreadsheet.

When the script is executed correctly, LibreOffice will be opened with the created file. If is like this case, that the spreadsheet includes formules (additions, multiplications,…) we need to press in LibreOffice Ctrl+Shift+F9 to refresh and update all the spreadsheet calculations. We will see how some cells will change.

The result would be a file similar to:

2015-10-05 15_57_38-fillingTest1.ods - LibreOffice Calc

The code is the following, only you need to make sure that the paths are correct:

from gvsig import *

from java.io import File
from java.util import Date

from org.jopendocument.model import OpenDocument
from org.jopendocument.dom.spreadsheet import SpreadSheet
from org.jopendocument.dom import OOUtils

def main(*args):
    #Editing example of ODS files with jOpenDocument
    
    #based on http://www.jopendocument.org/start_spreadsheet_2.html
    #template files http://www.jopendocument.org/downloads.html

    pathTemplate = r"C:/gvsig_informes/plantilla_invoice.ods"
    pathOutput = r"C:/gvsig_informes/resultado/fillingTest1.ods"

    #Access to spreadsheet and sheet number
    file = File(pathTemplate)
    sheet = SpreadSheet.createFromFile(file).getSheet(0)

    #Set actual date in I10 cell
    sheet.getCellAt("I10").setValue(Date())

    #Title
    sheet.getCellAt("B21").setValue("Pedido n. 01")
    
    #Modify the value of the cell 1,1. It would be B2
    sheet.setValueAt("Presupuesto parcela n. 1", 1, 1)

    #Some modifications to the cells
    sheet.getCellAt("F24").setValue(123)
    
    sheet.getCellAt("B27").setValue("Personal")
    sheet.getCellAt("H27").setValue(2)
    sheet.getSpreadSheet().getTableModel("Products").setValueAt(10, 5, 4) #F27

    #Save file
    outputFile = File(pathOutput)
    OOUtils.open(sheet.getSpreadSheet().saveAs(outputFile))

We hope that it would be helpful for reporting and to show the potential of gvSIG Scripting for using external libraries.

Any question about it, you can comment in here or in the lists.

This entry was posted in community, development, english, gvSIG Desktop, gvSIG development, scripting. Bookmark the permalink.

One Response to Editing Spreadsheets from gvSIG Scripting for reporting

  1. Pingback: Editing Spreadsheets from gvSIG Scripting for reporting | GeoNe.ws

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s