it.eng.spagobi.engines.qbe.crosstable.exporter
Class CrosstabXLSExporter

java.lang.Object
  extended by it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter
Direct Known Subclasses:
CrosstabXLSXExporter

public class CrosstabXLSExporter
extends java.lang.Object

Exports the crosstab data (formatted as a JSON object in input) into a XLS file. The JSON object should have this structure (a node is {node_key:"Text", node_childs:[...]}): columns: {...} contains tree node structure of the columns' headers rows: {...} contains tree node structure of the rows' headers data: [[...], [...], ...] 2-dimensional matrix containing crosstab data

Author:
Alberto Ghedin (alberto.ghedin@eng.it), Davide Zerbetto (davide.zerbetto@eng.it)

Field Summary
static int DEFAULT_CALCULATED_FIELD_DECIMALS
           
static java.lang.String DEFAULT_CELL_BACKGROUND_COLOR
           
static java.lang.String DEFAULT_CELL_BORDER_COLOR
           
static java.lang.String DEFAULT_CELL_COLOR
           
static short DEFAULT_CELL_FONT_SIZE
           
static java.lang.String DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR
           
static java.lang.String DEFAULT_DIMENSION_NAME_COLOR
           
static java.lang.String DEFAULT_FONT_NAME
           
static java.lang.String DEFAULT_HEADER_BACKGROUND_COLOR
           
static java.lang.String DEFAULT_HEADER_BORDER_COLOR
           
static java.lang.String DEFAULT_HEADER_COLOR
           
static short DEFAULT_HEADER_FONT_SIZE
           
static org.apache.log4j.Logger logger
          Logger component.
static java.lang.String PROPERTY_CALCULATED_FIELD_DECIMALS
           
static java.lang.String PROPERTY_CELL_BACKGROUND_COLOR
           
static java.lang.String PROPERTY_CELL_BORDER_COLOR
           
static java.lang.String PROPERTY_CELL_COLOR
           
static java.lang.String PROPERTY_CELL_FONT_SIZE
           
static java.lang.String PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR
           
static java.lang.String PROPERTY_DIMENSION_NAME_COLOR
           
static java.lang.String PROPERTY_FONT_NAME
           
static java.lang.String PROPERTY_HEADER_BACKGROUND_COLOR
           
static java.lang.String PROPERTY_HEADER_BORDER_COLOR
           
static java.lang.String PROPERTY_HEADER_COLOR
           
static java.lang.String PROPERTY_HEADER_FONT_SIZE
          Configuration properties
 
Constructor Summary
CrosstabXLSExporter(java.util.Properties properties)
           
 
Method Summary
protected  void buildColumnsHeader(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, java.util.List<Node> siblings, int rowNum, int columnNum, org.apache.poi.ss.usermodel.CreationHelper createHelper, java.util.Locale locale, org.apache.poi.ss.usermodel.CellStyle memberCellStyle, org.apache.poi.ss.usermodel.CellStyle dimensionCellStyle)
          Builds the columns' headers recursively with this order: |------------------------------------------| | 1 | 9 | |------------------------------------------| | 2 | 5 | 10 | |-----------|-----------------|------------| | 3 | 4 | 6 | 7 | 8 | 11 | 12 | |------------------------------------------|
 org.apache.poi.ss.usermodel.CellStyle buildDataCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)
           
protected  int buildDataMatrix(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, int rowOffset, int columnOffset, org.apache.poi.ss.usermodel.CreationHelper createHelper, MeasureFormatter measureFormatter)
           
 org.apache.poi.ss.usermodel.CellStyle buildDimensionCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)
           
 org.apache.poi.ss.usermodel.CellStyle buildHeaderCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)
           
 org.apache.poi.ss.usermodel.CellStyle buildNACellStyle(org.apache.poi.ss.usermodel.Sheet sheet)
           
protected  void buildRowHeaderTitle(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, int columnHeadersNumber, int startColumn, int startRow, org.apache.poi.ss.usermodel.CreationHelper createHelper, java.util.Locale locale, org.apache.poi.ss.usermodel.CellStyle cellStyle)
          Add the title of the columns in the row headers
protected  void buildRowsHeaders(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, java.util.List<Node> siblings, int rowNum, int columnNum, org.apache.poi.ss.usermodel.CreationHelper createHelper, java.util.Locale locale, org.apache.poi.ss.usermodel.CellStyle cellStyle)
          Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----| | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | | 7 | | | 6 |-----| | | | 8 | |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|
 int commonFillSheet(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, org.json.JSONObject crosstabJSON, org.apache.poi.ss.usermodel.CreationHelper createHelper, int startRow, java.util.Locale locale)
           
 int fillAlreadyCreatedSheet(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs, org.json.JSONObject crosstabJSON, org.apache.poi.ss.usermodel.CreationHelper createHelper, int startRow, java.util.Locale locale)
           
 int getCalculatedFieldDecimals()
           
protected  int getCellTypeNumeric()
           
