Package ch.rabanti.nanoxlsx4j

Class Worksheet

java.lang.Object
ch.rabanti.nanoxlsx4j.Worksheet
public class Worksheet extends Object
Class representing a worksheet of a workbook

  • Field Details

    • MAX_WORKSHEET_NAME_LENGTH

      public static final int MAX_WORKSHEET_NAME_LENGTH
      Maximum number of characters a worksheet name can have
      See Also:

    • DEFAULT_COLUMN_WIDTH

      public static final float DEFAULT_COLUMN_WIDTH
      Default column width as constant
      See Also:

    • DEFAULT_ROW_HEIGHT

      public static final float DEFAULT_ROW_HEIGHT
      Default row height as constant
      See Also:

    • MAX_COLUMN_NUMBER

      public static final int MAX_COLUMN_NUMBER
      Maximum column number (zero-based)
      See Also:

    • MAX_COLUMN_WIDTH

      public static final float MAX_COLUMN_WIDTH
      Maximum column width as constant
      See Also:

    • MAX_ROW_NUMBER

      public static final int MAX_ROW_NUMBER
      Maximum row number (zero-based)
      See Also:

    • MAX_ROW_HEIGHT

      public static final float MAX_ROW_HEIGHT
      Maximum row height as constant
      See Also:

    • MIN_COLUMN_NUMBER

      public static final int MIN_COLUMN_NUMBER
      Minimum column number (zero-based)
      See Also:

    • MIN_COLUMN_WIDTH

      public static final float MIN_COLUMN_WIDTH
      Minimum column width as constant
      See Also:

    • MIN_ROW_NUMBER

      public static final int MIN_ROW_NUMBER
      Minimum row number (zero-based)
      See Also:

    • MIN_ROW_HEIGHT

      public static final float MIN_ROW_HEIGHT
      Minimum row height as constant
      See Also:

    • AUTO_ZOOM_FACTOR

      public static final int AUTO_ZOOM_FACTOR
      Automatic zoom factor of a worksheet
      See Also:

    • MIN_ZOOM_FACTOR

      public static final int MIN_ZOOM_FACTOR
      Minimum zoom factor of a worksheet. If set to this value, the zoom is set to automatic
      See Also:

    • MAX_ZOOM_FACTOR

      public static final int MAX_ZOOM_FACTOR
      Maximum zoom factor of a worksheet
      See Also:

  • Constructor Details

    • Worksheet

      public Worksheet()
      Default constructor. A worksheet created with this constructor cannot be used to assign styles to a cell. This will cause an exception unless a reference to the workbook was set
      Throws:
      StyleException - Thrown if a style is added to the worksheet without w workbook reference

    • Worksheet

      public Worksheet(String name)
      Constructor with worksheet name
      API Note:
      Note that the worksheet name is not checked against other worksheets with this operation. This is later performed when the worksheet is added to the workbook
      Parameters:
      name - Name of the new worksheet
      Throws:
      FormatException - Thrown if the name contains illegal characters or is too long

    • Worksheet

      public Worksheet(String name, int id, Workbook reference)
      Constructor with workbook reference, name and sheet ID
      Parameters:
      name - Name of the worksheet
      id - ID of the worksheet (for internal use)
      reference - Reference to the parent Workbook
      Throws:
      FormatException - Thrown if the name contains illegal characters or is too long

  • Method Details

    • setAutoFilterRange

      public void setAutoFilterRange(String range)
      Sets the column auto filter within the defined column range
      Parameters:
      range - Range to apply auto filter on. The range could be 'A1:C10' for instance. The end row will be recalculated automatically when saving the file
      Throws:
      RangeException - Thrown if the passed range out of range
      FormatException - Thrown if the passed range is malformed

    • getAutoFilterRange

      public Range getAutoFilterRange()
      Gets the range of the auto filter. If null, no auto filters are applied
      Returns:
      Range of auto filter

    • getCells

      public Map<String,Cell> getCells()
      Gets the cells of the worksheet as map with the cell address as key and the cell object as value
      Returns:
      List of Cell objects

    • getColumns

      public Map<Integer,Column> getColumns()
      Gets all columns with non-standard properties, like auto filter applied or a special width as map with the zero-based column index as key and the column object as value
      Returns:
      map of columns

    • getCurrentCellDirection

      public Worksheet.CellDirection getCurrentCellDirection()
      Gets the direction when using AddNextCell method
      Returns:
      Cell direction

    • setCurrentCellDirection

      public void setCurrentCellDirection(Worksheet.CellDirection currentCellDirection)
      Sets the direction when using AddNextCell method
      Parameters:
      currentCellDirection - Cell direction

    • setCurrentColumnNumber

      public void setCurrentColumnNumber(int columnNumber)
      Sets the current column number (zero based)
      Parameters:
      columnNumber - Column number (zero based)
      Throws:
      RangeException - Thrown if the number is out of the valid range. Range is from 0 to 16383 (16384 columns)

    • setCurrentRowNumber

      public void setCurrentRowNumber(int rowNumber)
      Sets the current row number (zero based)
      Parameters:
      rowNumber - Row number (zero based)
      Throws:
      RangeException - Thrown if the number is out of the valid range. Range is from 0 to 1048575 (1048576 rows)

    • getCurrentColumnNumber

      public int getCurrentColumnNumber()
      Gets the current column number (zero based)
      Returns:
      Column number (zero-based)

    • getCurrentRowNumber

      public int getCurrentRowNumber()
      Gets the current row number (zero based)
      Returns:
      Row number (zero-based)

    • getDefaultColumnWidth

      public float getDefaultColumnWidth()
      Gets the default column width
      Returns:
      Default column width

    • setDefaultColumnWidth

      public void setDefaultColumnWidth(float defaultColumnWidth)
      Sets the default column width
      Parameters:
      defaultColumnWidth - Default column width
      Throws:
      RangeException - Throws a RangeException if the passed width is out of range (set)

    • getDefaultRowHeight

      public float getDefaultRowHeight()
      Gets the default Row height
      Returns:
      Default Row height

    • setDefaultRowHeight

      public void setDefaultRowHeight(float defaultRowHeight)
      Sets the default Row height
      Parameters:
      defaultRowHeight - Default Row height
      Throws:
      RangeException - Throws a RangeException if the passed height is out of range (set)

    • getHiddenRows

      public Map<Integer,Boolean> getHiddenRows()
      Gets the hidden rows as map with the zero-based row number as key and a boolean as value. True indicates hidden, false visible. Entries with the value false are not affecting the worksheet. These entries can be removed
      Returns:
      Map with hidden rows

    • getRowHeights

      public Map<Integer,Float> getRowHeights()
      Gets defined row heights as map with the zero-based row number as key and the height (float from 0 to 409.5) as value
      Returns:
      Map of row heights

    • getMergedCells

      public Map<String,Range> getMergedCells()
      Gets the merged cells (only references) as map with the cell address as key and the range object as value
      Returns:
      Hashmap with merged cell references

    • getSelectedCells

      public Range getSelectedCells()
      Deprecated.
      This method is a deprecated subset of the function SelectedCellRanges. SelectedCellRanges will get this function name in a future version. Therefore, the type will change
      Gets either null (if no cells are selected), or the first defined range of selected cells
      Returns:
      First cell range of the selected cells

    • getSelectedCellRanges

      public List<Range> getSelectedCellRanges()
      Gets all ranges of selected cells of this worksheet. An empty list is returned if no cells are selected
      Returns:
      All ranges of the selected cells

    • setSelectedCells

      public void setSelectedCells(String range)
      Deprecated.
      This method is a deprecated subset of the function AddSelectedCells. It will be removed in a future version
      Sets a single range of selected cells on this worksheet. All existing ranges will be removed range
      Parameters:
      range - Range as string to set as single cell range for selected cells, or null to remove the selected cells
      Throws:
      RangeException - Thrown if the passed range out of range
      FormatException - Thrown if the passed range is malformed

    • setSelectedCells

      public void setSelectedCells(Range range)
      Deprecated.
      This method is a deprecated subset of the function AddSelectedCells. It will be removed in a future version
      Sets a single range of selected cells on this worksheet. All existing ranges will be removed. Null will remove all selected cells
      Parameters:
      range - Range to set as single cell range for selected cells

    • setSelectedCells

      public void setSelectedCells(Address startAddress, Address endAddress)
      Deprecated.
      This method is a deprecated subset of the function AddSelectedCells. It will be removed in a future version
      Sets the selected cells on this worksheet. If both addresses are null, the selected cell range is removed
      Parameters:
      startAddress - Start address of the range to set as single cell range for selected cells
      endAddress - End address of the range to set as single cell range for selected cells
      Throws:
      RangeException - Thrown if either the start address or end address is null

    • addSelectedCells

      public void addSelectedCells(Range range)
      Adds a range to the selected cells on this worksheet
      Parameters:
      range - Cell range to be added as selected cells

    • addSelectedCells

      public void addSelectedCells(Address startAddress, Address endAddress)
      Adds a range to the selected cells on this worksheet
      Parameters:
      startAddress - Start address of the range to add
      endAddress - End address of the range to add

    • addSelectedCells

      public void addSelectedCells(String range)
      Adds a range to the selected cells on this worksheet. Null or empty as value will be ignored
      Parameters:
      range - Cell range to add as selected cells

    • getSheetID

      public int getSheetID()
      Gets the internal ID of the worksheet
      Returns:
      Worksheet ID

    • setSheetID

      public void setSheetID(int sheetID)
      Sets the internal ID of the worksheet
      Parameters:
      sheetID - Worksheet ID

    • getSheetName

      public String getSheetName()
      Gets the name of the sheet
      Returns:
      Name of the sheet

    • isUseSheetProtection

      public boolean isUseSheetProtection()
      Gets whether the worksheet is protected
      Returns:
      If true, the worksheet is protected

    • setUseSheetProtection

      public void setUseSheetProtection(boolean useSheetProtection)
      Sets whether the worksheet is protected
      Parameters:
      useSheetProtection - If true, the worksheet is protected

    • getSheetProtectionPassword

      public String getSheetProtectionPassword()
      Gets the password used for sheet protection
      API Note:
      If a workbook with password protected worksheets is loaded, only the getSheetProtectionPasswordHash() is loaded. The password itself cannot be recovered. Use the getSheetProtectionPasswordHash() getter to check whether there is a password set
      Returns:
      Password (UTF-8)

    • setSheetProtectionPasswordHash

      public void setSheetProtectionPasswordHash(String hash)
      Sets the encryption hash of the password, defined with setSheetProtectionPassword(String). This method is usually used when reading a workbook.
      API Note:
      Do not use this method to set a password. Use setSheetProtectionPassword(String) instead
      Parameters:
      hash - Hash, either loaded from a workbook or generated by Helper.generatePasswordHash(String)

    • getSheetProtectionPasswordHash

      public String getSheetProtectionPasswordHash()
      gets the encryption hash of the password, defined with setSheetProtectionPassword(String). The value will be null, if no password is defined
      Returns:
      Encrypted password as String

    • setSheetProtectionPassword

      public void setSheetProtectionPassword(String password)
      Sets or removes the password for worksheet protection. If set, UseSheetProtection will be also set to true
      Parameters:
      password - Password (UTF-8) to protect the worksheet. If the password is null or empty, no password will be used

    • getSheetProtectionValues

      public List<Worksheet.SheetProtectionValue> getSheetProtectionValues()
      Gets the list of SheetProtectionValues. These values define the allowed actions if the worksheet is protected
      Returns:
      List of SheetProtectionValues

    • getWorkbookReference

      public Workbook getWorkbookReference()
      Gets the Reference to the parent Workbook
      Returns:
      Workbook reference

    • setWorkbookReference

      public void setWorkbookReference(Workbook workbookReference)
      Sets the Reference to the parent Workbook
      Parameters:
      workbookReference - Workbook reference

    • isHidden

      public boolean isHidden()
      Gets whether the worksheet is hidden
      Returns:
      If true, the worksheet is not listed in the worksheet tabs of the workbook

    • setHidden

      public void setHidden(boolean hidden)
      Sets whether the worksheet is hidden
      If the worksheet is not part of a workbook, or the only one in the workbook, an exception will be thrown If the worksheet is the selected one, and attempted to set hidden, an exception will be thrown. Define another selected worksheet prior to this call, in this case.
      Parameters:
      hidden - If true, the worksheet is not listed as tab in the workbook's worksheet selection

    • getPaneSplitTopHeight

      public Float getPaneSplitTopHeight()
      Gets the height of the upper, horizontal split pane, measured from the top of the window.
      The value is nullable. If null, no horizontal split of the worksheet is applied.
      The value is only applicable to split the worksheet into panes, but not to freeze them.
      See also getPaneSplitAddress()
      API Note:
      Note: This value will be modified to the Excel-internal representation, calculated by Helper.getInternalPaneSplitHeight(float)
      Returns:
      Height of the top pane until the split line appears

    • getPaneSplitLeftWidth

      public Float getPaneSplitLeftWidth()
      Gets the width of the left, vertical split pane, measured from the left of the window.
      The value is nullable. If null, no vertical split of the worksheet is applied
      The value is only applicable to split the worksheet into panes, but not to freeze them.
      See also: getPaneSplitAddress()
      API Note:
      Note: This value will be modified to the Excel-internal representation, calculated by Helper.getInternalColumnWidth(float, float, float)}
      Returns:
      Width form the left border until the split line appears

    • getFreezeSplitPanes

      public Boolean getFreezeSplitPanes()
      Gets whether split panes are frozen.
      The value is nullable. If null, no freezing is applied. This property also does not apply if getPaneSplitAddress() is null
      Returns:
      True if panes are frozen

    • getPaneSplitTopLeftCell

      public Address getPaneSplitTopLeftCell()
      Gets the Top Left cell address of the bottom right pane if applicable and splitting is applied.
      The column is only relevant for vertical split, whereas the row component is only relevant for a horizontal split.
      The value is nullable. If null, no splitting was defined.
      Returns:
      Address of the top Left cell address of the bottom right pane

    • getPaneSplitAddress

      public Address getPaneSplitAddress()
      Gets the split address for frozen panes or if pane split was defined in number of columns and / or rows.
      For vertical splits, only the column component is considered. For horizontal splits, only the row component is considered.
      The value is nullable. If null, no frozen panes or split by columns / rows are applied to the worksheet. However, splitting can still be applied, if the value is defined in characters.
      See also: getPaneSplitLeftWidth() and getPaneSplitTopHeight() for splitting in characters (without freezing)
      Returns:
      Address where the panes splits the worksheet apart

    • getActivePane

      public Worksheet.WorksheetPane getActivePane()
      Gets the active Pane is splitting is applied.
      The value is nullable. If null, no splitting was defined
      Returns:
      Active pane if defined

    • getActiveStyle

      public Style getActiveStyle()
      Gets the active Style of the worksheet. If null, no style is defined as active
      Returns:
      Active style of the worksheet

    • isShowingGridLines

      public boolean isShowingGridLines()
      Gets whether grid lines are visible on the current worksheet. Default is true
      Returns:
      True if grid lines are visible

    • setShowingGridLines

      public void setShowingGridLines(boolean showGridLines)
      Sets whether grid lines are visible on the current worksheet. Default is true
      Parameters:
      showGridLines - True if grid lines are visible

    • isShowingRowColumnHeaders

      public boolean isShowingRowColumnHeaders()
      Gets whether the column and row headers are visible on the current worksheet. Default is true
      Returns:
      True if column and row headers are visible

    • setShowingRowColumnHeaders

      public void setShowingRowColumnHeaders(boolean showRowColumnHeaders)
      Sets whether the column and row headers are visible on the current worksheet. Default is true * @param showRowColumnHeaders
      Parameters:
      showRowColumnHeaders - True if column and row headers are visible

    • isShowingRuler

      public boolean isShowingRuler()
      Gets whether a ruler is displayed over the column headers. This value only applies if setViewType(SheetViewType) is set to Worksheet.SheetViewType.pageLayout. Default is true
      Returns:
      True if rules are visible

    • setShowingRuler

      public void setShowingRuler(boolean showRuler)
      Sets whether a ruler is displayed over the column headers. This value only applies if setViewType(SheetViewType) is set to Worksheet.SheetViewType.pageLayout. Default is true
      Parameters:
      showRuler - True if rulers are visible

    • getViewType

      public Worksheet.SheetViewType getViewType()
      Gets how the current worksheet is displayed in the spreadsheet application (Excel)
      Returns:
      View type of the current worksheet

    • setViewType

      public void setViewType(Worksheet.SheetViewType viewType)
      Sets how the current worksheet is displayed in the spreadsheet application (Excel)
      Parameters:
      viewType - View type of the current worksheet

    • getZoomFactor

      public int getZoomFactor()
      Gets the zoom factor of the setViewType(SheetViewType) of the current worksheet. If AUTO_ZOOM_FACTOR, the zoom factor is set to automatic
      Returns:
      Map of the zoom factors depending on the view type

    • setZoomFactor

      public void setZoomFactor(int zoomFactor)
      Sets the zoom factor of the setViewType(SheetViewType) of the current worksheet. If AUTO_ZOOM_FACTOR, the zoom factor is set to automatic
      API Note:
      It is possible to add further zoom factors for inactive view types, using the function setZoomFactor(SheetViewType, int)
      Parameters:
      zoomFactor - Map of the zoom factors depending on the view type
      Throws:
      WorksheetException - Thrown if the zoom factor is not AUTO_ZOOM_FACTOR or below MIN_ZOOM_FACTOR or above MAX_ZOOM_FACTOR

    • getZoomFactors

      public Map<Worksheet.SheetViewType,Integer> getZoomFactors()
      Gets all defined zoom factors per Worksheet.SheetViewType of the current worksheet. Use setZoomFactor(SheetViewType, int) to define the values
      Returns:
      Map of defined zoom factors of the current worksheet

    • addNextCell

      public void addNextCell(Object value)
      Adds an object to the next cell position. If the type of the value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      Throws:
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addNextCell

      public void addNextCell(Object value, Style style)
      Adds an object to the next cell position. If the type of the value does not match with one of the supported data types, it will be cast to a String.A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      style - Style object to apply on this cell
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCell

      public void addCell(Object value, int columnNumber, int rowNumber)
      Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      Throws:
      StyleException - Thrown if the active style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCell

      public void addCell(Object value, int columnNumber, int rowNumber, Style style)
      Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      style - Style to apply on the cell
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCell

      public void addCell(Object value, String address)
      Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      address - Cell address in the format A1 - XFD1048576
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCell

      public void addCell(Object value, String address, Style style)
      Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      value - Unspecified value to insert
      address - Cell address in the format A1 - XFD1048576
      style - Style to apply on the cell
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellFormula

      public void addCellFormula(String formula, String address)
      Adds a cell formula as string to the defined cell address
      Parameters:
      formula - Formula to insert
      address - Cell address in the format A1 - XFD1048576
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellFormula

      public void addCellFormula(String formula, String address, Style style)
      Adds a cell formula as string to the defined cell address
      Parameters:
      formula - Formula to insert
      address - Cell address in the format A1 - XFD1048576
      style - Style to apply on the cell
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellFormula

      public void addCellFormula(String formula, int columnNumber, int rowNumber)
      Adds a cell formula as string to the defined cell address
      Parameters:
      formula - Formula to insert
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellFormula

      public void addCellFormula(String formula, int columnNumber, int rowNumber, Style style)
      Adds a cell formula as string to the defined cell address
      Parameters:
      formula - Formula to insert
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      style - Style to apply on the cell
      Throws:
      StyleException - Thrown if the passed style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addNextCellFormula

      public void addNextCellFormula(String formula)
      Adds a formula as string to the next cell position
      Parameters:
      formula - Formula to insert
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addNextCellFormula

      public void addNextCellFormula(String formula, Style style)
      Adds a formula as string to the next cell position
      Parameters:
      formula - Formula to insert
      style - Style to apply on the cell
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellRange

      public void addCellRange(List<Object> values, Address startAddress, Address endAddress)
      Adds a list of object values to a defined cell range. If the type of the particular value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      values - List of unspecified objects to insert
      startAddress - Start address
      endAddress - End address
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellRange

      public void addCellRange(List<Object> values, Address startAddress, Address endAddress, Style style)
      Adds a list of object values to a defined cell range. If the type of the particular value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      values - List of unspecified objects to insert
      startAddress - Start address
      endAddress - End address
      style - Style to apply on the all cells of the range
      Throws:
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellRange

      public void addCellRange(List<Object> values, String cellRange)
      Adds a list of object values to a defined cell range. If the type of the particular value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      The data types of the passed list can be mixed. Recognized are the following data types: Cell (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All other types will be cast into a String using the default toString() method
      Parameters:
      values - List of unspecified objects to insert
      cellRange - Cell range as string in the format like A1:D1 or X10:X22
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • addCellRange

      public void addCellRange(List<Object> values, String cellRange, Style style)
      Adds a list of object values to a defined cell range. If the type of the particular value does not match with one of the supported data types, it will be cast to a String. A prepared object of the type Cell will not be cast but adjusted
      The data types of the passed list can be mixed. Recognized are the following data types: Cell * (prepared object), String, short, int, byte, double, float, boolean, long, BigDecimal, Date, Time, LocalTime. All * other types will be cast into a String using the default toString() method
      Parameters:
      values - List of unspecified objects to insert
      cellRange - Cell range as string in the format like A1:D1 or X10:X22
      style - Style to apply on the all cells of the range
      Throws:
      FormatException - Thrown if the passed address is malformed
      StyleException - Thrown if the default style was malformed
      RangeException - Thrown if the next cell is out of range (on row or column)

    • removeCell

      public boolean removeCell(int columnNumber, int rowNumber)
      Removes a previous inserted cell at the defined address
      Parameters:
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      Returns:
      Returns true if the cell could be removed (existed), otherwise false (did not exist)
      Throws:
      RangeException - Thrown if the resolved cell address is out of range

    • removeCell

      public boolean removeCell(String address)
      Removes a previous inserted cell at the defined address
      Parameters:
      address - Cell address in the format A1 - XFD1048576
      Returns:
      Returns true if the cell could be removed (existed), otherwise false (did not exist)
      Throws:
      RangeException - Thrown if the resolved cell address is out of range
      FormatException - Thrown if the passed address is malformed

    • setStyle

      public void setStyle(Range cellRange, Style style)
      Sets the passed style on the passed cell range. If cells are already existing, the style will be added or replaced. Otherwise, an empty cell will be added with the assigned style. If the passed style is null, all styles will be removed on existing cells and no additional (empty) cells are added to the worksheet
      Implementation Note:
      This method may invalidate an existing date and time value since dates and times are defined by specific style. The result of a redefinition will be a number, instead of a date or time
      Parameters:
      cellRange - Cell range to apply the style
      style - Style to apply or null to clear the range
      Throws:
      RangeException - Throws a RangeException if the range is invalid

    • setStyle

      public void setStyle(Address startAddress, Address endAddress, Style style)
      Sets the passed style on the passed cell range, derived from a start and end address. If cells are already existing, the style will be added or replaced. Otherwise, an empty cell will be added with the assigned style. If the passed style is null, all styles will be removed on existing cells and no additional (empty) cells are added to the worksheet
      Implementation Note:
      This method may invalidate an existing date and time value since dates and times are defined by specific style. The result of a redefinition will be a number, instead of a date or time
      Parameters:
      startAddress - Start address of the cell range
      endAddress - End address of the cell range
      style - Style to apply or null to clear the range

    • setStyle

      public void setStyle(Address address, Style style)
      Sets the passed style on the passed (singular) cell address. If the cell is already existing, the style will be added or replaced. Otherwise, an empty cell will be added with the assigned style. If the passed style is null, all styles will be removed on existing cells and no additional (empty) cells are added to the worksheet
      Implementation Note:
      This method may invalidate an existing date and time value since dates and times are defined by specific style. The result of a redefinition will be a number, instead of a date or time
      Parameters:
      address - Cell address to apply the style
      style - Style to apply or null to clear the range

    • setStyle

      public void setStyle(String addressExpression, Style style)
      Sets the passed style on the passed address expression. Such an expression may be a single cell or a cell range. If the cell is already existing, the style will be added or replaced. Otherwise, an empty cell will be added with the assigned style. If the passed style is null, all styles will be removed on existing cells and no additional (empty) cells are added to the worksheet
      Implementation Note:
      This method may invalidate an existing date and time value since dates and times are defined by specific style. The result of a redefinition will be a number, instead of a date or time
      Parameters:
      addressExpression - Expression of a cell address or range of addresses
      style - Style to apply or null to clear the range

    • getFirstColumnNumber

      public int getFirstColumnNumber()
      Gets the first existing column number in the current worksheet (zero-based)
      API Note:
      getFirstColumnNumber() will not return the first column with data in any case. If there is a formatted but empty cell (or many) before the first cell with data. getFirstColumnNumber() will return the column number of this empty cell. Use getFirstDataColumnNumber() in this case.
      Returns:
      Zero-based column number. In case of an empty worksheet, -1 will be returned

    • getFirstDataColumnNumber

      public int getFirstDataColumnNumber()
      Gets the first existing column number with data in the current worksheet (zero-based)
      API Note:
      getFirstDataColumnNumber() will ignore formatted but empty cells before the first column with data. If you want the first defined column, use getFirstColumnNumber() instead.
      Returns:
      Zero-based column number. In case of an empty worksheet, -1 will be returned

    • getFirstRowNumber

      public int getFirstRowNumber()
      Gets the first existing row number in the current worksheet (zero-based)
      API Note:
      getLastColumnNumber() will not return the first column with data in any case. If there is a formatted but empty cell (or many) before the first cell with data. getFirstRowNumber() will return the column number of this empty cell. Use getFirstDataRowNumber() in this case.
      Returns:
      Zero-based row number. In case of an empty worksheet, -1 will be returned

    • getFirstDataRowNumber

      public int getFirstDataRowNumber()
      Gets the first existing row number with data in the current worksheet (zero-based)
      API Note:
      getFirstDataRowNumber() will ignore formatted but empty cells before the first row with data. If you want the first defined row, use getFirstRowNumber() instead.
      Returns:
      Zero-based row number. In case of an empty worksheet, -1 will be returned

    • getLastColumnNumber

      public int getLastColumnNumber()
      Gets the last existing column number in the current worksheet (zero-based)
      API Note:
      getLastColumnNumber() will not return the last column with data in any case. If there is a formatted but empty cell (or many) after the last cell with data. getLastColumnNumber() will return the column number of this empty cell. Use getLastDataColumnNumber() in this case.
      Returns:
      Zero-based column number. In case of an empty worksheet, -1 will be returned

    • getLastDataColumnNumber

      public int getLastDataColumnNumber()
      Gets the last existing column number with data in the current worksheet (zero-based)
      API Note:
      getLastDataColumnNumber() will ignore formatted but empty cells after the last column with data. If you want the last defined column, use getLastColumnNumber() instead.
      Returns:
      Zero-based column number. in case of an empty worksheet, -1 will be returned

    • getLastRowNumber

      public int getLastRowNumber()
      Gets the last existing row number in the current worksheet (zero-based)
      API Note:
      getLastRowNumber() will not return the last row with data in any case. If there is a formatted but empty cell (or many) after the last cell with data. getLastRowNumber() will return the row number of this empty cell. Use getLastDataRowNumber() in this case.
      Returns:
      Zero-based row number. In case of an empty worksheet, -1 will be returned

    • getLastDataRowNumber

      public int getLastDataRowNumber()
      Gets the last existing row number with data in the current worksheet (zero-based)
      API Note:
      getLastDataRowNumber() will ignore formatted but empty cells after the last row with data. If you want the last defined row, use getLastRowNumber() instead.
      Returns:
      Zero-based row number. in case of an empty worksheet, -1 will be returned

    • getLastCellAddress

      public Address getLastCellAddress()
      Gets the last existing cell in the current worksheet (bottom right)
      API Note:
      getLastCellAddress() will not return the last cell with data in any case. If there is a formatted (or with definitions of hidden states, AutoFilters, heights or widths) but empty cell (or many) beyond the last cell with data, getLastCellAddress() will return the address of this empty cell. Use getLastDataCellAddress() in this case.
      Returns:
      Nullable Cell Address. If no cell address could be determined, null will be returned

    • getLastDataCellAddress

      public Address getLastDataCellAddress()
      Gets the last existing cell in the current worksheet (bottom right)
      API Note:
      getLastDataCellAddress() will ignore formatted (or with definitions of hidden states, AutoFilters, heights or widths) but empty cells beyond the last cell with data. If you want the last defined cell, use getLastCellAddress() instead.
      Returns:
      Nullable Cell Address. If no cell address could be determined, null will be returned

    • getFirstCellAddress

      public Address getFirstCellAddress()
      Gets the first existing cell in the current worksheet (bottom right)
      API Note:
      getFirstCellAddress() will not return the first cell with data in any case. If there is a formatted but empty cell (or many) before the first cell with data, GetLastCellAddress() will return the address of this empty cell. Use getFirstDataCellAddress() in this case.
      Returns:
      Nullable Cell Address. If no cell address could be determined, null will be returned

    • getFirstDataCellAddress

      public Address getFirstDataCellAddress()
      Gets the first existing cell with data in the current worksheet (bottom right)
      API Note:
      getFirstDataCellAddress() will ignore formatted but empty cells before the first cell with data. If you want the first defined cell, use getFirstCellAddress() instead.
      Returns:
      Nullable Cell Address. If no cell address could be determined, null will be returned

    • insertRow

      public void insertRow(int rowNumber, int numberOfNewRows)
      Inserts 'count' rows below the specified 'rowNumber'. Existing cells are moved down by the number of new rows. The inserted, new rows inherits the style of the original cell at the defined row number. The inserted cells are empty. The values can be set later
      API Note:
      Formulas / references are not adjusted
      Parameters:
      rowNumber - Row number below which the new row(s) will be inserted.
      numberOfNewRows - Number of rows to insert.

    • insertColumn

      public void insertColumn(int columnNumber, int numberOfNewColumns)
      Inserts 'count' columns right of the specified 'columnNumber'. Existing cells are moved to the right by the number of new columns. The inserted, new columns inherits the style of the original cell at the defined column number. The inserted cells are empty. The values can be set later
      API Note:
      Formulas / references are not adjusted
      Parameters:
      columnNumber - Column number right which the new column(s) will be inserted.
      numberOfNewColumns - Number of columns to insert.

    • firstCellByValue

      public Cell firstCellByValue(Object searchValue)
      Searches for the first occurrence of the value.
      Parameters:
      searchValue - The value to search for.
      Returns:
      The first cell containing the searched value or null if the value was not found

    • firstOrDefaultCell

      public Cell firstOrDefaultCell(Predicate<Cell> predicate)
      Searches for the first occurrence of the expression. Example: Cell cell = worksheet.firstOrDefaultCell(c -> c.getValue() != null && c.getValue().toString().contains("searchValue"));
      Parameters:
      predicate - The condition to match a cell.
      Returns:
      The first cell containing the searched value or null if the value was not found.

    • cellsByValue

      public List<Cell> cellsByValue(Object searchValue)
      Searches for cells that contain the specified value and returns a list of these cells.
      Parameters:
      searchValue - The value to search for.
      Returns:
      A list of cells that contain the specified value.

    • replaceCellValue

      public int replaceCellValue(Object oldValue, Object newValue)
      Replaces all occurrences of 'oldValue' with 'newValue' and returns the number of replacements.
      Parameters:
      oldValue - Old value.
      newValue - New value that should replace the old one.
      Returns:
      Count of replaced cell values.

    • addAllowedActionOnSheetProtection

      public void addAllowedActionOnSheetProtection(Worksheet.SheetProtectionValue typeOfProtection)
      Method to add allowed actions if the worksheet is protected. If one or more values are added, UseSheetProtection will be set to true
      API Note:
      If Worksheet.SheetProtectionValue.selectLockedCells is added, Worksheet.SheetProtectionValue.selectUnlockedCells is added automatically
      Parameters:
      typeOfProtection - Allowed action on the worksheet or cells

    • addHiddenColumn

      public void addHiddenColumn(int columnNumber)
      Sets the defined column as hidden
      Parameters:
      columnNumber - Column number to hide on the worksheet
      Throws:
      RangeException - Thrown if the passed row number was out of range

    • addHiddenColumn

      public void addHiddenColumn(String columnAddress)
      Sets the defined column as hidden
      Parameters:
      columnAddress - Column address to hide on the worksheet
      Throws:
      RangeException - Thrown if the passed row number was out of range

    • addHiddenRow

      public void addHiddenRow(int rowNumber)
      Sets the defined row as hidden
      Parameters:
      rowNumber - Row number to hide on the worksheet
      Throws:
      RangeException - Thrown if the passed column number was out of range

    • clearActiveStyle

      public void clearActiveStyle()
      Clears the active style of the worksheet. All later added cells will contain no style unless another active style is set

    • getCell

      public Cell getCell(Address address)
      Gets the cell of the specified address
      Parameters:
      address - Address of the cell
      Returns:
      Cell object
      Throws:
      WorksheetException - Throws a WorksheetException if the cell was null or not found on the cell table of this worksheet

    • getCell

      public Cell getCell(String address)
      Gets the cell of the specified address as String
      Parameters:
      address - Address string of the cell
      Returns:
      Cell object
      Throws:
      WorksheetException - Throws a WorksheetException if the cell was not found on the cell table of this worksheet

    • getCell

      public Cell getCell(int columnNumber, int rowNumber)
      Gets the cell of the specified column and row number (zero-based)
      Parameters:
      columnNumber - Column number of the cell (zero-based)
      rowNumber - Row number of the cell (zero-based)
      Returns:
      Cell object
      Throws:
      WorksheetException - Throws a WorksheetException if the cell was not found on the cell table of this worksheet

    • hasCell

      public boolean hasCell(Address address)
      Gets whether the specified address exists in the worksheet. Existing means that a value was stored at the address
      Parameters:
      address - Address to check
      Returns:
      True if the cell exists, otherwise false

    • hasCell

      public boolean hasCell(int columnNumber, int rowNumber)
      Gets whether the specified address exists in the worksheet. Existing means that a value was stored at the address
      Parameters:
      columnNumber - Column number of the cell to check (zero-based)
      rowNumber - Row number of the cell to check (zero-based)
      Returns:
      True if the cell exists, otherwise false
      Throws:
      RangeException - A RangeException is thrown if the column or row number is invalid

    • resetColumn

      public void resetColumn(int columnNumber)
      Resets the defined column, if existing. The corresponding instance will be removed from getColumns()
      API Note:
      If the column is inside an autoFilter-Range, the column cannot be entirely removed from getColumns(). The hidden state will be set to false and width to default, in this case.
      Parameters:
      columnNumber - Column number to reset (zero-based)

    • getRow

      public List<Cell> getRow(int rowNumber)
      Gets a row as list of cell objects
      Parameters:
      rowNumber - Row number (zero-based)
      Returns:
      List of cell objects. If the row doesn't exist, an empty list is returned

    • getColumn

      public List<Cell> getColumn(String columnAddress)
      Gets a column as list of cell objects
      Parameters:
      columnAddress - Column address
      Returns:
      List of cell objects. If the column doesn't exist, an empty list is returned
      Throws:
      RangeException - is thrown if the address is not valid

    • getColumn

      public List<Cell> getColumn(int columnNumber)
      Gets a column as list of cell objects
      Parameters:
      columnNumber - Column number (zero-based)
      Returns:
      List of cell objects. If the column doesn't exist, an empty list is returned

    • setColumnWidth

      public void setColumnWidth(String columnAddress, float width)
      Sets the width of the passed column address
      Parameters:
      columnAddress - Column address (A - XFD)
      width - Width from 0 to 255.0
      Throws:
      RangeException - Thrown if the address is out of the valid range (from 0 to 16383) or if the width is out of range (0 to 255)

    • setColumnWidth

      public void setColumnWidth(int columnNumber, float width)
      Sets the width of the passed column number (zero-based)
      Parameters:
      columnNumber - Column number (zero-based, from 0 to 16383)
      width - Width from 0 to 255.0
      Throws:
      RangeException - Thrown if the colum number is out of the valid range (from 0 to 16383) or if the width is out of range (0 to 255)

    • setColumnDefaultStyle

      public Style setColumnDefaultStyle(String columnAddress, Style style)
      Sets the default column style of the passed column address
      Parameters:
      columnAddress - Column address (A - XFD)
      style - Style to set as default. If null, the style is cleared
      Returns:
      Assigned style or null if cleared
      Throws:
      RangeException - Thrown if the address is out of the valid range (from 0 to 16383)

    • setColumnDefaultStyle

      public Style setColumnDefaultStyle(int columnNumber, Style style)
      Sets the default column style of the passed column number (zero-based)
      Parameters:
      columnNumber - Column number (zero-based, from 0 to 16383)
      style - Style to set as default. If null, the style is cleared
      Returns:
      Assigned style or null if cleared
      Throws:
      RangeException - Thrown if the column number is out of the valid range (from 0 to 16383)

    • setCurrentCellAddress

      public void setCurrentCellAddress(String address)
      Set the current cell address
      Parameters:
      address - Cell address in the format A1 - XFD1048576
      Throws:
      RangeException - Thrown if the address is out of the valid range. Range is for rows from 0 to 1048575 (1048576 rows) and for columns from 0 to 16383 (16384 columns)
      FormatException - Thrown if the passed address is malformed

    • setSheetName

      public void setSheetName(String sheetName)
      Sets the name of the sheet
      Parameters:
      sheetName - Name of the sheet
      Throws:
      FormatException - Thrown if the name contains illegal characters or is longer than 31 characters

    • setSheetName

      public void setSheetName(String sheetName, boolean sanitize)
      Sets the name of the sheet
      Parameters:
      sheetName - Name of the sheet
      sanitize - If true, the filename will be sanitized automatically according to the specifications of Excel
      Throws:
      WorksheetException - Thrown if no workbook is referenced. This information is necessary to determine whether the name already exists

    • setHorizontalSplit

      public void setHorizontalSplit(float topPaneHeight, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the horizontal split of the worksheet into two panes. The measurement in characters cannot be used to freeze panes
      Parameters:
      topPaneHeight - Height (similar to row height) from top of the worksheet to the split line in characters
      topLeftCell - Top Left cell address of the bottom right pane (if applicable). Only the row component is important in a horizontal split
      activePane - Active pane in the split window (can be null) (can be null)

    • setHorizontalSplit

      public void setHorizontalSplit(int numberOfRowsFromTop, boolean freeze, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the horizontal split of the worksheet into two panes. The measurement in rows can be used to split and freeze panes
      Parameters:
      numberOfRowsFromTop - Number of rows from top of the worksheet to the split line. The particular row heights are considered
      freeze - If true, all panes are frozen, otherwise remains movable
      topLeftCell - Top Left cell address of the bottom right pane (if applicable). Only the row component is important in a horizontal split
      activePane - Active pane in the split window (can be null)
      Throws:
      WorksheetException - WorksheetException Thrown if the row number of the top left cell is smaller the split panes number of rows from top, if freeze is applied

    • setVerticalSplit

      public void setVerticalSplit(int numberOfColumnsFromLeft, boolean freeze, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the vertical split of the worksheet into two panes. The measurement in columns can be used to split and freeze panes
      Parameters:
      numberOfColumnsFromLeft - Number of columns from left of the worksheet to the split line. The particular column widths are considered
      freeze - If true, all panes are frozen, otherwise remains movable
      topLeftCell - Top Left cell address of the bottom right pane (if applicable). Only the column component is important in a vertical split
      activePane - Active pane in the split window (can be null)
      Throws:
      WorksheetException - Thrown if the column number of the top left cell is smaller the split panes number of columns from left, if freeze is applied

    • setVerticalSplit

      public void setVerticalSplit(float leftPaneWidth, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the vertical split of the worksheet into two panes. The measurement in characters cannot be used to freeze panes
      Parameters:
      leftPaneWidth - Width (similar to column width) from left of the worksheet to the split line in characters
      topLeftCell - Top Left cell address of the bottom right pane (if applicable). Only the column component is important in a vertical split
      activePane - Active pane in the split window (can be null)

    • setSplit

      public void setSplit(Integer numberOfColumnsFromLeft, Integer numberOfRowsFromTop, boolean freeze, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the horizontal and vertical split of the worksheet into four panes. The measurement in rows and columns can be used to split and freeze panes
      Parameters:
      numberOfColumnsFromLeft - Number of columns from left of the worksheet to the split line. The particular column widths are considered.
      The parameter is nullable. If left null, the method acts identical to setHorizontalSplit(int, boolean, Address, WorksheetPane)
      numberOfRowsFromTop - Number of rows from top of the worksheet to the split line. The particular row heights are considered.
      The parameter is nullable. If left null, the method acts identical to setVerticalSplit(int, boolean, Address, WorksheetPane)
      freeze - If true, all panes are frozen, otherwise remains movable
      topLeftCell - Top Left cell address of the bottom right pane (if applicable)
      activePane - Active pane in the split window (can be null)
      Throws:
      WorksheetException - Thrown if the address of the top left cell is smaller the split panes address, if freeze is applied

    • setSplit

      public void setSplit(Float leftPaneWidth, Float topPaneHeight, Address topLeftCell, Worksheet.WorksheetPane activePane)
      Sets the horizontal and vertical split of the worksheet into four panes. The measurement in characters cannot be used to freeze panes
      Parameters:
      leftPaneWidth - Width (similar to column width) from left of the worksheet to the split line in characters.
      The parameter is nullable. If left null, the method acts identical to setHorizontalSplit(float, Address, WorksheetPane)
      topPaneHeight - Height (similar to row height) from top of the worksheet to the split line in characters.
      The parameter is nullable. If left null, the method acts identical to setVerticalSplit(float, Address, WorksheetPane)
      topLeftCell - Top Left cell address of the bottom right pane (if applicable)
      activePane - Active pane in the split window (can be null)

    • resetSplit

      public void resetSplit()
      Resets splitting of the worksheet into panes, as well as their freezing

    • goToNextColumn

      public void goToNextColumn()
      Moves the current position to the next column

    • goToNextColumn

      public void goToNextColumn(int numberOfColumns)
      Moves the current position to the next column with the number of cells to move
      API Note:
      The value can also be negative. However, resulting column numbers below 0 or above 16383 will cause an exception
      Parameters:
      numberOfColumns - Number of columns to move

    • goToNextColumn

      public void goToNextColumn(int numberOfColumns, boolean keepRowPosition)
      Moves the current position to the next column with the number of cells to move and the option to keep the row position
      API Note:
      The value can also be negative. However, resulting column numbers below 0 or above 16383 will cause an exception
      Parameters:
      numberOfColumns - Number of columns to move
      keepRowPosition - If true, the row position is preserved, otherwise set to 0

    • goToNextRow

      public void goToNextRow()
      Moves the current position to the next row (use for a new line)

    • goToNextRow

      public void goToNextRow(int numberOfRows)
      Moves the current position to the next row with the number of cells to move (use for a new line)
      API Note:
      The value can also be negative. However, resulting row numbers below 0 or above 1048575 will cause an exception
      Parameters:
      numberOfRows - Number of rows to move

    • goToNextRow

      public void goToNextRow(int numberOfRows, boolean keepColumnPosition)
      Moves the current position to the next row with the number of cells to move and the option to keep the row position (use for a new line)
      API Note:
      The value can also be negative. However, resulting row numbers below 0 or above 1048575 will cause an exception
      Parameters:
      numberOfRows - Number of rows to move
      keepColumnPosition - If true, the column position is preserved, otherwise set to 0

    • mergeCells

      public String mergeCells(Range cellRange)
      Merges the defined cell range
      Parameters:
      cellRange - Range to merge
      Returns:
      Returns the validated range of the merged cells (e.g. 'A1:B12')

    • mergeCells

      public String mergeCells(String cellRange)
      Merges the defined cell range
      Parameters:
      cellRange - Range to merge (e.g. 'A1:B12')
      Returns:
      Returns the validated range of the merged cells (e.g. 'A1:B12')
      Throws:
      FormatException - Thrown if the passed address is malformed

    • mergeCells

      public String mergeCells(Address startAddress, Address endAddress)
      Merges the defined cell range
      Parameters:
      startAddress - Start address of the merged cell range
      endAddress - End address of the merged cell range
      Returns:
      Returns the validated range of the merged cells (e.g. 'A1:B12')
      Throws:
      RangeException - Thrown if one of the passed cell addresses is out of range or if one or more cell addresses are already occupied in another merge range

    • recalculateAutoFilter

      public void recalculateAutoFilter()
      Method to recalculate the auto filter (columns) of this worksheet. This is an internal method. There is no need to use it. It must be public to require access from the XlsXWriter class

    • recalculateColumns

      public void recalculateColumns()
      Method to recalculate the collection of columns of this worksheet. This is an internal method. There is no need to use it. It must be public to require access from the XlsXWriter class

    • resolveMergedCells

      public void resolveMergedCells()
      Method to resolve all merged cells of the worksheet. Only the value of the very first cell of the merged cells range will be visible. The other values are still present (set to EMPTY) but will not be stored in the worksheet.
      This is an internal method. There is no need to use it.
      Throws:
      StyleException - Thrown if an unreferenced style was in the style sheet
      RangeException - Thrown if the cell range was not found

    • removeAutoFilter

      public void removeAutoFilter()
      Removes auto filters from the worksheet

    • removeHiddenColumn

      public void removeHiddenColumn(int columnNumber)
      Sets a previously defined, hidden column as visible again
      Parameters:
      columnNumber - Column number to make visible again
      Throws:
      RangeException - Thrown if the passed row number was out of range

    • removeHiddenColumn

      public void removeHiddenColumn(String columnAddress)
      Sets a previously defined, hidden column as visible again
      Parameters:
      columnAddress - Column address to make visible again
      Throws:
      RangeException - Thrown if the passed row number was out of range

    • removeHiddenRow

      public void removeHiddenRow(int rowNumber)
      Sets a previously defined, hidden row as visible again
      Parameters:
      rowNumber - Row number to hide on the worksheet
      Throws:
      RangeException - Thrown if the passed column number was out of range

    • removeMergedCells

      public void removeMergedCells(String range)
      Removes the defined merged cell range
      Parameters:
      range - Cell range to remove the merging
      Throws:
      RangeException - Thrown if the passed cell range was not merged earlier
      FormatException - Thrown if the passed address is malformed

    • removeSelectedCells

      public void removeSelectedCells()
      Removes the cell selection of this worksheet

    • removeRowHeight

      public void removeRowHeight(int rowNumber)
      Removes the defined, non-standard row height
      Parameters:
      rowNumber - Row number (zero-based)

    • removeAllowedActionOnSheetProtection

      public void removeAllowedActionOnSheetProtection(Worksheet.SheetProtectionValue value)
      Removes an allowed action on the current worksheet or its cells
      Parameters:
      value - Allowed action on the worksheet or cells

    • setActiveStyle

      public void setActiveStyle(Style style)
      Sets the active style of the worksheet. This style will be assigned to all later added cells
      Parameters:
      style - Style to set as active style
      Throws:
      StyleException - Thrown if the worksheet has no workbook referenced when trying to set the active style

    • setAutoFilter

      public void setAutoFilter(int startColumn, int endColumn)
      Sets the column auto filter within the defined column range
      Parameters:
      startColumn - Column number with the first appearance of an auto filter drop down
      endColumn - Column number with the last appearance of an auto filter drop down
      Throws:
      RangeException - Thrown if one of the passed column numbers are out of range

    • setAutoFilter

      public void setAutoFilter(String range)
      Sets the column auto filter within the defined column range
      Parameters:
      range - Range to apply auto filter on. The range could be 'A1:C10' for instance. The end row will be recalculated automatically when saving the file
      Throws:
      RangeException - Throws a RangeException if the passed range out of range
      FormatException - Throws an FormatException if the passed range is malformed

    • setCurrentCellAddress

      public void setCurrentCellAddress(int columnNumber, int rowNumber)
      Set the current cell address
      Parameters:
      columnNumber - Column number (zero based)
      rowNumber - Row number (zero based)
      Throws:
      RangeException - Thrown if the address is out of the valid range. Range is for rows from 0 to 1048575 (1048576 rows) and for columns from 0 to 16383 (16384 columns)

    • setRowHeight

      public void setRowHeight(int rowNumber, float height)
      Sets the height of the passed row number (zero-based)
      Parameters:
      rowNumber - Row number (zero-based, 0 to 1048575)
      height - Height from 0 to 409.5
      Throws:
      RangeException - Thrown if the address is out of the valid range. Range is from 0 to 1048575 (1048576 rows)

    • copy

      public Worksheet copy()
      Creates a (dereferenced) deep copy of this worksheet
      API Note:
      Not considered in the copy are the internal ID, the worksheet name and the workbook reference. Since styles are managed in a shared repository, no dereferencing is applied (Styles are not deep-copied).
      Use Workbook.copyWorksheetTo(Worksheet, String, Workbook)} or Workbook.copyWorksheetIntoThis(Worksheet, String) to add a copy of worksheet to a workbook. These methods will set the internal ID, name and workbook reference.
      Returns:
      Copy of this worksheet

    • setZoomFactor

      public void setZoomFactor(Worksheet.SheetViewType sheetViewType, int zoomFactor)
      Sets a zoom factor for a given Worksheet.SheetViewType. If AUTO_ZOOM_FACTOR, the zoom factor is set to automatic
      API Note:
      This factor is not the currently set factor. use the setter setZoomFactor(SheetViewType, int) to set the factor for the current Worksheet.SheetViewType
      Parameters:
      sheetViewType - Sheet view type to apply the zoom factor on
      zoomFactor - Zoom factor in percent
      Throws:
      WorksheetException - Thrown if the zoom factor is not AUTO_ZOOM_FACTOR or below MIN_ZOOM_FACTOR or above MAX_ZOOM_FACTOR

    • sanitizeWorksheetName

      public static String sanitizeWorksheetName(String input, Workbook workbook)
      Sanitizes a worksheet name
      Parameters:
      input - Name to sanitize
      workbook - Workbook reference
      Returns:
      Name of the sanitized worksheet
      Throws:
      WorksheetException - thrown if the workbook reference is null, since all worksheets have to be considered during sanitation