com.jrefinery.workbook
Class Worksheet

java.lang.Object
  |
  +--com.jrefinery.workbook.Worksheet

public class Worksheet

extends java.lang.Object

Represents one worksheet within a workbook.

Field Summary

protected Cells	cells The cells in the worksheet.
protected ColumnAttributesManager	columnAttributesManager Column attributes (width, margins, hidden etc).
protected java.util.ArrayList	comments Comments for cells in the worksheet.
static double	DEFAULT_COLUMN_WIDTH The default width for columns.
static double	DEFAULT_ROW_HEIGHT The default height for rows.
protected boolean	displayFormulae Display formulae rather than calculated values (true/false).
protected boolean	hideColHeader Hide the column header (true/false).
protected boolean	hideGrid Hide the grid (true/false).
protected boolean	hideRowHeader Hide the row header (true/false).
protected boolean	hideZero Hide zeros (true/false).
static int	MAX_COLUMNS The maximum number of columns in a worksheet.
static int	MAX_ROWS The maximum number of rows in a worksheet.
protected int	maxCol The index of the right-most column used in the worksheet.
protected int	maxRow The index of the bottom-most row used in the worksheet.
protected java.lang.String	name The name of the worksheet.
protected NamesManager	namesManager Manager for names defined in the worksheet.
protected PrintInformation	print_info Print setup.
protected RowAttributesManager	rowAttributesManager Row attributes (height, margins, hidden etc).
protected Selections	selections Ranges that are selected.
protected Solver	solver Settings for the solver utility.
protected Styles	styles Styles that apply to ranges within the worksheet.
protected double	zoom The current zoom factor (in the range 0.05 to 5.00, that is 5% to 500%).

Constructor Summary

Worksheet(java.lang.String name)
Standard constructor: creates a named empty worksheet.

Method Summary

