org.nuxeo.ecm.platform.groups.audit.service.acl.excel

Class ExcelBuilder

java.lang.Object

org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder

All Implemented Interfaces:

IExcelBuilder

Direct Known Subclasses:

ExcelBuilderMultiSheet

public class ExcelBuilder
extends Object
implements IExcelBuilder

A utility wrapper around Apache POI Excel spreadsheet builder. Comments only supported on XLS type (no XLSX) To add new excel features, see

Author:

Martin Pernollet

See Also:

(or a copy in /doc)

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	ExcelBuilder.Type

Field Summary

Fields

Modifier and Type	Field and Description
protected org.apache.poi.ss.usermodel.Font	boldFont
static boolean	CRASH_ON_CELL_OVERFLOW States if the builder should throw an IllegalArgumentException or simply warn with a log once a cell index exceed max number of columns or row.
protected org.apache.poi.ss.usermodel.CreationHelper	create
protected int	currentSheetId
protected org.apache.poi.ss.usermodel.Drawing	drawing
static int	LAST_COLUMN
static int	LAST_ROW
static int	MAX_COLUMN The max number of columns supported by an excel sheet (256="IV" column header)
static int	MAX_ROW The max number of rows supported by an excel sheet
protected Map<Integer,org.apache.poi.ss.usermodel.Sheet>	sheets
protected ExcelBuilder.Type	type
protected org.apache.poi.ss.usermodel.Workbook	workbook

Constructor Summary

Constructors

Constructor and Description
ExcelBuilder()
ExcelBuilder(ExcelBuilder.Type type)
ExcelBuilder(ExcelBuilder.Type type, String firstSheet)

Method Summary

Modifier and Type	Method and Description
org.apache.poi.ss.usermodel.Comment	addComment(org.apache.poi.ss.usermodel.Cell cell, String text, int row, int col, int colWidth, int rowHeight)
org.apache.poi.ss.usermodel.Comment	buildComment(String text, int row, int col, int colWidth, int rowHeight) Return a Comment.
Collection<org.apache.poi.ss.usermodel.Sheet>	getAllSheets()
org.apache.poi.ss.usermodel.Font	getBoldFont()
org.apache.poi.hssf.util.HSSFColor	getColor(byte r, byte g, byte b)
org.apache.poi.hssf.util.HSSFColor	getColor(ByteColor color)
org.apache.poi.ss.usermodel.Sheet	getCurrentSheet()
int	getCurrentSheetId()
org.apache.poi.hssf.usermodel.HSSFWorkbook	getHSSFWorkbook()
protected org.apache.poi.ss.usermodel.Cell	getOrCreateCell(int i, int j)
protected org.apache.poi.ss.usermodel.Row	getOrCreateRow(int i)
org.apache.poi.ss.usermodel.Workbook	getWorkbook()
org.apache.poi.ss.usermodel.Workbook	load(File file)
org.apache.poi.ss.usermodel.Workbook	load(String file)
int	loadPicture(String image)
void	mergeRange(int firstRow, int firstColumn, int lastRow, int lastColumn)
org.apache.poi.ss.usermodel.CellStyle	newCellStyle() Return a new cell style instance for the choosen workbook ExcelBuilder.Type.
org.apache.poi.ss.usermodel.CellStyle	newColoredCellStyle(ByteColor color)
org.apache.poi.ss.usermodel.Font	newFont()
org.apache.poi.ss.usermodel.Font	newFont(int size)
int	newSheet(int index, String name)
void	save(File file)
void	save(String file)
org.apache.poi.ss.usermodel.Cell	setCell(int row, int column, String content) Set a cell text content with no styling information.
org.apache.poi.ss.usermodel.Cell	setCell(int row, int column, String content, org.apache.poi.ss.usermodel.CellStyle style) Set a cell content at the given indices, and apply the style if it is not null.
void	setColumnWidth(int column, int width) Set the width (in units of 1/256th of a character width)
void	setColumnWidthAuto(int column)
void	setCurrentSheetId(int s)
void	setFreezePane(int colSplit, int rowSplit)
void	setFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)
void	setPicture(int pictureIdx, int col1, int row1, boolean resize)
void	setRowHeight(int row, int height)
void	setSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
protected boolean	sheetInitialized(int index)
protected boolean	validateCellIndex(int row, int column, String content) Validate a cell index.

