Package com.simplicite.util.tools

Class ExcelPOITool

  • public class ExcelPOITool
extends java.lang.Object
    Microsoft Excel binary using Apache POI toolbox

    • Field Detail

      • COLOR_WHITE

        public static final short COLOR_WHITE
        White

      • COLOR_BLUE

        public static final short COLOR_BLUE
        Blue

      • COLOR_RED

        public static final short COLOR_RED
        Red

      • COLOR_GREEN

        public static final short COLOR_GREEN
        Green

      • COLOR_GREY

        public static final short COLOR_GREY
        Grey

      • TYPE_STRING

        public static final org.apache.poi.ss.usermodel.CellType TYPE_STRING
        String type

      • TYPE_NUMERIC

        public static final org.apache.poi.ss.usermodel.CellType TYPE_NUMERIC
        Numeric type

      • TYPE_FORMULA

        public static final org.apache.poi.ss.usermodel.CellType TYPE_FORMULA
        Formula type

      • STRING_HEAD_WHITE

        public static final java.lang.String STRING_HEAD_WHITE
        String head cell white
        See Also:
        Constant Field Values

      • STRING_HEAD_GREY

        public static final java.lang.String STRING_HEAD_GREY
        String head cell grey
        See Also:
        Constant Field Values

      • NUMERIC_HEAD_WHITE

        public static final java.lang.String NUMERIC_HEAD_WHITE
        Numeric head cell white
        See Also:
        Constant Field Values

      • NUMERIC_HEAD_GREY

        public static final java.lang.String NUMERIC_HEAD_GREY
        Numeric head cell grey
        See Also:
        Constant Field Values

      • FORMULA_HEAD_WHITE

        public static final java.lang.String FORMULA_HEAD_WHITE
        Formula head cell white
        See Also:
        Constant Field Values

      • FORMULA_HEAD_GREY

        public static final java.lang.String FORMULA_HEAD_GREY
        Formula head cell grey
        See Also:
        Constant Field Values

      • STRING_CELL_WHITE

        public static final java.lang.String STRING_CELL_WHITE
        String cell white
        See Also:
        Constant Field Values

      • STRING_CELL_BLUE

        public static final java.lang.String STRING_CELL_BLUE
        String cell blue
        See Also:
        Constant Field Values

      • STRING_CELL_RED

        public static final java.lang.String STRING_CELL_RED
        String cell red
        See Also:
        Constant Field Values

      • STRING_CELL_GREEN

        public static final java.lang.String STRING_CELL_GREEN
        String cell green
        See Also:
        Constant Field Values

      • STRING_CELL_GREY

        public static final java.lang.String STRING_CELL_GREY
        String cell grey
        See Also:
        Constant Field Values

      • INTEGER_CELL_WHITE

        public static final java.lang.String INTEGER_CELL_WHITE
        Integer cell white
        See Also:
        Constant Field Values

      • INTEGER_CELL_BLUE

        public static final java.lang.String INTEGER_CELL_BLUE
        Integer cell blue
        See Also:
        Constant Field Values

      • INTEGER_CELL_RED

        public static final java.lang.String INTEGER_CELL_RED
        Integer cell red
        See Also:
        Constant Field Values

      • INTEGER_CELL_GREEN

        public static final java.lang.String INTEGER_CELL_GREEN
        Integer cell green
        See Also:
        Constant Field Values

      • INTEGER_CELL_GREY

        public static final java.lang.String INTEGER_CELL_GREY
        Integer cell grey
        See Also:
        Constant Field Values

      • INTEGER_EURO_CELL_WHITE

        public static final java.lang.String INTEGER_EURO_CELL_WHITE
        Integer cell white with Euro sign
        See Also:
        Constant Field Values

      • INTEGER_EURO_CELL_BLUE

        public static final java.lang.String INTEGER_EURO_CELL_BLUE
        Integer cell blue with Euro sign
        See Also:
        Constant Field Values

      • INTEGER_EURO_CELL_RED

        public static final java.lang.String INTEGER_EURO_CELL_RED
        Integer cell red with Euro sign
        See Also:
        Constant Field Values

      • INTEGER_EURO_CELL_GREEN

        public static final java.lang.String INTEGER_EURO_CELL_GREEN
        Integer cell green with Euro sign
        See Also:
        Constant Field Values

      • INTEGER_EURO_CELL_GREY

        public static final java.lang.String INTEGER_EURO_CELL_GREY
        Integer cell grey with Euro sign
        See Also:
        Constant Field Values

      • INTEGER_DOLLAR_CELL_WHITE

        public static final java.lang.String INTEGER_DOLLAR_CELL_WHITE
        Integer cell white with Dollar sign
        See Also:
        Constant Field Values

      • INTEGER_DOLLAR_CELL_BLUE

        public static final java.lang.String INTEGER_DOLLAR_CELL_BLUE
        Integer cell blue with Dollar sign
        See Also:
        Constant Field Values

      • INTEGER_DOLLAR_CELL_RED

        public static final java.lang.String INTEGER_DOLLAR_CELL_RED
        Integer cell red with Dollar sign
        See Also:
        Constant Field Values

      • INTEGER_DOLLAR_CELL_GREEN

        public static final java.lang.String INTEGER_DOLLAR_CELL_GREEN
        Integer cell green with Dollar sign
        See Also:
        Constant Field Values

      • INTEGER_DOLLAR_CELL_GREY

        public static final java.lang.String INTEGER_DOLLAR_CELL_GREY
        Integer cell grey with Dollar sign
        See Also:
        Constant Field Values

      • INTEGER_CHF_CELL_WHITE

        public static final java.lang.String INTEGER_CHF_CELL_WHITE
        Integer cell white with CHF sign
        See Also:
        Constant Field Values

      • INTEGER_CHF_CELL_BLUE

        public static final java.lang.String INTEGER_CHF_CELL_BLUE
        Integer cell blue with CHF sign
        See Also:
        Constant Field Values

      • INTEGER_CHF_CELL_RED

        public static final java.lang.String INTEGER_CHF_CELL_RED
        Integer cell red with CHF sign
        See Also:
        Constant Field Values

      • INTEGER_CHF_CELL_GREEN

        public static final java.lang.String INTEGER_CHF_CELL_GREEN
        Integer cell green with CHF sign
        See Also:
        Constant Field Values

      • INTEGER_CHF_CELL_GREY

        public static final java.lang.String INTEGER_CHF_CELL_GREY
        Integer cell grey with CHF sign
        See Also:
        Constant Field Values

      • NUMERIC_CELL_WHITE

        public static final java.lang.String NUMERIC_CELL_WHITE
        Numeric cell white
        See Also:
        Constant Field Values

      • NUMERIC_CELL_BLUE

        public static final java.lang.String NUMERIC_CELL_BLUE
        Numeric cell blue
        See Also:
        Constant Field Values

      • NUMERIC_CELL_RED

        public static final java.lang.String NUMERIC_CELL_RED
        Numeric cell red
        See Also:
        Constant Field Values

      • NUMERIC_CELL_GREEN

        public static final java.lang.String NUMERIC_CELL_GREEN
        Numeric cell green
        See Also:
        Constant Field Values

      • NUMERIC_CELL_GREY

        public static final java.lang.String NUMERIC_CELL_GREY
        Numeric cell grey
        See Also:
        Constant Field Values

      • NUMERIC_EURO_CELL_WHITE

        public static final java.lang.String NUMERIC_EURO_CELL_WHITE
        Numeric cell white with Euro sign
        See Also:
        Constant Field Values

      • NUMERIC_EURO_CELL_BLUE

        public static final java.lang.String NUMERIC_EURO_CELL_BLUE
        Numeric cell blue with Euro sign
        See Also:
        Constant Field Values

      • NUMERIC_EURO_CELL_RED

        public static final java.lang.String NUMERIC_EURO_CELL_RED
        Numeric cell red with Euro sign
        See Also:
        Constant Field Values

      • NUMERIC_EURO_CELL_GREEN

        public static final java.lang.String NUMERIC_EURO_CELL_GREEN
        Numeric cell green with Euro sign
        See Also:
        Constant Field Values

      • NUMERIC_EURO_CELL_GREY

        public static final java.lang.String NUMERIC_EURO_CELL_GREY
        Numeric cell grey with Euro sign
        See Also:
        Constant Field Values

      • NUMERIC_DOLLAR_CELL_WHITE

        public static final java.lang.String NUMERIC_DOLLAR_CELL_WHITE
        Numeric cell white with Dollar sign
        See Also:
        Constant Field Values

      • NUMERIC_DOLLAR_CELL_BLUE

        public static final java.lang.String NUMERIC_DOLLAR_CELL_BLUE
        Numeric cell blue with Dollar sign
        See Also:
        Constant Field Values

      • NUMERIC_DOLLAR_CELL_RED

        public static final java.lang.String NUMERIC_DOLLAR_CELL_RED
        Numeric cell red with Dollar sign
        See Also:
        Constant Field Values

      • NUMERIC_DOLLAR_CELL_GREEN

        public static final java.lang.String NUMERIC_DOLLAR_CELL_GREEN
        Numeric cell green with Dollar sign
        See Also:
        Constant Field Values

      • NUMERIC_DOLLAR_CELL_GREY

        public static final java.lang.String NUMERIC_DOLLAR_CELL_GREY
        Numeric cell grey with Dollar sign
        See Also:
        Constant Field Values

      • NUMERIC_CHF_CELL_WHITE

        public static final java.lang.String NUMERIC_CHF_CELL_WHITE
        Numeric cell white with CHF sign
        See Also:
        Constant Field Values

      • NUMERIC_CHF_CELL_BLUE

        public static final java.lang.String NUMERIC_CHF_CELL_BLUE
        Numeric cell blue with CHF sign
        See Also:
        Constant Field Values

      • NUMERIC_CHF_CELL_RED

        public static final java.lang.String NUMERIC_CHF_CELL_RED
        Numeric cell red with CHF sign
        See Also:
        Constant Field Values

      • NUMERIC_CHF_CELL_GREEN

        public static final java.lang.String NUMERIC_CHF_CELL_GREEN
        Numeric cell green with CHF sign
        See Also:
        Constant Field Values

      • NUMERIC_CHF_CELL_GREY

        public static final java.lang.String NUMERIC_CHF_CELL_GREY
        Numeric cell grey with CHF sign
        See Also:
        Constant Field Values

      • FORMULA_CELL_WHITE

        public static final java.lang.String FORMULA_CELL_WHITE
        Formula cell white
        See Also:
        Constant Field Values

      • FORMULA_CELL_BLUE

        public static final java.lang.String FORMULA_CELL_BLUE
        Formula cell blue
        See Also:
        Constant Field Values

      • FORMULA_CELL_RED

        public static final java.lang.String FORMULA_CELL_RED
        Formula cell red
        See Also:
        Constant Field Values

      • FORMULA_CELL_GREEN

        public static final java.lang.String FORMULA_CELL_GREEN
        Formula cell green
        See Also:
        Constant Field Values

      • FORMULA_CELL_GREY

        public static final java.lang.String FORMULA_CELL_GREY
        Formula cell grey
        See Also:
        Constant Field Values

      • DATE_CELL_WHITE

        public static final java.lang.String DATE_CELL_WHITE
        Date cell white
        See Also:
        Constant Field Values

      • DATE_CELL_BLUE

        public static final java.lang.String DATE_CELL_BLUE
        Date cell blue
        See Also:
        Constant Field Values

      • DATE_CELL_RED

        public static final java.lang.String DATE_CELL_RED
        Date cell red
        See Also:
        Constant Field Values

      • DATE_CELL_GREEN

        public static final java.lang.String DATE_CELL_GREEN
        Date cell green
        See Also:
        Constant Field Values

      • DATE_CELL_GREY

        public static final java.lang.String DATE_CELL_GREY
        Date cell grey
        See Also:
        Constant Field Values

      • DATETIME_CELL_WHITE

        public static final java.lang.String DATETIME_CELL_WHITE
        Date and time cell white
        See Also:
        Constant Field Values

      • DATETIME_CELL_BLUE

        public static final java.lang.String DATETIME_CELL_BLUE
        Date and time cell blue
        See Also:
        Constant Field Values

      • DATETIME_CELL_RED

        public static final java.lang.String DATETIME_CELL_RED
        Date and time cell red
        See Also:
        Constant Field Values

      • DATETIME_CELL_GREEN

        public static final java.lang.String DATETIME_CELL_GREEN
        Date and time cell green
        See Also:
        Constant Field Values

      • DATETIME_CELL_GREY

        public static final java.lang.String DATETIME_CELL_GREY
        Date and time cell grey
        See Also:
        Constant Field Values

    • Constructor Detail

      • ExcelPOITool

        @Deprecated
