org.apache.poi.hssf.model
Class Sheet

java.lang.Object
  |
  +--org.apache.poi.hssf.model.Sheet

public class Sheet

extends java.lang.Object

Low level model implementation of a Sheet (one workbook contains many sheets) This file contains the low level binary records starting at the sheets BOF and ending with the sheets EOF. Use HSSFSheet for a high level representation.

The structures of the highlevel API use references to this to perform most of their operations. Its probably unwise to use these low level structures directly unless you really know what you're doing. I recommend you read the Microsoft Excel 97 Developer's Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf before even attempting to use this.

Version:

1.0-pre

Author:

Andrew C. Oliver (acoliver at apache dot org), Glen Stampoultzis (glens at apache.org)

See Also:

Workbook, HSSFSheet

Field Summary

protected ValueRecordsAggregate	cells
protected boolean	containsLabels
protected DefaultColWidthRecord	defaultcolwidth
protected DefaultRowHeightRecord	defaultrowheight
protected DimensionsRecord	dims
protected int	dimsloc
protected GridsetRecord	gridset
protected MergeCellsRecord	merged
protected int	mergedloc
protected java.util.ArrayList	records
protected RowRecordsAggregate	rows

Constructor Summary

Sheet()
Creates new Sheet with no intialization --useless at this point

Method Summary

