List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:com.mycompany.gayamtakehomeexam.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*//from w ww . ja va2 s . c om 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.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * ??? /*from www.j a v a 2 s.c o m*/ * * @param sheet * @param row * @param column * @return */ public void setMergedRegion(Sheet sheet) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { // ???? CellRangeAddress ca = sheet.getMergedRegion(i); int firstRow = ca.getFirstRow(); if (startReadPos - 1 > firstRow) {// ?????? continue; } int lastRow = ca.getLastRow(); int mergeRows = lastRow - firstRow;// ? int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); // ??????? for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) { // ?? sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn)); j = j + mergeRows;// ? } } }
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 ava 2s.c om*/ 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 w w.j a v a2 s.co 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 w w. ja v a 2s . c o m 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 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. ja v a 2 s. c om*/ 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; }
From source file:com.netsteadfast.greenstep.bsc.command.ObjectivesDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) 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();//from w w w .jav a2s. c o m SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0); int row = 28; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("year"); 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 = 14; Row headRow = sh.createRow(row); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("Objectives metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, titleCellSize - 1)); row = row + 1; int cellLeft = 10; 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("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java
License:Apache License
private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { String dateType = (String) context.get("dateType"); String year = (String) context.get("startYearDate"); String orgId = (String) context.get("orgId"); String departmentName = ""; String dateTypeName = "Year"; if ("1".equals(dateType)) { dateTypeName = "In the first half"; }/* w ww. j a v a2s.c o m*/ if ("2".equals(dateType)) { dateTypeName = "In the second half"; } OrganizationVO organization = new OrganizationVO(); organization.setOrgId(orgId); DefaultResult<OrganizationVO> result = this.organizationService.findByUK(organization); if (result.getValue() != null) { organization = result.getValue(); departmentName = organization.getName(); } Row headRow = sh.createRow(row); headRow.setHeight((short) 700); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); 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 = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue("Personal Balance SourceCard"); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(vision.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); Cell titleCell1 = headRow.createCell(0); titleCell1.setCellValue("Department"); titleCell1.setCellStyle(cellHeadStyle); Cell titleCell2 = headRow.createCell(1); titleCell2.setCellValue(departmentName); titleCell2.setCellStyle(cellHeadStyle); Cell titleCell3 = headRow.createCell(2); titleCell3.setCellValue(departmentName); titleCell3.setCellStyle(cellHeadStyle); Cell titleCell4 = headRow.createCell(3); titleCell4.setCellValue(departmentName); titleCell4.setCellStyle(cellHeadStyle); Cell titleCell5 = headRow.createCell(4); titleCell5.setCellValue(departmentName); titleCell5.setCellStyle(cellHeadStyle); Cell titleCell6 = headRow.createCell(5); titleCell6.setCellValue(year + " " + dateTypeName); titleCell6.setCellStyle(cellHeadStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, cols - 2)); row++; headRow = sh.createRow(row); titleCell1 = headRow.createCell(0); titleCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle()); titleCell1.setCellStyle(cellHeadStyle); titleCell2 = headRow.createCell(1); titleCell2.setCellValue(BscReportPropertyUtils.getObjectiveTitle()); titleCell2.setCellStyle(cellHeadStyle); titleCell3 = headRow.createCell(2); titleCell3.setCellValue(BscReportPropertyUtils.getKpiTitle()); titleCell3.setCellStyle(cellHeadStyle); titleCell4 = headRow.createCell(3); titleCell4.setCellValue("Weight"); titleCell4.setCellStyle(cellHeadStyle); titleCell5 = headRow.createCell(4); titleCell5.setCellValue("Maximum\nTarget\nMinimum"); titleCell5.setCellStyle(cellHeadStyle); titleCell6 = headRow.createCell(5); titleCell6.setCellValue("Score"); titleCell6.setCellStyle(cellHeadStyle); row = row + 1; return row; }
From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java
License:Apache License
private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false);/*from www . j av a2 s . com*/ cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"))); 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); 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++); Cell cell1 = contentRow.createCell(0); cell1.setCellValue(perspective.getName()); cell1.setCellStyle(cellStyle); Cell titleCell2 = contentRow.createCell(1); titleCell2.setCellValue(objective.getName()); titleCell2.setCellStyle(cellStyle); Cell titleCell3 = contentRow.createCell(2); titleCell3.setCellValue(kpi.getName()); titleCell3.setCellStyle(cellStyle); Cell titleCell4 = contentRow.createCell(3); titleCell4.setCellValue(kpi.getWeight() + "%"); titleCell4.setCellStyle(cellStyle); Cell titleCell5 = contentRow.createCell(4); titleCell5.setCellValue("max: " + kpi.getMax() + "\n" + "target: " + kpi.getTarget() + "\n" + "min: " + kpi.getMin() + "\n" + "unit: " + kpi.getUnit()); titleCell5.setCellStyle(cellStyle); DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(0); XSSFCellStyle cellStyle2 = wb.createCellStyle(); cellStyle2.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont2 = wb.createFont(); cellFont2.setBold(false); cellFont2.setColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle2.setFont(cellFont2); cellStyle2.setWrapText(true); cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle2.setBorderBottom(BorderStyle.THIN); cellStyle2.setBorderTop(BorderStyle.THIN); cellStyle2.setBorderRight(BorderStyle.THIN); cellStyle2.setBorderLeft(BorderStyle.THIN); Cell titleCell6 = contentRow.createCell(5); titleCell6.setCellValue(BscReportSupportUtils.parse2(dateRangeScore.getScore())); titleCell6.setCellStyle(cellStyle2); } } } for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); // 2016-12-13 old work with POI 3.12 //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow()-1, 0, 0)); // 2016-12-13 new work with POI 3.15 int mrRow1 = mrRow + perspective.getRow() - 1; if (mrRow1 > mrRow) { sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow1, 0, 0)); } for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); // 2016-12-13 old work with POI 3.12 //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow()-1, 1, 1)); // 2016-12-13 new work with POI 3.15 int mrRow2 = mrRow + objective.getRow() - 1; if (mrRow2 > mrRow) { sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow2, 1, 1)); } mrRow += objective.getKpis().size(); } } return row++; }
From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java
License:Apache License
private int createPdca(XSSFWorkbook wb, XSSFSheet sh, int row, Context context) throws Exception { PdcaVO pdca = (PdcaVO) this.getResult(context); Row headRow = sh.createRow(row);/* ww w . j a va 2 s . c o m*/ headRow.setHeight((short) 700); // -------------------------------------------------------------------------------------- XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#d8d8d8")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); 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 = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(pdca.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); // -------------------------------------------------------------------------------------- XSSFColor bgLabelColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFCellStyle cellLabelStyle = wb.createCellStyle(); cellLabelStyle.setFillForegroundColor(bgLabelColor); cellLabelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellLabelFont = wb.createFont(); cellLabelFont.setBold(true); cellLabelFont.setColor(fnColor); cellLabelStyle.setFont(cellLabelFont); cellLabelStyle.setBorderBottom(BorderStyle.THIN); cellLabelStyle.setBorderTop(BorderStyle.THIN); cellLabelStyle.setBorderRight(BorderStyle.THIN); cellLabelStyle.setBorderLeft(BorderStyle.THIN); cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellLabelStyle.setAlignment(HorizontalAlignment.LEFT); cellLabelStyle.setWrapText(true); // -------------------------------------------------------------------------------------- XSSFColor bgNormalColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")); XSSFCellStyle cellNormalStyle = wb.createCellStyle(); cellNormalStyle.setFillForegroundColor(bgNormalColor); cellNormalStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellNormalFont = wb.createFont(); cellNormalFont.setBold(false); cellNormalFont.setColor(fnColor); cellNormalStyle.setFont(cellNormalFont); cellNormalStyle.setBorderBottom(BorderStyle.THIN); cellNormalStyle.setBorderTop(BorderStyle.THIN); cellNormalStyle.setBorderRight(BorderStyle.THIN); cellNormalStyle.setBorderLeft(BorderStyle.THIN); cellNormalStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellNormalStyle.setAlignment(HorizontalAlignment.LEFT); cellNormalStyle.setWrapText(true); // -------------------------------------------------------------------------------------- row++; Row labelRow = sh.createRow(row); Cell labelCell_0_1 = labelRow.createCell(0); labelCell_0_1.setCellValue("Responsibility"); labelCell_0_1.setCellStyle(cellLabelStyle); Cell labelCell_0_2 = labelRow.createCell(1); labelCell_0_2.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_2.setCellStyle(cellNormalStyle); Cell labelCell_0_3 = labelRow.createCell(2); labelCell_0_3.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_0_4 = labelRow.createCell(3); labelCell_0_4.setCellValue("Date range"); labelCell_0_4.setCellStyle(cellLabelStyle); Cell labelCell_0_5 = labelRow.createCell(4); labelCell_0_5.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_5.setCellStyle(cellNormalStyle); Cell labelCell_0_6 = labelRow.createCell(5); labelCell_0_6.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_1_1 = labelRow.createCell(0); labelCell_1_1.setCellValue("Confirm"); labelCell_1_1.setCellStyle(cellLabelStyle); Cell labelCell_1_2 = labelRow.createCell(1); labelCell_1_2.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_2.setCellStyle(cellNormalStyle); Cell labelCell_1_3 = labelRow.createCell(2); labelCell_1_3.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_1_4 = labelRow.createCell(3); labelCell_1_4.setCellValue("Confirm date"); labelCell_1_4.setCellStyle(cellLabelStyle); Cell labelCell_1_5 = labelRow.createCell(4); labelCell_1_5.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_5.setCellStyle(cellNormalStyle); Cell labelCell_1_6 = labelRow.createCell(5); labelCell_1_6.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_2_1 = labelRow.createCell(0); labelCell_2_1.setCellValue("Organization\nDepartment"); labelCell_2_1.setCellStyle(cellLabelStyle); Cell labelCell_2_2 = labelRow.createCell(1); labelCell_2_2.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_2.setCellStyle(cellNormalStyle); Cell labelCell_2_3 = labelRow.createCell(2); labelCell_2_3.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_3.setCellStyle(cellNormalStyle); Cell labelCell_2_4 = labelRow.createCell(3); labelCell_2_4.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_4.setCellStyle(cellNormalStyle); Cell labelCell_2_5 = labelRow.createCell(4); labelCell_2_5.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_5.setCellStyle(cellNormalStyle); Cell labelCell_2_6 = labelRow.createCell(5); labelCell_2_6.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_3_1 = labelRow.createCell(0); labelCell_3_1.setCellValue("KPIs"); labelCell_3_1.setCellStyle(cellLabelStyle); Cell labelCell_3_2 = labelRow.createCell(1); labelCell_3_2.setCellValue(pdca.getKpisAppendNames()); labelCell_3_2.setCellStyle(cellNormalStyle); Cell labelCell_3_3 = labelRow.createCell(2); labelCell_3_3.setCellValue(pdca.getKpisAppendNames()); labelCell_3_3.setCellStyle(cellNormalStyle); Cell labelCell_3_4 = labelRow.createCell(3); labelCell_3_4.setCellValue(pdca.getKpisAppendNames()); labelCell_3_4.setCellStyle(cellNormalStyle); Cell labelCell_3_5 = labelRow.createCell(4); labelCell_3_5.setCellValue(pdca.getKpisAppendNames()); labelCell_3_5.setCellStyle(cellNormalStyle); Cell labelCell_3_6 = labelRow.createCell(5); labelCell_3_6.setCellValue(pdca.getKpisAppendNames()); labelCell_3_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_4_1 = labelRow.createCell(0); labelCell_4_1.setCellValue("Parent PDCA"); labelCell_4_1.setCellStyle(cellLabelStyle); Cell labelCell_4_2 = labelRow.createCell(1); labelCell_4_2.setCellValue(pdca.getParentName()); labelCell_4_2.setCellStyle(cellNormalStyle); Cell labelCell_4_3 = labelRow.createCell(2); labelCell_4_3.setCellValue(pdca.getParentName()); labelCell_4_3.setCellStyle(cellNormalStyle); Cell labelCell_4_4 = labelRow.createCell(3); labelCell_4_4.setCellValue(pdca.getParentName()); labelCell_4_4.setCellStyle(cellNormalStyle); Cell labelCell_4_5 = labelRow.createCell(4); labelCell_4_5.setCellValue(pdca.getParentName()); labelCell_4_5.setCellStyle(cellNormalStyle); Cell labelCell_4_6 = labelRow.createCell(5); labelCell_4_6.setCellValue(pdca.getParentName()); labelCell_4_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_6_1 = labelRow.createCell(0); labelCell_6_1.setCellValue("TYPE"); labelCell_6_1.setCellStyle(cellLabelStyle); Cell labelCell_6_2 = labelRow.createCell(1); labelCell_6_2.setCellValue("Title"); labelCell_6_2.setCellStyle(cellLabelStyle); Cell labelCell_6_3 = labelRow.createCell(2); labelCell_6_3.setCellValue("Responsibility"); labelCell_6_3.setCellStyle(cellLabelStyle); Cell labelCell_6_4 = labelRow.createCell(3); labelCell_6_4.setCellValue("Date range"); labelCell_6_4.setCellStyle(cellLabelStyle); Cell labelCell_6_5 = labelRow.createCell(4); labelCell_6_5.setCellValue("Audit"); labelCell_6_5.setCellStyle(cellLabelStyle); Cell labelCell_6_6 = labelRow.createCell(5); labelCell_6_6.setCellValue("Audit date"); labelCell_6_6.setCellStyle(cellLabelStyle); // -------------------------------------------------------------------------------------- row++; int nRow = row; row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemPlan(), pdca.getAuditPlan()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemDo(), pdca.getAuditDo()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemCheck(), pdca.getAuditCheck()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemAction(), pdca.getAuditAction()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemPlan()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemDo()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemCheck()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemAction()); return row; }