Home  Products  Purchase  Downloads  Demos  Forums  Blogs  Ticket  Wiki  API  Corporate

com.aspose.cells
Class Worksheet

java.lang.Object
  extended by com.aspose.cells.Worksheet

public final class Worksheet
extends java.lang.Object

Represents a worksheet.


Method Summary
 void addPageBreaks(int row, int column)
          Adds a page break to the worksheet.
 void addPageBreaks(java.lang.String cellName)
          Adds a page break to the worksheet.
 void deleteColumns(int startIndex, int deletedColumns, boolean bShift)
          Removes columns from the worksheet.
 void deleteRows(int startIndex, int deletedRows, boolean bShift)
          Removes rows from the worksheet.
 void freezePanes(int row, int column, int freezedRows, int freezedColumns)
          Freezes panes at the specified cell in the worksheet.
 void freezePanes(java.lang.String cellName, int freezedRows, short freezedColumns)
          Freezes panes at the specified cell in the worksheet.
 Cell getCell(int rowIndex, int columnIndex)
          Gets the cell object with the specified row index and column index.
 Cell getCell(java.lang.String cellName)
          Gets the cell object by the cell name(like "A1").
 Style getColumnStyle(int columnIndex)
          Gets the style setting for the specified column.
 float getColumnWidth(int columnIndex)
          Gets the width of the column at the given column index, in unit of characters using default font.
 ConditionalFormattings getConditionalFormattings()
          Gets the ConditionalFormattings in the worksheet.
 float getDefaultColumnWidth()
          Returns the default column width of the worksheet, in unit of characters with default font.
 float getDefaultRowHeight()
          Gets the default row height of the worksheet in points.
 int getFirstRowIndex()
          Returns the first defined row index.
 short getFirstVisibleColumn()
          Gets first visible column index.
 int getFirstVisibleRow()
          Gets first visible row index.
 HPageBreaks getHPageBreaks()
          Get the HPageBreaks in the worksheet.
 Hyperlinks getHyperlinks()
          Get the Hyperlinks in the worksheet.
 int getLastRowIndex()
          Returns the last defined row index.
 java.util.ArrayList getMergedCells()
          Gets the collection of merged cell ranges in the worksheet.
 java.lang.String getName()
          Returns the name of the worksheet.
 PageSetup getPageSetup()
          Get the page setup options in the worksheet.
 Protection getProtection()
          Gets the current protection settings.
 Row getRow(int rowIndex)
          Gets the row at the specified row index.
 java.util.Iterator getRowIterator()
          Returns an iterator which can iterate all defined rows in this worksheet.
 Color getTabColor()
          Gets the colour of the tab below the sheet containing the sheet name.
 short getType()
          Returns the type of the worksheet.
 Validations getValidations()
          Get the Validations in the worksheet.
 VPageBreaks getVPageBreaks()
          Get the VPageBreaks in the worksheet.
 short getZoom()
          Gets the scaling factor in percent.
 void groupColumns(int startIndex, int endIndex)
          Groups columns.
 void groupColumns(int startIndex, int endIndex, boolean isHidden)
          Groups columns.
 void groupRows(int startIndex, int endIndex)
          Groups rows.
 void groupRows(int startIndex, int endIndex, boolean isHidden)
          Groups rows.
 int importResultSet(java.sql.ResultSet rs, int rowIndex, int columnIndex, boolean isFieldNameShown)
          Imports data in a ResultSet object to the worksheet.
 int importResultSet(java.sql.ResultSet rs, int rowIndex, int columnIndex, int rowNum, int columnNum, boolean isFieldNameShown)
          Imports data in a ResultSet object to the worksheet.
 int importResultSet(java.sql.ResultSet rs, java.lang.String startCell, boolean isFieldNameShown)
          Imports data in a ResultSet object to the worksheet.
 int importResultSet(java.sql.ResultSet rs, java.lang.String startCell, int rowNum, int columnNum, boolean isFieldNameShown)
          Imports data in a ResultSet object to the worksheet.
 void insertColumns(int columnIndex, int columnNum)
          Inserts multiple columns into the worksheet.
 void insertRows(int rowIndex, int rowNum)
          Inserts multiple rows into the worksheet.
 boolean isColumnHidden(int columnIndex)
          Checks if the specified column is hidden.
 boolean isGridlinesVisible()
          Gets if the the gride lines are visible.
 boolean isPageBreakPreview()
          Indicates the specified worksheet is shown in normal view or page break preview.
 boolean isRowColumnHeadersVisible()
          Gets whether the worksheet will display row and column headers.
 boolean isVisible()
          Gets if the worksheet is visible.
 void protect(Protection protection)
          Protects the worksheet.
 void removeCell(int rowIndex, int columnIndex)
          Removes a cell at the specified row index and column index from the worksheet.
 void removeCell(java.lang.String cellName)
          Removes a cell from the worksheet by the cell name.
 void removeRow(int rowIndex)
          Removes the row at the given position from the worksheet.
 void removeSplit()
          Removes splitted window.
 void setColumnHidden(int columnIndex, boolean isHidden)
          Sets whether the specified column to be hidden or not.
 void setColumnStyle(short columnIndex, Style style)
          Sets a style to the specified column.
 void setColumnWidth(int columnIndex, float columnWidth)
          Sets the width of the specified column, in unit of characters using default font.
 void setDefaultColumnWidth(float defaultColumnWidth)
          Sets the default column width of the worksheet, in unit of characters using default font.
 void setDefaultRowHeight(float defaultRowHeight)
          Sets the default row height of the worksheet, in unit of points.
 void setFirstVisibleColumn(int column)
          Sets first visible column index.
 void setFirstVisibleRow(int row)
          Sets first visible row index.
 void setGridlinesVisible(boolean gridlinesVisible)
          Sets whether the grid lines to be visible or not.
 void setName(java.lang.String name)
          Sets the worksheet name.
 void setPageBreakPreview(boolean pageBreakPreview)
          Sets the specified worksheet is shown in normal view or page break preview.
 void setRowColumnHeadersVisible(boolean rowColumnHeadersVisible)
          Sets whether the worksheet will display row and column headers.
 void setStyle(int startRow, int endRow, int startColumn, int endColumn, Style style)
          Sets a style for an area of cells.
 void setTabColor(Color tabColor)
          Sets the colour of the tab below the sheet containing the sheet name.
 void setVisible(boolean visible)
          Sets whether the worksheet to be visible or not.
 void setZoom(short zoom)
          Sets the scaling factor in percent.
 void split()
          Splits window.
 void unFreezePanes()
          Unfreezes panes in the worksheet.
 void ungroupColumns(int startIndex, int endIndex)
          Ungroups columns.
 void ungroupRows(int startIndex, int endIndex)
          Ungroups rows.
 void unprotect()
          Unprotects the worksheet.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

