List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle getDataFormat
@Override public short getDataFormat()
From source file:org.tentackle.ui.FormTableUtilityPopup.java
License:Open Source License
/** * Converts the table to an excel spreadsheet. * @param file the output file/* ww w . j a v a 2 s .com*/ * @param onlySelected true if export only selected rows * @throws IOException if export failed */ public void excel(File file, boolean onlySelected) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); TableModel model = table.getModel(); TableColumnModel columnModel = table.getColumnModel(); int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {}; int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows int cols = columnModel.getColumnCount(); // number of data columns short srow = 0; // current spreadsheet row // local copies cause might be changed String xTitle = this.title; String xIntro = this.intro; if (xTitle == null) { // get default from window title Window parent = FormHelper.getParentWindow(table); try { // paint page-title xTitle = ((FormWindow) parent).getTitle(); } catch (Exception e) { xTitle = null; } } if (xTitle != null) { HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); HSSFCell cell = row.createCell(0); cell.setCellStyle(cs); cell.setCellValue(new HSSFRichTextString(xTitle)); // region rowFrom, colFrom, rowTo, colTo sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1)); srow++; } if (xIntro != null || onlySelected) { HSSFRow row = sheet.createRow(srow); HSSFCell cell = row.createCell(0); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cs.setWrapText(true); cell.setCellStyle(cs); if (onlySelected) { if (xIntro == null) { xIntro = ""; } else { xIntro += ", "; } xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>"); } cell.setCellValue(new HSSFRichTextString(xIntro)); sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1)); srow += 3; } // column headers boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel; srow++; // always skip one line HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); for (int c = 0; c < cols; c++) { HSSFCell cell = row.createCell(c); cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel ? ((AbstractFormTableModel) model) .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex()) : model.getColumnName(columnModel.getColumn(c).getModelIndex()))); cell.setCellStyle(cs); } srow++; // default cell-style for date HSSFCellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); // cellstyles for numbers List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>(); HSSFDataFormat format = wb.createDataFormat(); for (int r = 0; r < rows; r++) { int modelRow = onlySelected ? selectedRows[r] : r; row = sheet.createRow(srow + (short) r); for (int i = 0; i < cols; i++) { int c = columnModel.getColumn(i).getModelIndex(); Object value = model.getValueAt(modelRow, c); HSSFCell cell = row.createCell(i); if (value instanceof Boolean) { cell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof BMoney) { BMoney money = (BMoney) value; cell.setCellValue(money.doubleValue()); String fmt = "#,##0"; if (money.scale() > 0) { fmt += "."; for (int j = 0; j < money.scale(); j++) { fmt += "0"; } } // create format short fmtIndex = format.getFormat(fmt); // check if there is already a cellstyle with this scale Iterator<HSSFCellStyle> iter = numberStyles.iterator(); boolean found = false; while (iter.hasNext()) { cs = iter.next(); if (cs.getDataFormat() == fmtIndex) { // reuse that found = true; break; } } if (!found) { // create a new style cs = wb.createCellStyle(); cs.setDataFormat(fmtIndex); numberStyles.add(cs); } cell.setCellStyle(cs); } else if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellValue((Date) value); cell.setCellStyle(dateStyle); } else if (value instanceof GregorianCalendar) { cell.setCellValue((GregorianCalendar) value); cell.setCellStyle(dateStyle); } else if (value != null) { cell.setCellValue(new HSSFRichTextString(value.toString())); } } } // set the width for each column for (int c = 0; c < cols; c++) { short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value sheet.setColumnWidth(c, width); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); // open Excel URLHelper.openURL(file.getPath()); }
From source file:poi.hssf.view.SVTableCellRenderer.java
License:Apache License
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;//from w w w. j av a 2 s .c om if (c != null) { HSSFCellStyle s = c.getCellStyle(); HSSFFont f = wb.getFontAt(s.getFontIndex()); setFont(SVTableUtils.makeFont(f)); if (s.getFillPattern() == HSSFCellStyle.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.getBorderTop(), s.getBorderRight(), s.getBorderBottom(), s.getBorderLeft(), hasFocus); setBorder(cellBorder); isBorderSet = true; //Set the value that is rendered for the cell switch (c.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: setValue(""); break; case HSSFCell.CELL_TYPE_BOOLEAN: if (c.getBooleanCellValue()) { setValue("true"); } else { setValue("false"); } break; case HSSFCell.CELL_TYPE_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 HSSFCell.CELL_TYPE_STRING: setValue(c.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_FORMULA: default: setValue("?"); } //Set the text alignment of the cell switch (s.getAlignment()) { case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_JUSTIFY: case HSSFCellStyle.ALIGN_FILL: setHorizontalAlignment(SwingConstants.LEFT); break; case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: setHorizontalAlignment(SwingConstants.CENTER); break; case HSSFCellStyle.ALIGN_GENERAL: case HSSFCellStyle.ALIGN_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, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_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; }