List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormat
public static String getBuiltinFormat(short index)
From source file:org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver.java
License:Open Source License
private void initDateTimeStyles(Workbook wb) { this.DATE_TIME_STYLE = wb.createCellStyle(); this.DATE_TIME_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_TIME_FORMAT)); this.DATE_STYLE = wb.createCellStyle(); this.DATE_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); this.TIME_STYLE = wb.createCellStyle(); this.TIME_STYLE.setDataFormat(HSSFDataFormat.getBuiltinFormat(TIME_FORMAT)); }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGenericComponent(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, ComponentExportOptions opt) throws Throwable { Object[] row = null;/*from w w w . j a v a 2s . c o m*/ Object obj = null; HSSFRow r = null; HSSFCell c = null; HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); if (opt.getCellsContent() != null) for (int i = 0; i < opt.getCellsContent().length; i++) { row = opt.getCellsContent()[i]; r = s.createRow(rownum); for (short j = 0; j < row.length; j++) { c = r.createCell(j); obj = row[j]; if (obj != null) { if (obj instanceof String) { try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(obj.toString()); c.setCellStyle(csText); } else if (obj instanceof BigDecimal || obj instanceof Double || obj instanceof Float || obj.getClass() == Double.TYPE || obj.getClass() == Float.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csDecNum); } else if (obj instanceof Integer || obj instanceof Short || obj instanceof Long || obj.getClass() == Integer.TYPE || obj.getClass() == Short.TYPE || obj.getClass() == Long.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csIntNum); } else if (obj instanceof Boolean) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj.getClass().equals(boolean.class)) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj instanceof Date || obj instanceof java.util.Date || obj instanceof java.sql.Timestamp) { c.setCellValue((java.util.Date) obj); c.setCellStyle(csDateTime); } } else { c.setCellValue(""); c.setCellStyle(csText); } } rownum++; } return rownum; }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGrid(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, GridExportOptions opt) throws Throwable { // declare a row object reference HSSFRow r = null;/*from w w w.ja va 2s . c om*/ // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"/*opt.getDateFormat()*/)); csDate.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDate.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDate.setBorderRight(HSSFCellStyle.BORDER_THIN); csDate.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTime = wb.createCellStyle(); csTime.setDataFormat(HSSFDataFormat .getBuiltinFormat(exportOptions.getTimeFormat().equals("HH:mm") ? "h:mm" : "h:mm AM/PM")); csTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csTime.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); // prepare vo getters methods... String methodName = null; String attributeName = null; Hashtable gettersMethods = new Hashtable(); Method[] voMethods = opt.getValueObjectType().getMethods(); for (int i = 0; i < voMethods.length; i++) { methodName = voMethods[i].getName(); if (methodName.startsWith("get")) { attributeName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4); if (opt.getExportAttrColumns().contains(attributeName)) gettersMethods.put(attributeName, voMethods[i]); } } Response response = null; int start = 0; Object value = null; Object vo = null; int type; boolean firstRow = true; if (opt.getTitle() != null && !opt.getTitle().equals("")) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getTitle()); c.setCellStyle(csTitle); rownum++; rownum++; } String[] filters = opt.getFilteringConditions(); if (filters != null) { for (int i = 0; i < filters.length; i++) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(filters[i]); rownum++; } rownum++; } do { response = opt.getGridDataLocator().loadData(GridParams.NEXT_BLOCK_ACTION, start, opt.getFilteredColumns(), opt.getCurrentSortedColumns(), opt.getCurrentSortedVersusColumns(), opt.getValueObjectType(), opt.getOtherGridParams()); if (response.isError()) throw new Exception(response.getErrorMessage()); for (int j = 0; j < ((VOListResponse) response).getRows().size(); j++) { if (firstRow) { firstRow = false; // create the first row... r = s.createRow(rownum++); for (short i = 0; i < opt.getExportColumns().size(); i++) { c = r.createCell(i); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getExportColumns().get(i).toString()); c.setCellStyle(csTitle); } for (int k = 0; k < opt.getTopRows().size(); k++) { // create a row for each top rows... vo = opt.getTopRows().get(k); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 0); } } // create a row vo = ((VOListResponse) response).getRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 1); } start = start + ((VOListResponse) response).getRows().size(); if (!((VOListResponse) response).isMoreRows()) break; } while (rownum < opt.getMaxRows()); for (int j = 0; j < opt.getBottomRows().size(); j++) { // create a row for each bottom rows... vo = opt.getBottomRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 2); } return rownum; }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;//from w w w.j a v a 2s . co m try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor((short) 0xc); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor((short) Font.COLOR_RED); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout(true); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; 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 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; 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))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //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 (short cellnum = (short) 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 try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.tentackle.ui.FormTableUtilityPopup.java
License:Open Source License
/** * Converts the table to an excel spreadsheet. * @param file the output file/*from w ww. jav a2 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();/* w ww . j ava 2 s .com*/ 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:org.zephyrsoft.sdb2.StatisticsController.java
License:Open Source License
public void exportStatisticsAll(SongsModel songs, File targetExcelFile) { // collect basic data Map<String, Song> songsByUUID = new HashMap<>(); for (Song song : songs) { songsByUUID.put(song.getUUID(), song); }//from w w w . j a v a 2 s. co m List<String> months = statistics.getUsedMonths(); // create a new workbook Workbook workbook = new HSSFWorkbook(); // define formats CellStyle integerStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); integerStyle.setDataFormat(df.getFormat("0")); CellStyle textStyle = workbook.createCellStyle(); textStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); CellStyle textBoldStyle = workbook.createCellStyle(); textBoldStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); org.apache.poi.ss.usermodel.Font font = workbook.createFont(); font.setColor(org.apache.poi.ss.usermodel.Font.COLOR_RED); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); textBoldStyle.setFont(font); for (String month : months) { Map<String, Integer> monthStatsByUUID = statistics.getStatisticsForMonth(month); Map<Song, Integer> monthStatsBySong = new TreeMap<>(); for (String uuid : monthStatsByUUID.keySet()) { Song song = songs.getByUUID(uuid); if (song != null) { monthStatsBySong.put(song, monthStatsByUUID.get(uuid)); } else { LOG.info("no song found in database for UUID {}", uuid); } } Sheet sheet = workbook.createSheet(month); Row row = null; int rownum = 0; row = sheet.createRow(rownum); int cellnum = 0; addTextCell(row, cellnum++, textBoldStyle, "Presentation Count"); addTextCell(row, cellnum++, textBoldStyle, "Song Title"); addTextCell(row, cellnum++, textBoldStyle, "Composer (Music)"); addTextCell(row, cellnum++, textBoldStyle, "Author (Text)"); addTextCell(row, cellnum++, textBoldStyle, "Publisher"); addTextCell(row, cellnum++, textBoldStyle, "Copyright Notes"); addTextCell(row, cellnum++, textBoldStyle, "Song Lyrics"); rownum++; for (Song song : monthStatsBySong.keySet()) { row = sheet.createRow(rownum); cellnum = 0; addIntegerCell(row, cellnum++, integerStyle, monthStatsBySong.get(song)); addTextCell(row, cellnum++, textStyle, song.getTitle()); addTextCell(row, cellnum++, textStyle, song.getComposer()); addTextCell(row, cellnum++, textStyle, song.getAuthorText()); addTextCell(row, cellnum++, textStyle, song.getPublisher()); addTextCell(row, cellnum++, textStyle, song.getAdditionalCopyrightNotes()); addTextCell(row, cellnum++, textStyle, song.getLyrics()); rownum++; } for (int i = 0; i < cellnum; i++) { sheet.autoSizeColumn(i); } sheet.createFreezePane(0, 1); } try (FileOutputStream out = new FileOutputStream(targetExcelFile)) { workbook.write(out); out.close(); LOG.info("all statistics exported"); } catch (IOException e) { ErrorDialog.openDialog(null, "Could not export the statistics to:\n" + targetExcelFile.getAbsolutePath() + "\n\nPlease verify that you have write access and the file is not opened by any other program!"); LOG.warn("could not write statistics to file", e); } }
From source file:padrao.Run.java
public static void main(String[] args) { try {/*from w ww . j av a 2s . c om*/ 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:paysheets.PaySheetFormatter.java
public static void addTitleRow(HSSFWorkbook workbook) { workbook.createSheet("Sheet 1"); // Each pay sheet only uses the first sheet HSSFSheet sheet = workbook.getSheetAt(0); setDefaultColumnWidth(sheet);/*from w w w . j a va 2 s . co m*/ HSSFRow row; HSSFCell cell; // Create a font and set its attributes Font font = workbook.createFont(); font.setFontHeightInPoints((short) 11); // Set the color to black (constant COLOR_NORMAL) font.setColor(Font.COLOR_NORMAL); font.setBold(true); // Create a cell style and set its properties CellStyle cs = workbook.createCellStyle(); // Set the data format to the built in text format cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // Set the cell style to use the font created previously cs.setFont(font); // Create the first title row row = sheet.createRow(0); // Use the default row height (-1) is sheet default row.setHeight((short) -1); // Add the first title row's 6 cells for (int cellNum = 0; cellNum < 6; cellNum++) { cell = row.createCell(cellNum); cell.setCellStyle(cs); } // Populate first row's values cell = row.getCell(PaySheet.DATE_INDEX); cell.setCellValue("DATE"); cell = row.getCell(PaySheet.CUST_INDEX); cell.setCellValue("CUSTOMER"); cell = row.getCell(PaySheet.PAY_INDEX); cell.setCellValue("PAY"); cell = row.getCell(PaySheet.NONSERIAL_INDEX); cell.setCellValue("EQUIPMENT"); cell = row.getCell(PaySheet.SERIAL_INDEX); cell.setCellValue("SERIALIZED"); cell = row.getCell(PaySheet.SHS_INDEX); cell.setCellValue("SHS"); // Create second title row row = sheet.createRow(1); row.setHeight((short) -1); // Add the cells to the row for (int cellNum = 0; cellNum < 3; cellNum++) { cell = row.createCell(cellNum); cell.setCellStyle(cs); } // Populate the second title row's values cell = row.getCell(PaySheet.WO_INDEX); cell.setCellValue("WORK ORDER"); cell = row.getCell(PaySheet.TYPE_INDEX); cell.setCellValue("TYPE"); cell = row.getCell(PaySheet.LEP_INDEX); cell.setCellValue("LEP"); // Add thick border around title row addJobBorder(workbook, 0); }
From source file:paysheets.PaySheetFormatter.java
public static void addJobFormatting(HSSFWorkbook workbook, int rowIndex) { HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row;/*from w w w . j av a 2s. com*/ HSSFCell cell; Font font = workbook.createFont(); font.setBold(false); font.setFontHeightInPoints((short) 10); font.setColor(Font.COLOR_NORMAL); // Create a cell style for general text CellStyle generalStyle = workbook.createCellStyle(); generalStyle.setFont(font); generalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // Create a cell style for dates CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setFont(font); // Set the cell data format to date (0xe) is the built in format dateStyle.setDataFormat((short) 0xe); dateStyle.setAlignment(CellStyle.ALIGN_LEFT); // Create a new row at the given index row = sheet.createRow(rowIndex); // Format the first row for the new job for (int cellNum = 0; cellNum < 6; cellNum++) { cell = row.createCell(cellNum); // Only the first cell uses the date style if (cellNum > 0) { cell.setCellStyle(generalStyle); } else { cell.setCellStyle(dateStyle); } } // Create second row for the new Job at rowIndex + 1 row = sheet.createRow(rowIndex + 1); for (int cellNum = 0; cellNum < 3; cellNum++) { cell = row.createCell(cellNum); cell.setCellStyle(generalStyle); } }