Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:com.knsi.WriteDB.java

public static void writeTo(JTextField labels[], String pof) {
    try {/*from  ww w  .ja  v a2 s .c om*/
        File db = new File(labels[4].getText() + ".xlsx");
        System.out.println(db.getAbsolutePath());

        Date d = new Date();
        System.out.println(d.toString());
        FileInputStream dbStream = new FileInputStream(db);
        XSSFWorkbook details = new XSSFWorkbook(dbStream);
        XSSFSheet spreadsheet1 = details.getSheet("details");
        XSSFSheet spreadsheet2 = details.getSheet("Fees");
        XSSFSheet spreadsheet3 = details.getSheet("Performance");
        XSSFSheet spreadsheet4 = details.getSheet("Attendence");

        System.out.println(spreadsheet1.getLastRowNum() + "------------" + spreadsheet2.getLastRowNum());
        //Create row object
        XSSFRow row1, row2, row3, row4;
        //This data needs to be written (Object[])

        String obj1[] = new String[14];
        int j = 1;
        for (int i = 0; i <= 12; i++) {
            if (i == 4) {
                continue;
            }
            if (labels[i].getText().equalsIgnoreCase("")) {
                obj1[j] = "N/A";
            } else {
                obj1[j] = labels[i].getText();
            }
            j++;
        }
        //obj1[0]=Integer.toString(spreadsheet1.getLastRowNum()+1);
        obj1[0] = getRegId(obj1[0], Integer.toString(spreadsheet1.getLastRowNum() + 1), labels[4].getText(),
                pof);
        obj1[13] = d.toString();
        System.out.println("The details for the details sheet is:");
        for (int i = 0; i < 14; i++) {
            System.out.println(obj1[i]);
        }

        String obj2[] = new String[4];
        obj2[0] = obj1[0];
        obj2[1] = labels[13].getText();
        obj2[2] = labels[14].getText();
        obj2[3] = labels[13].getText();

        System.out.println("The details for the fees sheet is:");
        for (int i = 0; i < 4; i++) {
            System.out.println(obj2[i]);
        }

        String obj3[] = new String[2];
        obj3[0] = obj1[0];
        obj3[1] = obj1[1];

        int rowid1 = spreadsheet1.getLastRowNum() + 1;
        int rowid2 = spreadsheet2.getLastRowNum() + 1;
        int rowid3 = spreadsheet3.getLastRowNum() + 1;
        int rowid4 = spreadsheet4.getLastRowNum() + 1;

        row1 = spreadsheet1.createRow(rowid1);
        row2 = spreadsheet2.createRow(rowid2);
        row3 = spreadsheet3.createRow(rowid3);
        row4 = spreadsheet4.createRow(rowid4);

        int cellid = 0;
        for (String obj : obj1) {
            Cell cell = row1.createCell(cellid++);
            cell.setCellValue(obj);
        }

        cellid = 0;
        for (String obj : obj2) {
            Cell cell = row2.createCell(cellid++);
            cell.setCellValue(obj);
        }

        cellid = 0;
        for (String obj : obj3) {
            Cell cell = row3.createCell(cellid);
            cell.setCellValue(obj);
            Cell cell2 = row4.createCell(cellid);
            cell2.setCellValue(obj);
            cellid++;
        }

        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(db);
        details.write(out);
        out.close();
        System.out.println(db + " written successfully");
        s = db.getAbsolutePath();
    } catch (Exception ee) {
        s = ee.getMessage();
        System.out.println(s);

    }

}

From source file:com.liferay.events.global.mobile.portlet.PollsPortlet.java

License:Open Source License

