Example usage for org.apache.poi.hssf.usermodel HSSFRow createCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. createCell

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFRow createCell.

Prototype

@Override
public HSSFCell createCell(int column)

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {/*  www .j a  v a 2s  . c o  m*/
        HSSFSheet worksheet = workbook.createSheet("localizations");

        HSSFCellStyle systemStyle = workbook.createCellStyle();
        systemStyle.setFillForegroundColor(HSSFColor.RED.index);
        systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Integer currentRow = 0;
        HSSFRow headLine = worksheet.createRow(++currentRow);
        headLine.createCell(0).setCellValue("File");
        headLine.createCell(1).setCellValue("Property");
        headLine.createCell(2).setCellValue("Source Value");
        headLine.createCell(3).setCellValue("Excel Value");

        HSSFRow row;

        for (LocalizationLog.Type type : LocalizationLog.Type.values()) {
            List<LocalizationLog> logs = getLogsByType(differences, type);
            if (logs.size() > 0) {
                row = worksheet.createRow(++currentRow);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(LogHelper.getMessageByType(type));
                cell.setCellStyle(LogHelper.getStyleByType(workbook, type));
                for (int i = 1; i < 4; i++) {
                    row.createCell(i);
                }

                CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);

                for (LocalizationLog log : logs) {
                    createNewLogRow(worksheet, ++currentRow, log);
                }

                row = worksheet.createRow(++currentRow);
                for (int i = 0; i < 4; i++) {
                    row.createCell(i);
                }

                region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);
            }
        }

        worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3));
        worksheet.createFreezePane(0, headLine.getRowNum() + 1);

        for (int i = 0; i < worksheet.getLastRowNum(); i++) {
            worksheet.autoSizeColumn(i);
        }
    } finally {
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

protected static HSSFRow createNewLogRow(HSSFSheet worksheet, Integer currentRow, LocalizationLog log) {
    HSSFRow row;
    row = worksheet.createRow(currentRow);

    HSSFCell cell = row.createCell(0);
    cell.setCellValue(log.getFile());// ww  w  .  ja  va 2s. c om

    cell = row.createCell(1);
    cell.setCellValue(log.getParameterName());

    cell = row.createCell(2);
    cell.setCellValue(log.getSourceValue());

    cell = row.createCell(3);
    cell.setCellValue(log.getExcelValue());
    return row;
}

From source file:com.haulmont.yarg.formatters.impl.xls.HSSFCellHelper.java

License:Apache License

public static HSSFCell getCellFromReference(HSSFSheet templateSheet, int colIndex, int rowIndex) {
    HSSFRow row = templateSheet.getRow(rowIndex);
    row = row == null ? templateSheet.createRow(rowIndex) : row;
    HSSFCell cell = row.getCell(colIndex);
    cell = cell == null ? row.createCell(colIndex) : cell;
    return cell;/*w w w. j a  va 2 s .c  om*/
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * copies template cell to result row into result column. Fills this cell with data from band
 *
 * @param templateCell - template cell/*  w  w w .  j  ava2s  .  c  o  m*/
 * @param resultRow    - result row
 * @param resultColumn - result column
 * @param band         - band
 */
private HSSFCell copyCellFromTemplate(HSSFCell templateCell, HSSFRow resultRow, int resultColumn,
        BandData band) {
    if (templateCell == null)
        return null;

    HSSFCell resultCell = resultRow.createCell(resultColumn);

    HSSFCellStyle templateStyle = templateCell.getCellStyle();
    HSSFCellStyle resultStyle = copyCellStyle(templateStyle);
    resultCell.setCellStyle(resultStyle);

    String templateCellValue = "";
    int cellType = templateCell.getCellType();

    if (cellType != HSSFCell.CELL_TYPE_FORMULA && cellType != HSSFCell.CELL_TYPE_NUMERIC) {
        HSSFRichTextString richStringCellValue = templateCell.getRichStringCellValue();
        templateCellValue = richStringCellValue != null ? richStringCellValue.getString() : "";

        templateCellValue = extractStyles(templateCell, resultCell, templateCellValue, band);
    }

    if (cellType == HSSFCell.CELL_TYPE_STRING && containsJustOneAlias(templateCellValue)) {
        updateValueCell(rootBand, band, templateCellValue, resultCell,
                drawingPatriarchsMap.get(resultCell.getSheet()));
    } else {
        String cellValue = inlineBandDataToCellString(templateCell, templateCellValue, band);
        setValueToCell(resultCell, cellValue, cellType);
    }

    return resultCell;
}

From source file:com.huateng.struts.query.action.T50202Action.java

License:Open Source License

public String download() {

    String brhBelow = InformationUtil.getBrhGroupString(brhId);
    System.out.println(brhBelow);

    String thisMonStart = date + "01";
    String thisMonEnd = date + "31";
    String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231";
    String lastMon = thisMonStart;//start?
    String thisYearStart = date.substring(0, 4) + "0101";
    String thisYearEnd = date.substring(0, 4) + "1231";

    try {//ww  w.jav a  2  s.  c  o m
        //?
        String[][] data = new String[40][9];
        //?data
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                if (j < 4) {
                    data[i][j] = "";
                } else {
                    data[i][j] = "0";
                }
            }
        }
        Iterator it = null;
        //???
        //?data
        data[0][0] = "1";
        data[0][1] = "?";
        data[0][3] = "";
        data[1][1] = "";
        data[1][2] = "?";
        data[1][3] = "";
        data[2][2] = "?";
        data[2][3] = "";
        String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear
                + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon
                + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart
                + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3,"
                + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd
                + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow
                + " group by CONN_TYPE";
        List list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                int index = 0;
                if ("J".equals(obj[0])) {
                    index = 1;
                } else {
                    index = 2;
                }
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
            }
            data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4]));
            data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5]));
            data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6]));
            data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7]));
            data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8]));
        }
        //???
        //?data
        data[3][0] = "2";
        data[3][1] = "?POS";
        data[3][3] = "?";
        data[4][1] = "";
        data[4][2] = "?";
        data[4][3] = "?";
        data[5][2] = "?";
        data[5][3] = "?";
        sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear
                + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon
                + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart
                + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3,"
                + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd
                + "') then 1 else 0 end) as c4 "
                + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO "
                + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE";
        list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                int index = 0;
                if ("J".equals(obj[0])) {
                    index = 4;
                } else {
                    index = 5;
                }
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
            }
            data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4]));
            data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5]));
            data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6]));
            data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7]));
            data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8]));
        }
        //???
        //?data
        data[6][0] = "3";
        data[6][1] = "?";
        sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp "
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date <= '" + lastYear
                + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) "
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date < '" + lastMon
                + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd
                + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd
                + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) "
                + "order by MCHNT_TP_GRP ";
        list = commQueryDAO.findBySQLQuery(sql);
        int index = 6;
        int len = 0;
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                if (!StringUtil.isNull(obj[0])
                        && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) {
                    int a = obj[0].toString().indexOf("(");
                    int b = obj[0].toString().indexOf("");
                    if (a > 0 && b > 0) {
                        data[index][2] = obj[0].toString().substring(0, a < b ? a : b);
                    } else {
                        data[index][2] = obj[0].toString().substring(0, a + b + 1);
                    }
                } else {
                    data[index][2] = obj[0].toString();
                }
                data[index][3] = "";
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
                index++;
                len++;
            }
        }
        data[index][0] = "4";
        data[index][1] = "?POS";
        sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp "
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear
                + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) "
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon
                + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart
                + "' and t.REC_CRT_TS <= '" + thisMonEnd
                + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart
                + "' and t.REC_CRT_TS <= '" + thisYearEnd
                + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) "
                + "order by MCHNT_TP_GRP ";
        list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                if (!StringUtil.isNull(obj[0])
                        && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) {
                    int a = obj[0].toString().indexOf("(");
                    int b = obj[0].toString().indexOf("");
                    if (a > 0 && b > 0) {
                        data[index][2] = obj[0].toString().substring(0, a < b ? a : b);
                    } else {
                        data[index][2] = obj[0].toString().substring(0, a + b + 1);
                    }
                } else {
                    data[index][2] = obj[0].toString();
                }
                data[index][3] = "?";
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
                index++;
            }
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        sheet.setDefaultColumnWidth(10);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 6000);
        //?
        HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook);
        HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook);
        HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook);
        HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook);
        HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook);

        short rowIndex = 0;// 
        // 
        row = sheet.createRow(rowIndex++);
        row.setHeight((short) 800);
        cell = row.createCell(0);
        cell.setCellStyle(styleTitle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("?");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ??
        rowIndex++;
        row = sheet.createRow(rowIndex++);
        cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue("");
        cell = row.createCell(1);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + "");
        cell = row.createCell(3);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue("");
        cell = row.createCell(4);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue(InformationUtil.getBrhName(brhId));

        String[] titles = { "??", "        ", "", "??", "", "", "",
                "", "" };
        row = sheet.createRow(rowIndex++);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(styleBold);
        }
        for (int i = 0; i < index; i++) {
            row = sheet.createRow(rowIndex++);
            for (int j = 0; j < data[i].length; j++) {
                cell = row.createCell(j);
                if (j < 4) {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(styleCenter);
                    cell.setCellValue(data[i][j]);
                } else {
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styleRight);
                    cell.setCellValue(Double.valueOf(data[i][j]));
                }
            }
        }

        int start = 1;
        sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8));

        sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1));

        sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1));

        String path = ExcelUtil.writeFiles(workbook,
                "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls");

        return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path);

    } catch (Exception e) {
        e.printStackTrace();
        return returnService("?,?", e);
    }
}

