jet.report.xls
Class Worksheet

java.lang.Object
  jet.report.xls.Worksheet

public class Worksheet
extends java.lang.Object

It specifies a WorkSheet of a WorkBook.

 GlobalWorksheet gws = null;//get it from the Workbook object
 Worksheet sheet = new Worksheet( null );
 int r = 1, c = 1;
 double v = 99.8;
 Xf xf = new Xf();
 int xfIndx = gws.getXfIndex( xf );
 BNumber number = new BNumber(r, c, xfIndx, v);
 sheet.addCell( number );//write a number cell to the second row and the second column cell
        
 r = 2;
 v = 99.888;
 xf = new Xf();
 xf.setFormatIndex( gws.getFormatIndex( "($#,##0.00_);[Red]($#,##0.00)", null ) );//set the cell format as "($#,##0.00_);[Red]($#,##0.00)".
 number = new BNumber( r, c, gws.getXfIndex( xf ), v );
 sheet.addCell( number );
                
 r = 3;
 xf = new Xf();
 BFont font = new BFont();
 font.setFontName( "Wingdings 3" );//set the font name as Wingdings 3
 font.setFontHeight( ( short )(15*20) );//set the font height as 15 point
 font.setBoldStyle( true );//set the font as bold
        
 xf.setFontIndex( ( short ) gws.getFontIndex( font ) );         
 xfIndx = gws.getXfIndex( xf );
 BLabel label = new BLabel(r, c, xfIndx, "Jinfonet", null );//create a label cell, and its position is the fourth row, second column
 sheet.addLabel( label, gws );

 r = 8;
 xf = new Xf();
 font = new BFont();
 font.setColor( ColorConstants.RED );//set the font color as red
 //font.setColor( ( short ) gws.getColorIndex( Color.red.getRGB() & 0x00ffffff ) );//you can also set the font color as red with this method
 

Note: There are two formats to get the Color index:

1. You can get the color index by this method GlobalWorksheet.getColorIndex(int).
2. You can get the color index by using the constant value in the ColorConstants.

For the first method, you should add these colors to GlobalWorksheet using the method GlobalWorksheet.addColor(int) and invoke the method GlobalWorksheet.makePalette() after all colors are added before invoking GlobalWorksheet.getColorIndex(int).

These two formats can not be used at the same time. Otherwise, the result might be incorrect. In other words, you should select one of them to use.

 xf.setFontIndex( ( short ) gws.getFontIndex( font ) );         
 xfIndx = gws.getXfIndex( xf );
 
 BLabel label = new BLabel(r, c, xfIndx, "This is Red Jinfonet", null );//create a label cell, and its position is the fourth row, the second column
 sheet.addLabel( label, gws ); 
 

Field Summary

static byte	X It is an index used to get the freeze panel column position.
static byte	Y It is an index used to get the freeze panel row position.

Constructor Summary

Worksheet(java.lang.String encoding)
Creates an empty Worksheet object with all the fields empty.

Method Summary

void	addCell(Blank cell) Adds a cell to the Worksheet.
void	addChart(ChartInfo chartInfo, GlobalWorksheet gws, java.lang.String encoding) Adds a chart to the Worksheet.
java.lang.StringBuffer	addFormula(Formula formula, GlobalWorksheet gws) Adds a formula to the Worksheet.
void	addHyperLink(jet.report.xls.HLink link) Adds a Label hyperlink object to this object.
void	addHyperLink(jet.report.xls.HLink link, GlobalWorksheet gws, java.lang.String encoding, BFont font, boolean defFont) Adds a HyperLink in the sheet.
void	addLabel(BLabel cell, GlobalWorksheet gws) Adda a Label record to the Worksheet.
int	addMergeRegion(Region region) Adds a merged region.
void	addPicture(GlobalWorksheet gws, java.lang.String path, jet.report.xls.ImgInfo imgInfo) Adds a picture to the Worksheet.
void	addPicture(GlobalWorksheet gws, java.lang.String path, jet.report.xls.ImgInfo imgInfo, jet.report.xls.HLink link) Adds a picture with link format to the Worksheet .
void	addShapes(GlobalWorksheet gws, jet.report.xls.ShapesInfo shapesInfo) Adds a shape to the Worksheet.
void	clearFreeze() Unfreezes panes.
void	converLabel() Converts the Label record to LabelSST record in the Worksheet for share label in the whole worksheet.
short	getCalcMode() Gets the calculation mode.
short	getColumnWidth(short column) Gets the width of a given column in the unit of 1/20th of a point width (twips?).
short	getDefaultRowHeight() Gets the default row height.
short	getDefaultRowHeightOptions() Gets the default row height option
short	getDefColWidth() Gets the default width of the column.
java.lang.String	getFooter() Gets the print footer string.
short[]	getFrozen() Gets the freezing position.
boolean	getGridLine() Gets whether the window should display gridlines.
boolean	getGridset() Gets whether the user has ever changed the setting of the Gridlines option.
boolean	getHCenter() Gets whether the sheet is to be centered between horizontal margins when printed.
java.lang.String	getHeader() Gets the print header string.
double	getMaxChange() Gets the Maximum iteration changes.
Region	getMergeRegionAt(int index) Gets a special merged region.
int	getNumMergeRegions() Gets the number of all merged regions.
boolean	getPageBreak() Gets whether to have the window display the page break option.
boolean	getPrintGridlines() Gets whether to print gridlines.
boolean	getPrintHeaders() Gets whether to print row and column heading.
boolean	getRecalc() Gets whether to recalculate before saving.
short	getRefMode() Gets the Reference mode.
java.lang.String	getSheetName() Gets the Worksheet name.
boolean	getVCenter() Gets whether the sheet is to be centered between vertical margins when printed.
short	getWSBool() Gets the WSBool option flag.
boolean	isIteration() Gets the Iteration option.
void	modifyMsoDrawing(int increase) Modifies the MsoDrawing record.
void	removeMergeRegion(int index) Removes a merged region.
void	setCalcMode(short mode) Sets the calculation mode.
void	setColumnWidth(short column, short width) Sets the width of a given column in the unit of 1/20th of a character width.
void	setDefaultRowHeight(short rowheight) Sets the default row height.
void	setDefaultRowHeightOptions(short flags) Sets the default row height option.
void	setDefColWidth(short colwidth) Sets the default width of the column.
void	setFooter(java.lang.String printPageFooter) Sets print footer string and the length of the footer string.
void	setFreezePane(short x, short y) Sets to freeze Panes.
void	setGridLine(boolean bool) Sets whether the window should display gridlines.
void	setGridset(boolean bool) Sets whether the user has ever changed the setting of the Gridlines option.
void	setHCenter(boolean center) Sets whether the sheet is to be centered between horizontal margins when printed.
void	setHeader(java.lang.String printPageHeader) Sets the print header string and the length of the header string.
void	setIteration(boolean bool) Sets the Iteration option.
void	setMaxChange(double change) Sets the Maximum iteration changes.
void	setPageBreak(boolean bool) Sets whether to have the window display the page break option.
void	setPrintGridlines(boolean print) Sets whether to print gridlines.
void	setPrintHeaders(boolean print) Sets whether to print row and column heading.
void	setPrintLandscape(boolean isLandscape) Sets the Print Landscape according to the flag.
void	setRecalc(boolean recalc) Sets whether to recalculate before saving.
void	setRefMode(short mode) Sets the Reference mode.
void	setRowHeight(int rowNum, short height) Sets the height of the special row.
void	setSheetName(java.lang.String sheetname) Sets the Worksheet name.
void	setVCenter(boolean center) Sets whether the sheet is to be centered between vertical margins when printed.
void	setWSBool(short flag) Sets the WSBool option flag.
void	setZoomValue(int value) Sets the zoom value.

Methods inherited from class java.lang.Object

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

Field Detail

X

public static final byte X

It is an index used to get the freeze panel column position.

See Also:

Constant Field Values

Y

public static final byte Y

It is an index used to get the freeze panel row position.

See Also:

Constant Field Values

Constructor Detail

Worksheet

public Worksheet(java.lang.String encoding)

Creates an empty Worksheet object with all the fields empty.

Parameters:

encoding - the encoding parameter can be null or whatever, we don't use it now

Method Detail

setSheetName

public void setSheetName(java.lang.String sheetname)

Sets the Worksheet name.

Parameters:

sheetname - The name of the Worksheet.

setZoomValue

public void setZoomValue(int value)

Sets the zoom value.

Parameters:

value - the max value is 400 and the min value is 10 That is, the value scope is from 10% to 400%.

setPrintLandscape

public void setPrintLandscape(boolean isLandscape)

Sets the Print Landscape according to the flag.

Parameters:

isLandscape -

setFreezePane

public void setFreezePane(short x,
                          short y)

Sets to freeze Panes.

Parameters:

x - the column index

y - the row index

clearFreeze

public void clearFreeze()

Unfreezes panes.

getFrozen

public short[] getFrozen()

Gets the freezing position. If the worksheet is unfrozen, the return value will be null. You can get the coordinate x value through ret[Worksheet.X] and y value through ret[Worksheet.Y] if the ret is not null.

Returns:

shrot[] ret

getSheetName

public java.lang.String getSheetName()

Gets the Worksheet name.

addLabel

public void addLabel(BLabel cell,
                     GlobalWorksheet gws)

Adda a Label record to the Worksheet.

Parameters:

cell - the Label to be added

gws - the GloabalWorksheet in which the Sst is to be added

addHyperLink

public void addHyperLink(jet.report.xls.HLink link)

Adds a Label hyperlink object to this object. We did not add a label to this position.

Parameters:

link -

addHyperLink

public void addHyperLink(jet.report.xls.HLink link,
                         GlobalWorksheet gws,
                         java.lang.String encoding,
                         BFont font,
                         boolean defFont)

Adds a HyperLink in the sheet.

Parameters:

link - the HyperLink to be added

gws - the GloabalWorksheet of the workbook

defFont - whether to use the default font of the HyperLink to the cell in which to add the HyperLink

converLabel

public void converLabel()

Converts the Label record to LabelSST record in the Worksheet for share label in the whole worksheet.

addCell

public void addCell(Blank cell)

Adds a cell to the Worksheet.

Parameters:

cell - the cell to be added, which can be BNumber or BoolErr

addFormula

public java.lang.StringBuffer addFormula(Formula formula,
                                         GlobalWorksheet gws)
                                  throws java.io.IOException

Adds a formula to the Worksheet.

Parameters:

formula - the formula to be added

Throws:

java.io.IOException

modifyMsoDrawing

public void modifyMsoDrawing(int increase)

Modifies the MsoDrawing record.

addPicture

public void addPicture(GlobalWorksheet gws,
                       java.lang.String path,
                       jet.report.xls.ImgInfo imgInfo)

Adds a picture to the Worksheet.

Parameters:

gws - the GlobalWorksheet object

path - the image path, such as "pic.jpg"

imgInfo - the ImgInfo object

addPicture

public void addPicture(GlobalWorksheet gws,
                       java.lang.String path,
                       jet.report.xls.ImgInfo imgInfo,
                       jet.report.xls.HLink link)

Adds a picture with link format to the Worksheet .

Parameters:

gws - the GlobalWorksheet object

path - the image path, such as "pic.jpg"

imgInfo - the ImgInfo object

link - the link info

addShapes

public void addShapes(GlobalWorksheet gws,
                      jet.report.xls.ShapesInfo shapesInfo)

Adds a shape to the Worksheet.

Parameters:

gws - the GlobalWorksheetobject

shapesInfo - the ShapesInfo object

addChart

public void addChart(ChartInfo chartInfo,
                     GlobalWorksheet gws,
                     java.lang.String encoding)
              throws java.io.IOException

Adds a chart to the Worksheet.

Parameters:

chartInfo - the information of the chart to be addded

Throws:

java.io.IOException

setCalcMode

public void setCalcMode(short mode)

Sets the calculation mode.

Parameters:

mode - the calculation mode, 0 for manual, 1 for automatic -1 for automatic, except for tables

getCalcMode

public short getCalcMode()

Gets the calculation mode.

Returns:

the calculation mode, 0 for manual, 1 for automatic; -1 for automatic, except for tables

setRefMode

public void setRefMode(short mode)

Sets the Reference mode.

Parameters:

mode - the Reference mode, 0 for R1C1 mode and 1 for A1 mode

getRefMode

public short getRefMode()

Gets the Reference mode.

Returns:

the Reference mode, 0 for R1C1 mode and 1 for A1 mode

setIteration

public void setIteration(boolean bool)

Sets the Iteration option.

Parameters:

bool - no matter the Iteration option is on or not, 0 is for off and 1 is for on

isIteration

public boolean isIteration()

Gets the Iteration option.

Returns:

no matter the Iteration option is on or not, 0 is for off and 1 is for on

setMaxChange

public void setMaxChange(double change)

Sets the Maximum iteration changes.

Parameters:

change - changes of the maximum iteration

getMaxChange

public double getMaxChange()

Gets the Maximum iteration changes.

Returns:

changes of the maximum iteration

setRecalc

public void setRecalc(boolean recalc)

Sets whether to recalculate before saving.

Parameters:

recalc - whether or not to recalculate before saving

getRecalc

public boolean getRecalc()

Gets whether to recalculate before saving.

Returns:

whether or not to recalculate before saving

setPrintHeaders

public void setPrintHeaders(boolean print)

Sets whether to print row and column heading.

Parameters:

print - whether to print row and column heading or not

getPrintHeaders

public boolean getPrintHeaders()

Gets whether to print row and column heading.

Returns:

whether to print row and column heading or not

setPrintGridlines

public void setPrintGridlines(boolean print)

Sets whether to print gridlines.

Parameters:

print - whether to print gridlines or not

getPrintGridlines

public boolean getPrintGridlines()

Gets whether to print gridlines.

Returns:

whether to print gridlines or not

setGridset

public void setGridset(boolean bool)

Sets whether the user has ever changed the setting of the Gridlines option.

Parameters:

bool - whether the user has ever changed the setting of the Gridlines option or not

getGridset

public boolean getGridset()

Gets whether the user has ever changed the setting of the Gridlines option.

Returns:

whether the user has ever changed the setting of the Gridlines option or not

setPageBreak

public void setPageBreak(boolean bool)

Sets whether to have the window display the page break option.

Parameters:

bool - whether to have window display page break option or not

getPageBreak

public boolean getPageBreak()

Gets whether to have the window display the page break option.

Returns:

whether to have window display page break option or not

setGridLine

public void setGridLine(boolean bool)

Sets whether the window should display gridlines.

Parameters:

bool - whether the window should display gridlines

getGridLine

public boolean getGridLine()

Gets whether the window should display gridlines.

Returns:

whether the window should display gridlines or not

setDefaultRowHeight

public void setDefaultRowHeight(short rowheight)

Sets the default row height.

Parameters:

rowheight - the default row height

getDefaultRowHeight

public short getDefaultRowHeight()

Gets the default row height.

Returns:

the default row height

setDefaultRowHeightOptions

public void setDefaultRowHeightOptions(short flags)

Sets the default row height option.

Parameters:

flags - the default row height option The value of the flags can be INCOMPATIBLE, ZERO, EXASC or EXDSC in class DefRowHeight or the combined value of them.

getDefaultRowHeightOptions

public short getDefaultRowHeightOptions()

Gets the default row height option

Returns:

the default row height option The value of the flags can be INCOMPATIBLE, ZERO, EXASC or EXDSC in class DefRowHeight or the combined value of them.

setWSBool

public void setWSBool(short flag)

Sets the WSBool option flag.

Parameters:

flag - the WSBool option flag The value of the flags can be SHOWAUTOBREAKS, DIALOG, APPLYSTYLES, RWSUMSBELOW, COLSUMSRIGHT, FITTOPAGE, DSPGUTS, AEE, AFE in class WSBool or the combined value of them.

getWSBool

public short getWSBool()

Gets the WSBool option flag.

Returns:

the WSBool option flag The return value of this method can be SHOWAUTOBREAKS, DIALOG, APPLYSTYLES, RWSUMSBELOW, COLSUMSRIGHT, FITTOPAGE, DSPGUTS, AEE, AFE in class WSBool or the combined value of them.

setHeader

public void setHeader(java.lang.String printPageHeader)

Sets the print header string and the length of the header string.

Parameters:

printPageHeader - the header string

getHeader

public java.lang.String getHeader()

Gets the print header string.

Returns:

the header string

setFooter

public void setFooter(java.lang.String printPageFooter)

Sets print footer string and the length of the footer string.

Parameters:

printPageFooter - the footer string

getFooter

public java.lang.String getFooter()

Gets the print footer string.

Returns:

the footer string

setHCenter

public void setHCenter(boolean center)

Sets whether the sheet is to be centered between horizontal margins when printed.

Parameters:

center - whether the sheet is to be centered between horizontal margins when printed

getHCenter

public boolean getHCenter()

Gets whether the sheet is to be centered between horizontal margins when printed.

Returns:

whether the sheet is to be centered between horizontal margins when printed

setVCenter

public void setVCenter(boolean center)

Sets whether the sheet is to be centered between vertical margins when printed.

Parameters:

center - whether the sheet is to be centered between vertical margins when printed

getVCenter

public boolean getVCenter()

Gets whether the sheet is to be centered between vertical margins when printed.

Returns:

whether the sheet is to be centered between vertical margins when printed

setDefColWidth

public void setDefColWidth(short colwidth)

Sets the default width of the column.

Parameters:

colwidth - the default width of the column

getDefColWidth

public short getDefColWidth()

Gets the default width of the column.

Returns:

the default width of the column

getColumnWidth

public short getColumnWidth(short column)

Gets the width of a given column in the unit of 1/20th of a point width (twips?).

Returns:

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

setColumnWidth

public void setColumnWidth(short column,
                           short width)

Sets the width of a given column in the unit of 1/20th of a character width. If the default column width is not 8, the method will be unuseful. So if you want to set the column width, you must insure that getDefColWidth() is 8.

Parameters:

column - - the column number

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

addMergeRegion

public int addMergeRegion(Region region)

Adds a merged region.

Parameters:

region - the merged region

removeMergeRegion

public void removeMergeRegion(int index)

Removes a merged region.

Parameters:

index - the index of the merged region to be removed

getMergeRegionAt

public Region getMergeRegionAt(int index)

Gets a special merged region.

Parameters:

index - the index of the merged region to be gotten

getNumMergeRegions

public int getNumMergeRegions()

Gets the number of all merged regions.

Returns:

the number of all merged regions

setRowHeight

public void setRowHeight(int rowNum,
                         short height)

Sets the height of the special row.

Parameters:

rowNum - the row number to set

height - the row height to set