Example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. setCellValue

Introduction

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

Prototype

@SuppressWarnings("fallthrough")
public void setCellValue(boolean value)

Source Link

Document

set a boolean value for the cell

Usage

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

public void exportTable(Table<Entity> table, List<Table.Column> columns, Boolean exportExpanded,
        ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) {

    if (display == null) {
        throw new IllegalArgumentException("ExportDisplay is null");
    }/*from w  ww . ja v  a  2  s  .co  m*/

    createWorkbookWithSheet();
    createFonts();
    createFormats();

    int r = 0;
    if (filterDescription != null) {
        for (r = 0; r < filterDescription.size(); r++) {
            String line = filterDescription.get(r);
            HSSFRow row = sheet.createRow(r);
            if (r == 0) {
                HSSFRichTextString richTextFilterName = new HSSFRichTextString(line);
                richTextFilterName.applyFont(boldFont);
                row.createCell(0).setCellValue(richTextFilterName);
            } else {
                row.createCell(0).setCellValue(line);
            }
        }
        r++;
    }
    HSSFRow row = sheet.createRow(r);
    createAutoColumnSizers(columns.size());

    float maxHeight = sheet.getDefaultRowHeightInPoints();

    CellStyle headerCellStyle = wb.createCellStyle();
    headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    for (Table.Column column : columns) {
        String caption = column.getCaption();

        int countOfReturnSymbols = StringUtils.countMatches(caption, "\n");
        if (countOfReturnSymbols > 0) {
            maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints());
            headerCellStyle.setWrapText(true);
        }
    }
    row.setHeightInPoints(maxHeight);

    for (int c = 0; c < columns.size(); c++) {
        Table.Column column = columns.get(c);
        String caption = column.getCaption();

        HSSFCell cell = row.createCell(c);
        HSSFRichTextString richTextString = new HSSFRichTextString(caption);
        richTextString.applyFont(boldFont);
        cell.setCellValue(richTextString);

        ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer();
        sizer.notifyCellValue(caption, boldFont);
        sizers[c] = sizer;

        cell.setCellStyle(headerCellStyle);
    }

    CollectionDatasource datasource = table.getDatasource();
    if (exportMode == ExportMode.SELECTED_ROWS && table.getSelected().size() > 0) {
        Set<Entity> selected = table.getSelected();
        List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains)
                .collect(Collectors.toList());
        for (Entity item : ordered) {
            createRow(table, columns, 0, ++r, item.getId());
        }
    } else {
        if (table instanceof TreeTable) {
            TreeTable treeTable = (TreeTable) table;
            HierarchicalDatasource ds = treeTable.getDatasource();
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object itemId : ds.getRootItemIds()) {
                r = createHierarhicalRow(treeTable, columns, exportExpanded, r, itemId);
            }
        } else if (table instanceof GroupTable && datasource instanceof GroupDatasource
                && ((GroupDatasource) datasource).hasGroups()) {
            GroupDatasource ds = (GroupDatasource) datasource;
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object item : ds.rootGroups()) {
                r = createGroupRow((GroupTable) table, columns, ++r, (GroupInfo) item, 0);
            }
        } else {
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object itemId : datasource.getItemIds()) {
                createRow(table, columns, 0, ++r, itemId);
            }
        }
    }

    for (int c = 0; c < columns.size(); c++) {
        sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC);
    }

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
    } catch (IOException e) {
        throw new RuntimeException("Unable to write document", e);
    }
    if (fileName == null) {
        fileName = messages.getTools().getEntityCaption(datasource.getMetaClass());
    }

    display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS);
}

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