Methods inherited from class java.lang.Object

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

Field Detail

MAX_COLUMN

public static final int MAX_COLUMN

The max number of columns supported by an excel sheet (256="IV" column header)

See Also:

Constant Field Values

MAX_ROW

public static int MAX_ROW

The max number of rows supported by an excel sheet

LAST_COLUMN

public static final int LAST_COLUMN

See Also:

Constant Field Values

LAST_ROW

public static final int LAST_ROW

CRASH_ON_CELL_OVERFLOW

public static final boolean CRASH_ON_CELL_OVERFLOW

States if the builder should throw an IllegalArgumentException or simply warn with a log once a cell index exceed max number of columns or row.

See Also:

Constant Field Values

type

protected ExcelBuilder.Type type

workbook

protected org.apache.poi.ss.usermodel.Workbook workbook

create

protected org.apache.poi.ss.usermodel.CreationHelper create

drawing

protected org.apache.poi.ss.usermodel.Drawing drawing

boldFont

protected org.apache.poi.ss.usermodel.Font boldFont

currentSheetId

protected int currentSheetId

sheets

protected Map<Integer,org.apache.poi.ss.usermodel.Sheet> sheets

Constructor Detail

ExcelBuilder

public ExcelBuilder()

ExcelBuilder

public ExcelBuilder(ExcelBuilder.Type type)

ExcelBuilder

public ExcelBuilder(ExcelBuilder.Type type,
                    String firstSheet)

Method Detail

getWorkbook

public org.apache.poi.ss.usermodel.Workbook getWorkbook()

Specified by:

getWorkbook in interface IExcelBuilder

getHSSFWorkbook

public org.apache.poi.hssf.usermodel.HSSFWorkbook getHSSFWorkbook()

setCell

public org.apache.poi.ss.usermodel.Cell setCell(int row,
                                                int column,
                                                String content,
                                                org.apache.poi.ss.usermodel.CellStyle style)

Set a cell content at the given indices, and apply the style if it is not null. If row(i) does not exist yet, it is created, otherwise it is recycled. If cell(i,j) does not exist yet, it is created, otherwise it is recycled. Reminder: excel support a maximum of 65,536 rows and 256 columns per sheet

Specified by:

setCell in interface IExcelBuilder

Parameters:

row - row index

column - column index

content - a string to display in the cell

style - a style to apply to the cell

Returns:

the created or retrieved cell in case additional stuff should be done on it.

validateCellIndex

protected boolean validateCellIndex(int row,
                                    int column,
                                    String content)

Validate a cell index. If cell index is out of maximum number of rows/columns:

• throws an IllegalArgumentException if CRASH_ON_CELL_OVERFLOW is set to true.
• otherwise emit a log warning and return false to invalidate this cell.

setCell

public org.apache.poi.ss.usermodel.Cell setCell(int row,
                                                int column,
                                                String content)

Set a cell text content with no styling information.

Specified by:

setCell in interface IExcelBuilder

See Also:

setCell(int i, int j, String content, CellStyle style)}

getCurrentSheet

public org.apache.poi.ss.usermodel.Sheet getCurrentSheet()

Specified by:

getCurrentSheet in interface IExcelBuilder

getCurrentSheetId

public int getCurrentSheetId()

Specified by:

getCurrentSheetId in interface IExcelBuilder

setCurrentSheetId

public void setCurrentSheetId(int s)

Specified by:

setCurrentSheetId in interface IExcelBuilder

newSheet

public int newSheet(int index,
                    String name)

Specified by:

newSheet in interface IExcelBuilder

getAllSheets

public Collection<org.apache.poi.ss.usermodel.Sheet> getAllSheets()

Specified by:

getAllSheets in interface IExcelBuilder

sheetInitialized

protected boolean sheetInitialized(int index)

setRowHeight

public void setRowHeight(int row,
                         int height)

Specified by:

