List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:com.endro.belajar.controller.MainController.java
private void clickedExport() { exportPenjualan.getButtonExport().addActionListener(new ActionListener() { @Override/* www . j a v a 2 s. c o m*/ public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = exportPenjualan.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = exportPenjualan.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(exportPenjualan, "Form tanggal diisi dengan lengkap!"); } catch (IOException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } finally { exportPenjualan.dispose(); exportPenjualan = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = exportPenjualan.getChooserSaveFile().showOpenDialog(exportPenjualan); if (returnVal == exportPenjualan.getChooserSaveFile().APPROVE_OPTION) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Just Example"); List<InvoiceOrder> list = daftarInvoice; Integer rowTable = 0; Integer cellTable = 0; CellStyle cellStyleTanggal = workbook.createCellStyle(); CellStyle cellStyleHeader = workbook.createCellStyle(); CellStyle cellStyleDouble = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFFont font = workbook.createFont(); cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); cellStyleDouble.setDataFormat( createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000")); font.setBold(true); cellStyleHeader.setFont(font); cellStyleHeader.setWrapText(true); //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS); for (InvoiceOrder order : list) { Row row = sheet.createRow(rowTable); if (rowTable == 0) { sheet.setColumnWidth(0, 2000); Cell cellHeader = row.createCell(0); cellHeader.setCellValue("ID"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(1, 5000); cellHeader = row.createCell(1); cellHeader.setCellValue("Nomor Transaksi"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(2, 4000); cellHeader = row.createCell(2); cellHeader.setCellValue("Tanggal"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(3, 6000 * 3); cellHeader = row.createCell(3); cellHeader.setCellValue("Informasi Posting"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(4, 4850); cellHeader = row.createCell(4); cellHeader.setCellValue("Total Sebelum Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(5, 5000); cellHeader = row.createCell(5); cellHeader.setCellValue("Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(6, 4500); cellHeader = row.createCell(6); cellHeader.setCellValue("Total Setelah Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(7, 5000 * 2); cellHeader = row.createCell(7); cellHeader.setCellValue("Alamat Pengiriman"); cellHeader.setCellStyle(cellStyleHeader); } else { row.createCell(0).setCellValue((Integer) order.getPk()); row.createCell(1).setCellValue((String) order.getNomortransaksi()); Cell cellTanggal = row.createCell(2); cellTanggal.setCellValue((Date) order.getTanggal()); cellTanggal.setCellStyle(cellStyleTanggal); row.createCell(3).setCellValue((String) order.getInformasiposting()); Cell cellDouble = row.createCell(4); cellDouble.setCellValue(order.getTotalbelumdiskon()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(5); cellDouble.setCellValue(order.getDiskonfaktur()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(6); cellDouble.setCellValue(order.getTotalsetelahdiskon()); cellDouble.setCellStyle(cellStyleDouble); row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null" : order.getAlamatpengiriman()); } rowTable++; } File file = exportPenjualan.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(exportPenjualan, "Telah tersimpan di " + file + File.separator + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?", "Notification", JOptionPane.OK_CANCEL_OPTION); if (pesan == JOptionPane.YES_OPTION) { if ("Linux".equals(System.getProperty("os.name"))) { String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } else if ("Windows".equals(System.getProperty("os.name"))) { String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } } } else { exportPenjualan.getChooserSaveFile().cancelSelection(); } } }); }
From source file:com.evidon.areweprivateyet.Aggregator.java
License:Open Source License
private void createContent(Workbook wb, Sheet s, String map) { Map<String, String> out = new HashMap<String, String>(); int rownum = 2; int cellnum = 0; // create a merged list of domains. domains.clear();/*from w w w. jav a 2 s . c o m*/ for (String database : results.keySet()) { if (database.equals("baseline")) { Analyzer ra = results.get(database); Map<String, Integer> mapToUse = this.getMap(map, ra); for (String domain : mapToUse.keySet()) { if ((!domains.contains(domain)) && !exclusions.contains(domain)) { domains.add(domain); out.put(domain, ""); } } } } CellStyle numberStyle = wb.createCellStyle(); numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number")); s.setColumnWidth(0, 5000); for (String domain : domains) { cellnum = 0; Row r = s.createRow(rownum); Cell c = r.createCell(cellnum); c.setCellValue(domain); cellnum++; for (String database : results.keySet()) { Analyzer ra = results.get(database); Map<String, Integer> mapToUse = this.getMap(map, ra); c = r.createCell(cellnum); try { if (mapToUse.containsKey(domain)) { c.setCellValue(mapToUse.get(domain)); } else { c.setCellValue(0); } } catch (Exception e) { c.setCellValue(0); } c.setCellStyle(numberStyle); cellnum++; } rownum++; } // Totals. rownum++; cellnum = 1; Row r = s.createRow(rownum); Cell c = r.createCell(0); c.setCellValue("Totals:"); for (int i = 0; i < results.keySet().size(); i++) { c = r.createCell(cellnum); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) { Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } else { Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue()); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } cellnum++; } // Delta/Reduction rownum++; cellnum = 1; r = s.createRow(rownum); c = r.createCell(0); c.setCellValue("Tracking Decrease:"); for (int i = 0; i < results.keySet().size(); i++) { c = r.createCell(cellnum); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) { Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } else { Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue()); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } cellnum++; } }
From source file:com.evidon.areweprivateyet.Aggregator.java
License:Open Source License
public void createSpreadSheet() throws Exception { int row = 2, cell = 0, sheet = 0; FileOutputStream file = new FileOutputStream(path + "analysis.xls"); Workbook wb = new HSSFWorkbook(); // content: total content length sheet. Sheet s = wb.createSheet();// w w w . ja v a2 s . c o m wb.setSheetName(sheet, "Content Length"); this.createHeader(wb, s, "Total Content Length in MB", 0); Row r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); c.setCellValue(results.get(database).totalContentLength / 1024 / 1024); cell++; } row++; cell = 0; r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); if (database.equals("baseline")) { c.setCellValue("Decrease:"); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), "0"); decrease.put(database, contents); } else { c = r.createCell(cell); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(database, contents); } cell++; } sheet++; // When content is created, baseline is used as a base for every entry. For example, // if baseline contained doubleclick.com, this will be output and each other analyzer's // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick. // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never // shown in the spreadsheet or any other results. // so this means if we have tracker/whatever URLs in a non-baseline profile // and these URLs are NOT in the baseline profile, // we wouldn't see those trackers/whatever in the final comparison. // content: HTTP Requests s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Requests"); this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1); this.createContent(wb, s, "requestCountPerDomain"); sheet++; // content: HTTP Set-Cookie Responses s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Set-Cookie Responses"); this.createHeader(wb, s, "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1); this.createContent(wb, s, "setCookieResponses"); sheet++; // content: Cookie Added - Cookie Deleted s = wb.createSheet(); wb.setSheetName(sheet, "Cookies Added-Deleted"); this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1); this.createContent(wb, s, "cookieTotals"); sheet++; // content: Local Storage counts per domain s = wb.createSheet(); wb.setSheetName(sheet, "Local Storage"); this.createHeader(wb, s, "Local Storage counts per domain", 1); this.createContent(wb, s, "localStorageContents"); sheet++; // content: Pretty Chart s = wb.createSheet(); wb.setSheetName(sheet, "Overall"); int rownum = 0, cellnum = 0; // Header r = s.createRow(rownum); Cell c = r.createCell(0); s.setColumnWidth(0, 8000); c.setCellValue( "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)"); rownum++; r = s.createRow(rownum); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellValue(database); CellStyle cs = wb.createCellStyle(); Font f = wb.createFont(); f.setBoldweight(Font.BOLDWEIGHT_BOLD); cs.setFont(f); c.setCellStyle(cs); cellnum++; } CellStyle numberStyle = wb.createCellStyle(); numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number")); // Content for (String type : decrease.get("baseline").keySet()) { cellnum = 0; rownum++; r = s.createRow(rownum); c = r.createCell(cellnum); c.setCellValue(type); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellStyle(numberStyle); double decreaseValue = Double.parseDouble(decrease.get(database).get(type)); if (decreaseValue < 0) decreaseValue = 0; c.setCellValue(decreaseValue); cellnum++; } } /* for (String database : decrease.keySet()) { for (String type : decrease.get(database).keySet()) { System.out.println(database + "|" + type + "|" + decrease.get(database).get(type)); } } */ wb.write(file); file.close(); }
From source file:com.eyeq.pivot4j.export.poi.ExcelExporter.java
License:Common Public License
protected CellStyle createValueCellStyle() { CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName(fontFamily);/* w w w . j a v a 2 s . c om*/ font.setFontHeightInPoints((short) fontSize); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); style.setFont(font); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); style.setDataFormat((short) 4); return style; }
From source file:com.eyeq.pivot4j.export.poi.ExcelExporter.java
License:Common Public License
protected CellStyle createAggregationCellStyle() { CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName(fontFamily);//from w w w . j a va2s . c o m font.setFontHeightInPoints((short) fontSize); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); style.setFont(font); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); switch (format) { case XSSF: case SXSSF: ((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(Color.lightGray)); break; case HSSF: style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); break; default: assert false; } style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); style.setDataFormat((short) 4); return style; }
From source file:com.firstonesoft.poi.TimesheetDemo.java
License:Apache License
/** * Create a library of cell styles/*from ww w. j a va2 s .c om*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java
License:Apache License
public void build() { // /*from ww w . ja va 2 s .c o m*/ if (excelSheet.hasTitle) { addTitle(excelSheet.title); } merge(0, 0, 0, excelSheet.getFieldList().size() - 1); // addHeaderRow(excelSheet.sheet.getLastRowNum()); excelSheet.setFirstDataRowIndex(excelSheet.sheet.getLastRowNum() + 1); // ? addItemsToSheet(); CellStyle cellStyle = excelSheet.sheet.getWorkbook().createCellStyle(); cellStyle.setDataFormat(excelSheet.sheet.getWorkbook().getCreationHelper().createDataFormat() .getFormat("yyyy-MM-dd HH:mm")); setColumnStyle(2, excelSheet.getFirstDataRowIndex(), excelSheet.sheet.getLastRowNum(), cellStyle); }
From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java
License:Open Source License
private static CellStyle getDateTimeCellStyle(XSSFWorkbook wb) {// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); return cellStyle; }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
private static CellStyle getDateTimeCellStyle(HSSFWorkbook wb) {// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); return cellStyle; }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
@SuppressWarnings("unused") private static CellStyle getStringCellStyle(HSSFWorkbook wb) {// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@")); return cellStyle; }