public void exportDataGrid(DataGrid<Entity> dataGrid, List<DataGrid.Column> columns, ExportDisplay display,
        List<String> filterDescription, String fileName, ExportMode exportMode) {
    if (display == null) {
        throw new IllegalArgumentException("ExportDisplay is null");
    }/*from  w  w  w  . j a  va 2  s  .co  m*/

    createWorkbookWithSheet();
    createFonts();
    createFormats();

    int r = 0;
    if (filterDescription != null) {
        for (r = 0; r < filterDescription.size(); r++) {
            String line = filterDescription.get(r);
            HSSFRow row = sheet.createRow(r);
            if (r == 0) {
                HSSFRichTextString richTextFilterName = new HSSFRichTextString(line);
                richTextFilterName.applyFont(boldFont);
                row.createCell(0).setCellValue(richTextFilterName);
            } else {
                row.createCell(0).setCellValue(line);
            }
        }
        r++;
    }
    HSSFRow row = sheet.createRow(r);
    createAutoColumnSizers(columns.size());

    float maxHeight = sheet.getDefaultRowHeightInPoints();

    CellStyle headerCellStyle = wb.createCellStyle();
    headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    for (DataGrid.Column column : columns) {
        String caption = column.getCaption();

        int countOfReturnSymbols = StringUtils.countMatches(caption, "\n");
        if (countOfReturnSymbols > 0) {
            maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints());
            headerCellStyle.setWrapText(true);
        }
    }
    row.setHeightInPoints(maxHeight);

    for (int c = 0; c < columns.size(); c++) {
        DataGrid.Column column = columns.get(c);
        String caption = column.getCaption();

        HSSFCell cell = row.createCell(c);
        HSSFRichTextString richTextString = new HSSFRichTextString(caption);
        richTextString.applyFont(boldFont);
        cell.setCellValue(richTextString);

        ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer();
        sizer.notifyCellValue(caption, boldFont);
        sizers[c] = sizer;

        cell.setCellStyle(headerCellStyle);
    }

    CollectionDatasource datasource = dataGrid.getDatasource();
    if (exportMode == ExportMode.SELECTED_ROWS && dataGrid.getSelected().size() > 0) {
        Set<Entity> selected = dataGrid.getSelected();
        List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains)
                .collect(Collectors.toList());
        for (Entity item : ordered) {
            createDataGridRow(dataGrid, columns, 0, ++r, item.getId());
        }
    } else {
        for (Object itemId : datasource.getItemIds()) {
            createDataGridRow(dataGrid, columns, 0, ++r, itemId);
        }
    }

    for (int c = 0; c < columns.size(); c++) {
        sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC);
    }

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
    } catch (IOException e) {
        throw new RuntimeException("Unable to write document", e);
    }
    if (fileName == null) {
        fileName = messages.getTools().getEntityCaption(datasource.getMetaClass());
    }

    display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS);
}

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