getCell

public Cell getCell(int rowIndex,
                    int columnIndex)
Gets the cell object with the specified row index and column index.

Parameters:
rowIndex - row index
columnIndex - column index
Returns:
the cell object at the specified row index and column index.
Throws:
java.lang.IllegalArgumentException - if any of the indexes is out of range.
ExcelException - if the worksheet has been removed.

getCell

public Cell getCell(java.lang.String cellName)
Gets the cell object by the cell name(like "A1").

Parameters:
cellName - cell name.
Returns:
the cell object.
Throws:
java.lang.IllegalArgumentException - if the cell name is invalid.
ExcelException - if the worksheet has been removed from the workbook.

getRow

public Row getRow(int rowIndex)
Gets the row at the specified row index.

Parameters:
rowIndex - row index.
Returns:
the row object.
Throws:
java.lang.IllegalArgumentException - if index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

insertRows

public void insertRows(int rowIndex,
                       int rowNum)
Inserts multiple rows into the worksheet.

Parameters:
rowIndex - The row index to insert before.
rowNum - Number of rows to be inserted.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

insertColumns

public void insertColumns(int columnIndex,
                          int columnNum)
Inserts multiple columns into the worksheet.

Parameters:
columnIndex - The column index to insert before.
columnNum - Number of columns to be inserted.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

removeRow

