Package ch.rabanti.nanoxlsx4j

Class Workbook

java.lang.Object
ch.rabanti.nanoxlsx4j.Workbook
public class Workbook extends Object
Class representing a workbook
Author:
Raphael Stoeckli

  • Field Details

    • WS

      public Shortener WS
      Shortener omits getter and setter to simplify the access (Can throw a WorksheetException if not defined)

  • Constructor Details

    • Workbook

      public Workbook()
      Default constructor. No initial worksheet is created. Use addWorksheet(String) (or overloads) to add one

    • Workbook

      public Workbook(boolean createWorksheet)
      Constructor with additional parameter to create a default worksheet. This constructor can be used to define a workbook that is saved as stream
      Parameters:
      createWorksheet - If true, a default worksheet with the name 'Sheet1' will be created and set as current worksheet

    • Workbook

      public Workbook(String sheetName)
      Constructor with additional parameter to create a default worksheet with the specified name. This constructor can be used to define a workbook that is saved as stream
      Parameters:
      sheetName - Name of the first worksheet. The name will be sanitized automatically according to the specifications of Excel
      Throws:
      FormatException - Thrown if the worksheet name contains illegal characters

    • Workbook

      public Workbook(String filename, String sheetName)
      Constructor with filename ant the name of the first worksheet. This constructor can be used to define a workbook that is saved as stream
      Parameters:
      filename - Filename of the workbook
      sheetName - Name of the first worksheet. The name will be sanitized automatically according to the specifications of Excel
      Throws:
      FormatException - Thrown if the worksheet name contains illegal characters

    • Workbook

      public Workbook(String filename, String sheetName, boolean sanitizeSheetName)
      Constructor with filename ant the name of the first worksheet
      Parameters:
      filename - Filename of the workbook
      sheetName - Name of the first worksheet
      sanitizeSheetName - If true, the name of the worksheet will be sanitized automatically according to the specifications of Excel

  • Method Details

    • getCurrentWorksheet

      public Worksheet getCurrentWorksheet()
      Gets the current worksheet
      Returns:
      Current worksheet reference

    • getFilename

      public String getFilename()
      Gets the filename of the workbook
      API Note:
      Note that the file name is not sanitized. If a filename is set that is not compliant to the file system, saving of the workbook may fail
      Returns:
      Filename of the workbook

    • setFilename

      public void setFilename(String filename)
      Sets the filename of the workbook
      Parameters:
      filename - Filename of the workbook

    • getSelectedWorksheet

      public int getSelectedWorksheet()
      Gets the selected worksheet. The selected worksheet is not the current worksheet while design time but the selected sheet in the output file
      Returns:
      Zero-based worksheet index

    • getWorkbookMetadata

      public Metadata getWorkbookMetadata()
      Gets the metadata object of the workbook
      Returns:
      Metadata object

    • setWorkbookMetadata

      public void setWorkbookMetadata(Metadata workbookMetadata)
      Sets the metadata object of the workbook
      Parameters:
      workbookMetadata - Metadata object

    • setWorkbookProtection

      public void setWorkbookProtection(boolean useWorkbookProtection)
      Sets whether the workbook is protected
      Parameters:
      useWorkbookProtection - If true, the workbook is protected otherwise not

    • getWorkbookProtectionPassword

      public String getWorkbookProtectionPassword()
      Gets the password used for workbook protection
      API Note:
      The password of this property is stored in plan text at runtime but not stored to a workbook. See also getWorkbookProtectionPassword() for the generated hash
      Returns:
      Password (UTF-8)

    • getWorkbookProtectionPasswordHash

      public String getWorkbookProtectionPasswordHash()
      Hash of the protected workbook, originated from getWorkbookProtectionPassword()
      API Note:
      The plain text password cannot be recovered when loading a workbook. The hash is retrieved and can be reused, if no changes are made in the area of workbook protection (setWorkbookProtection(boolean, boolean, boolean, String))
      Returns:
      Hash for the workbook protection

    • setWorkbookProtectionPasswordHash

      public void setWorkbookProtectionPasswordHash(String workbookProtectionPasswordHash)
      Sets the password hash for the protection, generated by Helper.generatePasswordHash(String)
      API Note:
      This method should only be used internally. Please use setWorkbookProtection(boolean, boolean, boolean, String) to set a workbook password
      Parameters:
      workbookProtectionPasswordHash - Password hash as string

    • getWorksheets

      public List<Worksheet> getWorksheets()
      Gets the list of worksheets in the workbook
      Returns:
      List of worksheet objects

    • isStructureLockedIfProtected

      public boolean isStructureLockedIfProtected()
      Gets whether the structure are locked if workbook is protected
      Returns:
      True if the structure is locked when the workbook is protected

    • isWindowsLockedIfProtected

      public boolean isWindowsLockedIfProtected()
      Gets whether the windows are locked if workbook is protected
      Returns:
      True if the windows are locked when the workbook is protected

    • isWorkbookProtectionUsed

      public boolean isWorkbookProtectionUsed()
      Gets whether the workbook is protected
      Returns:
      If true, the workbook is protected otherwise not

    • isHidden

      public boolean isHidden()
      Gets whether the workbook is hidden
      Implementation Note:
      A hidden workbook can only be made visible, using another, already visible Excel window
      Returns:
      If true hidden, otherwise visible

    • setHidden

      public void setHidden(boolean hidden)
      Sets whether the workbook is hidden
      Implementation Note:
      A hidden workbook can only be made visible, using another, already visible Excel window
      Parameters:
      hidden - If true hidden, otherwise visible

    • addMruColor

      public void addMruColor(String color)
      Adds a color value (HEX; 6-digit RGB or 8-digit ARGB) to the MRU list
      Parameters:
      color - RGB code in hex format (either 6 characters, e.g. FF00AC or 8 characters with leading alpha value). Alpha will be set to full opacity (FF) in case of 6 characters

    • getMruColors

      public List<String> getMruColors()
      Gets the MRU color list
      Returns:
      Immutable list of color values

    • clearMruColors

      public void clearMruColors()
      Clears the MRU color list

    • addStyle

      public Style addStyle(Style style)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated
      Adds a style to the style manager
      Parameters:
      style - Style to add
      Returns:
      The managed style of the style manager

    • addStyleComponent

      public Style addStyleComponent(Style baseStyle, AbstractStyle newComponent)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated.
      Adds a style component to a style
      Parameters:
      baseStyle - Style to append a component
      newComponent - Component to add to the baseStyle
      Returns:
      The managed style of the style manager

    • addWorksheet

      public void addWorksheet(String name)
      Adding a new Worksheet. The new worksheet will be defined as current worksheet
      Parameters:
      name - Name of the new worksheet
      Throws:
      WorksheetException - Thrown if the name of the worksheet already exists
      FormatException - Thrown if the worksheet name contains illegal characters or is out of range (length between 1 an 31)

    • addWorksheet

      public void addWorksheet(String name, boolean sanitizeSheetName)
      Adding a new Worksheet with a sanitizing option. The new worksheet will be defined as current worksheet
      Parameters:
      name - Name of the new worksheet
      sanitizeSheetName - If true, the name of the worksheet will be sanitized automatically according to the specifications of Excel
      Throws:
      WorksheetException - Thrown if the name of the worksheet already exists and sanitizeSheetName is false
      FormatException - Thrown if the worksheet name contains illegal characters or is out of range (length between 1 an 31) and sanitizeSheetName is false

    • addWorksheet

      public void addWorksheet(Worksheet worksheet)
      Adding a new Worksheet. The new worksheet will be defined as current worksheet
      Parameters:
      worksheet - Prepared worksheet object
      Throws:
      WorksheetException - Thrown if the name of the worksheet already exists
      FormatException - Thrown if the worksheet name contains illegal characters or is out of range (length between 1 an 31

    • addWorksheet

      public void addWorksheet(Worksheet worksheet, boolean sanitizeSheetName)
      Adding a new Worksheet. The new worksheet will be defined as current worksheet
      Parameters:
      worksheet - Prepared worksheet object
      Throws:
      WorksheetException - Thrown if the name of the worksheet already exists, when sanitation is false
      FormatException - Thrown if the worksheet name contains illegal characters or is out of range (length between 1 an 31) and sanitation is false

    • removeStyle

      public void removeStyle(Style style)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated.
      Removes the passed style from the style sheet
      API Note:
      Note: This method is available due to compatibility reasons. Added styles are actually not removed by it since unused styles are disposed automatically
      Parameters:
      style - Style to remove

    • removeStyle

      public void removeStyle(String styleName)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated.
      Removes the defined style from the style sheet of the workbook
      API Note:
      Note: This method is available due to compatibility reasons. Added styles are actually not removed by it since unused styles are disposed automatically
      Parameters:
      styleName - Name of the style to be removed

    • removeStyle

      public void removeStyle(Style style, boolean onlyIfUnused)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated.
      Removes the defined style from the style manager of the workbook
      API Note:
      Note: This method is available due to compatibility reasons. Added styles are actually not removed by it since unused styles are disposed automatically
      Parameters:
      style - Style to remove
      onlyIfUnused - If true, the style will only be removed if not used in any cell

    • removeStyle

      public void removeStyle(String styleName, boolean onlyIfUnused)
      Deprecated.
      This method has no direct impact on the generated file and is deprecated.
      Removes the defined style from the style manager of the workbook
      API Note:
      Note: This method is available due to compatibility reasons. Added styles are actually not removed by it since unused styles are disposed automatically
      Parameters:
      styleName - Name of the style to remove
      onlyIfUnused - If true, the style will only be removed if not used in any cell

    • removeWorksheet

      public void removeWorksheet(String name)
      Removes the defined worksheet based on its name. If the worksheet is the current or selected worksheet, the current and / or the selected worksheet will be set to the last worksheet of the workbook. If the last worksheet is removed, the selected worksheet will be set to 0 and the current worksheet to null.
      Parameters:
      name - Name of the worksheet
      Throws:
      WorksheetException - thrown if the name of the worksheet is unknown

    • removeWorksheet

      public void removeWorksheet(int index)
      Removes the defined worksheet based on its index. If the worksheet is the current or selected worksheet, the current and / or the selected worksheet will be set to the last worksheet of the workbook. If the last worksheet is removed, the selected worksheet will be set to 0 and the current worksheet to null.
      Parameters:
      index - Index within the worksheets list
      Throws:
      WorksheetException - thrown if the index is out of range

    • resolveMergedCells

      public void resolveMergedCells()
      Method to resolve all merged cells in all worksheets. 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

    • save

      public void save() throws IOException
      Saves the workbook
      Throws:
      IOException - Throws IOException in case of an error

    • saveAs

      public void saveAs(String filename) throws IOException
      Saves the workbook with the defined name
      Parameters:
      filename - Filename of the saved workbook
      Throws:
      IOException - Thrown in case of an error

    • saveAsStream

      public void saveAsStream(OutputStream stream) throws IOException
      Save the workbook to an output stream
      Parameters:
      stream - Output Stream
      Throws:
      IOException - Thrown in case of an error

    • setCurrentWorksheet

      public Worksheet setCurrentWorksheet(String name)
      Sets the current worksheet
      Parameters:
      name - Name of the worksheet
      Returns:
      Returns the current worksheet
      Throws:
      WorksheetException - Thrown if the name of the worksheet is unknown

    • setCurrentWorksheet

      public Worksheet setCurrentWorksheet(int worksheetIndex)
      Sets the current worksheet
      Parameters:
      worksheetIndex - Zero-based worksheet index
      Returns:
      Returns the current worksheet
      Throws:
      WorksheetException - Thrown if the name of the worksheet is unknown

    • setCurrentWorksheet

      public void setCurrentWorksheet(Worksheet worksheet)
      Sets the current worksheet
      Parameters:
      worksheet - Worksheet object (must be in the collection of worksheets)
      Throws:
      WorksheetException - Thrown if the worksheet was not found in the worksheet collection

    • setSelectedWorksheet

      public void setSelectedWorksheet(String name)
      Sets the selected worksheet in the output workbook
      Parameters:
      name - Name of the worksheet
      Throws:
      WorksheetException - Throws a WorksheetException if the name of the worksheet is unknown or if it is hidden
      RangeException - Throws a RangeException if the index of the worksheet is out of range

    • setSelectedWorksheet

      public void setSelectedWorksheet(int worksheetIndex)
      Sets the selected worksheet in the output workbook
      Note: This method does not set the current worksheet while design time. Use SetCurrentWorksheet instead for this
      Parameters:
      worksheetIndex - Zero-based worksheet index
      Throws:
      RangeException - Throws a RangeException if the index of the worksheet is out of range
      WorksheetException - Throws a WorksheetException if the worksheet is hidden

    • setSelectedWorksheet

      public void setSelectedWorksheet(Worksheet worksheet)
      Sets the selected worksheet in the output workbook
      Note: This method does not set the current worksheet while design time. Use SetCurrentWorksheet instead for this
      Parameters:
      worksheet - Worksheet object (must be in the collection of worksheets)
      Throws:
      WorksheetException - Throws a WorksheetException if the worksheet was not found in the worksheet collection or if it is hidden

    • getWorksheet

      public Worksheet getWorksheet(int index)
      Gets a worksheet from this workbook by index
      Parameters:
      index - Index of the worksheet
      Returns:
      Worksheet with the passed index
      Throws:
      RangeException - Throws a RangeException if the worksheet was not found in the worksheet collection

    • getWorksheet

      public Worksheet getWorksheet(String name)
      Gets a worksheet from this workbook by name
      Parameters:
      name - Name of the worksheet
      Returns:
      Worksheet with the passed name
      Throws:
      WorksheetException - Throws a WorksheetException if the worksheet was not found in the worksheet collection

    • setWorkbookProtection

      public void setWorkbookProtection(boolean state, boolean protectWindows, boolean protectStructure, String password)
      Sets or removes the workbook protection. If protectWindows and protectStructure are both false, the workbook will not be protected
      Parameters:
      state - If true, the workbook will be protected, otherwise not
      protectWindows - If true, the windows will be locked if the workbook is protected
      protectStructure - If true, the structure will be locked if the workbook is protected
      password - Optional password. If null or empty, no password will be set in case of protection

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(String sourceWorksheetName, String newWorksheetName)
      Copies a worksheet of the current workbook by its name
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetName - Name of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy). The name will be sanitized
      Returns:
      Copied worksheet

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(String sourceWorksheetName, String newWorksheetName, boolean sanitizeSheetName)
      Copies a worksheet of the current workbook by its name
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetName - Name of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy)
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(int sourceWorksheetIndex, String newWorksheetName)
      Copies a worksheet of the current workbook by its index
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetIndex - Index of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy). The name will be sanitized
      Returns:
      Copied worksheet

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(int sourceWorksheetIndex, String newWorksheetName, boolean sanitizeSheetName)
      Copies a worksheet of the current workbook by its index
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetIndex - Index of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy)
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(Worksheet sourceWorksheet, String newWorksheetName)

    • copyWorksheetIntoThis

      public Worksheet copyWorksheetIntoThis(Worksheet sourceWorksheet, String newWorksheetName, boolean sanitizeSheetName)
      Copies a worksheet of any workbook into the current workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept. The source worksheet can originate from any workbook
      Parameters:
      sourceWorksheet - Worksheet to copy
      newWorksheetName - Name of the new worksheet (copy)
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public Worksheet copyWorksheetTo(String sourceWorksheetName, String newWorksheetName, Workbook targetWorkbook)
      Copies a worksheet of the current workbook by its name into another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetName - Name of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy). The name will be sanitized
      targetWorkbook - Workbook to copy the worksheet into
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public Worksheet copyWorksheetTo(String sourceWorksheetName, String newWorksheetName, Workbook targetWorkbook, boolean sanitizeSheetName)
      Copies a worksheet of the current workbook by its name into another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetName - Name of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy)
      targetWorkbook - Workbook to copy the worksheet into
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public Worksheet copyWorksheetTo(int sourceWorksheetIndex, String newWorksheetName, Workbook targetWorkbook)
      Copies a worksheet of the current workbook by its index into another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetIndex - Index of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy). The name will be sanitized
      targetWorkbook - Workbook to copy the worksheet into
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public Worksheet copyWorksheetTo(int sourceWorksheetIndex, String newWorksheetName, Workbook targetWorkbook, boolean sanitizeSheetName)
      Copies a worksheet of the current workbook by its index into another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheetIndex - Index of the worksheet to copy, originated in this workbook
      newWorksheetName - Name of the new worksheet (copy)
      targetWorkbook - Workbook to copy the worksheet into
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public static Worksheet copyWorksheetTo(Worksheet sourceWorksheet, String newWorksheetName, Workbook targetWorkbook)
      Copies a worksheet of any workbook into the another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheet - Worksheet to copy
      newWorksheetName - Name of the new worksheet (copy). The name will be sanitized
      targetWorkbook - Workbook to copy the worksheet into
      Returns:
      Copied worksheet

    • copyWorksheetTo

      public static Worksheet copyWorksheetTo(Worksheet sourceWorksheet, String newWorksheetName, Workbook targetWorkbook, boolean sanitizeSheetName)
      Copies a worksheet of any workbook into the another workbook
      API Note:
      The copy is not set as current worksheet. The existing one is kept
      Parameters:
      sourceWorksheet - Worksheet to copy
      newWorksheetName - Name of the new worksheet (copy)
      targetWorkbook - Workbook to copy the worksheet into
      sanitizeSheetName - If true, the new name will be automatically sanitized if a name collision occurs
      Returns:
      Copied worksheet

    • validateWorksheets

      public void validateWorksheets()
      Validates the worksheets regarding several conditions that must be met:
      - At least one worksheet must be defined
      - A hidden worksheet cannot be the selected one
      - At least one worksheet must be visible
      If one of the conditions is not met, an exception is thrown
      API Note:
      If an import is in progress, these rules are disabled to avoid conflicts by the order of loaded worksheets

    • load

      public static Workbook load(String filename) throws IOException, IOException
      Loads a workbook from a file
      Parameters:
      filename - Filename of the workbook
      Returns:
      Workbook object
      Throws:
      IOException - Throws IOException in case of an error
      IOException

    • load

      public static Workbook load(String filename, ImportOptions importOptions) throws IOException, IOException
      Loads a workbook from a file with import options
      Parameters:
      filename - Filename of the workbook
      importOptions - Import options to override the data types of columns or cells. These options can be used to cope with wrong interpreted data, caused by irregular styles
      Returns:
      Workbook object
      Throws:
      IOException - Throws IOException in case of an error
      IOException

    • load

      public static Workbook load(InputStream stream) throws IOException, IOException
      Loads a workbook from an input stream
      Parameters:
      stream - Stream containing the workbook
      Returns:
      Workbook object
      Throws:
      IOException - Throws IOException in case of an error
      IOException

    • load

      public static Workbook load(InputStream stream, ImportOptions importOptions) throws IOException, IOException
      Loads a workbook from an input stream with import options
      Parameters:
      stream - Stream containing the workbook
      importOptions - Import options to override the data types of columns or cells. These options can be used to cope with wrong interpreted data, caused by irregular styles
      Returns:
      Workbook object
      Throws:
      IOException - Throws IOException in case of an error
      IOException

    • setImportState

      public void setImportState(boolean state)
      Sets the import state. If an import is in progress, no validity checks on are performed to avoid conflicts by incomplete data (e.g. hidden worksheets)
      Parameters:
      state - True if an import is in progress, otherwise false