protected void formatValueCell(HSSFCell cell, @Nullable Object cellValue,
        @Nullable MetaPropertyPath metaPropertyPath, int sizersIndex, int notificationRequired, int level,
        @Nullable Integer groupChildCount) {
    if (cellValue == null) {
        return;//from   ww w .j  a  v a  2 s  .  co m
    }

    String childCountValue = "";
    if (groupChildCount != null) {
        childCountValue = " (" + groupChildCount + ")";
    }

    if (cellValue instanceof IdProxy) {
        cellValue = ((IdProxy) cellValue).get();
    }

    if (cellValue instanceof Number) {
        Number n = (Number) cellValue;
        final Datatype datatype = Datatypes.getNN(n.getClass());
        String str;
        if (sizersIndex == 0) {
            str = createSpaceString(level) + datatype.format(n);
            cell.setCellValue(str);
        } else {
            try {
                str = datatype.format(n);
                Number result = (Number) datatype.parse(str);
                if (result != null) {
                    if (n instanceof Integer || n instanceof Long || n instanceof Byte || n instanceof Short) {
                        cell.setCellValue(result.longValue());
                        cell.setCellStyle(integerFormatCellStyle);
                    } else {
                        cell.setCellValue(result.doubleValue());
                        cell.setCellStyle(doubleFormatCellStyle);
                    }
                }
            } catch (ParseException e) {
                throw new RuntimeException("Unable to parse numeric value", e);
            }
            cell.setCellType(CellType.NUMERIC);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Date) {
        Class javaClass = null;
        boolean supportTimezones = false;
        TimeZone timeZone = userSessionSource.getUserSession().getTimeZone();
        if (metaPropertyPath != null) {
            MetaProperty metaProperty = metaPropertyPath.getMetaProperty();
            if (metaProperty.getRange().isDatatype()) {
                javaClass = metaProperty.getRange().asDatatype().getJavaClass();
            }
            Boolean ignoreUserTimeZone = metadataTools.getMetaAnnotationValue(metaProperty,
                    IgnoreUserTimeZone.class);
            supportTimezones = timeZone != null && Objects.equals(Date.class, javaClass)
                    && !Boolean.TRUE.equals(ignoreUserTimeZone);
        }
        Date date = (Date) cellValue;
        if (supportTimezones) {
            TimeZone currentTimeZone = LocaleUtil.getUserTimeZone();
            try {
                LocaleUtil.setUserTimeZone(timeZone);
                cell.setCellValue(date);
            } finally {
                if (Objects.equals(currentTimeZone, TimeZone.getDefault())) {
                    LocaleUtil.resetUserTimeZone();
                } else {
                    LocaleUtil.setUserTimeZone(currentTimeZone);
                }
            }
        } else {
            cell.setCellValue(date);
        }

        if (Objects.equals(java.sql.Time.class, javaClass)) {
            cell.setCellStyle(timeFormatCellStyle);
        } else if (Objects.equals(java.sql.Date.class, javaClass)) {
            cell.setCellStyle(dateFormatCellStyle);
        } else {
            cell.setCellStyle(dateTimeFormatCellStyle);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            String str = Datatypes.getNN(Date.class).format(date);
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Boolean) {
        String str = "";
        if (sizersIndex == 0) {
            str += createSpaceString(level);
        }
        str += ((Boolean) cellValue) ? trueStr : falseStr;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof EnumClass) {
        String nameKey = cellValue.getClass().getSimpleName() + "." + cellValue.toString();
        final String message = sizersIndex == 0
                ? createSpaceString(level) + messages.getMessage(cellValue.getClass(), nameKey)
                : messages.getMessage(cellValue.getClass(), nameKey);

        cell.setCellValue(message + childCountValue);
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(message, stdFont);
        }
    } else if (cellValue instanceof Entity) {
        Entity entityVal = (Entity) cellValue;
        String instanceName = entityVal.getInstanceName();
        String str = sizersIndex == 0 ? createSpaceString(level) + instanceName : instanceName;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Collection) {
        String str = "";
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else {
        String strValue = cellValue == null ? "" : cellValue.toString();
        String str = sizersIndex == 0 ? createSpaceString(level) + strValue : strValue;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    }
}

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

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

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

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }//  w  w w  .j a v  a2 s  .co  m
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

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 {/*  w w  w .  j a  v  a 2 s  .  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;/*from   w  w w .  j a  v a2 s . com*/
    row = worksheet.createRow(currentRow);

    HSSFCell cell = row.createCell(0);
    cell.setCellValue(log.getFile());

    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.XLSFormatter.java

License:Apache License

/**
 * Copies template cell to result cell and fills it with bandData data
 *
 * @param bandData          - bandData/*www  .j  a  va2 s.  com*/
 * @param templateCellValue - template cell value
 * @param resultCell        - result cell
 */
protected void updateValueCell(BandData rootBand, BandData bandData, String templateCellValue,
        HSSFCell resultCell, HSSFPatriarch patriarch) {
    String parameterName = templateCellValue;
    parameterName = unwrapParameterName(parameterName);
    String fullParameterName = bandData.getName() + "." + parameterName;

    if (StringUtils.isEmpty(parameterName))
        return;

    if (!bandData.getData().containsKey(parameterName)) {
        resultCell.setCellValue((String) null);
        return;
    }

    Object value = bandData.getData().get(parameterName);

    if (value == null) {
        resultCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        return;
    }

    String formatString = getFormatString(parameterName, fullParameterName);
    InlinerAndMatcher inlinerAndMatcher = getContentInlinerForFormat(formatString);
    if (inlinerAndMatcher != null) {
        inlinerAndMatcher.contentInliner.inlineToXls(patriarch, resultCell, value, inlinerAndMatcher.matcher);
        return;
    }

    if (formatString != null) {
        resultCell.setCellValue(new HSSFRichTextString(formatValue(value, parameterName, fullParameterName)));
    } else if (value instanceof Number) {
        resultCell.setCellValue(((Number) value).doubleValue());
    } else if (value instanceof Boolean) {
        resultCell.setCellValue((Boolean) value);
    } else if (value instanceof Date) {
        resultCell.setCellValue((Date) value);
    } else {
        resultCell.setCellValue(new HSSFRichTextString(formatValue(value, parameterName, fullParameterName)));
    }
}

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

License:Apache License

protected void setValueToCell(HSSFCell resultCell, String cellValue, int cellType) {
    if (StringUtils.isNotEmpty(cellValue)) {
        switch (cellType) {
        case HSSFCell.CELL_TYPE_FORMULA:
            resultCell.setCellFormula(cellValue);
            break;
        case HSSFCell.CELL_TYPE_STRING:
            resultCell.setCellValue(new HSSFRichTextString(cellValue));
            break;
        default://from w w w. j  ava  2s . c o  m
            resultCell.setCellValue(cellValue);
            break;
        }

    } else {
        resultCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
    }
}

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 {//from   w  ww.  ja v a 2s.c om
        //?
        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);//from   w w  w  . j  a v a  2 s.co m
        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;
}