public void removeRow(int rowIndex)
Removes the row at the given position from the worksheet. The specified row is removed and other rows remain unchanged. Use removeRows(int, int, boolean) to make more control.

Parameters:
rowIndex - row index.
Throws:
java.lang.IllegalArgumentException - if row index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

deleteRows

public void deleteRows(int startIndex,
                       int deletedRows,
                       boolean bShift)
Removes rows from the worksheet.

Parameters:
startIndex - the first row index to be removed.
deletedRows - Number of rows to be removed.
bShift - whether the rows after the deleted rows should be shifted up.

deleteColumns

public void deleteColumns(int startIndex,
                          int deletedColumns,
                          boolean bShift)
Removes columns from the worksheet.

Parameters:
startIndex - the first column index to be removed.
deletedColumns - Number of columns to be removed.
bShift - whether the columns after the deleted columns should be shifted left.

removeCell

public void removeCell(int rowIndex,
                       int columnIndex)
Removes a cell at the specified row index and column index from the worksheet.

Parameters:
rowIndex - row index of the cell.
columnIndex - column Index of the cell.
Throws:
java.lang.IllegalArgumentException - if any of the indexes is out of range.
ExcelException - if the worksheet has been removed from the workbook.

removeCell

public void removeCell(java.lang.String cellName)
Removes a cell from the worksheet by the cell name.

Parameters:
cellName - cell name.
Throws:
java.lang.IllegalArgumentException - if the cell name is invalid.
ExcelException - if the worksheet has been removed from the workbook.

setStyle

public void setStyle(int startRow,
                     int endRow,
                     int startColumn,
                     int endColumn,
                     Style style)
Sets a style for an area of cells.

Parameters:
startRow - start row index.
endRow - end row index.
startColumn - start column index.
endColumn - end column index.
style - style to set.
Throws:
java.lang.IllegalArgumentException - if any of the indexes is out of range, or start index is greater than end index.
ExcelException - if the worksheet has been removed from the workbook.

importResultSet

public int importResultSet(java.sql.ResultSet rs,
                           int rowIndex,
                           int columnIndex,
                           int rowNum,
                           int columnNum,
                           boolean isFieldNameShown)
                    throws java.sql.SQLException
Imports data in a ResultSet object to the worksheet.

Parameters:
rs - the ResultSet object to import from.
rowIndex - start row index in the worksheet.
columnIndex - start column index in the worksheet.
rowNum - number of rows to import.
columnNum - number of columns to import.
isFieldNameShown - Indicates whether the field name of the resultset will be imported to the first row.
Returns:
how many rows actually imported.
Throws:
java.sql.SQLException - if an error occurs while reading from the resultset.
ExcelException - if the worksheet has been removed from the workbook.

importResultSet

public int importResultSet(java.sql.ResultSet rs,
                           java.lang.String startCell,
                           int rowNum,
                           int columnNum,
                           boolean isFieldNameShown)
                    throws java.sql.SQLException
Imports data in a ResultSet object to the worksheet.

Parameters:
rs - the ResultSet object to import from.
startCell - name of start cell to insert the resultset, such as "A1".
rowNum - number of rows to be imported.
columnNum - number of columns to be imported.
isFieldNameShown - Indicates whether the field name of the resultset will be imported to the first row.
Returns:
how many rows actually imported.
Throws:
java.sql.SQLException - if an error occurs while reading from the resultset.
ExcelException - if the worksheet has been removed from the workbook.

importResultSet

public int importResultSet(java.sql.ResultSet rs,
                           int rowIndex,
                           int columnIndex,
                           boolean isFieldNameShown)
                    throws java.sql.SQLException
Imports data in a ResultSet object to the worksheet.

Parameters:
rs - the ResultSet object to import from.
rowIndex - start row index in the worksheet.
columnIndex - start column index in the worksheet.
isFieldNameShown - Indicates whether the field name of the resultset will be imported to the first row.
Returns:
how many rows actually imported.
Throws:
java.sql.SQLException - if an error occurs while reading from the resultset.
ExcelException - if the worksheet has been removed from the workbook.

importResultSet

