Search in sources :

Example 1 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project head by mifos.

the class XlsLoansAccountImporter method getCellDateValue.

private Date getCellDateValue(final HSSFRow row, final XlsLoansImportTemplateConstants currentCell) throws Exception {
    HSSFCell cell = null;
    try {
        cell = row.getCell(currentCell.getValue(), HSSFRow.RETURN_BLANK_AS_NULL);
        Date rawDate = null;
        rawDate = (cell == null) ? null : cell.getDateCellValue();
        if (rawDate == null) {
            throw new NullPointerException();
        }
        return rawDate;
    } catch (Exception ex) {
        String invalidDateString = (cell == null) ? "" : getCellStringValue(row, currentCell);
        List<Object> moreParams = new ArrayList<Object>();
        moreParams.add(invalidDateString);
        throw new XlsParsingException(getCellError(XlsMessageConstants.INVALID_DATE, row, currentCell.getValue(), moreParams));
    }
}
Also used : HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) List(java.util.List) ArrayList(java.util.ArrayList) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) Date(java.util.Date) LocalDate(org.joda.time.LocalDate)

Example 2 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class NewLinesInCells method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFFont f2 = wb.createFont();
    cs = wb.createCellStyle();
    cs.setFont(f2);
    // Word Wrap MUST be turned on
    cs.setWrapText(true);
    r = s.createRow(2);
    r.setHeight((short) 0x349);
    c = r.createCell(2);
    c.setCellType(CellType.STRING);
    c.setCellValue("Use \n with word wrap on to create a new line");
    c.setCellStyle(cs);
    s.setColumnWidth(2, (int) ((50 * 8) / ((double) 1 / 20)));
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) FileOutputStream(java.io.FileOutputStream) HSSFRow(org.apache.poi.hssf.usermodel.HSSFRow) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) HSSFFont(org.apache.poi.hssf.usermodel.HSSFFont) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

Example 3 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class InCellLists method demonstrateMethodCalls.

/**
     * Call each of the list creation methods.
     *
     * @param outputFilename A String that encapsulates the name of and path to
     *                       the Excel spreadsheet file this code will create.
     */
public void demonstrateMethodCalls(String outputFilename) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {
        HSSFSheet sheet = workbook.createSheet("In Cell Lists");
        HSSFRow row = sheet.createRow(0);
        // Create a cell at A1 and insert a single, bulleted, item into
        // that cell.
        HSSFCell cell = row.createCell(0);
        this.bulletedItemInCell(workbook, "List Item", cell);
        // Create a cell at A2 and insert a plain list - that is one
        // whose items are neither bulleted or numbered - into that cell.
        row = sheet.createRow(1);
        cell = row.createCell(0);
        ArrayList<String> listItems = new ArrayList<String>();
        listItems.add("List Item One.");
        listItems.add("List Item Two.");
        listItems.add("List Item Three.");
        listItems.add("List Item Four.");
        this.listInCell(workbook, listItems, cell);
        // The row height and cell width are set here to ensure that the
        // list may be seen.
        row.setHeight((short) 1100);
        sheet.setColumnWidth(0, 9500);
        // Create a cell at A3 and insert a numbered list into that cell.
        // Note that a couple of items have been added to the listItems
        // ArrayList
        row = sheet.createRow(2);
        cell = row.createCell(0);
        listItems.add("List Item Five.");
        listItems.add("List Item Six.");
        this.numberedListInCell(workbook, listItems, cell, 1, 2);
        row.setHeight((short) 1550);
        // Create a cell at A4 and insert a numbered list into that cell.
        // Note that a couple of items have been added to the listItems
        // ArrayList
        row = sheet.createRow(3);
        cell = row.createCell(0);
        listItems.add("List Item Seven.");
        listItems.add("List Item Eight.");
        listItems.add("List Item Nine.");
        listItems.add("List Item Ten.");
        this.bulletedListInCell(workbook, listItems, cell);
        row.setHeight((short) 2550);
        // Insert a plain, multi-level list into cell A5. Note that
        // the major difference here is that the list items are passed as
        // an ArrayList of MultiLevelListItems. Note that an ArrayList
        // of instances of an inner class was used here in preference to
        // a Hashtable or HashMap as the ArrayList will preserve the
        // ordering of the items added to it; the first item added will
        // be the first item recovered and the last item added, the last
        // item recovered. Alternatively, a LinkedHashMap could be used
        // to preserve order.
        row = sheet.createRow(4);
        cell = row.createCell(0);
        ArrayList<MultiLevelListItem> multiLevelListItems = new ArrayList<MultiLevelListItem>();
        listItems = new ArrayList<String>();
        listItems.add("ML List Item One - Sub Item One.");
        listItems.add("ML List Item One - Sub Item Two.");
        listItems.add("ML List Item One - Sub Item Three.");
        listItems.add("ML List Item One - Sub Item Four.");
        multiLevelListItems.add(new MultiLevelListItem("List Item One.", listItems));
        // Passing either null or an empty ArrayList will signal that
        // there are no lower level items associated with the top level
        // item
        multiLevelListItems.add(new MultiLevelListItem("List Item Two.", null));
        multiLevelListItems.add(new MultiLevelListItem("List Item Three.", null));
        listItems = new ArrayList<String>();
        listItems.add("ML List Item Four - Sub Item One.");
        listItems.add("ML List Item Four - Sub Item Two.");
        listItems.add("ML List Item Four - Sub Item Three.");
        multiLevelListItems.add(new MultiLevelListItem("List Item Four.", listItems));
        this.multiLevelListInCell(workbook, multiLevelListItems, cell);
        row.setHeight((short) 2800);
        // Insert a numbered multi-level list into cell A6. Note that the
        // same ArrayList as constructed for the above plain multi-level
        // list example will be re-used
        row = sheet.createRow(5);
        cell = row.createCell(0);
        this.multiLevelNumberedListInCell(workbook, multiLevelListItems, cell, 1, 1, 1, 2);
        row.setHeight((short) 2800);
        // Insert a numbered multi-level list into cell A7. Note that the
        // same ArrayList as constructed for the plain multi-level list
        // example will be re-used
        row = sheet.createRow(6);
        cell = row.createCell(0);
        this.multiLevelBulletedListInCell(workbook, multiLevelListItems, cell);
        row.setHeight((short) 2800);
        // Save the completed workbook
        FileOutputStream fos = new FileOutputStream(new File(outputFilename));
        try {
            workbook.write(fos);
        } finally {
            fos.close();
        }
    } catch (FileNotFoundException fnfEx) {
        System.out.println("Caught a: " + fnfEx.getClass().getName());
        System.out.println("Message: " + fnfEx.getMessage());
        System.out.println("Stacktrace follows...........");
        fnfEx.printStackTrace(System.out);
    } catch (IOException ioEx) {
        System.out.println("Caught a: " + ioEx.getClass().getName());
        System.out.println("Message: " + ioEx.getMessage());
        System.out.println("Stacktrace follows...........");
        ioEx.printStackTrace(System.out);
    } finally {
        workbook.close();
    }
}
Also used : ArrayList(java.util.ArrayList) FileNotFoundException(java.io.FileNotFoundException) HSSFRow(org.apache.poi.hssf.usermodel.HSSFRow) HSSFRichTextString(org.apache.poi.hssf.usermodel.HSSFRichTextString) IOException(java.io.IOException) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) FileOutputStream(java.io.FileOutputStream) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) File(java.io.File)

