Example 5.1. Exporting data to a spreadsheet

# After the connection is established a new spreadsheet document
# is created
spreadsheet = Office.createSpreadsheet()

# Create a new table using the standard table procedure
table 'openoffice_test1':
    a   b   c   d   e
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'

# Now the table is available in the database, so we can import
# it into OpenOffice

# At first write the header...
spreadsheet.setCellValue(0, 0, 'a')
spreadsheet.setCellValue(1, 0, 'b')
spreadsheet.setCellValue(2, 0, 'c')
spreadsheet.setCellValue(3, 0, 'd')
spreadsheet.setCellValue(4, 0, 'e')

# ...and next write the data
trans None <- 'openoffice_test1':
	$spreadsheet.setCellValue(0, __rowNumber__+1, a)
	$spreadsheet.setCellValue(1, __rowNumber__+1, b)
	$spreadsheet.setCellValue(2, __rowNumber__+1, c)
	$spreadsheet.setCellValue(3, __rowNumber__+1, d)
	$spreadsheet.setCellValue(4, __rowNumber__+1, e)

# The task is done, but this is a very slow method and the user has
# to know column names
# There also is an optimized and fast method of reading a table into
# openspreadsheet.

# Add a new sheet to our spreadsheet document
sheet1 = spreadsheet.newSheet("FastImport")

# Read the table...
data = tableRead('openoffice_test1')
spreadsheet.setCellValue(0,0, data)
    

The above script opens a spreadsheet file with two spreadsheets, each of which containins the data from the openoffice_test1 table.

Figure 5.2. Spreadsheet with data copied from a database table

Example 5.2. Creating charts in a spreadsheet

spreadsheet = Office.createSpreadsheet()

# Prepare some data to write to the active sheet
values = [ ['Value', 'Count'],
           ['5', 300],
           ['6', 102],
           ['7', 105],
           ['8', 15],
           ['9', 5],
           ['10', 45]
         ]
# Writes the prepared data array to the sheet. Begining at the cell 0,0 (A1)
spreadsheet.setCellValue(0,0, values)

# Insert the chart
chart = spreadsheet.addChart("unique_name")

# Set where the source data is
chart.setDataRanges( collection( [ spreadsheet.getCellRangeByPosition(0,0, 1, 6) ] ) )
# the data range can consist of separate ranges, so it is necessary to use collection([ range1, range2, ...]) syntax

# The position and size of the chart is defined by another cell range
chart.setPositionAndSize( spreadsheet.getCellRangeByPosition( 0, 7, 6, 14) )

# The first row is a header
chart.setFirstColumnHeader( 1 )

# The first column contains data labels, so should be treated
# as a header
chart.setFirstColumnHeader( 1 )
    

The spreadsheet created by this script is shown in the figure below.

Figure 5.3. Spreadsheet with data copied from a database table

Example 5.3. Creating a pivot table

# After the connection is established a new spreadsheet document
# is created
spreadsheet = Office.createSpreadsheet()

# Create sample data
table 'openoffice_test1':
    a   b   c   d   e
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'
    1   1   2   3   'ABC'
    2   2   1   3   'ABC'
    4   4   2   1   'DEF'

# Export the data into a spreadsheet
data = tableRead('openoffice_test1')

# Write data array into the spreadsheet
spreadsheet.setCellDataArray(0, 0, data)

# Some properties of the data pilot have to be set before it can
# be added to the spreadsheet
dpp = DataPilotProperties()

# Add column indexes which should be treated as columns
dpp.addColumn(1)
dpp.addColumn(2)
dpp.addColumn(3)

# Add a row index
dpp.addRow(0)

# Indicate which column should be used as the data for the
# data pilot
dpp.addData(4)

# Specify the source data range...
sourceCellRange = spreadsheet.getCurrentSheet().getCellRangeByPosition(0,0, 4, 9)

# ... and the destination of the inserted data pilot
destinationCell = spreadsheet.getCurrentSheet().getCellByPosition(0, 11)

# Insert the prepared data pilot
spreadsheet.insertDataPilot(dpp, sourceCellRange, destinationCell)
    

The spreadsheet created by this script is shown in the figure below.

Figure 5.4. Spreadsheet with a pivot table

Example 5.4. Using the predefined styles

spreadsheet = Office.createSpreadsheet()

# Insert a value into cell and set a predefined style
spreadsheet.setCellValue(1,1, "Some text", spreadsheet.header1Style)
    

Example 5.5. Creating custom styles

spreadsheet = Office.createSpreadsheet()

# Create an empty array for styles
oStyle = []

# Use Office.createStyle to provide a style object.
oStyle.append(Office.createStyle(OStyle.CELL_BACK_COLOR, OStyle.COLOR_RED))
oStyle.append(Office.createStyle(OStyle.FONT_SIZE, 15))
oStyle.append(Office.createStyle(OStyle.FONT_WEIGHT, 150.0))
oStyle.append(Office.createStyle(OStyle.FONT_COLOR, OStyle.COLOR_WHITE))

#Now apply the prepared style to cell value
spreadsheet.setCellValue(1,1, "Some text", oStyle)
spreadsheet.setOptimalColumnWidth(1)