void	addCell(Cell cell) Sets the value at the specified cell.
void	addName(java.lang.String name, java.lang.String value) Adds a name to the worksheet.
void	addStyleRegion(StyleRegion styleRegion) Adds the specified style region.
void	applyBackgroundColor(StyleColor color, int column, int row) Applies the background color to the specified cell.
void	applyBackgroundColor(StyleColor color, int startColumn, int startRow, int endColumn, int endRow) Applies the background color to the specified region.
void	applyBorder(StyleBorder border, int column, int row) Applies the foreground color to the specified cell.
void	applyBorder(StyleBorder border, int startColumn, int startRow, int endColumn, int endRow) Applies the border to the specified region.
void	applyFont(StyleFont font, int column, int row) Applies the font to the specified cell.
void	applyFont(StyleFont font, int startColumn, int startRow, int endColumn, int endRow) Applies the font to the specified region.
void	applyForegroundColor(StyleColor color, int column, int row) Applies the foreground color to the specified cell.
void	applyForegroundColor(StyleColor color, int startColumn, int startRow, int endColumn, int endRow) Applies the foreground color to the specified region.
void	applyHorizontalAlignment(int align, int column, int row) Applies a horizontal alignment setting to a cell.
void	applyHorizontalAlignment(int align, int startColumn, int startRow, int endColumn, int endRow) Applies a horizontal alignment setting to a range of cells.
void	applyOutline(int column, int row) Applies a thin outline border to a cell.
void	applyOutline(int borderStyle, int column, int row) Applies a border to a cell.
void	applyOutline(int startColumn, int startRow, int endColumn, int endRow) Applies a thin outline border to a range of cells.
void	applyOutline(int lineStyle, int startColumn, int startRow, int endColumn, int endRow) Applies a border around a range of cells.
void	applyPatternColor(StyleColor color, int column, int row) Applies the pattern color to the specified cell.
void	applyPatternColor(StyleColor color, int startColumn, int startRow, int endColumn, int endRow) Applies the pattern color to the specified region.
void	applyVerticalAlignment(int align, int column, int row) Applies a vertical alignment setting to a cell.
void	applyVerticalAlignment(int align, int startColumn, int startRow, int endColumn, int endRow) Applies a vertical alignment setting to a range of cells.
void	applyWrapText(boolean wrapText, int column, int row) Applies the "wrap-text" setting to the specified cell.
void	applyWrapText(boolean wrapText, int startColumn, int startRow, int endColumn, int endRow) Applies the "wrap-text" setting to the specified region.
static java.lang.String	cellReference(int column, int row) Returns a cell reference using letters for the column (e.g.
ColumnAttributesManager	getColumnAttributesManager() Returns a reference to the object managing the column attributes.
java.lang.String	getName() Returns the name of the worksheet.
NamesManager	getNamesManager() Returns a reference to the names manager.
RowAttributesManager	getRowAttributesManager() Returns a reference to the object managing the row attributes.
double	getZoom() Returns the zoom factor.
void	putComment(java.lang.String comment, int column, int row) Puts a comment in a cell, overwriting any existing cell comments.
void	putDate(com.jrefinery.workbook.SerialDate date, int column, int row) Puts a date in a cell, overwriting any existing cell contents.
void	putFormula(java.lang.String formula, int column, int row) Puts a formula in a cell, overwriting any existing cell contents.
void	putLabel(java.lang.String label, int column, int row) Puts a label in a cell, overwriting any existing cell contents.
void	putValue(double value, int column, int row) Puts a value in a cell, overwriting any existing cell contents.
void	setColumnWidth(int column, double width) Sets the width of one column.
void	setColumnWidth(int startColumn, int endColumn, double width) Sets the width of a range of columns.
void	setName(java.lang.String name) Sets the name of the worksheet.
void	setRowHeight(int row, double height) Sets the height of one row.
void	setRowHeight(int startRow, int endRow, double height) Sets the height of a range of rows.
void	setStyle(Style style, int startColumn, int startRow, int endColumn, int endRow) Set the style for the specified range of the worksheet.
void	setZoom(double percent) Sets the zoom factor.
void	writeWorksheet(java.io.PrintStream out, java.lang.String indent) Writes a worksheet element in the Gnumeric format to a stream.

Methods inherited from class java.lang.Object

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

Field Detail

MAX_COLUMNS

public static final int MAX_COLUMNS

The maximum number of columns in a worksheet.

MAX_ROWS

public static final int MAX_ROWS

The maximum number of rows in a worksheet.

DEFAULT_COLUMN_WIDTH

public static final double DEFAULT_COLUMN_WIDTH

The default width for columns.

DEFAULT_ROW_HEIGHT

public static final double DEFAULT_ROW_HEIGHT

The default height for rows.

name

protected java.lang.String name

The name of the worksheet.

displayFormulae

protected boolean displayFormulae

Display formulae rather than calculated values (true/false).

hideZero

protected boolean hideZero

Hide zeros (true/false).

hideGrid

protected boolean hideGrid

Hide the grid (true/false).

hideColHeader

protected boolean hideColHeader

Hide the column header (true/false).

hideRowHeader

protected boolean hideRowHeader

Hide the row header (true/false).

maxCol

protected int maxCol

The index of the right-most column used in the worksheet.

maxRow

protected int maxRow

The index of the bottom-most row used in the worksheet.

zoom

protected double zoom

The current zoom factor (in the range 0.05 to 5.00, that is 5% to 500%).

namesManager

protected NamesManager namesManager

Manager for names defined in the worksheet.

print_info

protected PrintInformation print_info

Print setup.

styles

protected Styles styles

Styles that apply to ranges within the worksheet.

columnAttributesManager

protected ColumnAttributesManager columnAttributesManager

Column attributes (width, margins, hidden etc).

rowAttributesManager

protected RowAttributesManager rowAttributesManager

Row attributes (height, margins, hidden etc).

selections

protected Selections selections

Ranges that are selected.

cells

protected Cells cells

The cells in the worksheet.

comments

protected java.util.ArrayList comments

Comments for cells in the worksheet.

solver

protected Solver solver

Settings for the solver utility.

Constructor Detail

Worksheet

public Worksheet(java.lang.String name)

Standard constructor: creates a named empty worksheet.

Parameters:

name - The name of the worksheet.

Method Detail

getName

public java.lang.String getName()

Returns the name of the worksheet.

Returns:

The name of the worksheet.

setName

public void setName(java.lang.String name)

Sets the name of the worksheet.

Parameters:

name - The new name for the worksheet.

getZoom

public double getZoom()

Returns the zoom factor.

Returns:

The zoom factor.

setZoom

public void setZoom(double percent)

Sets the zoom factor.

Parameters:

percent - The new zoom factor (should be in the range 0.05 to 5.00).

getNamesManager

public NamesManager getNamesManager()

Returns a reference to the names manager.

Returns:

A reference to the names manager.

getColumnAttributesManager

public ColumnAttributesManager getColumnAttributesManager()

Returns a reference to the object managing the column attributes.

Returns:

A reference to the column attributes manager.

getRowAttributesManager

public RowAttributesManager getRowAttributesManager()

Returns a reference to the object managing the row attributes.

Returns:

A reference to the row attributes manager.

setColumnWidth

public void setColumnWidth(int column,
                           double width)

Sets the width of one column.

Parameters:

column - The column (0 to Worksheets.MAX_COLUMNS-1).

width - The new width.

setColumnWidth

public void setColumnWidth(int startColumn,
                           int endColumn,
                           double width)

Sets the width of a range of columns.

Parameters:

startColumn - The start column (0 <= startColumn < Worksheets.MAX_COLUMNS).

endColumn - The end column (startColumn <= endColumn < Worksheets.MAX_COLUMNS).

width - The new width.

setRowHeight

public void setRowHeight(int row,
                         double height)

Sets the height of one row.

Parameters:

row - The row (0 <= startRow < Worksheets.MAX_ROWS).

height - The new height.

setRowHeight

public void setRowHeight(int startRow,
                         int endRow,
                         double height)

Sets the height of a range of rows.

Parameters:

startRow - The row (0 <= startRow < Worksheets.MAX_ROWS).

endRow - The row (startRow <= endRow < Worksheets.MAX_ROWS).

height - The new height (height >= 0.0).

addCell

public void addCell(Cell cell)

Sets the value at the specified cell.

Parameters:

cell - The new cell.

addName

public void addName(java.lang.String name,
                    java.lang.String value)

Adds a name to the worksheet. For now, there is no validation performed here, so be careful what you add.

Parameters:

name - The new name.

value - The value that the name resolves to.

addStyleRegion

public void addStyleRegion(StyleRegion styleRegion)

Adds the specified style region.

Note: this method will not adjust row sizes for the particular style that is being applied. Not sure what is the best way to achieve this...

Parameters:

styleRegion - The style region being added.

setStyle

public void setStyle(Style style,
                     int startColumn,
                     int startRow,
                     int endColumn,
                     int endRow)

Set the style for the specified range of the worksheet.

Note: this method will not adjust row sizes for the particular style that is being applied. Not sure what is the best way to achieve this...

Parameters:

style - The style to be applied.

startColumn - The start column for the style region.

startRow - The start row for the style region.

endColumn - The end column for the style region.

endRow - The end row for the style region.

putDate

public void putDate(com.jrefinery.workbook.SerialDate date,
                    int column,
                    int row)

Puts a date in a cell, overwriting any existing cell contents.

Parameters:

date - The date.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

putFormula

public void putFormula(java.lang.String formula,
                       int column,
                       int row)

Puts a formula in a cell, overwriting any existing cell contents.

Parameters:

formula - The formula (not validated).

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

putLabel

public void putLabel(java.lang.String label,
                     int column,
                     int row)

Puts a label in a cell, overwriting any existing cell contents.

Parameters:

label - The label.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

putValue

public void putValue(double value,
                     int column,
                     int row)

Puts a value in a cell, overwriting any existing cell contents.

Parameters:

value - The value.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

putComment

public void putComment(java.lang.String comment,
                       int column,
                       int row)

Puts a comment in a cell, overwriting any existing cell comments.

Parameters:

comment - The comment.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyFont

public void applyFont(StyleFont font,
                      int column,
                      int row)

Applies the font to the specified cell.

This may involve restructuring the style regions.

Parameters:

font - The font.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyFont

public void applyFont(StyleFont font,
                      int startColumn,
                      int startRow,
                      int endColumn,
                      int endRow)

Applies the font to the specified region.

This may involve restructuring the style regions.

Parameters:

font - The font.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyHorizontalAlignment

public void applyHorizontalAlignment(int align,
                                     int column,
                                     int row)

Applies a horizontal alignment setting to a cell.

This may involve restructuring the style regions.

Parameters:

align - The new alignment.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyHorizontalAlignment

public void applyHorizontalAlignment(int align,
                                     int startColumn,
                                     int startRow,
                                     int endColumn,
                                     int endRow)

Applies a horizontal alignment setting to a range of cells.

This may involve restructuring the style regions.

Parameters:

align - The new alignment.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyVerticalAlignment

public void applyVerticalAlignment(int align,
                                   int column,
                                   int row)

Applies a vertical alignment setting to a cell.

This may involve restructuring the style regions.

Parameters:

align - The new alignment.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyVerticalAlignment

public void applyVerticalAlignment(int align,
                                   int startColumn,
                                   int startRow,
                                   int endColumn,
                                   int endRow)

Applies a vertical alignment setting to a range of cells.

This may involve restructuring the style regions.

Parameters:

align - The new alignment.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyWrapText

public void applyWrapText(boolean wrapText,
                          int column,
                          int row)

Applies the "wrap-text" setting to the specified cell.

This may involve restructuring the style regions.

Parameters:

wrapText - The new value of the wrap-text flag.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyWrapText

public void applyWrapText(boolean wrapText,
                          int startColumn,
                          int startRow,
                          int endColumn,
                          int endRow)

Applies the "wrap-text" setting to the specified region.

This may involve restructuring the style regions.

Parameters:

wrapText - The new value of the wrap-text flag.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyBorder

public void applyBorder(StyleBorder border,
                        int column,
                        int row)

Applies the foreground color to the specified cell.

This may involve restructuring the style regions.

Parameters:

border - The border.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyBorder

public void applyBorder(StyleBorder border,
                        int startColumn,
                        int startRow,
                        int endColumn,
                        int endRow)

Applies the border to the specified region.

This may involve restructuring the style regions.

Parameters:

border - The border.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyOutline

public void applyOutline(int column,
                         int row)

Applies a thin outline border to a cell.

Parameters:

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyOutline

public void applyOutline(int startColumn,
                         int startRow,
                         int endColumn,
                         int endRow)

Applies a thin outline border to a range of cells.

Parameters:

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyOutline

public void applyOutline(int borderStyle,
                         int column,
                         int row)

Applies a border to a cell.

applyOutline

public void applyOutline(int lineStyle,
                         int startColumn,
                         int startRow,
                         int endColumn,
                         int endRow)

Applies a border around a range of cells.

Parameters:

lineStyle - The style of line for the border.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyForegroundColor

public void applyForegroundColor(StyleColor color,
                                 int column,
                                 int row)

Applies the foreground color to the specified cell.

This may involve restructuring the style regions.

Parameters:

color - The color.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyForegroundColor

public void applyForegroundColor(StyleColor color,
                                 int startColumn,
                                 int startRow,
                                 int endColumn,
                                 int endRow)

Applies the foreground color to the specified region.

This may involve restructuring the style regions.

Parameters:

color - The color.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyBackgroundColor

public void applyBackgroundColor(StyleColor color,
                                 int column,
                                 int row)

Applies the background color to the specified cell.

This may involve restructuring the style regions.

Parameters:

color - The color.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyBackgroundColor

public void applyBackgroundColor(StyleColor color,
                                 int startColumn,
                                 int startRow,
                                 int endColumn,
                                 int endRow)

Applies the background color to the specified region.

This may involve restructuring the style regions.

Parameters:

color - The color.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

applyPatternColor

public void applyPatternColor(StyleColor color,
                              int column,
                              int row)

Applies the pattern color to the specified cell.

This may involve restructuring the style regions.

Parameters:

color - The color.

column - The column (0 <= column < Worksheet.MAX_COLUMNS).

row - The row (0 <= row < Worksheet.MAX_ROWS).

applyPatternColor

public void applyPatternColor(StyleColor color,
                              int startColumn,
                              int startRow,
                              int endColumn,
                              int endRow)

Applies the pattern color to the specified region.

This may involve restructuring the style regions.

Parameters:

color - The color.

startColumn - The start column (0 <= startColumn < Worksheet.MAX_COLUMNS).

startRow - The start row (0 <= startRow < Worksheet.MAX_ROWS).

endColumn - The end column (0 <= endColumn < Worksheet.MAX_COLUMNS).

endRow - The end row (0 <= endRow < Worksheet.MAX_ROWS).

cellReference

public static java.lang.String cellReference(int column,
                                             int row)

Returns a cell reference using letters for the column (e.g. D4) and the row number.

Note that internally we use zero based indices for the column and row numbers. But externally the user expects the first row to be number 1 (not 0).

Parameters:

column - The cell's column (0 <= column < Worksheet.MAX_COLUMNS).

row - The cell's row (0 <= row < Worksheet.MAX_ROWS).

writeWorksheet

public void writeWorksheet(java.io.PrintStream out,
                           java.lang.String indent)

Writes a worksheet element in the Gnumeric format to a stream.

Parameters:

out - The output stream.

indent - The indentation.