Example 4 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class SVTableCellEditor method getTableCellEditorComponent.

/**
   *  Gets the tableCellEditorComponent attribute of the SVTableCellEditor object
   *
   * @return             The tableCellEditorComponent value
   */
@Override
public Component getTableCellEditorComponent(JTable table, Object value, boolean isSelected, int row, int column) {
    System.out.println("GetTableCellEditorComponent");
    HSSFCell cell = (HSSFCell) value;
    if (cell != null) {
        HSSFCellStyle style = cell.getCellStyle();
        HSSFFont f = wb.getFontAt(style.getFontIndex());
        boolean isbold = f.getBold();
        boolean isitalics = f.getItalic();
        int fontstyle = Font.PLAIN;
        if (isbold) {
            fontstyle = Font.BOLD;
        }
        if (isitalics) {
            fontstyle = fontstyle | Font.ITALIC;
        }
        int fontheight = f.getFontHeightInPoints();
        if (fontheight == 9) {
            //fix for stupid ol Windows
            fontheight = 10;
        }
        Font font = new Font(f.getFontName(), fontstyle, fontheight);
        editor.setFont(font);
        if (style.getFillPatternEnum() == FillPatternType.SOLID_FOREGROUND) {
            editor.setBackground(getAWTColor(style.getFillForegroundColor(), white));
        } else {
            editor.setBackground(white);
        }
        editor.setForeground(getAWTColor(f.getColor(), black));
        //Set the value that is rendered for the cell
        switch(cell.getCellTypeEnum()) {
            case BLANK:
                editor.setText("");
                break;
            case BOOLEAN:
                if (cell.getBooleanCellValue()) {
                    editor.setText("true");
                } else {
                    editor.setText("false");
                }
                break;
            case NUMERIC:
                editor.setText(Double.toString(cell.getNumericCellValue()));
                break;
            case STRING:
                editor.setText(cell.getRichStringCellValue().getString());
                break;
            case FORMULA:
            default:
                editor.setText("?");
        }
        switch(style.getAlignmentEnum()) {
            case LEFT:
            case JUSTIFY:
            case FILL:
                editor.setHorizontalAlignment(SwingConstants.LEFT);
                break;
            case CENTER:
            case CENTER_SELECTION:
                editor.setHorizontalAlignment(SwingConstants.CENTER);
                break;
            case GENERAL:
            case RIGHT:
                editor.setHorizontalAlignment(SwingConstants.RIGHT);
                break;
            default:
                editor.setHorizontalAlignment(SwingConstants.LEFT);
                break;
        }
    }
    return editor;
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) HSSFFont(org.apache.poi.hssf.usermodel.HSSFFont) Font(java.awt.Font) HSSFFont(org.apache.poi.hssf.usermodel.HSSFFont)

