List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setBold
public void setBold(boolean bold)
From source file:bandaru_excelreadwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*//from w w w . ja va 2 s . c o m Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(style2.ALIGN_CENTER); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Bandaru, Sreekanth"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_SINGLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); combined.setAlignment(combined.ALIGN_CENTER); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIME.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"), CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"), CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"), CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"), CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"), CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26") }; /* setting new rule to apply alternate colors to cells having same Fenre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"), CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"), CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"), CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"), CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"), CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"), CellRangeAddress.valueOf("A29:F29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; CellStyle style4 = workbook.createCellStyle(); XSSFFont my_font2 = (XSSFFont) workbook.createFont(); my_font2.setBold(true); style4.setFont(my_font2); rowMain.setRowStyle(style4); rowMain.createCell(mainCellIndex++).setCellValue("SNO"); rowMain.createCell(mainCellIndex++).setCellValue("Genre"); rowMain.createCell(mainCellIndex++).setCellValue("Rating"); rowMain.createCell(mainCellIndex++).setCellValue("Movie Name"); rowMain.createCell(mainCellIndex++).setCellValue("Director"); rowMain.createCell(mainCellIndex++).setCellValue("Release Date"); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:br.com.sose.utils.BigGridDemo_temp.java
License:Apache License
/** * Create a library of cell styles./*from w ww. ja va 2 s .c om*/ */ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("date", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("header", style5); return styles; }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
private XSSFCellStyle getModulStyle(XSSFWorkbook wb) { XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); font.setFontHeight(10);/* w w w .j a va2 s .co m*/ font.setFontName(fontName.getStringValue()); cellStyle.setFont(font); return cellStyle; }
From source file:com.endro.belajar.controller.InvoiceProdukController.java
private void clickedbuttonExportDialog() { dialogExport.getButtonExport().addActionListener(new ActionListener() { @Override/*from w w w.j a v a 2 s . co m*/ public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = dialogExport.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = dialogExport.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException | IOException ex) { Logger.getLogger(InvoiceProdukController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(dialogExport, "Form tanggal diisi dengan lengkap!"); } finally { dialogExport.dispose(); dialogExport = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = dialogExport.getChooserSaveFile().showOpenDialog(dialogExport); if (returnVal == dialogExport.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 = dialogExport.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(dialogExport, "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 { dialogExport.getChooserSaveFile().cancelSelection(); } } }); }
From source file:com.endro.belajar.controller.MainController.java
private void clickedExport() { exportPenjualan.getButtonExport().addActionListener(new ActionListener() { @Override//ww w . ja v a2s. com 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.hauldata.dbpa.file.book.XlsxTargetSheet.java
License:Apache License
private static Font getFont(FontStyles fontStyles, SXSSFWorkbook book, Map<FontStyles, XSSFFont> fontsUsed, Map<Integer, XSSFColor> colorsUsed) { XSSFFont font = fontsUsed.get(fontStyles); if (font != null) { return font; }//from w w w. j ava 2 s. c o m font = (XSSFFont) book.createFont(); if (fontStyles.color != null) { font.setColor(getColor(fontStyles.color, book, colorsUsed)); } if (fontStyles.fontStyle != null) { switch (fontStyles.fontStyle) { case NORMAL: break; case ITALIC: font.setItalic(true); break; } } if (fontStyles.fontWeight != null) { switch (fontStyles.fontWeight) { case NORMAL: break; case BOLD: font.setBold(true); break; } } if (fontStyles.textDecorationLine != null) { switch (fontStyles.textDecorationLine) { case NONE: break; case LINE_THROUGH: font.setStrikeout(true); break; case UNDERLINE: font.setUnderline((fontStyles.textDecorationStyle == FontStyles.TextDecorationStyle.DOUBLE) ? FontUnderline.DOUBLE : FontUnderline.SINGLE); break; } } fontsUsed.put(fontStyles, font); return font; }
From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadStyle.setFont(cellHeadFont); sh.setColumnWidth(0, 12000);//from w ww . jav a 2 s. co m int row = 0; Row nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyle); cell1.setCellValue("KPI"); Cell cell2 = nowRow.createCell(1); cell2.setCellStyle(cellHeadStyle); cell2.setCellValue("Maximum"); Cell cell3 = nowRow.createCell(2); cell3.setCellStyle(cellHeadStyle); cell3.setCellValue("Target"); Cell cell4 = nowRow.createCell(3); cell4.setCellStyle(cellHeadStyle); cell4.setCellValue("Minimum"); Cell cell5 = nowRow.createCell(4); cell5.setCellStyle(cellHeadStyle); cell5.setCellValue("Current score"); Cell cell6 = nowRow.createCell(5); cell6.setCellStyle(cellHeadStyle); cell6.setCellValue("Previous score"); Cell cell7 = nowRow.createCell(6); cell7.setCellStyle(cellHeadStyle); cell7.setCellValue("Change(%)"); row++; List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>) context.get("periodDatas"); for (PeriodTrendsData<KpiVO> periodData : periodDatas) { nowRow = sh.createRow(row); cell1 = nowRow.createCell(0); cell1.setCellValue(periodData.getCurrent().getName()); cell2 = nowRow.createCell(1); cell2.setCellValue(periodData.getCurrent().getMax()); cell3 = nowRow.createCell(2); cell3.setCellValue(periodData.getCurrent().getTarget()); cell4 = nowRow.createCell(3); cell4.setCellValue(periodData.getCurrent().getMin()); cell5 = nowRow.createCell(4); cell5.setCellValue(BscReportSupportUtils.parse2(periodData.getCurrent().getScore())); cell6 = nowRow.createCell(5); cell6.setCellValue(BscReportSupportUtils.parse2(periodData.getPrevious().getScore())); cell7 = nowRow.createCell(6); cell7.setCellValue(BscReportSupportUtils.parse2(periodData.getChange())); row++; } nowRow = sh.createRow(row); cell1 = nowRow.createCell(0); cell1.setCellValue("Current period: " + (String) context.get("currentPeriodDateRange") + " , Previous period: " + (String) context.get("previousPeriodDateRange")); }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { Row headRow = sh.createRow(row);// w ww . j a va 2 s . co m headRow.setHeight((short) 700); int cell = 0; XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor())); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor())); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(bgColor); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadFont.setColor(fnColor); cellHeadStyle.setFont(cellHeadFont); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellHeadStyle.setAlignment(HorizontalAlignment.CENTER); cellHeadStyle.setWrapText(true); int cols = 12; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 4000); Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue( vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore())); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); // ------------------------------------------------------------------------ bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())); fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())); cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(bgColor); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadFont.setColor(fnColor); cellHeadStyle.setFont(cellHeadFont); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellHeadStyle.setAlignment(HorizontalAlignment.CENTER); cellHeadStyle.setWrapText(true); row++; headRow = sh.createRow(row); cell = 0; int titleCols = 4; for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle()); headCell1.setCellStyle(cellHeadStyle); } for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle()); headCell1.setCellStyle(cellHeadStyle); } for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3)); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7)); sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11)); // ------------------------------------------------------------------------ return 2; }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { Map<String, String> managementMap = BscKpiCode.getManagementMap(false); //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false); int itemCols = 4; int mrRow = row; for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); for (int kx = 0; kx < objective.getKpis().size(); kx++) { KpiVO kpi = objective.getKpis().get(kx); Row contentRow = sh.createRow(row++); contentRow.setHeight((short) 4000); int cell = 0; for (int i = 0; i < itemCols; i++) { String content = this.getItemsContent(perspective.getName(), perspective.getScore(), perspective.getWeight(), perspective.getTarget(), perspective.getMin()); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(// w w w . ja v a 2 s . c om new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0 && ox == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES", perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "", 0); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } for (int i = 0; i < itemCols; i++) { String content = this.getItemsContent(objective.getName(), objective.getScore(), objective.getWeight(), objective.getTarget(), objective.getMin()); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0 && kx == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES", objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } for (int i = 0; i < itemCols; i++) { //String content = this.getKpisContent(kpi, managementMap, calculationMap); String content = this.getKpisContent(kpi, managementMap); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(), kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(), kpi.getQuasiRange()); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } } } } for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3)); for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7)); for (int kx = 0; kx < objective.getKpis().size(); kx++) { sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11)); } mrRow += objective.getKpis().size(); } } return row++; }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { String frequency = (String) context.get("frequency"); String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim(); String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim(); String startDate = StringUtils.defaultString((String) context.get("startDate")).trim(); String endDate = StringUtils.defaultString((String) context.get("endDate")).trim(); String date1 = startDate;//from w w w .j a v a2 s .c om String date2 = endDate; if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) { date1 = startYearDate + "/01/01"; date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12); } Map<String, Object> headContentMap = new HashMap<String, Object>(); this.fillHeadContent(context, headContentMap); XSSFCellStyle cellStyleLabel = wb.createCellStyle(); cellStyleLabel.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()))); cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFontLabel = wb.createFont(); cellFontLabel.setBold(false); cellFontLabel .setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()))); cellStyleLabel.setFont(cellFontLabel); cellStyleLabel.setWrapText(true); //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleLabel.setBorderBottom(BorderStyle.THIN); cellStyleLabel.setBorderTop(BorderStyle.THIN); cellStyleLabel.setBorderRight(BorderStyle.THIN); cellStyleLabel.setBorderLeft(BorderStyle.THIN); int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores() .size(); int cell = 0; for (int i = 0; i < cols; i++) { String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency) + " Date range: " + date1 + " ~ " + date2 + "\n" + StringUtils.defaultString((String) headContentMap.get("headContent")); Row headRow = sh.createRow(row); headRow.setHeight((short) 700); Cell headCell1 = headRow.createCell(cell); headCell1.setCellValue(content); headCell1.setCellStyle(cellStyleLabel); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; int kpiCols = 4; int kpiRows = 2; for (PerspectiveVO perspective : vision.getPerspectives()) { for (ObjectiveVO objective : perspective.getObjectives()) { for (KpiVO kpi : objective.getKpis()) { cell = 0; for (int r = 0; r < kpiRows; r++) { Row contentRow = sh.createRow(row++); contentRow.setHeight((short) 400); for (int c = 0; c < kpiCols; c++) { XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(c); contentCell1.setCellValue(kpi.getName()); contentCell1.setCellStyle(cellStyle); } cell = 4; if (r == 0) { // date for (int d = 0; d < kpi.getDateRangeScores().size(); d++) { DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue(dateRangeScore.getDate()); contentCell1.setCellStyle(cellStyle); } } if (r == 1) { // score for (int d = 0; d < kpi.getDateRangeScores().size(); d++) { DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue( " " + BscReportSupportUtils.parse2(dateRangeScore.getScore())); contentCell1.setCellStyle(cellStyle); byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore()); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } } sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1)); } } } return row++; }