@Override
public void serveResource(ResourceRequest request, ResourceResponse response)
        throws PortletException, IOException {
    // do search and return result
    String cmd = ParamUtil.getString(request, "cmd");
    long questionId = ParamUtil.getLong(request, "questionId");

    EventPollQuestion question;/*w w  w . j  a  va 2 s.c o  m*/
    List<EventPollAnswer> answers;
    try {
        question = EventPollQuestionLocalServiceUtil.getEventPollQuestion(questionId);
        answers = EventPollAnswerLocalServiceUtil.getAllAnswerObjs(questionId);
    } catch (SystemException e) {
        throw new PortletException("Cannot get answers for questionId " + questionId);
    } catch (PortalException e) {
        throw new PortletException("Cannot get question or answers for questionId " + questionId);
    }

    if (Validator.equals(cmd, "exportAnswersCSV")) {

        File f = FileUtil.createTempFile();

        CSVWriter writer = new CSVWriter(new FileWriter(f), ',');

        // find out all headers
        List<String> headers = new ArrayList<String>();
        headers.add("ID");
        headers.add("RAW ANSWER");

        Set<String> payloadHeaders = new HashSet<String>();

        for (EventPollAnswer answer : answers) {
            JSONObject payloadObj = null;

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }
            Iterator<String> keyIt = payloadObj.keys();

            while (keyIt.hasNext()) {
                String key = keyIt.next();
                payloadHeaders.add(key);
            }
        }

        headers.addAll(payloadHeaders);

        Map<String, Integer> headerCols = new HashMap<String, Integer>();
        for (int i = 0; i < headers.size(); i++) {
            headerCols.put(headers.get(i), i);
        }

        // now print them
        writer.writeNext(headers.toArray(new String[] {}));

        for (EventPollAnswer answer : answers) {
            List<String> vals = new ArrayList<String>();

            JSONObject payloadObj = null;

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }

            for (String headerCol : headers) {
                String val;
                if (headerCol.equals("ID")) {
                    val = String.valueOf(answer.getAnswerId());
                } else if (headerCol.equals("RAW ANSWER")) {
                    val = String.valueOf(answer.getAnswer());
                } else {
                    val = payloadObj.getString(headerCol);
                }

                if (Validator.isNull(val)) {
                    val = "";
                }
                vals.add(val);
            }

            writer.writeNext(vals.toArray(new String[] {}));
        }
        writer.flush();
        writer.close();
        PortletResponseUtil.sendFile(request, response,
                question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".csv", new FileInputStream(f));

    } else if (Validator.equals(cmd, "exportAnswersXLSX")) {

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Poll Answers");

        Row headerRow = sheet.createRow(0);
        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellValue("ID");

        headerCell = headerRow.createCell(1);
        headerCell.setCellValue("Raw Answer");

        HashMap<String, Integer> rowMap = new HashMap<String, Integer>();

        int currentRow = 1;
        int nextHeaderCol = 2;
        for (EventPollAnswer answer : answers) {
            Row row = sheet.createRow(currentRow);
            currentRow++;
            JSONObject payloadObj = null;
            long answerId = answer.getAnswerId();
            Cell idCell = row.createCell(0);
            idCell.setCellValue(String.valueOf(answerId));

            try {
                payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload());
                if (Validator.isNull(payloadObj)) {
                    continue;
                }
            } catch (JSONException e) {
                throw new PortletException("cannot read payload: " + answer.getPayload());
            }

            Cell answerCell = row.createCell(1);
            answerCell.setCellValue(String.valueOf(answer.getAnswer()));

            Iterator<String> keyIt = payloadObj.keys();

            while (keyIt.hasNext()) {
                String key = keyIt.next();
                Integer headerCol = rowMap.get(key);
                if (Validator.isNull(headerCol)) {
                    rowMap.put(key, nextHeaderCol);
                    Cell nextHeaderCell = headerRow.createCell(nextHeaderCol);
                    nextHeaderCell.setCellValue(key.toUpperCase());
                    headerCol = nextHeaderCol;
                    nextHeaderCol++;
                }
                Cell cell = row.createCell(headerCol);
                cell.setCellValue(payloadObj.getString(key));
            }
        }

        File f = FileUtil.createTempFile();
        FileOutputStream fos = new FileOutputStream(f);
        workbook.write(fos);
        fos.flush();
        fos.close();
        PortletResponseUtil.sendFile(request, response,
                question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".xlsx", new FileInputStream(f));

    }
}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileTransformed() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;//from  w  w  w  .  j  ava  2  s . c o m
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }

    XSSFWorkbook transformedWB = new XSSFWorkbook();
    transformedWB.createSheet();
    XSSFSheet transformedS = transformedWB.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        transformedS.createRow(i);
    }
    /*
    elkezdnk vgigmenni az alap sheeten
    ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre
    */
    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum()).setCellValue(sum);
        }
    }
    for (int index : matchingIndexes) {
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            row.getCell(index).setCellValue(3.14159);
        }
        /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        if(i == index){
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j);
                transformedS.getRow(j).createCell(transformedColumnCount).setCellValue(
                        row.getCell(i).getRawValue()
                );
            }
            transformedColumnCount++;
        }
        }*/
    }
    int columnsInTransformed = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            String cellValue = row.getCell(j).getRawValue();
            if (!cellValue.equals("3.14159")) {
                transformedS.getRow(i).createCell(columnsInTransformed);
                transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue);
                columnsInTransformed++;
            }
        }
        columnsInTransformed = 0;
    }

    row = transformedS.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            transformedWB.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }

}

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);//from  w  w w  .  j a  va  2  s  .  c  o  m
    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);
    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);//w ww  . j a  v  a  2  s . c  o m
    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  .  ja v  a2 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;//www . ja v  a 2  s  .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 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 w w  w.j  a  v  a 2  s  .  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 ww .  jav  a  2s  . c  o  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;
}

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  ww.j av a  2s.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;
}