Example 5 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class SVTableCellRenderer method getTableCellRendererComponent.

@Override
public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
    boolean isBorderSet = false;
    //If the JTables default cell renderer has been setup correctly the
    //value will be the HSSFCell that we are trying to render
    HSSFCell c = (HSSFCell) value;
    if (c != null) {
        HSSFCellStyle s = c.getCellStyle();
        HSSFFont f = wb.getFontAt(s.getFontIndex());
        setFont(SVTableUtils.makeFont(f));
        if (s.getFillPatternEnum() == FillPatternType.SOLID_FOREGROUND) {
            setBackground(SVTableUtils.getAWTColor(s.getFillForegroundColor(), SVTableUtils.white));
        } else
            setBackground(SVTableUtils.white);
        setForeground(SVTableUtils.getAWTColor(f.getColor(), SVTableUtils.black));
        cellBorder.setBorder(SVTableUtils.getAWTColor(s.getTopBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getRightBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getBottomBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getLeftBorderColor(), SVTableUtils.black), s.getBorderTopEnum(), s.getBorderRightEnum(), s.getBorderBottomEnum(), s.getBorderLeftEnum(), hasFocus);
        setBorder(cellBorder);
        isBorderSet = true;
        //Set the value that is rendered for the cell
        switch(c.getCellTypeEnum()) {
            case BLANK:
                setValue("");
                break;
            case BOOLEAN:
                if (c.getBooleanCellValue()) {
                    setValue("true");
                } else {
                    setValue("false");
                }
                break;
            case NUMERIC:
                short format = s.getDataFormat();
                double numericValue = c.getNumericCellValue();
                if (cellFormatter.useRedColor(format, numericValue))
                    setForeground(Color.red);
                else
                    setForeground(null);
                setValue(cellFormatter.format(format, c.getNumericCellValue()));
                break;
            case STRING:
                setValue(c.getRichStringCellValue().getString());
                break;
            case FORMULA:
            default:
                setValue("?");
        }
        //Set the text alignment of the cell
        switch(s.getAlignmentEnum()) {
            case LEFT:
            case JUSTIFY:
            case FILL:
                setHorizontalAlignment(SwingConstants.LEFT);
                break;
            case CENTER:
            case CENTER_SELECTION:
                setHorizontalAlignment(SwingConstants.CENTER);
                break;
            case GENERAL:
            case RIGHT:
                setHorizontalAlignment(SwingConstants.RIGHT);
                break;
            default:
                setHorizontalAlignment(SwingConstants.LEFT);
                break;
        }
    } else {
        setValue("");
        setBackground(SVTableUtils.white);
    }
    if (hasFocus) {
        if (!isBorderSet) {
            //This is the border to paint when there is no border
            //and the cell has focus
            cellBorder.setBorder(SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, BorderStyle.NONE, BorderStyle.NONE, BorderStyle.NONE, BorderStyle.NONE, isSelected);
            setBorder(cellBorder);
        }
        if (table.isCellEditable(row, column)) {
            setForeground(UIManager.getColor("Table.focusCellForeground"));
            setBackground(UIManager.getColor("Table.focusCellBackground"));
        }
    } else if (!isBorderSet) {
        setBorder(noFocusBorder);
    }
    // ---- begin optimization to avoid painting background ----
    Color back = getBackground();
    boolean colorMatch = (back != null) && (back.equals(table.getBackground())) && table.isOpaque();
    setOpaque(!colorMatch);
    // ---- end optimization to aviod painting background ----
    return this;
}
Also used : HSSFCellStyle(org.apache.poi.hssf.usermodel.HSSFCellStyle) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) Color(java.awt.Color) HSSFFont(org.apache.poi.hssf.usermodel.HSSFFont)

Aggregations

HSSFCell (org.apache.poi.hssf.usermodel.HSSFCell)147 HSSFRow (org.apache.poi.hssf.usermodel.HSSFRow)96 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)93 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)91 HSSFCellStyle (org.apache.poi.hssf.usermodel.HSSFCellStyle)31 HSSFFormulaEvaluator (org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator)30 HSSFRichTextString (org.apache.poi.hssf.usermodel.HSSFRichTextString)28 Test (org.junit.Test)25 FileOutputStream (java.io.FileOutputStream)24 IOException (java.io.IOException)18 HSSFFont (org.apache.poi.hssf.usermodel.HSSFFont)16 File (java.io.File)13 CellValue (org.apache.poi.ss.usermodel.CellValue)13 ArrayList (java.util.ArrayList)11 AssertionFailedError (junit.framework.AssertionFailedError)10 HashMap (java.util.HashMap)9 OutputStream (java.io.OutputStream)8 Map (java.util.Map)8 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)8 FileInputStream (java.io.FileInputStream)6