protected  int getCellTypeString()
           
 org.apache.poi.ss.usermodel.CellStyle getNumberFormat(int j, java.util.Map<java.lang.Integer,org.apache.poi.ss.usermodel.CellStyle> decimalFormats, org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.usermodel.CreationHelper createHelper, CrossTab.CellType celltype)
           
 java.lang.Object getProperty(java.lang.String propertyName)
           
 int initSheet(org.apache.poi.ss.usermodel.Sheet sheet, CrossTab cs)
          Sheet initialization.
 void setProperty(java.lang.String propertyName, java.lang.Object propertyValue)
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

logger

public static transient org.apache.log4j.Logger logger
Logger component.


PROPERTY_HEADER_FONT_SIZE

public static final java.lang.String PROPERTY_HEADER_FONT_SIZE
Configuration properties

See Also:
Constant Field Values

PROPERTY_HEADER_COLOR

public static final java.lang.String PROPERTY_HEADER_COLOR
See Also:
Constant Field Values

PROPERTY_HEADER_BACKGROUND_COLOR

public static final java.lang.String PROPERTY_HEADER_BACKGROUND_COLOR
See Also:
Constant Field Values

PROPERTY_HEADER_BORDER_COLOR

public static final java.lang.String PROPERTY_HEADER_BORDER_COLOR
See Also:
Constant Field Values

PROPERTY_CELL_FONT_SIZE

public static final java.lang.String PROPERTY_CELL_FONT_SIZE
See Also:
Constant Field Values

PROPERTY_CELL_COLOR

public static final java.lang.String PROPERTY_CELL_COLOR
See Also:
Constant Field Values

PROPERTY_CELL_BACKGROUND_COLOR

public static final java.lang.String PROPERTY_CELL_BACKGROUND_COLOR
See Also:
Constant Field Values

PROPERTY_CELL_BORDER_COLOR

public static final java.lang.String PROPERTY_CELL_BORDER_COLOR
See Also:
Constant Field Values

PROPERTY_DIMENSION_NAME_COLOR

public static final java.lang.String PROPERTY_DIMENSION_NAME_COLOR
See Also:
Constant Field Values

PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR

public static final java.lang.String PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR
See Also:
Constant Field Values

PROPERTY_FONT_NAME

public static final java.lang.String PROPERTY_FONT_NAME
See Also:
Constant Field Values

PROPERTY_CALCULATED_FIELD_DECIMALS

public static final java.lang.String PROPERTY_CALCULATED_FIELD_DECIMALS
See Also:
Constant Field Values

DEFAULT_HEADER_FONT_SIZE

public static final short DEFAULT_HEADER_FONT_SIZE
See Also:
Constant Field Values

DEFAULT_HEADER_COLOR

public static final java.lang.String DEFAULT_HEADER_COLOR
See Also:
Constant Field Values

DEFAULT_HEADER_BACKGROUND_COLOR

public static final java.lang.String DEFAULT_HEADER_BACKGROUND_COLOR
See Also:
Constant Field Values

DEFAULT_HEADER_BORDER_COLOR

public static final java.lang.String DEFAULT_HEADER_BORDER_COLOR
See Also:
Constant Field Values

DEFAULT_CELL_FONT_SIZE

public static final short DEFAULT_CELL_FONT_SIZE
See Also:
Constant Field Values

DEFAULT_CELL_COLOR

public static final java.lang.String DEFAULT_CELL_COLOR
See Also:
Constant Field Values

DEFAULT_CELL_BACKGROUND_COLOR

public static final java.lang.String DEFAULT_CELL_BACKGROUND_COLOR
See Also:
Constant Field Values

DEFAULT_CELL_BORDER_COLOR

public static final java.lang.String DEFAULT_CELL_BORDER_COLOR
See Also:
Constant Field Values

DEFAULT_DIMENSION_NAME_COLOR

public static final java.lang.String DEFAULT_DIMENSION_NAME_COLOR
See Also:
Constant Field Values

DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR

public static final java.lang.String DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR
See Also:
Constant Field Values

DEFAULT_FONT_NAME

public static final java.lang.String DEFAULT_FONT_NAME
See Also:
Constant Field Values

DEFAULT_CALCULATED_FIELD_DECIMALS

public static final int DEFAULT_CALCULATED_FIELD_DECIMALS
See Also:
Constant Field Values
Constructor Detail

CrosstabXLSExporter

public CrosstabXLSExporter(java.util.Properties properties)
Method Detail

setProperty

public void setProperty(java.lang.String propertyName,
                        java.lang.Object propertyValue)

getProperty

public java.lang.Object getProperty(java.lang.String propertyName)

fillAlreadyCreatedSheet

public int fillAlreadyCreatedSheet(org.apache.poi.ss.usermodel.Sheet sheet,
                                   CrossTab cs,
                                   org.json.JSONObject crosstabJSON,
                                   org.apache.poi.ss.usermodel.CreationHelper createHelper,
                                   int startRow,
                                   java.util.Locale locale)
                            throws org.json.JSONException,
                                   it.eng.qbe.serializer.SerializationException
Throws:
org.json.JSONException
it.eng.qbe.serializer.SerializationException

commonFillSheet

