List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setDataFormat
@Override public void setDataFormat(short fmt)
From source file:org.sevenorcas.style.app.mod.ss.SpreadsheetCell.java
/** * Get cell format// ww w . j a v a 2s . c o m * @param wb * @return */ public HSSFCellStyle getCellStyle(HSSFWorkbook wb) { //EX1 if (sheet.containsStyleId(styleId)) { return sheet.getStyle(styleId); } HSSFCellStyle style = wb.createCellStyle(); Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null); switch (clazzX != null ? clazzX : CLASS_STRING) { case CLASS_DATE: if (!isHeader()) { CreationHelper createHelper = wb.getCreationHelper(); style = wb.createCellStyle(); style.setDataFormat(createHelper.createDataFormat() .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy")); } style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_PERCENTAGE: style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("0.00%")); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_INTEGER: case CLASS_LONG: style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_DOUBLE: case CLASS_UKURS: //Needs work // if (numberFormat != null){ // createHelper = wb.getCreationHelper(); // style = wb.createCellStyle(); // style.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat)); // } style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_INTEGER_LEFT: case CLASS_STRING: case CLASS_BOOLEAN: case CLASS_CHARACTER: default: style.setAlignment(HSSFCellStyle.ALIGN_LEFT); } sheet.setCellStyle(wb, style, this, styleId); return style; }
From source file:org.tentackle.ui.FormTableUtilityPopup.java
License:Open Source License
/** * Converts the table to an excel spreadsheet. * @param file the output file/* w w w . java2 s. c o m*/ * @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:org.webguitoolkit.ui.util.export.ExcelTableExport.java
License:Apache License
public void writeTo(Table table, OutputStream out) { TableExportOptions exportOptions = table.getExportOptions(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet();//from w w w.ja v a 2s . co m HSSFFont fontbold = wb.createFont(); fontbold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headstyle = wb.createCellStyle(); headstyle.setFont(fontbold); setExcelheadstyle(headstyle); // create dateStyle HSSFCellStyle cellStyleDate = wb.createCellStyle(); if (StringUtils.isNotEmpty(exportOptions.getExcelDateFormat())) { cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat(exportOptions.getExcelDateFormat())); } else { cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); } setExcelDateStyle(cellStyleDate); sheet = excelExport(table, sheet); String sheetName = exportOptions.getExcelSheetName(); if (StringUtils.isEmpty(sheetName)) { sheetName = StringUtils.isNotEmpty(table.getTitle()) ? table.getTitle() : "sheet"; } if (sheetName.length() > 30) { sheetName = sheetName.substring(0, 30); } // DM: 19.11.2010: Slashes in Sheetname are not allowed, e.g. "Planned deliveries / disposals" did throw // IllegalArgumentException. // --> catch Exception and set 'Sheet1' as default. try { wb.setSheetName(0, sheetName); } catch (IllegalArgumentException e) { logger.error("Sheetname is not valid:" + sheetName + " using Sheet1 as default.", e); wb.setSheetName(0, "Sheet1"); } try { wb.write(out); } catch (IOException e) { logger.error(e); } }
From source file:padrao.Run.java
public static void main(String[] args) { try {//www . j a va 2 s. c o m FileOutputStream fileOut = new FileOutputStream("poi-test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("POI Worksheet"); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue("Hello"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellA1.setCellStyle(cellStyle); HSSFCell cellB1 = row1.createCell((short) 1); cellB1.setCellValue("Goodbye"); cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellB1.setCellStyle(cellStyle); HSSFCell cellC1 = row1.createCell((short) 2); cellC1.setCellValue(true); HSSFCell cellD1 = row1.createCell((short) 3); cellD1.setCellValue(new Date()); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cellD1.setCellStyle(cellStyle); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:percobaannpoi.NewNPOI.java
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed try {/*from w ww . j a va2s . co m*/ FileOutputStream fileOut = new FileOutputStream("poi-test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("POI Worksheet"); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue("Hello"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellA1.setCellStyle(cellStyle); HSSFCell cellB1 = row1.createCell((short) 1); cellB1.setCellValue("Goodbye"); cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellB1.setCellStyle(cellStyle); HSSFCell cellC1 = row1.createCell((short) 2); cellC1.setCellValue(true); HSSFCell cellD1 = row1.createCell((short) 3); cellD1.setCellValue(new Date()); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cellD1.setCellStyle(cellStyle); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:poi.hssf.usermodel.examples.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;// w w w . j a v a 2 s . c o m // create a new file FileOutputStream out = new FileOutputStream("workbook.xls"); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet(); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it red f.setColor(HSSFColor.RED.index); // make it bold //arial is the default font f.setBoldweight(f.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it the color at palette index 0xf (white) f2.setColor(HSSFColor.WHITE.index); //make it bold f2.setBoldweight(f2.BOLDWEIGHT_BOLD); //set cell stlye cs.setFont(f); //set the cell format see HSSFDataFromat for a full list cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set foreground fill to red cs2.setFillForegroundColor(HSSFColor.RED.index); // set the font cs2.setFont(f2); // set the sheet name to HSSF Test wb.setSheetName(0, "HSSF Test"); // create a sheet with 300 rows (0-299) for (rownum = 0; rownum < 300; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 50 cells (0-49) (the += 2 becomes apparent later for (int cellnum = 0; cellnum < 50; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); } // create a string cell (see why += 2 in the c = r.createCell(cellnum + 1); // set the cell's string value to "TEST" c.setCellValue("TEST"); // make this column a bit wider s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20))); // on every other row if ((rownum % 2) == 0) { // set this to the white on red cell style // we defined above c.setCellStyle(cs2); } } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (int cellnum = 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); }
From source file:poi.hssf.usermodel.examples.CreateDateCells.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(0);/* w w w .java 2 s. c o m*/ // Create a cell and put a date value in it. The first cell is not styled as a date. HSSFCell cell = row.createCell(0); cell.setCellValue(new Date()); // we style the second cell as a date (and time). It is important to create a new cell style from the workbook // otherwise you can end up modifying the built in style and effecting not only this cell but other cells. HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Inserts a single bulleted item into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell.// w w w .ja v a2 s . c o m * @param listItem An instance of the String class encapsulating the * items text. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list item * will be written. */ public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) { // A format String must be built to ensure that the contents of the // cell appear as a bulleted item. HSSFDataFormat format = workbook.createDataFormat(); String formatString = InCellLists.BULLET_CHARACTER + " @"; int formatIndex = format.getFormat(formatString); // Construct an HSSFCellStyle and set it's data formt to use the // object created above. HSSFCellStyle bulletStyle = workbook.createCellStyle(); bulletStyle.setDataFormat((short) formatIndex); // Set the cells contents and style. cell.setCellValue(new HSSFRichTextString(listItem)); cell.setCellStyle(bulletStyle); }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.// www . ja v a 2 s. com */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); // wb.close(); }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells./*w ww. ja va 2 s . c o m*/ */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); try { wb.write(out); } finally { out.close(); } wb.close(); }