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

        public static final short COLOR_BLUE
      • COLOR_RED

        public static final short COLOR_RED
      • COLOR_GREEN

        public static final short COLOR_GREEN
      • COLOR_GREY

        public static final short COLOR_GREY
      • TYPE_STRING

        public static final org.apache.poi.ss.usermodel.CellType TYPE_STRING
      • TYPE_NUMERIC

        public static final org.apache.poi.ss.usermodel.CellType TYPE_NUMERIC
      • TYPE_FORMULA

        public static final org.apache.poi.ss.usermodel.CellType TYPE_FORMULA
      • STRING_HEAD_WHITE

        public static final java.lang.String STRING_HEAD_WHITE
        See Also:
        Constant Field Values
      • STRING_HEAD_GREY

        public static final java.lang.String STRING_HEAD_GREY
        See Also:
        Constant Field Values
      • NUMERIC_HEAD_WHITE

        public static final java.lang.String NUMERIC_HEAD_WHITE
        See Also:
        Constant Field Values
      • NUMERIC_HEAD_GREY

        public static final java.lang.String NUMERIC_HEAD_GREY
        See Also:
        Constant Field Values
      • FORMULA_HEAD_WHITE

        public static final java.lang.String FORMULA_HEAD_WHITE
        See Also:
        Constant Field Values
      • FORMULA_HEAD_GREY

        public static final java.lang.String FORMULA_HEAD_GREY
        See Also:
        Constant Field Values
      • STRING_CELL_WHITE

        public static final java.lang.String STRING_CELL_WHITE
        See Also:
        Constant Field Values
      • STRING_CELL_BLUE

        public static final java.lang.String STRING_CELL_BLUE
        See Also:
        Constant Field Values
      • STRING_CELL_GREEN

        public static final java.lang.String STRING_CELL_GREEN
        See Also:
        Constant Field Values
      • STRING_CELL_GREY

        public static final java.lang.String STRING_CELL_GREY
        See Also:
        Constant Field Values
      • INTEGER_CELL_WHITE

        public static final java.lang.String INTEGER_CELL_WHITE
        See Also:
        Constant Field Values
      • INTEGER_CELL_BLUE

        public static final java.lang.String INTEGER_CELL_BLUE
        See Also:
        Constant Field Values
      • INTEGER_CELL_RED

        public static final java.lang.String INTEGER_CELL_RED
        See Also:
        Constant Field Values
      • INTEGER_CELL_GREEN

        public static final java.lang.String INTEGER_CELL_GREEN
        See Also:
        Constant Field Values
      • INTEGER_CELL_GREY

        public static final java.lang.String INTEGER_CELL_GREY
        See Also:
        Constant Field Values
      • INTEGER_EURO_CELL_WHITE

        public static final java.lang.String INTEGER_EURO_CELL_WHITE
        See Also:
        Constant Field Values
      • INTEGER_EURO_CELL_BLUE

        public static final java.lang.String INTEGER_EURO_CELL_BLUE
        See Also:
        Constant Field Values
      • INTEGER_EURO_CELL_RED

        public static final java.lang.String INTEGER_EURO_CELL_RED
        See Also:
        Constant Field Values
      • INTEGER_EURO_CELL_GREEN

        public static final java.lang.String INTEGER_EURO_CELL_GREEN
        See Also:
        Constant Field Values
      • INTEGER_EURO_CELL_GREY

        public static final java.lang.String INTEGER_EURO_CELL_GREY
        See Also:
        Constant Field Values
      • INTEGER_DOLLAR_CELL_WHITE

        public static final java.lang.String INTEGER_DOLLAR_CELL_WHITE
        See Also:
        Constant Field Values
      • INTEGER_DOLLAR_CELL_BLUE

        public static final java.lang.String INTEGER_DOLLAR_CELL_BLUE
        See Also:
        Constant Field Values
      • INTEGER_DOLLAR_CELL_RED

        public static final java.lang.String INTEGER_DOLLAR_CELL_RED
        See Also:
        Constant Field Values
      • INTEGER_DOLLAR_CELL_GREEN

        public static final java.lang.String INTEGER_DOLLAR_CELL_GREEN
        See Also:
        Constant Field Values
      • INTEGER_DOLLAR_CELL_GREY

        public static final java.lang.String INTEGER_DOLLAR_CELL_GREY
        See Also:
        Constant Field Values
      • NUMERIC_CELL_WHITE

        public static final java.lang.String NUMERIC_CELL_WHITE
        See Also:
        Constant Field Values
      • NUMERIC_CELL_BLUE

        public static final java.lang.String NUMERIC_CELL_BLUE
        See Also:
        Constant Field Values
      • NUMERIC_CELL_RED

        public static final java.lang.String NUMERIC_CELL_RED
        See Also:
        Constant Field Values
      • NUMERIC_CELL_GREEN

        public static final java.lang.String NUMERIC_CELL_GREEN
        See Also:
        Constant Field Values
      • NUMERIC_CELL_GREY

        public static final java.lang.String NUMERIC_CELL_GREY
        See Also:
        Constant Field Values
      • NUMERIC_EURO_CELL_WHITE

        public static final java.lang.String NUMERIC_EURO_CELL_WHITE
        See Also:
        Constant Field Values
      • NUMERIC_EURO_CELL_BLUE

        public static final java.lang.String NUMERIC_EURO_CELL_BLUE
        See Also:
        Constant Field Values
      • NUMERIC_EURO_CELL_RED

        public static final java.lang.String NUMERIC_EURO_CELL_RED
        See Also:
        Constant Field Values
      • NUMERIC_EURO_CELL_GREEN

        public static final java.lang.String NUMERIC_EURO_CELL_GREEN
        See Also:
        Constant Field Values
      • NUMERIC_EURO_CELL_GREY

        public static final java.lang.String NUMERIC_EURO_CELL_GREY
        See Also:
        Constant Field Values
      • NUMERIC_DOLLAR_CELL_WHITE

        public static final java.lang.String NUMERIC_DOLLAR_CELL_WHITE
        See Also:
        Constant Field Values
      • NUMERIC_DOLLAR_CELL_BLUE

        public static final java.lang.String NUMERIC_DOLLAR_CELL_BLUE
        See Also:
        Constant Field Values
      • NUMERIC_DOLLAR_CELL_RED

        public static final java.lang.String NUMERIC_DOLLAR_CELL_RED
        See Also:
        Constant Field Values
      • NUMERIC_DOLLAR_CELL_GREEN

        public static final java.lang.String NUMERIC_DOLLAR_CELL_GREEN
        See Also:
        Constant Field Values
      • NUMERIC_DOLLAR_CELL_GREY

        public static final java.lang.String NUMERIC_DOLLAR_CELL_GREY
        See Also:
        Constant Field Values
      • FORMULA_CELL_WHITE

        public static final java.lang.String FORMULA_CELL_WHITE
        See Also:
        Constant Field Values
      • FORMULA_CELL_BLUE

        public static final java.lang.String FORMULA_CELL_BLUE
        See Also:
        Constant Field Values
      • FORMULA_CELL_RED

        public static final java.lang.String FORMULA_CELL_RED
        See Also:
        Constant Field Values
      • FORMULA_CELL_GREEN

        public static final java.lang.String FORMULA_CELL_GREEN
        See Also:
        Constant Field Values
      • FORMULA_CELL_GREY

        public static final java.lang.String FORMULA_CELL_GREY
        See Also:
        Constant Field Values
      • DATETIME_CELL_WHITE

        public static final java.lang.String DATETIME_CELL_WHITE
        See Also:
        Constant Field Values
      • DATETIME_CELL_BLUE

        public static final java.lang.String DATETIME_CELL_BLUE
        See Also:
        Constant Field Values
      • DATETIME_CELL_RED

        public static final java.lang.String DATETIME_CELL_RED
        See Also:
        Constant Field Values
      • DATETIME_CELL_GREEN

        public static final java.lang.String DATETIME_CELL_GREEN
        See Also:
        Constant Field Values
      • DATETIME_CELL_GREY

        public static final java.lang.String DATETIME_CELL_GREY
        See Also:
        Constant Field Values
    • Constructor Detail

      • ExcelPOITool

        @Deprecated
        public ExcelPOITool()
                     throws java.lang.Exception
        Deprecated.
        Use constructor with additional argument
        Throws:
        java.lang.Exception
      • ExcelPOITool

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

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

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

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

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

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

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

        public ExcelPOITool​(java.io.InputStream in,
                            boolean openxml)
                     throws java.lang.Exception
        Constructor (workbook from input stream)
        Parameters:
        in - Input stream
        openxml - Use OpenXML format (.xlsx)?
        Throws:
        java.lang.Exception
    • 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
      • newCell

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

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

        public ExcelPOITool.ExcelCell newCell​(int cellIndex,
                                              double value)
        Get new cell
        Parameters:
        cellIndex - Cell index
        value - Cell double content
      • 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
      • 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
      • getStringCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getStringCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                               short bgcolor,
                                                                               boolean head)
      • getStringCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getStringCellStyle​(short bgcolor,
                                                                        boolean head)
      • getNumericCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getNumericCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                                short bgcolor,
                                                                                boolean head)
      • 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)
      • getNumericCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getNumericCellStyle​(short bgcolor,
                                                                         boolean head)
      • getDateCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getDateCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                             short bgcolor,
                                                                             boolean head)
      • getDateCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getDateCellStyle​(short bgcolor,
                                                                      boolean head)
      • getDateTimeCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getDateTimeCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                                 short bgcolor,
                                                                                 boolean head)
      • getDateTimeCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getDateTimeCellStyle​(short bgcolor,
                                                                          boolean head)
      • getFormulaCellStyle

        public static org.apache.poi.ss.usermodel.CellStyle getFormulaCellStyle​(org.apache.poi.ss.usermodel.Workbook wb,
                                                                                short bgcolor,
                                                                                boolean head)
      • getFormulaCellStyle

        public org.apache.poi.ss.usermodel.CellStyle getFormulaCellStyle​(short bgcolor,
                                                                         boolean head)
      • getWorkbook

        public org.apache.poi.ss.usermodel.Workbook getWorkbook()
      • getStyles

        public java.util.Map<java.lang.String,​org.apache.poi.ss.usermodel.CellStyle> getStyles()
        Custom styles
      • add

        public org.apache.poi.ss.usermodel.Sheet add​(ExcelPOITool.ExcelSheet sheet)
        Add a sheet
        Parameters:
        sheet - 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
      • 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.lang.Exception
        Deprecated.
        Use generateToByteArray
        Throws:
        java.lang.Exception
      • generateToByteArray

        public byte[] generateToByteArray()
                                   throws java.lang.Exception
        Generate workbook as byte array
        Throws:
        java.lang.Exception
      • generateToFile

        public void generateToFile​(java.lang.String fileName)
                            throws java.lang.Exception
        Generate workbook to specified file
        Throws:
        java.lang.Exception
      • generate

        public void generate​(java.io.OutputStream out)
                      throws java.lang.Exception
        Generate workbook to specified output stream
        Throws:
        java.lang.Exception
      • updateFormulas

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