From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java

@SuppressWarnings("unchecked")
private boolean validateExcelCauseCodeContent(HSSFSheet sheet, HSSFCellStyle errorStyle, List<State> steps) {

    State state = State.findStateByLable(steps, STEP2_LABEL);
    if (state == null) {
        state = new State();
        state.setDescription("Data validation");
        state.setLabel(STEP2_LABEL);/* ww w  . ja va2s.  c  om*/
        state.setStatus(EStatus.IN_PROGRESS);
        steps.add(state);
    }

    boolean error = false;
    if (colIndexes == null) {
        int lastCellNO = sheet.getRow(ROW_ALERT_TYPE).getLastCellNum();
        HSSFCell cell = sheet.getRow(ROW_ALERT_TYPE).createCell(lastCellNO + 1);
        cell.setCellStyle(errorStyle);
        cell.setCellValue(new HSSFRichTextString(ERROR_UNKONW_TYPE));
        error = true;
    } else {
        Iterator<Row> rowIter = sheet.rowIterator();
        int rowCounter = -1;
        int totalRows = sheet.getLastRowNum();
        int colStart = colIndexes.getColCauseCode();
        int colEnd = colIndexes.getColInternalId();

        while (rowIter.hasNext()) {
            HSSFRow row = (HSSFRow) rowIter.next();
            rowCounter++;

            int progress = (int) ((float) rowCounter / totalRows * 100);
            state.setProgress(progress);

            if (rowCounter <= ROW_TABLE_HEAD) {
                continue;
            }

            StringBuffer errorMsg = new StringBuffer();
            for (int col = colStart; col <= colEnd; col++) {
                HSSFCell cell = row.getCell(col);

                if (col == colIndexes.getColInternalId()) {
                    if (!isCauseCodeExists(cell)) {

                        buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID",
                                ERROR_INTERNAL_ID_NOT_EXIST);
                    } else {
                        Long alertTypeId = getAlertTypeId(cell);
                        if (alertTypeId != null
                                && alertTypeId != this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) {
                            buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID",
                                    ERROR_ALERT_TYPE_NOT_MATCH);
                        }
                    }

                }

                if (col == colIndexes.getColCauseCode()) {
                    if (cell == null) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }
                    HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId());
                    if (!isCauseCodeExists(causeCodeIdCell)) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }

                    boolean pass = true;
                    // if no change continue;
                    String alertCauseNameInCell = null;
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        alertCauseNameInCell = cell.getStringCellValue();
                    } else {
                        pass = false;
                    }

                    if (alertCauseNameInCell == null || "".equals(alertCauseNameInCell.trim())) {
                        pass = false;
                    }

                    if (!pass) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }

                    if (alertCauseNameInCell.length() > 128) {
                        alertCauseNameInCell = alertCauseNameInCell.substring(0, 128);
                    }

                    String alertCauseNameInDb = getAlertCauseName(causeCodeIdCell);
                    // compare the cc name and cause code name under id. if
                    // not same check it's availability. if same ignore.
                    if (!strCompare(alertCauseNameInDb, alertCauseNameInCell)) {

                        List<AlertCause> acList = null;
                        try {
                            acList = getEntityManager().createNamedQuery("findActiveAlertCauseByNameAndTypeId")
                                    .setParameter("alertCauseName", alertCauseNameInCell.trim().toUpperCase())
                                    .setParameter("alertTypeId",
                                            this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode()))
                                    .getResultList();
                            if (acList.size() <= 0) {
                                buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                        ERROR_UNKONW_CAUSE_CODE);
                            }
                        } catch (Exception e) {
                            log.error(e.getMessage(), e);
                        }
                    }

                }

                if (col == colIndexes.getColTargetDate()) {
                    if (!isDateFormat(cell)) {//CC Target Date is an optional field
                        buildErrorMsg(errorMsg, colIndexes.getColTargetDate(), "CC target date",
                                ERROR_BAD_DATE_FORMAT);
                    }
                }

                if (col == colIndexes.getColOwner()) {
                    if (!isOwnerExistsInBluePage(cell)) {//CC Owner is an optional field
                        buildErrorMsg(errorMsg, colIndexes.getColOwner(), "CC owner", ERROR_UNKNOW_OWNER);
                    }
                }
            }

            if (errorMsg.length() > 0) {
                HSSFCell msgCell = row.createCell(colIndexes.getColMessage());
                msgCell.setCellStyle(errorStyle);
                msgCell.setCellValue(new HSSFRichTextString(errorMsg.toString()));
                error = true;
            }
        }
    }

    if (error) {
        state.setStatus(EStatus.FAILED);
    } else {
        if (state.getProgress() == 100 && state.getStatus().getPriority() < EStatus.FINISHED.getPriority()) {
            state.setStatus(EStatus.FINISHED);
        }
    }
    return error;
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