public int importResultSet(java.sql.ResultSet rs,
                           java.lang.String startCell,
                           boolean isFieldNameShown)
                    throws java.sql.SQLException
Imports data in a ResultSet object to the worksheet.

Parameters:
rs - the ResultSet object to import from.
startCell - name of start cell to insert the resultset, such as "A1".
isFieldNameShown - Indicates whether the field name of the resultset will be imported to the first row.
Returns:
how many rows actually imported.
Throws:
java.sql.SQLException - if an error occurs while reading from the resultset.
ExcelException - if the worksheet has been removed from the workbook.

getRowIterator

public java.util.Iterator getRowIterator()
Returns an iterator which can iterate all defined rows in this worksheet. It accesses all rows order by row index.

Returns:
an object that implements the Iterator interface.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

getName

public java.lang.String getName()
Returns the name of the worksheet.

Returns:
name of the worksheet

getType

public short getType()
Returns the type of the worksheet. It is one of the constant fields of SheetType. Now only SheetType.Worksheet is supported.

Returns:
type of the worksheet.
See Also:
SheetType

isVisible

public boolean isVisible()
Gets if the worksheet is visible.

Returns:
true if the worksheet is visible.

setName

public void setName(java.lang.String name)
Sets the worksheet name.

Parameters:
name - name of the worksheet. The name length should be 1 to 31.
Throws:
java.lang.IllegalArgumentException - if name is invalid
ExcelException - if the worksheet has been removed from the workbook, or a worksheet with the same name already exists.

setVisible

public void setVisible(boolean visible)
Sets whether the worksheet to be visible or not.

Parameters:
visible - true for visible
Throws:
ExcelException - if the worksheet has been removed from the workbook.

getZoom

public short getZoom()
Gets the scaling factor in percent.


setZoom

public void setZoom(short zoom)
Sets the scaling factor in percent. It should be between 10 and 400. If you changed the sheet view mode,you should reset the scaling factor.

Parameters:
zoom - the scaling factor in percent.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

getFirstVisibleRow

public int getFirstVisibleRow()
Gets first visible row index.

Returns:
first visible row index.

setFirstVisibleRow

public void setFirstVisibleRow(int row)
Sets first visible row index.

Parameters:
row - first visible row index.
Throws:
java.lang.IllegalArgumentException - if row index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

getFirstVisibleColumn

public short getFirstVisibleColumn()
Gets first visible column index.

Returns:
first visible column index.

setFirstVisibleColumn

public void setFirstVisibleColumn(int column)
Sets first visible column index.

Parameters:
column - first visible column index,zero besed.
Throws:
java.lang.IllegalArgumentException - if column index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

getTabColor

public Color getTabColor()
Gets the colour of the tab below the sheet containing the sheet name.

Returns:
the colour of the tab below the sheet containing the sheet name.

setTabColor

public void setTabColor(Color tabColor)
Sets the colour of the tab below the sheet containing the sheet name.

Parameters:
tabColor - the colour of the tab below the sheet containing the sheet name.

split

public void split()
Splits window.

Throws:
ExcelException - if the worksheet has been removed from the workbook.

removeSplit

public void removeSplit()
Removes splitted window.

Throws:
ExcelException - if the worksheet has been removed from the workbook.

freezePanes

public void freezePanes(int row,
                        int column,
                        int freezedRows,
                        int freezedColumns)
Freezes panes at the specified cell in the worksheet.

Parameters:
row - row index.
column - column index.
freezedRows - number of visible rows in top pane, no more than row index.
freezedColumns - number of visible columns in left pane, no more than column index.
Throws:
java.lang.IllegalArgumentException - if row index,column index,freezedRows or freezedColumns is out of range.
java.lang.IllegalArgumentException - if row index and column index all are zero. number of rows and number of columns all are zero.
ExcelException - if the worksheet has been removed from the workbook.

freezePanes

public void freezePanes(java.lang.String cellName,
                        int freezedRows,
                        short freezedColumns)
Freezes panes at the specified cell in the worksheet.

Parameters:
cellName - cell name.
freezedRows - number of visible rows in top pane, no more than row index.
freezedColumns - number of visible columns in left pane, no more than column index.
Throws:
java.lang.IllegalArgumentException - if cell name is invalid.
See Also:
freezePanes(int, int, int, int)

unFreezePanes

public void unFreezePanes()
Unfreezes panes in the worksheet.

Throws:
ExcelException - if the worksheet has been removed from the workbook.

isPageBreakPreview

public boolean isPageBreakPreview()
Indicates the specified worksheet is shown in normal view or page break preview.

Returns:
whether the specified worksheet is shown in normal view or page break preview.

setPageBreakPreview

public void setPageBreakPreview(boolean pageBreakPreview)
Sets the specified worksheet is shown in normal view or page break preview. If this parameter is changed ,the current scaling factor will be cached and the cached factor will replace the current scaling factor. You should reset the scaling factor which you want to set.

Parameters:
pageBreakPreview - whether the specified worksheet is shown in normal view or page break preview.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

isGridlinesVisible

public boolean isGridlinesVisible()
Gets if the the gride lines are visible.


setGridlinesVisible

public void setGridlinesVisible(boolean gridlinesVisible)
Sets whether the grid lines to be visible or not.

Parameters:
gridlinesVisible - whether the grid lines to be visible or not.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

isRowColumnHeadersVisible

public boolean isRowColumnHeadersVisible()
Gets whether the worksheet will display row and column headers.


setRowColumnHeadersVisible

public void setRowColumnHeadersVisible(boolean rowColumnHeadersVisible)
Sets whether the worksheet will display row and column headers.

Parameters:
rowColumnHeadersVisible - whether the worksheet will display row and column headers.
Throws:
ExcelException - if the worksheet has been removed from the workbook.

getFirstRowIndex

public int getFirstRowIndex()
Returns the first defined row index.

Returns:
row index of the first defined row. Returns -1 if no row is defined.

getLastRowIndex

public int getLastRowIndex()
Returns the last defined row index.

Returns:
row index of the last defined row. Returns -1 if no row is defined.

getDefaultColumnWidth

public float getDefaultColumnWidth()
Returns the default column width of the worksheet, in unit of characters with default font.

Returns:
the default column width in characters.

setDefaultColumnWidth

public void setDefaultColumnWidth(float defaultColumnWidth)
Sets the default column width of the worksheet, in unit of characters using default font.

Parameters:
defaultColumnWidth - the default column width of the worksheet in characters. 0-255.
Throws:
java.lang.IllegalArgumentException - if the given column width is invalid(< 0 or > 255 ).
ExcelException - if the worksheet has been removed from the workbook.

getColumnWidth

public float getColumnWidth(int columnIndex)
Gets the width of the column at the given column index, in unit of characters using default font.

Parameters:
columnIndex - column index.
Returns:
column width in characters.
Throws:
java.lang.IllegalArgumentException - if column index is out of range.

setColumnWidth

public void setColumnWidth(int columnIndex,
                           float columnWidth)
Sets the width of the specified column, in unit of characters using default font.

Parameters:
columnIndex - column index.
columnWidth - column width in characters(0-255).
Throws:
java.lang.IllegalArgumentException - if either column index or column width is invalid.
ExcelException - if the worksheet has been removed from the workbook.

isColumnHidden

public boolean isColumnHidden(int columnIndex)
Checks if the specified column is hidden.

Parameters:
columnIndex - column index.
Returns:
true if the column is hidden.
Throws:
java.lang.IllegalArgumentException - if column index is out of range.

setColumnHidden

public void setColumnHidden(int columnIndex,
                            boolean isHidden)
Sets whether the specified column to be hidden or not.

Parameters:
columnIndex - column index.
isHidden - true for hidden.
Throws:
java.lang.IllegalArgumentException - if the column index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

getColumnStyle

public Style getColumnStyle(int columnIndex)
Gets the style setting for the specified column.

Parameters:
columnIndex - column index.
Returns:
style of the column.
Throws:
java.lang.IllegalArgumentException - if column index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

setColumnStyle

public void setColumnStyle(short columnIndex,
                           Style style)
Sets a style to the specified column.

