Class JxlsSheet

  • All Implemented Interfaces:
    JxlsConstants

    public class JxlsSheet
    extends java.lang.Object
    implements JxlsConstants
    JxlsSheet: a class to wrap and simplify the use of Apache POI Sheet in the context of OpenXava Principle: As per Excel, the rows and colums start at 1 Types of cells: Date, Number, Formulas and Text Formulas can be encoded as Excel: A1, $A1, A$1, $A$1, R1C1 (R[-1]C1 is not implemented) Jxls: R1C1, $R1C1, R1$C1, $R1$C1 and they can start with = Usage: JxlsWorkbook wb = new JxlsWorkbook("Test"); JxlsSheet sheet = wb.addSheet("Test"); sheet.setValue(3, 4, "Pi", wb.addStyle(TEXT).setAlign(CENTER).setBold()); sheet.setValue(4, 4, 3.141592654, wb.addStyle(FLOAT).setAllBorders(THIN_BORDER)); sheet.setFormula(4, 5, "=2*$R4$C4", wb.addStyle("##0.0000")); sheet.setFormula(4, 6, "=2*R4C4", wb.addStyle("##0.000"));
    Author:
    Laurent Wibaux
    • Constructor Detail

      • JxlsSheet

        protected JxlsSheet​(JxlsWorkbook workbook,
                            java.lang.String name)
      • JxlsSheet

        protected JxlsSheet​(JxlsWorkbook workbook,
                            java.lang.String name,
                            int index)
    • Method Detail

      • setColumnWidths

        public JxlsSheet setColumnWidths​(int column,
                                         int... widths)
        Sets the width of one or more columns
        Parameters:
        column - the index of the first column width to set (1 based)
        widths - the widths of the columns to set
        Returns:
        the JxlsSheet to chain other settings
      • setColumnStyles

        public JxlsSheet setColumnStyles​(int column,
                                         JxlsStyle... styles)
        Sets the width of one or more columns
        Parameters:
        column - the index of the first column style to set (1 based)
        styles - the styles of the columns to set
        Returns:
        the JxlsSheet to chain other settings
      • setFreezePane

        public JxlsSheet setFreezePane​(int columnSplit,
                                       int rowSplit)
        Sets the freeze pane of the sheet
        Parameters:
        columnSplit - the column at which right the pane will freeze
        rowSplit - the row under which the pane will freeze
        Returns:
        the JxlsSheet to chain other settings
      • setZoom

        public JxlsSheet setZoom​(int zoom)
        Sets the zoom percentage used to display the sheet
        Parameters:
        zoom - the percentage (1 to 100)
        Returns:
        the JxlsSheet to chain other settings
      • setHeaders

        public void setHeaders​(int startColumn,
                               int row,
                               JxlsStyle style,
                               java.lang.String... headers)
        Sets column headers Usage: // set the first column header as "Invoice" with style // do not set any header for the second column // set the third column as "Customer" with style and a width of 20 // set the fourth column as "Customer" with style, right alignment and a width of 10 setHeaders(1, 1, style, "Invoice", null, "Customer|20", "Cost|r", "Margin|r|10");
        Parameters:
        startColumn - the column at which to start setting the headers
        row - the row containing the headers
        style - the style to use for the headers
        headers - a list of headers. The format for the headers is name[|align][|columnWidth]
      • setValues

        public void setValues​(int startColumn,
                              int row,
                              java.lang.Object... values)
        Sets multiple values in one row
        Parameters:
        startColumn - the column at which to start setting the values
        row - the row at which the values are set
        values - the values
      • setValue

        public JxlsCell setValue​(int column,
                                 int row,
                                 java.lang.Object value)
        Sets the value at column,row
        Parameters:
        column - the column of the cell to set
        row - the column of the cell to set
        value - the value of the cell to set
        Returns:
        the cell which as been set
      • setValue

        public JxlsCell setValue​(int column,
                                 int row,
                                 java.lang.Object value,
                                 JxlsStyle style)
        Sets the value at column,row
        Parameters:
        column - the column of the cell to set
        row - the column of the cell to set
        value - the value of the cell to set
        style - the style of the cell to set
        Returns:
        the cell which as been set
      • setFormula

        public JxlsCell setFormula​(int column,
                                   int row,
                                   java.lang.String formula)
        Sets the formula at column,row sheet.setFormula(1, 1, "=A1+$A2+$R1C3+R1$C4");
        Parameters:
        column - the column of the cell to set
        row - the column of the cell to set
        formula - the formula of the cell to set
        Returns:
        the cell which as been set
      • setFormula

        public JxlsCell setFormula​(int column,
                                   int row,
                                   java.lang.String formula,
                                   JxlsStyle style)
        Sets the formula at column,row sheet.setFormula(1, 1, "=A1+$A2+$R1C3+R1$C4");
        Parameters:
        column - the column of the cell to set
        row - the column of the cell to set
        formula - the formula of the cell to set
        style - the style of the cell to set
        Returns:
        the cell which as been set
      • getValue

        public java.lang.Object getValue​(int column,
                                         int row)
        Gets the object value contained in the cell at position column,row
        Parameters:
        column - the column of the cell to retrieve
        row - the row of the cell to retrieve
        Returns:
        the value of the cell
      • getNumericValue

        public double getNumericValue​(int column,
                                      int row)
        Gets the numeric value contained in the cell at position column,row
        Parameters:
        column - the column of the cell to retrieve
        row - the row of the cell to retrieve
        Returns:
        the value of the cell if a number, the time in ms for a date, 0 in other cases
      • getStringValue

        public java.lang.String getStringValue​(int column,
                                               int row)
        Gets the string value contained in the cell at position column,row
        Parameters:
        column - the column of the cell to retrieve
        row - the row of the cell to retrieve
        Returns:
        the value of the cell converted as a text
      • getCell

        public JxlsCell getCell​(int column,
                                int row)
        Gets the JxlsCell contained in the cell at position column,row
        Parameters:
        column - the column of the cell to retrieve
        row - the row of the cell to retrieve
        Returns:
        the cell
      • getLastRowNumber

        public int getLastRowNumber()
        Gets the row number of the bottom most cell
        Returns:
        the row number
      • createPOISheet

        protected void createPOISheet​(JxlsWorkbook workbook,
                                      org.apache.poi.ss.usermodel.Workbook wb)