com.groiss.gui.excel

Class ExcelUtils

java.lang.Object

com.groiss.gui.excel.ExcelUtils

public class ExcelUtils
extends Object

Utility class for creating Excel files.

Method Summary

Modifier and Type	Method and Description
static org.apache.poi.ss.usermodel.Cell	createDataCell(org.apache.poi.ss.usermodel.Row row, int col, Object value, org.apache.poi.ss.usermodel.CellStyle basicStyle, org.apache.poi.ss.usermodel.CellStyle floatStyle, org.apache.poi.ss.usermodel.CellStyle decimalStyle, org.apache.poi.ss.usermodel.CellStyle dateStyle, org.apache.poi.ss.usermodel.CellStyle textStyle, org.apache.poi.ss.usermodel.CreationHelper ch) Creates a Cell object in the given row and column.
static org.apache.poi.ss.usermodel.Cell	createHeaderCell(org.apache.poi.ss.usermodel.Row row, int col, org.apache.poi.ss.usermodel.CellStyle style, String value) Get a new header Cell object with given information
static org.apache.poi.ss.usermodel.Workbook	exportToWorkbook(String title, List<Map<String,Object>> data, NavigationTreeNode n, List<String> selectedCols) Export a list of given data to Excel.
static org.apache.poi.ss.usermodel.Workbook	exportToWorkbook(String title, TableModel tm, NavigationTreeNode n) Export a TableModel to Excel.
static org.apache.poi.ss.usermodel.Workbook	exportToWorkbook(String title, TableModel tm, NavigationTreeNode n, List<String> selectedCols) Export a TableModel to Excel.
static org.apache.poi.ss.usermodel.Font	getArialFont(org.apache.poi.ss.usermodel.Workbook book) Creates a standard Font object with font Arial.
static org.apache.poi.ss.usermodel.CellStyle	getBasicStyle(org.apache.poi.ss.usermodel.Workbook book) Creates a basic CellStyle object which is used a default object for other special styles (e.g. decimal, etc.).
static org.apache.poi.ss.usermodel.Cell	getCell(org.apache.poi.ss.usermodel.Row row, org.apache.poi.ss.usermodel.CellStyle style, int col) Get a new Cell object with given information
static org.apache.poi.ss.usermodel.CellStyle	getDateStyle(org.apache.poi.ss.usermodel.Workbook book) Creates a CellStyle object for Date values.
static org.apache.poi.ss.usermodel.CellStyle	getDecimalStyle(org.apache.poi.ss.usermodel.Workbook book) Creates a CellStyle object for decimal values which are not of type Float or Double.
static org.apache.poi.ss.usermodel.CellStyle	getFloatStyle(org.apache.poi.ss.usermodel.Workbook book) Creates a CellStyle object for decimal values which are of type Float or Double.
static org.apache.poi.ss.usermodel.CellStyle	getTextStyle(org.apache.poi.ss.usermodel.Workbook book) Creates a CellStyle object for ordinary texts

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Detail

exportToWorkbook

public static org.apache.poi.ss.usermodel.Workbook exportToWorkbook(String title,
                                                                    List<Map<String,Object>> data,
                                                                    NavigationTreeNode n,
                                                                    List<String> selectedCols)
                                                             throws Exception

Export a list of given data to Excel. The following system column ids are not exported to Excel:

• oid
• dndHandle
• selector
• Icon

Parameters:

title - the value used as Excel sheet name

data - the data list of maps (as used in dojo grids); each list entry (= Map object) is a row in the Excel; the Map consists of the column (header) id (= key of Map) which is translated to appropriate (internationalized) name in Excel sheet and the value of the column (= value of Map)

n - the table node which configures the table

selectedCols - a list of column ids to show in Excel

Returns:

a poi workbook (SXSSFWorkbook)

Throws:

Exception

exportToWorkbook

public static org.apache.poi.ss.usermodel.Workbook exportToWorkbook(String title,
                                                                    TableModel tm,
                                                                    NavigationTreeNode n)

Export a TableModel to Excel. The following system column ids are not exported to Excel:

• oid
• dndHandle
• selector
• Icon

Parameters:

title - the value used as Excel sheet name

tm - the data TableModel

n - the table node which configures the table

Returns:

a poi workbook (SXSSFWorkbook)

exportToWorkbook

public static org.apache.poi.ss.usermodel.Workbook exportToWorkbook(String title,
                                                                    TableModel tm,
                                                                    NavigationTreeNode n,
                                                                    List<String> selectedCols)