Parameters:
columnIndex - column index.
style - style to set.
Throws:
java.lang.IllegalArgumentException - if column index is out of range.
ExcelException - if the worksheet has been removed from the workbook.

getDefaultRowHeight

public float getDefaultRowHeight()
Gets the default row height of the worksheet in points.

Returns:
the default row height in points.

setDefaultRowHeight

public void setDefaultRowHeight(float defaultRowHeight)
Sets the default row height of the worksheet, in unit of points.

Parameters:
defaultRowHeight - row height in points(0-409).
Throws:
java.lang.IllegalArgumentException - if the row height is invalid.
ExcelException - if the worksheet has been removed from the workbook.

getMergedCells

public java.util.ArrayList getMergedCells()
Gets the collection of merged cell ranges in the worksheet.

Returns:
CellArea collection. You can manipulate this collection to add or remove merged cells.

getHyperlinks

public Hyperlinks getHyperlinks()
Get the Hyperlinks in the worksheet.

Returns:
Hyperlinks.You can manipulate this Hyperlinks to add Hyperlink.

getValidations

public Validations getValidations()
Get the Validations in the worksheet.

Returns:
Validations.You can manipulate this Validations to add validation.

getVPageBreaks

public VPageBreaks getVPageBreaks()
Get the VPageBreaks in the worksheet.

Returns:
VPageBreaks.You can manipulate this VPageBreaks to add vertical page breaks.

getHPageBreaks

public HPageBreaks getHPageBreaks()
Get the HPageBreaks in the worksheet.

Returns:
HPageBreaks.You can manipulate this HPageBreaks to add horizontal page breaks.

addPageBreaks

public void addPageBreaks(java.lang.String cellName)
Adds a page break to the worksheet.

Parameters:
cellName - cell name.
Throws:
java.lang.IllegalArgumentException - if cell name is invalid.

addPageBreaks

public void addPageBreaks(int row,
                          int column)
Adds a page break to the worksheet.

Parameters:
row - row index,zero based
column - column index,zero based.
Throws:
java.lang.IllegalArgumentException - if row index or cell indexs is invalid.

getPageSetup

public PageSetup getPageSetup()
Get the page setup options in the worksheet.

Returns:
PageSetup.You can manipulate this object to set all page setup options.

getConditionalFormattings

public ConditionalFormattings getConditionalFormattings()
Gets the ConditionalFormattings in the worksheet.

Returns:
ConditionalFormattings.You can manipulate this object to set conditional formatting.

getProtection

public Protection getProtection()
Gets the current protection settings.

Returns:
protection options. If the worksheet is not protected, null is returned.

protect

public void protect(Protection protection)
Protects the worksheet.

Parameters:
protection - protection options.

unprotect

public void unprotect()
Unprotects the worksheet.


groupRows

public void groupRows(int startIndex,
                      int endIndex)
Groups rows.

Parameters:
startIndex - The first row index to be grouped.
endIndex - The last row index to be grouped.

groupRows

public void groupRows(int startIndex,
                      int endIndex,
                      boolean isHidden)
Groups rows.

Parameters:
startIndex - The first row index to be grouped.
endIndex - The last row index to be grouped.
isHidden - Specifies if the grouped columns are hidden.

ungroupRows

public void ungroupRows(int startIndex,
                        int endIndex)
Ungroups rows.

Parameters:
startIndex - The first row index to be ungrouped.
endIndex - The last row index to be ungrouped.

groupColumns

public void groupColumns(int startIndex,
                         int endIndex)
Groups columns.

Parameters:
startIndex - The first column index to be grouped.
endIndex - The last column index to be grouped.

groupColumns

public void groupColumns(int startIndex,
                         int endIndex,
                         boolean isHidden)
Groups columns.

Parameters:
startIndex - The first column index to be grouped.
endIndex - The last column index to be grouped.
isHidden - Specifies if the grouped columns are hidden.

ungroupColumns

public void ungroupColumns(int startIndex,
                           int endIndex)
Ungroups columns.

Parameters:
startIndex - The first column index to be ungrouped.
endIndex - The last column index to be ungrouped.