private String outputData(Object[] poaData, HSSFRow rowct) {

    StringBuffer lsbData = new StringBuffer();
    String lsData = null;//  w  ww.  j av a  2s .c  o  m

    for (int i = 0; poaData != null && i < poaData.length; i++) {
        lsData = poaData[i] == null ? "" : poaData[i].toString();
        rowct.createCell((int) i).setCellValue(lsData);
    }

    return lsbData.toString();
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

private void printHeader(String psReportName, Long plAccountNumber, String[] psaColumnHeader, HSSFSheet sheet) {
    HSSFRow rowhead0 = sheet.createRow((int) 0);
    SimpleDateFormat lsdfNow = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
    Object[] loaReportHeader = null;

    if (plAccountNumber != null) {
        loaReportHeader = new Object[] { psReportName, plAccountNumber,
                lsdfNow.format(Calendar.getInstance().getTime()) };
    } else {//from  ww w .java2 s .c om
        loaReportHeader = new Object[] { psReportName, lsdfNow.format(Calendar.getInstance().getTime()) };
    }

    rowhead0.createCell((int) 0).setCellValue("IBM Confidential");
    HSSFRow rowhead1 = sheet.createRow((int) 1);
    outputData(loaReportHeader, rowhead1);
    HSSFRow rowhead2 = sheet.createRow((int) 2);
    outputData(psaColumnHeader, rowhead2);
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

@SuppressWarnings("deprecation")
public String getErrorExcel(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;//from   w  ww  . j  a  va2 s  . c om
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;
    BillingTriggerValidation validateDto = null;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.errors") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getErrorLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("ERROR LOG");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            System.out.println(dtoObj.getErrorLogValue());
                            if (dtoObj.getErrorLogValue() == null) {
                                wc.setCellValue("No Errors");
                            } else {
                                wc.setCellValue(dtoObj.getErrorLogValue().toString());
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

public String getResultExcel(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;//from w ww.j a  va 2  s.  c  om
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.success") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getResultLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("RESULT LOG" + "_" + "ORDERNO");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            System.out.println(dtoObj.getResultLogValue());
                            if (dtoObj.getResultLogValue() == null) {
                                wc.setCellValue("SUCCESS");
                            } else {
                                if (dtoObj.getOrderNo() != null)
                                    wc.setCellValue(dtoObj.getResultLogValue().toString() + "_("
                                            + dtoObj.getOrderNo().toString() + ")");
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}