void	addDBCellRecords() Not currently used method to calculate and add dbcell records
int	addMergedRegion(short rowFrom, short colFrom, short rowTo, short colTo)
void	addRow(RowRecord row) Adds a row record to the sheet
void	addValueRecord(short row, CellValueRecordInterface col) Adds a value record to the sheet's contained binary records (i.e.
void	checkDimsLoc(Record rec, int recloc) in the event the record is a dimensions record, resets both the loc index and dimsloc index
void	convertLabelRecords(Workbook wb) This is basically a kludge to deal with the now obsolete Label records.
BlankRecord	createBlank(short row, short col) create a BLANK record (does not add it to the records contained in this sheet)
protected Record	createBOF() creates the BOF record
protected Record	createCalcCount() creates the CalcCount record and sets it to 0x64 (default number of iterations)
protected Record	createCalcMode() creates the CalcMode record and sets it to 1 (automatic formula caculation)
protected Record	createColInfo() creates the ColumnInfo Record and sets it to a default column/width
protected Record	createDefaultColWidth() creates the DefaultColWidth Record and sets it to 8
protected Record	createDefaultRowHeight() creates the DefaultRowHeight Record and sets its options to 0 and rowheight to 0xff
protected Record	createDelta() creates the Delta record and sets it to 0.0010 (default accuracy)
protected Record	createDimensions() creates the Dimensions Record and sets it to bogus values (you should set this yourself or let the high level API do it for you)
protected Record	createEOF() creates the EOF record
protected Record	createFooter() creates the Footer Record and sets it to nothing/0 length
FormulaRecord	createFormula(short row, short col, java.lang.String formula) Attempts to parse the formula into PTGs and create a formula record DOES NOT WORK YET
protected Record	createGridset() creates the Gridset record and sets it to true (user has mucked with the gridlines)
protected Record	createGuts() creates the Guts record and sets leftrow/topcol guttter and rowlevelmax/collevelmax to 0
protected Record	createHCenter() creates the HCenter Record and sets it to false (don't horizontally center)
protected Record	createHeader() creates the Header Record and sets it to nothing/0 length
protected Record	createIndex() creates the Index record - not currently used
protected Record	createIteration() creates the Iteration record and sets it to false (don't iteratively calculate formulas)
LabelSSTRecord	createLabelSST(short row, short col, int index) Create a LABELSST Record (does not add it to the records contained in this sheet)
protected Record	createMergedCells()
NumberRecord	createNumber(short row, short col, double value) Create a NUMBER Record (does not add it to the records contained in this sheet)
protected Record	createPrintGridlines() creates the PrintGridlines record and sets it to false (that makes for ugly sheets).
protected Record	createPrintHeaders() creates the PrintHeaders record and sets it to false (we don't create headers yet so why print them)
protected Record	createPrintSetup() creates the PrintSetup Record and sets it to defaults and marks it invalid
protected Record	createRefMode() creates the RefMode record and sets it to A1 Mode (default reference mode)
RowRecord	createRow(int row) Create a row record.
protected Record	createSaveRecalc() creates the SaveRecalc record and sets it to true (recalculate before saving)
protected Record	createSelection() Creates the Selection record and sets it to nothing selected
static Sheet	createSheet() Creates a sheet with all the usual records minus values and the "index" record (not required).
static Sheet	createSheet(java.util.List records, int sheetnum) read support (offset = 0) Same as createSheet(Record[] recs, int, int) only the record offset is assumed to be 0.
static Sheet	createSheet(java.util.List recs, int sheetnum, int offset) read support (offset used as starting point for search) for low level API.
protected Record	createVCenter() creates the VCenter Record and sets it to false (don't horizontally center)
protected Record	createWindowTwo() creates the WindowTwo Record and sets it to:
protected Record	createWSBool() creates the WSBoolRecord and sets its values to defaults
Record	findFirstRecordBySid(short sid) Returns the first occurance of a record matching a particular sid.
short	getColumnWidth(short column) get the width of a given column in units of 1/20th of a point width (twips?)
short	getDefaultColumnWidth() get the default column width for the sheet (if the columns do not define their own width)
short	getDefaultRowHeight() get the default row height for the sheet (if the rows do not define their own height)
int	getDimsLoc() get the location of the DimensionsRecord (which is the last record before the value section)
GridsetRecord	getGridsetRecord() Gets the gridset record for this sheet.
int	getLoc() Returns the location pointer to the first record to look for when adding rows/values
MergeCellsRecord.MergedRegion	getMergedRegionAt(int index)
RowRecord	getNextRow() get the NEXT RowRecord (from LOC).
CellValueRecordInterface	getNextValueRecord() get the NEXT value record (from LOC).
int	getNumMergedRegions()
int	getNumRecords() Returns the number of low level binary records in this sheet.
int	getPreOffset() get the preoffset when using DBCELL records (currently unused) - this is the position of this sheet within the whole file.
java.util.List	getRecords()
RowRecord	getRow(short rownum) get the NEXT (from LOC) RowRecord where rownumber matches the given rownum.
int	getSize()
boolean	isGridsPrinted() get whether gridlines are printed.
void	removeMergedRegion(int index)
void	removeRow(RowRecord row) Removes a row record This method is not loc sensitive, it resets loc to = dimsloc so no worries.
void	removeValueRecord(short row, CellValueRecordInterface col) remove a value record from the records array.
void	replaceValueRecord(CellValueRecordInterface newval) replace a value record from the records array.
byte[]	serialize() Serializes all records in the sheet into one big byte array.
int	serialize(int offset, byte[] data) Serializes all records in the sheet into one big byte array.
void	setColumnWidth(short column, short width) set the width for a given column in 1/20th of a character width units
void	setDefaultColumnWidth(short dcw) set the default column width for the sheet (if the columns do not define their own width)
void	setDefaultRowHeight(short dch) set the default row height for the sheet (if the rows do not define their own height)
void	setDimensions(short firstrow, short firstcol, short lastrow, short lastcol) Per an earlier reported bug in working with Andy Khan's excel read library.
void	setGridsPrinted(boolean value) set whether gridlines printed or not.
void	setLoc(int loc) set the locator for where we should look for the next value record.
void	setPreOffset(int offset) Set the preoffset when using DBCELL records (currently unused) - this is the position of this sheet within the whole file.

Methods inherited from class java.lang.Object

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

Field Detail

records

protected java.util.ArrayList records

containsLabels

protected boolean containsLabels

dimsloc

protected int dimsloc

dims

protected DimensionsRecord dims

defaultcolwidth

protected DefaultColWidthRecord defaultcolwidth

defaultrowheight

protected DefaultRowHeightRecord defaultrowheight

gridset

protected GridsetRecord gridset

merged

protected MergeCellsRecord merged

mergedloc

protected int mergedloc

cells

protected ValueRecordsAggregate cells

rows

protected RowRecordsAggregate rows

Constructor Detail

Sheet

public Sheet()

Creates new Sheet with no intialization --useless at this point

See Also:

createSheet(List,int,int)

Method Detail

createSheet

public static Sheet createSheet(java.util.List recs,
                                int sheetnum,
                                int offset)

read support (offset used as starting point for search) for low level API. Pass in an array of Record objects, the sheet number (0 based) and a record offset (should be the location of the sheets BOF record). A Sheet object is constructed and passed back with all of its initialization set to the passed in records and references to those records held. This function is normally called via Workbook.

Parameters:

recs - array containing those records in the sheet in sequence (normally obtained from RecordFactory)

sheetnum - integer specifying the sheet's number (0,1 or 2 in this release)

offset - of the sheet's BOF record

Returns:

Sheet object with all values set to those read from the file

See Also:

Workbook, Record

createSheet

public static Sheet createSheet(java.util.List records,
                                int sheetnum)

read support (offset = 0) Same as createSheet(Record[] recs, int, int) only the record offset is assumed to be 0.

Parameters:

records - array containing those records in the sheet in sequence (normally obtained from RecordFactory)

sheetnum - integer specifying the sheet's number (0,1 or 2 in this release)

Returns:

Sheet object

createSheet

public static Sheet createSheet()

Creates a sheet with all the usual records minus values and the "index" record (not required). Sets the location pointer to where the first value records should go. Use this to create a sheet from "scratch".

Returns:

Sheet object with all values set to defaults

addMergedRegion

public int addMergedRegion(short rowFrom,
                           short colFrom,
                           short rowTo,
                           short colTo)

removeMergedRegion

public void removeMergedRegion(int index)

getMergedRegionAt

public MergeCellsRecord.MergedRegion getMergedRegionAt(int index)

getNumMergedRegions

public int getNumMergedRegions()

convertLabelRecords

public void convertLabelRecords(Workbook wb)

This is basically a kludge to deal with the now obsolete Label records. If you have to read in a sheet that contains Label records, be aware that the rest of the API doesn't deal with them, the low level structure only provides read-only semi-immutable structures (the sets are there for interface conformance with NO impelmentation). In short, you need to call this function passing it a reference to the Workbook object. All labels will be converted to LabelSST records and their contained strings will be written to the Shared String tabel (SSTRecord) within the Workbook.

Parameters:

wb - sheet's matching low level Workbook structure containing the SSTRecord.

See Also:

LabelRecord, LabelSSTRecord, SSTRecord

getNumRecords

public int getNumRecords()

Returns the number of low level binary records in this sheet. This adjusts things for the so called AgregateRecords.

See Also:

Record

setDimensions

public void setDimensions(short firstrow,
                          short firstcol,
                          short lastrow,
                          short lastcol)

Per an earlier reported bug in working with Andy Khan's excel read library. This sets the values in the sheet's DimensionsRecord object to be correct. Excel doesn't really care, but we want to play nice with other libraries.

See Also:

DimensionsRecord

setLoc

public void setLoc(int loc)

set the locator for where we should look for the next value record. The algorythm will actually start here and find the correct location so you can set this to 0 and watch performance go down the tubes but it will work. After a value is set this is automatically advanced. Its also set by the create method. So you probably shouldn't mess with this unless you have a compelling reason why or the help for the method you're calling says so. Check the other methods for whether they care about the loc pointer. Many of the "modify" and "remove" methods re-initialize this to "dimsloc" which is the location of the Dimensions Record and presumably the start of the value section (at or around 19 dec).

Parameters:

loc - the record number to start at

getLoc

public int getLoc()

Returns the location pointer to the first record to look for when adding rows/values

setPreOffset

public void setPreOffset(int offset)

Set the preoffset when using DBCELL records (currently unused) - this is the position of this sheet within the whole file.

Parameters:

offset - the offset of the sheet's BOF within the file.

getPreOffset

public int getPreOffset()

get the preoffset when using DBCELL records (currently unused) - this is the position of this sheet within the whole file.

Returns:

offset the offset of the sheet's BOF within the file.

serialize

public byte[] serialize()

Serializes all records in the sheet into one big byte array. Use this to write the sheet out.

Returns:

byte[] array containing the binary representation of the records in this sheet

serialize

public int serialize(int offset,
                     byte[] data)

Serializes all records in the sheet into one big byte array. Use this to write the sheet out.

Parameters:

offset - to begin write at

data - array containing the binary representation of the records in this sheet

createRow

public RowRecord createRow(int row)

Create a row record. (does not add it to the records contained in this sheet)

Parameters:

row - number

Returns:

RowRecord created for the passed in row number

See Also:

RowRecord

createLabelSST

public LabelSSTRecord createLabelSST(short row,
                                     short col,
                                     int index)

Create a LABELSST Record (does not add it to the records contained in this sheet)

Parameters:

row - the row the LabelSST is a member of

col - the column the LabelSST defines

index - the index of the string within the SST (use workbook addSSTString method)

Returns:

LabelSSTRecord newly created containing your SST Index, row,col.

See Also:

SSTRecord

createNumber

public NumberRecord createNumber(short row,
                                 short col,
                                 double value)

Create a NUMBER Record (does not add it to the records contained in this sheet)

Parameters:

row - the row the NumberRecord is a member of

col - the column the NumberRecord defines

value - for the number record

Returns:

NumberRecord for that row, col containing that value as added to the sheet

createBlank

public BlankRecord createBlank(short row,
                               short col)

create a BLANK record (does not add it to the records contained in this sheet)

Parameters:

row - - the row the BlankRecord is a member of

col - - the column the BlankRecord is a member of

createFormula

public FormulaRecord createFormula(short row,
                                   short col,
                                   java.lang.String formula)

Attempts to parse the formula into PTGs and create a formula record DOES NOT WORK YET

Parameters:

row - - the row for the formula record

col - - the column of the formula record

formula - - a String representing the formula. To be parsed to PTGs

Returns:

bogus/useless formula record

addValueRecord

public void addValueRecord(short row,
                           CellValueRecordInterface col)

Adds a value record to the sheet's contained binary records (i.e. LabelSSTRecord or NumberRecord).

This method is "loc" sensitive. Meaning you need to set LOC to where you want it to start searching. If you don't know do this: setLoc(getDimsLoc). When adding several rows you can just start at the last one by leaving loc at what this sets it to.

Parameters:

row - the row to add the cell value to

col - the cell value record itself.

removeValueRecord

public void removeValueRecord(short row,
                              CellValueRecordInterface col)

remove a value record from the records array. This method is not loc sensitive, it resets loc to = dimsloc so no worries.

Parameters:

row - - the row of the value record you wish to remove

col - - a record supporting the CellValueRecordInterface.

See Also:

CellValueRecordInterface

replaceValueRecord

public void replaceValueRecord(CellValueRecordInterface newval)

replace a value record from the records array. This method is not loc sensitive, it resets loc to = dimsloc so no worries.

Parameters:

newval - - a record supporting the CellValueRecordInterface. this will replace the cell value with the same row and column. If there isn't one, one will be added.

addRow

public void addRow(RowRecord row)

Adds a row record to the sheet

This method is "loc" sensitive. Meaning you need to set LOC to where you want it to start searching. If you don't know do this: setLoc(getDimsLoc). When adding several rows you can just start at the last one by leaving loc at what this sets it to.

Parameters:

row - the row record to be added

See Also:

setLoc(int)

removeRow

public void removeRow(RowRecord row)

Removes a row record This method is not loc sensitive, it resets loc to = dimsloc so no worries.

Parameters:

row - the row record to remove

getNextValueRecord

public CellValueRecordInterface getNextValueRecord()

get the NEXT value record (from LOC). The first record that is a value record (starting at LOC) will be returned.

This method is "loc" sensitive. Meaning you need to set LOC to where you want it to start searching. If you don't know do this: setLoc(getDimsLoc). When adding several rows you can just start at the last one by leaving loc at what this sets it to. For this method, set loc to dimsloc to start with, subsequent calls will return values in (physical) sequence or NULL when you get to the end.

Returns:

CellValueRecordInterface representing the next value record or NULL if there are no more

See Also:

setLoc(int)

getNextRow

public RowRecord getNextRow()

get the NEXT RowRecord (from LOC). The first record that is a Row record (starting at LOC) will be returned.

This method is "loc" sensitive. Meaning you need to set LOC to where you want it to start searching. If you don't know do this: setLoc(getDimsLoc). When adding several rows you can just start at the last one by leaving loc at what this sets it to. For this method, set loc to dimsloc to start with. subsequent calls will return rows in (physical) sequence or NULL when you get to the end.

Returns:

RowRecord representing the next row record or NULL if there are no more

See Also:

setLoc(int)

getRow

public RowRecord getRow(short rownum)

get the NEXT (from LOC) RowRecord where rownumber matches the given rownum. The first record that is a Row record (starting at LOC) that has the same rownum as the given rownum will be returned.

This method is "loc" sensitive. Meaning you need to set LOC to where you want it to start searching. If you don't know do this: setLoc(getDimsLoc). When adding several rows you can just start at the last one by leaving loc at what this sets it to. For this method, set loc to dimsloc to start with. subsequent calls will return rows in (physical) sequence or NULL when you get to the end.

Parameters:

rownum - which row to return (careful with LOC)

Returns:

RowRecord representing the next row record or NULL if there are no more

See Also:

setLoc(int)

addDBCellRecords

public void addDBCellRecords()

Not currently used method to calculate and add dbcell records

createBOF

protected Record createBOF()

creates the BOF record

Returns:

record containing a BOFRecord

See Also:

BOFRecord, Record

createIndex

protected Record createIndex()

creates the Index record - not currently used

Returns:

record containing a IndexRecord

See Also:

IndexRecord, Record

createCalcMode

protected Record createCalcMode()

creates the CalcMode record and sets it to 1 (automatic formula caculation)

Returns:

record containing a CalcModeRecord

See Also:

CalcModeRecord, Record

createCalcCount

protected Record createCalcCount()

creates the CalcCount record and sets it to 0x64 (default number of iterations)

Returns:

record containing a CalcCountRecord

See Also:

CalcCountRecord, Record

createRefMode

protected Record createRefMode()

creates the RefMode record and sets it to A1 Mode (default reference mode)

Returns:

record containing a RefModeRecord

See Also:

RefModeRecord, Record

createIteration

protected Record createIteration()

creates the Iteration record and sets it to false (don't iteratively calculate formulas)

Returns:

record containing a IterationRecord

See Also:

IterationRecord, Record

createDelta

protected Record createDelta()

creates the Delta record and sets it to 0.0010 (default accuracy)

Returns:

record containing a DeltaRecord

See Also:

DeltaRecord, Record

createSaveRecalc

protected Record createSaveRecalc()

creates the SaveRecalc record and sets it to true (recalculate before saving)

Returns:

record containing a SaveRecalcRecord

See Also:

SaveRecalcRecord, Record

createPrintHeaders

protected Record createPrintHeaders()

creates the PrintHeaders record and sets it to false (we don't create headers yet so why print them)

Returns:

record containing a PrintHeadersRecord

See Also:

PrintHeadersRecord, Record

createPrintGridlines

protected Record createPrintGridlines()

creates the PrintGridlines record and sets it to false (that makes for ugly sheets). As far as I can tell this does the same thing as the GridsetRecord

Returns:

record containing a PrintGridlinesRecord

See Also:

PrintGridlinesRecord, Record

createGridset

protected Record createGridset()

creates the Gridset record and sets it to true (user has mucked with the gridlines)

Returns:

record containing a GridsetRecord

See Also:

GridsetRecord, Record

createGuts

protected Record createGuts()

creates the Guts record and sets leftrow/topcol guttter and rowlevelmax/collevelmax to 0

Returns:

record containing a GutsRecordRecord

See Also:

GutsRecord, Record

createDefaultRowHeight

protected Record createDefaultRowHeight()

creates the DefaultRowHeight Record and sets its options to 0 and rowheight to 0xff

Returns:

record containing a DefaultRowHeightRecord

See Also:

DefaultRowHeightRecord, Record

createWSBool

protected Record createWSBool()

creates the WSBoolRecord and sets its values to defaults

Returns:

record containing a WSBoolRecord

See Also:

WSBoolRecord, Record

createHeader

protected Record createHeader()

creates the Header Record and sets it to nothing/0 length

Returns:

record containing a HeaderRecord

See Also:

HeaderRecord, Record

createFooter

protected Record createFooter()

creates the Footer Record and sets it to nothing/0 length

Returns:

record containing a FooterRecord

See Also:

FooterRecord, Record

createHCenter

protected Record createHCenter()

creates the HCenter Record and sets it to false (don't horizontally center)

Returns:

record containing a HCenterRecord

See Also:

HCenterRecord, Record

createVCenter

protected Record createVCenter()

creates the VCenter Record and sets it to false (don't horizontally center)

Returns:

record containing a VCenterRecord

See Also:

VCenterRecord, Record

createPrintSetup

protected Record createPrintSetup()

creates the PrintSetup Record and sets it to defaults and marks it invalid

Returns:

record containing a PrintSetupRecord

See Also:

PrintSetupRecord, Record

createDefaultColWidth

protected Record createDefaultColWidth()

creates the DefaultColWidth Record and sets it to 8

Returns:

record containing a DefaultColWidthRecord

See Also:

DefaultColWidthRecord, Record

createColInfo

protected Record createColInfo()

creates the ColumnInfo Record and sets it to a default column/width

Returns:

record containing a ColumnInfoRecord

See Also:

ColumnInfoRecord

getDefaultColumnWidth

public short getDefaultColumnWidth()

get the default column width for the sheet (if the columns do not define their own width)

Returns:

default column width

isGridsPrinted

public boolean isGridsPrinted()

get whether gridlines are printed.

Returns:

true if printed

setGridsPrinted

public void setGridsPrinted(boolean value)

set whether gridlines printed or not.

Parameters:

value - True if gridlines printed.

setDefaultColumnWidth

public void setDefaultColumnWidth(short dcw)

set the default column width for the sheet (if the columns do not define their own width)

Parameters:

dcw - default column width

setDefaultRowHeight

public void setDefaultRowHeight(short dch)

set the default row height for the sheet (if the rows do not define their own height)

getDefaultRowHeight

public short getDefaultRowHeight()

get the default row height for the sheet (if the rows do not define their own height)

Returns:

default row height

getColumnWidth

public short getColumnWidth(short column)

get the width of a given column in units of 1/20th of a point width (twips?)

Parameters:

column - index

Returns:

column width in units of 1/20th of a point (twips?)

See Also:

DefaultColWidthRecord, ColumnInfoRecord, setColumnWidth(short,short)

setColumnWidth

public void setColumnWidth(short column,
                           short width)

set the width for a given column in 1/20th of a character width units

Parameters:

column - - the column number

width - (in units of 1/20th of a character width)

createDimensions

protected Record createDimensions()

creates the Dimensions Record and sets it to bogus values (you should set this yourself or let the high level API do it for you)

Returns:

record containing a DimensionsRecord

See Also:

DimensionsRecord, Record

createWindowTwo

protected Record createWindowTwo()

creates the WindowTwo Record and sets it to:

options = 0x6b6

toprow = 0

leftcol = 0

headercolor = 0x40

pagebreakzoom = 0x0

normalzoom = 0x0

Returns:

record containing a WindowTwoRecord

See Also:

WindowTwoRecord, Record

createSelection

protected Record createSelection()

Creates the Selection record and sets it to nothing selected

Returns:

record containing a SelectionRecord

See Also:

SelectionRecord, Record

createMergedCells

protected Record createMergedCells()

createEOF

protected Record createEOF()

creates the EOF record

Returns:

record containing a EOFRecord

See Also:

EOFRecord, Record

getDimsLoc

public int getDimsLoc()

get the location of the DimensionsRecord (which is the last record before the value section)

Returns:

location in the array of records of the DimensionsRecord

checkDimsLoc

public void checkDimsLoc(Record rec,
                         int recloc)

in the event the record is a dimensions record, resets both the loc index and dimsloc index

getSize

public int getSize()

getRecords

public java.util.List getRecords()

getGridsetRecord

public GridsetRecord getGridsetRecord()

Gets the gridset record for this sheet.

findFirstRecordBySid

public Record findFirstRecordBySid(short sid)

Returns the first occurance of a record matching a particular sid.