setRowHeight in interface IExcelBuilder

setColumnWidth

public void setColumnWidth(int column,
                           int width)

Set the width (in units of 1/256th of a character width)

Specified by:

setColumnWidth in interface IExcelBuilder

setColumnWidthAuto

public void setColumnWidthAuto(int column)

Specified by:

setColumnWidthAuto in interface IExcelBuilder

setFreezePane

public void setFreezePane(int colSplit,
                          int rowSplit)

Specified by:

setFreezePane in interface IExcelBuilder

setFreezePane

public void setFreezePane(int colSplit,
                          int rowSplit,
                          int leftmostColumn,
                          int topRow)

Specified by:

setFreezePane in interface IExcelBuilder

setSplitPane

public void setSplitPane(int xSplitPos,
                         int ySplitPos,
                         int leftmostColumn,
                         int topRow,
                         int activePane)

Specified by:

setSplitPane in interface IExcelBuilder

mergeRange

public void mergeRange(int firstRow,
                       int firstColumn,
                       int lastRow,
                       int lastColumn)

Specified by:

mergeRange in interface IExcelBuilder

newCellStyle

public org.apache.poi.ss.usermodel.CellStyle newCellStyle()

Return a new cell style instance for the choosen workbook ExcelBuilder.Type.

Specified by:

newCellStyle in interface IExcelBuilder

getOrCreateCell

protected org.apache.poi.ss.usermodel.Cell getOrCreateCell(int i,
                                                           int j)

getOrCreateRow

protected org.apache.poi.ss.usermodel.Row getOrCreateRow(int i)

addComment

public org.apache.poi.ss.usermodel.Comment addComment(org.apache.poi.ss.usermodel.Cell cell,
                                                      String text,
                                                      int row,
                                                      int col,
                                                      int colWidth,
                                                      int rowHeight)

Specified by:

addComment in interface IExcelBuilder

buildComment

public org.apache.poi.ss.usermodel.Comment buildComment(String text,
                                                        int row,
                                                        int col,
                                                        int colWidth,
                                                        int rowHeight)

Return a Comment. Comments are supported only on XLS file (HSSF framework).

Parameters:

row -

col -

colWidth -

rowHeight -

Returns:

newColoredCellStyle

public org.apache.poi.ss.usermodel.CellStyle newColoredCellStyle(ByteColor color)

Specified by:

newColoredCellStyle in interface IExcelBuilder

getColor

public org.apache.poi.hssf.util.HSSFColor getColor(ByteColor color)

Specified by:

getColor in interface IExcelBuilder

getColor

public org.apache.poi.hssf.util.HSSFColor getColor(byte r,
                                                   byte g,
                                                   byte b)

loadPicture

public int loadPicture(String image)
                throws IOException

Specified by:

loadPicture in interface IExcelBuilder

Throws:

IOException

setPicture

public void setPicture(int pictureIdx,
                       int col1,
                       int row1,
                       boolean resize)

Specified by:

setPicture in interface IExcelBuilder

getBoldFont

public org.apache.poi.ss.usermodel.Font getBoldFont()

Specified by:

getBoldFont in interface IExcelBuilder

newFont

public org.apache.poi.ss.usermodel.Font newFont(int size)

Specified by:

newFont in interface IExcelBuilder

newFont

public org.apache.poi.ss.usermodel.Font newFont()

Specified by:

newFont in interface IExcelBuilder

save

public void save(String file)
          throws IOException

Specified by:

save in interface IExcelBuilder

Throws:

IOException

save

public void save(File file)
          throws IOException

Specified by:

save in interface IExcelBuilder

Throws:

IOException

load

public org.apache.poi.ss.usermodel.Workbook load(String file)
                                          throws org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                                                 IOException

Specified by:

load in interface IExcelBuilder

Throws:

org.apache.poi.openxml4j.exceptions.InvalidFormatException

IOException

load

public org.apache.poi.ss.usermodel.Workbook load(File file)
                                          throws org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                                                 IOException

Specified by:

load in interface IExcelBuilder

Throws:

org.apache.poi.openxml4j.exceptions.InvalidFormatException

IOException