List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle
void setCellStyle(CellStyle style);
Set the style for the cell.
From source file:com.mycompany.gannaraputakehomeexam.WritingToExcel.java
public void writeSongsToExcel(List<SongsList> songList) { /*//from w ww.ja v a 2 s . co m Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Gannarapu_Output"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); XSSFFont font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); style2.setFont(font); style2.setAlignment(CellStyle.ALIGN_CENTER); // style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); // style2.setFillPattern(CellStyle.SOLID_FOREGROUND); // 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("Gannarapu, Anirudh"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_DOUBLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("5"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.RED.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 Genre */ 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; Cell SNO = rowMain.createCell(mainCellIndex++); SNO.setCellValue("SNO"); SNO.setCellStyle(style2); Cell gen = rowMain.createCell(mainCellIndex++); gen.setCellValue("Genre"); gen.setCellStyle(style2); Cell credit = rowMain.createCell(mainCellIndex++); credit.setCellValue("Credit Score"); credit.setCellStyle(style2); Cell name = rowMain.createCell(mainCellIndex++); name.setCellValue("Album Name"); name.setCellStyle(style2); Cell art = rowMain.createCell(mainCellIndex++); art.setCellValue("Artist"); art.setCellStyle(style2); Cell release = rowMain.createCell(mainCellIndex++); release.setCellValue("Release Date"); release.setCellStyle(style2); /* populating cell values */ int rowIndex = 4; int sno = 1; for (SongsList 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:com.mycompany.gayamtakehomeexam.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*/*from ww w. ja v a2 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); CellStyle style1 = workbook.createCellStyle();//Create style Font font = workbook.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold style1.setFont(font);//set it to bold style1.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style13 = workbook.createCellStyle();//Create style Font font13 = workbook.createFont();//Create font style13.setAlignment(CellStyle.ALIGN_LEFT); CellStyle style14 = workbook.createCellStyle();//Create style Font font14 = workbook.createFont();//Create font style14.setAlignment(CellStyle.ALIGN_RIGHT); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style2.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); 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++); /* Applying underline to Name */ Font underlineFont = workbook.createFont(); underlineFont.setUnderline(HSSFFont.U_SINGLE); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.setFont(underlineFont); cel.setCellStyle(combined); cel.setCellValue("Gayam, Prathibha"); /* 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.LIGHT_ORANGE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:G4") }; 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:G5"), CellRangeAddress.valueOf("A6:G6"), CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"), CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"), CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"), CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"), CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26") }; /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"), CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"), CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"), CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"), CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"), CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"), CellRangeAddress.valueOf("A29:G29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; Cell cell0 = rowMain.createCell(mainCellIndex++); cell0.setCellValue("SNO"); cell0.setCellStyle(style1); Cell cell1 = rowMain.createCell(mainCellIndex++); cell1.setCellValue("Genre"); cell1.setCellStyle(style1); Cell cell2 = rowMain.createCell(mainCellIndex++); cell2.setCellValue("Credit Score"); cell2.setCellStyle(style1); Cell cell3 = rowMain.createCell(mainCellIndex++); cell3.setCellValue("Album Name"); cell3.setCellStyle(style1); Cell cell4 = rowMain.createCell(mainCellIndex++); cell4.setCellValue("Artist"); cell4.setCellStyle(style1); Cell cell5 = rowMain.createCell(mainCellIndex++); cell5.setCellValue("Release Date"); cell5.setCellStyle(style1); /* 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 */ Cell cell20 = row.createCell(cellIndex++); cell20.setCellValue(sno++); cell20.setCellStyle(style14); /* second place in row is Genre */ Cell cell21 = row.createCell(cellIndex++); cell21.setCellValue(song.getGenre()); cell21.setCellStyle(style13); /* third place in row is Critic score */ Cell cell22 = row.createCell(cellIndex++); cell22.setCellValue(song.getCriticscore()); cell22.setCellStyle(style14); /* fourth place in row is Album name */ Cell cell23 = row.createCell(cellIndex++); cell23.setCellValue(song.getAlbumname()); cell23.setCellStyle(style13); /* fifth place in row is Artist */ Cell cell24 = row.createCell(cellIndex++); cell24.setCellValue(song.getArtist()); cell24.setCellStyle(style13); /* 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:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>// w w w . j a v a2 s. co m * 1. : POI UTIL * 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> * </pre> * * @method Name : createWorkBook * @param workbook, sheetName, list, colNames * @return Workbook * @throws Exception * */ @SuppressWarnings("deprecation") public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list, List<Map<String, Object>> colNames) throws Exception { Row row; Cell cell; /* ? ? START */ /* Workbook workbook = new Workbook(); */ CellStyle titleStyle = workbook.createCellStyle(); CellStyle cellStyle = workbook.createCellStyle(); CellStyle contentStyle = workbook.createCellStyle(); CellStyle contentStyle_2 = workbook.createCellStyle(); /* ? */ /* ? ? */ Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 13); titleFont.setFontName("? "); /* ? */ Font colNameFont = workbook.createFont(); colNameFont.setFontHeightInPoints((short) 10); colNameFont.setFontName("? "); /* ? */ Font contentFont = workbook.createFont(); /* ? ? ? */ titleStyle.setFont(titleFont); /* ? / ? ? */ cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */ cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setFont(colNameFont); /* ? / ? */ contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); /* ? / ? */ contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT); contentStyle_2.setFont(contentFont); /* ? */ Sheet sheet = workbook.createSheet(sheetName); /* ?? */ int sheet1_row = 0; List<Map<String, Object>> colList = colNames; /* ? START */ row = sheet.createRow(sheet1_row); for (int i = 0; i < colList.size(); i++) { cell = row.createCell(i); cell.setCellValue(String.valueOf(colList.get(i).get("item" + i))); cell.setCellStyle(cellStyle); } sheet1_row++; /* ? END */ for (Map<String, Object> map : list) { row = sheet.createRow(sheet1_row); for (int j = 0; j < colList.size(); j++) { cell = row.createCell(j); cell.setCellStyle(contentStyle); if (null != map.get(colList.get(j).get("item" + j))) { cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j)))); } else { cell.setCellValue(""); } /* ? ?? */ sheet.autoSizeColumn((short) j); sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312); } sheet1_row++; } return workbook; }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * Excel? /* www . ja v a2s . co m*/ * * @Title: WriteExcel * @Date : 2014-9-11 ?01:33:59 * @param wb * @param rowList * @param xlsPath */ private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) { if (wb == null) { out("???"); return; } Sheet sheet = wb.getSheetAt(0);// sheet // ??????? int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1; int t = 0;// out("???" + rowList.size()); for (Row row : rowList) { if (row == null) continue; // ??? int pos = findInExcel(sheet, row); Row r = null;// ?????? if (pos >= 0) { sheet.removeRow(sheet.getRow(pos)); r = sheet.createRow(pos); } else { r = sheet.createRow(lastRowNum + t++); } //?? CellStyle newstyle = wb.createCellStyle(); //? for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = r.createCell(i);// ?? cell.setCellValue(getCellValue(row.getCell(i)));// ??? // cell.setCellStyle(row.getCell(i).getCellStyle());// if (row.getCell(i) == null) continue; copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ???? cell.setCellStyle(newstyle);// ? // sheet.autoSizeColumn(i);// } } out("???:" + (rowList.size() - t) + " ?" + t); // ?? setMergedRegion(sheet); try { // ??Excel FileOutputStream outputStream = new FileOutputStream(xlsPath); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { out("Excel?? "); e.printStackTrace(); } }
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);/*w ww . j a v a2 s.com*/ cellHeadStyle.setFont(cellHeadFont); sh.setColumnWidth(0, 12000); 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);/*from w ww. j ava 2 s . c o 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(/*from w ww . ja v a 2 s . c o m*/ 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 ww . 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++; }
From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int 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);//from www . ja v a 2s. c o m cellHeadStyle.setFont(cellHeadFont); sh.setColumnWidth(0, 12000); int left = 0; int row = 0; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); for (Map<String, Object> data : chartDatas) { Row nowRow = sh.createRow(row); Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); if (row == 0) { 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("Score"); List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData .get("dateRangeScores"); for (Map<String, Object> rangeScore : dateRangeScores) { Cell cell = nowRow.createCell(5 + left); cell.setCellStyle(cellHeadStyle); cell.setCellValue(String.valueOf(rangeScore.get("date"))); left++; } row++; } left = 0; nowRow = sh.createRow(row); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell cell1 = nowRow.createCell(0); cell1.setCellValue(String.valueOf(nodeData.get("name"))); Cell cell2 = nowRow.createCell(1); cell2.setCellValue(String.valueOf(nodeData.get("max"))); Cell cell3 = nowRow.createCell(2); cell3.setCellValue(String.valueOf(nodeData.get("target"))); Cell cell4 = nowRow.createCell(3); cell4.setCellValue(String.valueOf(nodeData.get("min"))); Cell cell5 = nowRow.createCell(4); cell5.setCellValue(String.valueOf(nodeData.get("score"))); cell5.setCellStyle(cellStyle); List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData.get("dateRangeScores"); for (Map<String, Object> rangeScore : dateRangeScores) { bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("bgColor"))); fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("fontColor"))); cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell cell = nowRow.createCell(5 + left); cell.setCellStyle(cellHeadStyle); cell.setCellValue(String.valueOf(rangeScore.get("score"))); cell.setCellStyle(cellStyle); left++; } row++; } return row + 1; }
From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context, int row) throws Exception { String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barChartsData")); BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content); ByteArrayOutputStream barBos = new ByteArrayOutputStream(); ImageIO.write(barImage, "png", barBos); barBos.flush();/*w w w . j a v a2 s. co m*/ SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), row, 0); //int row = 28; row = row + 32; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("dateRangeLabel"); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadStyle.setFont(cellHeadFont); int titleCellSize = 9; Row headRow = sh.createRow(row); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("KPIs metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, titleCellSize - 1)); row = row + 1; int cellLeft = 5; int rowSpace = 17; for (Map<String, Object> data : chartDatas) { Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); String pngImageData = SimpleUtils.getPNGBase64Content((String) nodeData.get("outerHTML")); BufferedImage imageData = SimpleUtils.decodeToImage(pngImageData); ByteArrayOutputStream imgBos = new ByteArrayOutputStream(); ImageIO.write(imageData, "png", imgBos); imgBos.flush(); SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(true); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); int perTitleCellSize = 4; Row nowRow = sh.createRow(row); for (int i = 0; i < perTitleCellSize; i++) { Cell cell1 = nowRow.createCell(cellLeft); cell1.setCellStyle(cellStyle); cell1.setCellValue((String) nodeData.get("name")); } sh.addMergedRegion(new CellRangeAddress(row, row, cellLeft, cellLeft + perTitleCellSize - 1)); nowRow = sh.createRow(row + 1); Cell cell2 = nowRow.createCell(cellLeft); cell2.setCellValue("Maximum: " + String.valueOf(nodeData.get("max"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 4); Cell cell5 = nowRow.createCell(cellLeft); cell5.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }