Package ch.rabanti.picoxlsx4j

Class Workbook

  • public class Workbook
extends Object
    Class representing a workbook
    Author:
    Raphael Stoeckli

    • Field Detail

      • WS

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

    • Constructor Detail

      • 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
        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 Detail

      • getCurrentWorksheet

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

      • getFilename

        public String getFilename()
        Gets the filename of the workbook
        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

      • 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

      • 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

      • getWorkbookMetadata

        public Metadata getWorkbookMetadata()
        Gets the meta data object of the workbook
        Returns:
        Meta data object

      • setWorkbookMetadata

        public void setWorkbookMetadata​(Metadata workbookMetadata)
        Sets the meta data object of the workbook
        Parameters:
        workbookMetadata - Meta data 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
        Returns:
        Password (UTF-8)

      • 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

      • getStyleManager

        public StyleManager getStyleManager()
        Gets the style manager of this workbook
        Returns:
        Style manager object

      • addStyle

        public Style addStyle​(Style style)
        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)
        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

      • removeStyle

        public void removeStyle​(Style style)
        Removes the passed style from the style sheet
        Parameters:
        style - Style to remove
        Throws:
        StyleException - Thrown if the style is not defined in the style sheet

      • removeStyle

        public void removeStyle​(String styleName)
        Removes the defined style from the style sheet of the workbook
        Parameters:
        styleName - Name of the style to be removed
        Throws:
        StyleException - Thrown if the style is not defined in the style sheet

      • removeStyle

        public void removeStyle​(Style style,
                        boolean onlyIfUnused)
        Removes the defined style from the style manager of the workbook
        Parameters:
        style - Style to remove
        onlyIfUnused - If true, the style will only be removed if not used in any cell
        Throws:
        StyleException - Thrown if the style is not defined in the style sheet

      • removeStyle

        public void removeStyle​(String styleName,
                        boolean onlyIfUnused)
        Removes the defined style from the style manager of the workbook
        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
        Parameters:
        name - Name of the worksheet
        Throws:
        WorksheetException - Thrown if the name of the worksheet is unknown

      • resolveMergedCells

        public void resolveMergedCells()
        Method to resolve all merged cells in all worksheets. Only the value of the very first cell of the locked cells range will be visible. The other values are still present (set to EMPTY) but will not be stored in the worksheet.
        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 a 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

      • 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