Export a TableModel to Excel. In addition this method allows to define a list of column ids that should be exported. The following column ids are not exported to Excel:

• oid
• dndHandle
• selector
• Icon

Parameters:

title - the value used as Excel sheet name

tm - the data TableModel

n - the table node which configures the table

selectedCols - a list of column ids to show in Excel

Returns:

a poi workbook (SXSSFWorkbook)

getCell

public static org.apache.poi.ss.usermodel.Cell getCell(org.apache.poi.ss.usermodel.Row row,
                                                       org.apache.poi.ss.usermodel.CellStyle style,
                                                       int col)

Get a new Cell object with given information

Parameters:

row - the row where cell should be created

style - the style of the cell

col - the column number the cell represents

Returns:

the Cell object

createHeaderCell

public static org.apache.poi.ss.usermodel.Cell createHeaderCell(org.apache.poi.ss.usermodel.Row row,
                                                                int col,
                                                                org.apache.poi.ss.usermodel.CellStyle style,
                                                                String value)

Get a new header Cell object with given information

Parameters:

row - the row where cell should be created (should be the header row)

col - the column number the cell represents

style - the style of the cell

value - a rich string value for the cell

Returns:

the Cell object

createDataCell

public static org.apache.poi.ss.usermodel.Cell createDataCell(org.apache.poi.ss.usermodel.Row row,
                                                              int col,
                                                              Object value,
                                                              org.apache.poi.ss.usermodel.CellStyle basicStyle,
                                                              org.apache.poi.ss.usermodel.CellStyle floatStyle,
                                                              org.apache.poi.ss.usermodel.CellStyle decimalStyle,
                                                              org.apache.poi.ss.usermodel.CellStyle dateStyle,
                                                              org.apache.poi.ss.usermodel.CellStyle textStyle,
                                                              org.apache.poi.ss.usermodel.CreationHelper ch)

Creates a Cell object in the given row and column. Configure the cell according to type of value.

Parameters:

row - the row where cell is located

col - the column number the cell represents

value - the value to enrich the cell value. Following

basicStyle - the basic style which is used for cells with empty value or if no value conversion was possible (= an error occurred)

floatStyle - the style used for Number values of type Float or Double

decimalStyle - the style used for Number values which are not of type Float or Double

dateStyle - the style used for Date values

textStyle - the style used for all other values that does not corresponds with other styles

ch - a CreationHelper object to create a rich text string for cells where

• no value exists (= empty value)
• a value exists with style type 'textStyle'
• a conversion of the value was not possible (= an error occurred)

Returns:

the Cell object

getDecimalStyle

public static org.apache.poi.ss.usermodel.CellStyle getDecimalStyle(org.apache.poi.ss.usermodel.Workbook book)

Creates a CellStyle object for decimal values which are not of type Float or Double.

Parameters:

book - the workbook to create the CellStyle object

Returns:

the CellStyle object

getTextStyle

public static org.apache.poi.ss.usermodel.CellStyle getTextStyle(org.apache.poi.ss.usermodel.Workbook book)

Creates a CellStyle object for ordinary texts

Parameters:

book - the workbook to create the CellStyle object

Returns:

the CellStyle object

getBasicStyle

public static org.apache.poi.ss.usermodel.CellStyle getBasicStyle(org.apache.poi.ss.usermodel.Workbook book)

Creates a basic CellStyle object which is used a default object for other special styles (e.g. decimal, etc.). Following basic styles are set:

• Foreground color of the cell is set to white
• A thin border is defined for the cell

Parameters:

book - the workbook to create the CellStyle object

Returns:

the CellStyle object

getFloatStyle

public static org.apache.poi.ss.usermodel.CellStyle getFloatStyle(org.apache.poi.ss.usermodel.Workbook book)

Creates a CellStyle object for decimal values which are of type Float or Double.
The format is: #,##0.00

Parameters:

book - the workbook to create the CellStyle object

Returns:

the CellStyle object

getDateStyle

public static org.apache.poi.ss.usermodel.CellStyle getDateStyle(org.apache.poi.ss.usermodel.Workbook book)

Creates a CellStyle object for Date values.
The format is: m/d/yy h:mm

Parameters:

book - the workbook to create the CellStyle object

Returns:

the CellStyle object

getArialFont

public static org.apache.poi.ss.usermodel.Font getArialFont(org.apache.poi.ss.usermodel.Workbook book)

Creates a standard Font object with font Arial.

Parameters:

book - the workbook to create the Font object

Returns:

the Font object