public ExcelPOITool()
             throws java.io.IOException
        Deprecated.
        Use constructor with additional argument
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(boolean openxml)
             throws java.io.IOException
        Constructor (empty workbook)
        Parameters:
        openxml - Use OpenXML format (.xlsx)?
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(java.lang.String path)
             throws java.io.IOException
        Constructor (workbook from existing file)
        Parameters:
        path - File path
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(java.io.File file)
             throws java.io.IOException
        Constructor (workbook from existing file)
        Parameters:
        file - File
        Throws:
        java.io.IOException

      • ExcelPOITool

        @Deprecated
public ExcelPOITool​(byte[] data)
             throws java.io.IOException
        Deprecated.
        Use constructor with additional argument
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(byte[] data,
                    boolean openxml)
             throws java.io.IOException
        Constructor (workbook from byte array data)
        Parameters:
        data - Byte array data
        openxml - Use OpenXML format (.xlsx)?
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(Grant g,
                    DocumentDB doc)
             throws java.io.IOException
        Constructor (workbook from document)
        Parameters:
        g - Grant
        doc - Document
        Throws:
        java.io.IOException

      • ExcelPOITool

        @Deprecated
public ExcelPOITool​(java.io.InputStream in)
             throws java.io.IOException
        Deprecated.
        Use constructor with additional argument
        Throws:
        java.io.IOException

      • ExcelPOITool

        public ExcelPOITool​(java.io.InputStream in,
                    boolean openxml)
             throws java.io.IOException
        Constructor (workbook from input stream)
        Parameters:
        in - Input stream
        openxml - Use OpenXML format (.xlsx)?
        Throws:
        java.io.IOException

    • Method Detail

      • newSheet

        public ExcelPOITool.ExcelSheet newSheet​(java.lang.String label)
        Get new sheet
        Parameters:
        label - Sheet label

      • newRow

        public ExcelPOITool.ExcelRow newRow​(int rowIndex)
        Get new row
        Parameters:
        rowIndex - Row index

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      ObjectField field,
                                      java.lang.String value)
        Get new cell
        Parameters:
        cellIndex - Cell index
        field - Field
        value - value

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      java.lang.String content)
        Get new cell
        Parameters:
        cellIndex - Cell index
        content - Cell content
        Returns:
        Cell

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      int value)
        Get new cell
        Parameters:
        cellIndex - Cell index
        value - Cell integer content
        Returns:
        Cell

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      float value)
        Get new cell
        Parameters:
        cellIndex - Cell index
        value - Cell float content
        Returns:
        Cell

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      double value)
        Get new cell
        Parameters:
        cellIndex - Cell index
        value - Cell double content
        Returns:
        Cell

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      java.lang.String content,
                                      org.apache.poi.ss.usermodel.CellType type)
        Get new cell
        Parameters:
        cellIndex - Cell index
        content - Cell content
        type - Cell type

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      java.lang.String content,
                                      org.apache.poi.ss.usermodel.CellType type,
                                      java.lang.String styleName)
        Get new cell
        Parameters:
        cellIndex - Cell index
        content - Cell content
        type - Cell type
        styleName - Cell style name
        Returns:
        Cell

      • newCell

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                      java.lang.String content,
                                      org.apache.poi.ss.usermodel.CellType type,
                                      java.lang.String styleName,
                                      ObjectField field)
        Get new cell
        Parameters:
        cellIndex - Cell index
        content - Cell content
        type - Cell type
        styleName - Cell style name
        field - Field
        Returns:
        Cell

      • getStringCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getStringCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                       short bgcolor,
                                                                       boolean head)
        Get string cell style
        Parameters:
        wb - Workbook
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getStringCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getStringCellStyle​(short bgcolor,
                                                                boolean head)
        Get string cell style
        Parameters:
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getNumericCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getNumericCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                        short bgcolor,
                                                                        boolean head)
        Get numeric cell style
        Parameters:
        wb - Workbook
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getNumericCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getNumericCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                        short bgcolor,
                                                                        boolean head,
                                                                        int precision,
                                                                        java.lang.String currency)
        Get string cell style
        Parameters:
        wb - Workbook
        bgcolor - Background color
        head - Head?
        precision - Precision
        currency - Currency
        Returns:
        Cell style

      • getNumericCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getNumericCellStyle​(short bgcolor,
                                                                 boolean head)
        Get string cell style
        Parameters:
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getDateCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getDateCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                     short bgcolor,
                                                                     boolean head)
        Get date cell style
        Parameters:
        wb - Workbook
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getDateCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getDateCellStyle​(short bgcolor,
                                                              boolean head)
        Get date cell style
        Parameters:
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getDateTimeCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getDateTimeCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                         short bgcolor,
                                                                         boolean head)
        Get date and time cell style
        Parameters:
        wb - Workbook
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getDateTimeCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getDateTimeCellStyle​(short bgcolor,
                                                                  boolean head)
        Get date and time cell style
        Parameters:
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getFormulaCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getFormulaCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                        short bgcolor,
                                                                        boolean head)
        Get formula cell style
        Parameters:
        wb - Workbook
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getFormulaCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getFormulaCellStyle​(short bgcolor,
                                                                 boolean head)
        Get date cell style
        Parameters:
        bgcolor - Backgroynd color
        head - Head?
        Returns:
        Cell style

      • getWorkbook

        public org.apache.poi.ss.usermodel.Workbook getWorkbook()
        Get workbook
        Returns:
        POI workbook

      • getStyles

        public java.util.Map<java.lang.String,​org.apache.poi.ss.usermodel.CellStyle> getStyles()
        Get custom styles
        Returns:
        Styles map

      • add

        public org.apache.poi.ss.usermodel.Sheet add​(ExcelPOITool.ExcelSheet sheet)
        Add a sheet
        Parameters:
        sheet - Sheet
        Returns:
        POI sheet

      • add

        public org.apache.poi.ss.usermodel.Sheet add​(ExcelPOITool.ExcelSheet sheet,
                                             int autoSizeColumns,
                                             int freezeRows)
        Add a sheet to workbook
        Parameters:
        sheet - Sheet
        autoSizeColumns - Auto size columns
        freezeRows - Number of top rows to freeze
        Returns:
        POI sheet

      • addJPEGPicture

        public void addJPEGPicture​(byte[] data,
                           org.apache.poi.ss.usermodel.Sheet sheet,
                           int row,
                           int col)
        Add JPEG picture
        Parameters:
        data - Picture data
        sheet - Sheet
        row - Row
        col - Column

      • addPNGPicture

        public void addPNGPicture​(byte[] data,
                          org.apache.poi.ss.usermodel.Sheet sheet,
                          int row,
                          int col)
        Add PNG picture
        Parameters:
        data - Picture data
        sheet - Sheet
        row - Row
        col - Column

      • generateToString

        @Deprecated
public java.lang.String generateToString()
                                  throws java.io.IOException
        Deprecated.
        Use other generate methods
        Throws:
        java.io.IOException

      • generateToByteArray

        public byte[] generateToByteArray()
                           throws java.io.IOException
        Generate workbook as byte array
        Returns:
        Byte array
        Throws:
        java.io.IOException - If generation fails

      • generateToFile

        public void generateToFile​(java.lang.String fileName)
                    throws java.io.IOException
        Generate workbook to file
        Parameters:
        fileName - File name
        Throws:
        java.io.IOException - If generation fails

      • generate

        public void generate​(java.io.OutputStream out)
              throws java.io.IOException
        Generate workbook to specified output stream
        Parameters:
        out - Output stream
        Throws:
        java.io.IOException - If generation fails

      • updateFormulas

        public void updateFormulas​(org.apache.poi.ss.usermodel.Sheet sheet)
        Update formulas in sheet
        Parameters:
        sheet - Sheet