org.dhatim.fastexcel

Class Worksheet

java.lang.Object

org.dhatim.fastexcel.Worksheet

public class Worksheet
extends Object

A worksheet is a set of cells.

Field Summary

Fields

Modifier and Type	Field and Description
static int	MAX_COL_WIDTH Maximum column width in Excel.
static int	MAX_COLS Maximum number of columns in Excel.
static int	MAX_ROWS Maximum number of rows in Excel.

Method Summary

Modifier and Type	Method and Description
void	comment(int r, int c, String comment) Assign a note/comment to a cell.
void	finish() Finish the construction of this worksheet.
void	flush() Write all the rows currently in memory to the workbook's output stream.
void	formula(int r, int c, String expression) Set the cell formula at the given coordinates.
String	getName() Get worksheet name.
VisibilityState	getVisibilityState()
Workbook	getWorkbook() Get parent workbook.
void	hideColumn(int column) Hide the given column.
void	hideRow(int row) Hide the given row.
void	protect(String password) Protects the sheet with a password.
void	protect(String password, Set<SheetProtectionOption> options) Protects the sheet with a password.
void	protect(String password, SheetProtectionOption... options) Protects the sheet with a password.
Range	range(int top, int left, int bottom, int right) Create a new range of cells.
void	setVisibilityState(VisibilityState visibilityState) Sets the visibility state of the sheet
void	showColumn(int column) Show the given column.
void	showRow(int row) Show the given row.
StyleSetter	style(int r, int c) Get a new style setter for a cell.
Object	value(int r, int c) Get the cell value (or formula) at the given coordinates.
void	value(int r, int c, Object value) Set the cell value at the given coordinates.
void	width(int c, double width) Specify the width for the given column.

Methods inherited from class java.lang.Object

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

Field Detail

MAX_ROWS

public static final int MAX_ROWS

Maximum number of rows in Excel.

See Also:

Constant Field Values

MAX_COLS

public static final int MAX_COLS

Maximum number of columns in Excel.

See Also:

Constant Field Values

MAX_COL_WIDTH

public static final int MAX_COL_WIDTH

Maximum column width in Excel.

See Also:

Constant Field Values

Method Detail

getName

public String getName()

Get worksheet name.

Returns:

Worksheet name.

getWorkbook

public Workbook getWorkbook()

Get parent workbook.

Returns:

Parent workbook.

setVisibilityState

public void setVisibilityState(VisibilityState visibilityState)

Sets the visibility state of the sheet

This is done by setting the state attribute in the workbook.xml.

getVisibilityState

public VisibilityState getVisibilityState()

hideRow

public void hideRow(int row)

Hide the given row.

Parameters:

row - Zero-based row number

showRow

public void showRow(int row)

Show the given row.

Parameters:

row - Zero-based row number

hideColumn

public void hideColumn(int column)

Hide the given column.

Parameters:

column - Zero-based column number

showColumn

public void showColumn(int column)

Show the given column.

Parameters:

column - Zero-based column number

protect

public void protect(String password)

Protects the sheet with a password. This method protects all the default SheetProtectionOptions and 'sheet'. (Note that this is not very secure and only meant for discouraging changes.)

Parameters:

password - The password to use.

protect

public void protect(String password,
                    SheetProtectionOption... options)

Protects the sheet with a password. (Note that this is not very secure and only meant for discouraging changes.)

Parameters:

password - The password to use.

options - An array of all the SheetProtectionOptions to protect.

protect

public void protect(String password,
                    Set<SheetProtectionOption> options)

Protects the sheet with a password. (Note that this is not very secure and only meant for discouraging changes.)

Parameters:

password - The password to use.

options - A Set of all the SheetProtectionOptions to protect.

width

public void width(int c,
                  double width)

Specify the width for the given column. Will autoSize by default.

The maximum column width in excel is 255. The colum width in excel is the number of characters that can be displayed with the standard font (first font in the workbook).

Note: The xml spec specifies additional padding for each cell (Section 3.3.1.12 of the OOXML spec) which will result in slightly less characters being displayed then what is given here.

Parameters:

c - Zero-based column number

width - The width of the column in character widths

value

public void value(int r,
                  int c,
                  Object value)

Set the cell value at the given coordinates.

Parameters:

r - Zero-based row number.

c - Zero-based column number.

value - Cell value. Supported types are String, Date, LocalDate, LocalDateTime, ZonedDateTime, Number and Boolean implementations. Note Excel timestamps do not carry any timezone information; Date values are converted to an Excel serial number with the system timezone. If you need a specific timezone, prefer passing a ZonedDateTime.

value

public Object value(int r,
                    int c)

Get the cell value (or formula) at the given coordinates.

Parameters:

r - Zero-based row number.

c - Zero-based column number.

Returns:

Cell value (or Formula).

formula

public void formula(int r,
                    int c,
                    String expression)

Set the cell formula at the given coordinates.

Parameters:

r - Zero-based row number.

c - Zero-based column number.

expression - Cell formula expression.

style

public StyleSetter style(int r,
                         int c)

Get a new style setter for a cell.

Parameters:

r - Zero-based row number.

c - Zero-based column number.

Returns:

Newly created style setter.

range

public Range range(int top,
                   int left,
                   int bottom,
                   int right)

Create a new range of cells. Note coordinates are reordered if necessary to make sure top <= bottom and left <= right.

Parameters:

top - Top row.

left - Left column.

bottom - Bottom row.

right - Right column.

Returns:

Newly created range.

finish

public void finish()
            throws IOException

Finish the construction of this worksheet. This creates the worksheet file on the workbook's output stream. Rows and cells in this worksheet are then destroyed.

Throws:

IOException - If an I/O error occurs.

flush

public void flush()
           throws IOException

Write all the rows currently in memory to the workbook's output stream. Call this method periodically when working with huge data sets. After calling flush(), all the rows created so far become inaccessible.
Notes:

• All columns must be defined before calling this method: do not add or merge columns after calling flush().
• When a Worksheet is flushed, no other worksheet can be flushed until finish() is called.

Throws:

IOException - If an I/O error occurs.

comment

public void comment(int r,
                    int c,
                    String comment)

Assign a note/comment to a cell. The comment popup will be twice the size of the cell and will be initially hidden.

Comments are stored in memory till call to finish() - calling flush() does not write them to output stream.

Parameters:

r - Zero-based row number.

c - Zero-based column number.

comment - Note text