public int commonFillSheet(org.apache.poi.ss.usermodel.Sheet sheet,
                           CrossTab cs,
                           org.json.JSONObject crosstabJSON,
                           org.apache.poi.ss.usermodel.CreationHelper createHelper,
                           int startRow,
                           java.util.Locale locale)
                    throws it.eng.qbe.serializer.SerializationException,
                           org.json.JSONException
Throws:
it.eng.qbe.serializer.SerializationException
org.json.JSONException

initSheet

public int initSheet(org.apache.poi.ss.usermodel.Sheet sheet,
                     CrossTab cs)
              throws org.json.JSONException
Sheet initialization. We create as many rows as it is required to contain the crosstab.

Parameters:
sheet - The XLS sheet
json - The crosstab data (it must have been enriched with the calculateDescendants method)
Throws:
org.json.JSONException

buildDataMatrix

protected int buildDataMatrix(org.apache.poi.ss.usermodel.Sheet sheet,
                              CrossTab cs,
                              int rowOffset,
                              int columnOffset,
                              org.apache.poi.ss.usermodel.CreationHelper createHelper,
                              MeasureFormatter measureFormatter)
                       throws org.json.JSONException
Throws:
org.json.JSONException

getCellTypeNumeric

protected int getCellTypeNumeric()

getCellTypeString

protected int getCellTypeString()

buildNACellStyle

public org.apache.poi.ss.usermodel.CellStyle buildNACellStyle(org.apache.poi.ss.usermodel.Sheet sheet)

buildRowsHeaders

protected void buildRowsHeaders(org.apache.poi.ss.usermodel.Sheet sheet,
                                CrossTab cs,
                                java.util.List<Node> siblings,
                                int rowNum,
                                int columnNum,
                                org.apache.poi.ss.usermodel.CreationHelper createHelper,
                                java.util.Locale locale,
                                org.apache.poi.ss.usermodel.CellStyle cellStyle)
                         throws org.json.JSONException
Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----| | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | | 7 | | | 6 |-----| | | | 8 | |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|

Parameters:
sheet - The sheet of the XLS file
siblings - The siblings nodes of the headers structure
rowNum - The row number where the first sibling must be inserted
columnNum - The column number where the siblings must be inserted
createHelper - The file creation helper
Throws:
org.json.JSONException

buildRowHeaderTitle

protected void buildRowHeaderTitle(org.apache.poi.ss.usermodel.Sheet sheet,
                                   CrossTab cs,
                                   int columnHeadersNumber,
                                   int startColumn,
                                   int startRow,
                                   org.apache.poi.ss.usermodel.CreationHelper createHelper,
                                   java.util.Locale locale,
                                   org.apache.poi.ss.usermodel.CellStyle cellStyle)
                            throws org.json.JSONException
Add the title of the columns in the row headers

Parameters:
sheet -
titles - list of titles
columnHeadersNumber - number of column headers
startColumn - first column of the crosstab in the xls
startRow - first row of the crosstab in the xls
createHelper -
Throws:
org.json.JSONException

buildDimensionCellStyle

public org.apache.poi.ss.usermodel.CellStyle buildDimensionCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)

buildHeaderCellStyle

public org.apache.poi.ss.usermodel.CellStyle buildHeaderCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)

buildDataCellStyle

public org.apache.poi.ss.usermodel.CellStyle buildDataCellStyle(org.apache.poi.ss.usermodel.Sheet sheet)

buildColumnsHeader

protected void buildColumnsHeader(org.apache.poi.ss.usermodel.Sheet sheet,
                                  CrossTab cs,
                                  java.util.List<Node> siblings,
                                  int rowNum,
                                  int columnNum,
                                  org.apache.poi.ss.usermodel.CreationHelper createHelper,
                                  java.util.Locale locale,
                                  org.apache.poi.ss.usermodel.CellStyle memberCellStyle,
                                  org.apache.poi.ss.usermodel.CellStyle dimensionCellStyle)
                           throws org.json.JSONException
Builds the columns' headers recursively with this order: |------------------------------------------| | 1 | 9 | |------------------------------------------| | 2 | 5 | 10 | |-----------|-----------------|------------| | 3 | 4 | 6 | 7 | 8 | 11 | 12 | |------------------------------------------|

Parameters:
sheet - The sheet of the XLS file
siblings - The siblings nodes of the headers structure
rowNum - The row number where the siblings must be inserted
columnNum - The column number where the first sibling must be inserted
createHelper - The file creation helper
dimensionCellStyle - The cell style for cells containing dimensions (i.e. attributes' names)
memberCellStyle - The cell style for cells containing members (i.e. attributes' values)
Throws:
org.json.JSONException

getNumberFormat

public org.apache.poi.ss.usermodel.CellStyle getNumberFormat(int j,
                                                             java.util.Map<java.lang.Integer,org.apache.poi.ss.usermodel.CellStyle> decimalFormats,
                                                             org.apache.poi.ss.usermodel.Sheet sheet,
                                                             org.apache.poi.ss.usermodel.CreationHelper createHelper,
                                                             CrossTab.CellType celltype)

getCalculatedFieldDecimals

public int getCalculatedFieldDecimals()