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.aimluck.eip.modules.screens.ALXlsScreen.java

License:Open Source License

/**
 * /*from ww w . j a v a2s .com*/
 * @param wb
 * @param sheet_name
 * @param headers
 *          ?
 * @param cell_enc_types
 *          ?
 * @return
 */
protected HSSFSheet createHSSFSheet(HSSFWorkbook wb, String sheet_name, String[] headers,
        short[] cell_enc_types) {

    HSSFSheet sheet = wb.createSheet(sheet_name);
    wb.setSheetName(0, sheet_name, HSSFWorkbook.ENCODING_UTF_16);

    HSSFRow row1 = sheet.createRow(0);
    int len = headers.length;
    for (int i = 0; i < len; i++) {
        HSSFCell cell_row1 = row1.createCell((short) i);
        cell_row1.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell_row1.setCellValue(headers[i]);
    }

    return sheet;
}

From source file:com.aimluck.eip.modules.screens.ALXlsScreen.java

License:Open Source License

/**
 * xls??//from w  ww  . j a  v a2  s.  c o m
 * 
 * @param newrow
 * @param cell_enc_types
 * @param rows
 */
protected void addRow(HSSFRow newrow, short[] cell_enc_types, String[] rows) {
    int len = rows.length;
    for (int j = 0; j < len; j++) {
        HSSFCell cell_newrow = newrow.createCell((short) j);
        if (cell_enc_types[j] == HSSFCell.CELL_TYPE_NUMERIC) {
            try {
                cell_newrow.setCellValue(Double.parseDouble(rows[j]));
            } catch (Exception e) {
                cell_newrow.setCellValue("");
            }
        } else if (cell_enc_types[j] == HSSFCell.ENCODING_UTF_16) {
            cell_newrow.setEncoding(cell_enc_types[j]);
            cell_newrow.setCellValue(rows[j]);
        } else {
            cell_newrow.setCellValue(rows[j]);
        }
    }
}

From source file:com.aimluck.eip.modules.screens.ALXlsScreen.java

License:Open Source License

protected void addFooter(HSSFRow newrow, short[] cell_enc_types, String[] rows) {
    int len = rows.length;
    for (int j = 0; j < len; j++) {
        HSSFCell cell_newrow = newrow.createCell((short) j);
        if (cell_enc_types[j] == HSSFCell.CELL_TYPE_NUMERIC) {
            cell_enc_types[j] = HSSFCell.CELL_TYPE_FORMULA;
            cell_newrow.setCellValue(rows[j]);
        } else if (cell_enc_types[j] == HSSFCell.ENCODING_UTF_16) {
            cell_newrow.setEncoding(cell_enc_types[j]);
            cell_newrow.setCellValue(rows[j]);
        } else {/*from  ww  w  .jav a2 s .c  o  m*/
            cell_newrow.setCellValue(rows[j]);
        }
    }
}

From source file:com.alibaba.differ.biz.TableExporter.java

License:Open Source License

public void export() throws IOException {
    fc.setFileFilter(new ExcelFileFilter());
    fc.setFileHidingEnabled(true);//w  ww.  j av a2  s.  c  om
    fc.setAcceptAllFileFilterUsed(false);
    int returnValue = fc.showSaveDialog(null);
    if (returnValue != JFileChooser.APPROVE_OPTION) {
        return;
    }
    File file = fc.getSelectedFile();
    if (file.exists()) {
        JOptionPane.showMessageDialog(null, "");
        return;
    }
    FileOutputStream fos = new FileOutputStream(file + ".xls");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet hs = wb.createSheet();
    TableModel tm = table.getModel();
    int row = tm.getRowCount();
    int cloumn = tm.getColumnCount();
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 11);
    style.setFont(font);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style1.setFillForegroundColor(HSSFColor.ORANGE.index);
    style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font1 = wb.createFont();
    font1.setFontHeightInPoints((short) 15);
    font1.setBoldweight((short) 700);
    style1.setFont(font);

    for (int i = 0; i < row + 1; i++) {
        HSSFRow hr = hs.createRow(i);
        for (int j = 0; j < cloumn; j++) {
            if (i == 0) {
                String value = tm.getColumnName(j);
                hs.setColumnWidth(j, UIConfig.EXCEL_COLUMN_WIDTH);
                HSSFRichTextString srts = new HSSFRichTextString(value);
                HSSFCell hc = hr.createCell((short) j);
                hc.setCellStyle(style1);
                hc.setCellValue(srts);
            } else {
                if (tm.getValueAt(i - 1, j) != null) {
                    String value = tm.getValueAt(i - 1, j).toString();
                    HSSFRichTextString srts = new HSSFRichTextString(value);
                    HSSFCell hc = hr.createCell((short) j);
                    hc.setCellStyle(style);

                    if (value.equals("") || value == null) {
                        hc.setCellValue(new HSSFRichTextString(""));
                    } else {
                        hc.setCellValue(srts);
                    }
                }
            }
        }
    }
    wb.write(fos);
    fos.close();
    JOptionPane.showMessageDialog(null, "Excel");
}

From source file:com.anphat.list.controller.ListStaffController.java

public File exportFile(List<StaffDTO> lstStaffInfo) {
    try {/*from  ww  w  . java  2 s. c  o  m*/
        FileOutputStream fileOut = new FileOutputStream(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("danhsachnhanvien");
        HSSFCellStyle cellStyle;
        HSSFCellStyle rowStyle;

        HSSFCellStyle cellStyleLeft = null;
        HSSFCellStyle cellStyleRight = null;
        //style left
        cellStyleLeft = workbook.createCellStyle();
        cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //phai
        cellStyleRight = workbook.createCellStyle();
        cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cellStyleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);

        //                //header bang
        HSSFRow row5 = worksheet.createRow(0);

        HSSFCell cellA1 = row5.createCell(0);
        cellA1.setCellValue(BundleUtils.getString("STT"));
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        //rowstyle
        rowStyle = workbook.createCellStyle();
        rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        rowStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        rowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        rowStyle.setWrapText(true);

        cellA1.setCellStyle(cellStyle);

        HSSFCell cellB1 = row5.createCell(1);
        cellB1.setCellValue(BundleUtils.getString("cms.StaffInfo.code"));
        cellB1.setCellStyle(cellStyle);

        HSSFCell cellC1 = row5.createCell(2);
        cellC1.setCellValue(BundleUtils.getString("cms.StaffInfo.name"));
        cellC1.setCellStyle(cellStyle);
        HSSFCell cellD1 = row5.createCell(3);
        cellD1.setCellValue(BundleUtils.getString("cms.StaffInfo.email"));
        cellD1.setCellStyle(cellStyle);
        HSSFCell cellE1 = row5.createCell(4);
        cellE1.setCellValue(BundleUtils.getString("cms.StaffInfo.birthdate"));
        cellE1.setCellStyle(cellStyle);
        HSSFCell cellF1 = row5.createCell(5);
        cellF1.setCellValue(BundleUtils.getString("cms.StaffInfo.phoneNumber"));
        cellF1.setCellStyle(cellStyle);
        HSSFCell cellG1 = row5.createCell(6);
        cellG1.setCellValue(BundleUtils.getString("cms.StaffInfo.deptName"));
        cellG1.setCellStyle(cellStyle);
        HSSFCell cellH1 = row5.createCell(7);
        cellH1.setCellValue(BundleUtils.getString("cms.StaffInfo.staffType"));
        cellH1.setCellStyle(cellStyle);
        HSSFCell cellI1 = row5.createCell(8);
        cellI1.setCellValue(BundleUtils.getString("cms.StaffInfo.status"));
        cellI1.setCellStyle(cellStyle);
        //content bang
        if (!lstStaffInfo.isEmpty()) {
            int j = 0;
            for (StaffDTO staff : lstStaffInfo) {
                HSSFRow row = worksheet.createRow(j + 1);

                HSSFCell cellA = row.createCell(0);
                cellA.setCellValue(j + 1);
                cellA.setCellStyle(rowStyle);
                HSSFCell cellB = row.createCell(1);
                cellB.setCellValue((staff.getDeptId() == null) ? Constants.NULL : staff.getCode());
                cellB.setCellStyle(cellStyleLeft);
                HSSFCell cellC = row.createCell(2);
                cellC.setCellValue((staff.getCode() == null) ? Constants.NULL : staff.getName());
                cellC.setCellStyle(cellStyleLeft);
                HSSFCell cellD = row.createCell(3);
                cellD.setCellValue((staff.getName() == null) ? Constants.NULL : staff.getEmail());
                cellD.setCellStyle(cellStyleLeft);
                HSSFCell cellE = row.createCell(4);
                //                    cellE.setCellValue((staff.getEmail() == null) ? Constants.NULL : staff.getBirthDate());
                cellE.setCellStyle(cellStyleLeft);
                HSSFCell cellF = row.createCell(5);
                cellF.setCellValue((staff.getTelNumber() == null) ? Constants.NULL : staff.getTelNumber());
                cellF.setCellStyle(cellStyleLeft);
                HSSFCell cellG = row.createCell(6);
                //                    cellG.setCellValue((staff.getDeptName() == null) ? Constants.NULL : staff.getDeptName());
                cellG.setCellStyle(cellStyleLeft);
                HSSFCell cellH = row.createCell(7);
                cellH.setCellValue((staff.getStaffType() == null) ? Constants.NULL
                        : BundleUtils.getString(
                                "staff.type." + DataUtil.getStringEscapeHTML4(staff.getStaffType())));
                cellH.setCellStyle(cellStyleLeft);
                HSSFCell cellI = row.createCell(8);
                cellI.setCellValue((staff.getStatus() == null) ? Constants.NULL
                        : BundleUtils.getString(
                                "common.status." + DataUtil.getStringEscapeHTML4(staff.getStatus())));
                cellI.setCellStyle(cellStyleLeft);
                j++;
            }
            //Set Width
            for (int i = 0; i <= 0; i++) {
                worksheet.setColumnWidth(i, 2000);
            }
            for (int i = 1; i <= 7; i++) {
                worksheet.setColumnWidth(i, 5000);
            }
            for (int i = 8; i <= 10; i++) {
                worksheet.setColumnWidth(i, 3000);
            }
        }
        try {
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    File file = new File(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF);
    return file;

}

From source file:com.app.ExcelView.java

License:Open Source License

/**
 * ?Excel/*from   w w w  .  ja v a  2  s  .  com*/
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

private HSSFSheet createTestConditionSheet(String sheetName) {
    // ??, ?????//from w  ww. java2  s  .  co  m
    int maxColumn = 0;
    HSSFSheet sheet = workbook.createSheet(sheetName);
    for (ConditionSheetItem item : ConditionSheetItem.values()) {
        HSSFCell cell = getCell(sheet, item.getRow(), item.getCol());
        cell.setCellValue(item.getName());
        cell.setCellStyle(titleStyle);
        if (maxColumn < item.getCol()) {
            maxColumn = item.getCol();
        }
    }

    // ??????
    HSSFCell tableNameCell = getCell(sheet, ConditionSheetItem.TABLE_NAME.getRow(),
            ConditionSheetItem.TABLE_NAME.getCol() + 1);
    tableNameCell.setCellStyle(fixedValueStyle);
    tableNameCell.setCellValue(tableName);

    HSSFCell rowMatichingConditionCell = getCell(sheet, ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(),
            ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1);
    rowMatichingConditionCell.setCellValue(RowMatchingCondition.NONE.getJapaneseName());

    // ??
    int startRow = ConditionSheetItem.NO.getRow();
    int endRow = configureColumns(sheet, startRow);

    // ?
    setExplicitListConstraint(sheet, RowMatchingCondition.getJapaneseNames(),
            ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(),
            ConditionSheetItem.ROW_MATCHING_CONDITION.getRow(),
            ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1,
            ConditionSheetItem.ROW_MATCHING_CONDITION.getCol() + 1);

    setExplicitListConstraint(sheet, ColumnMatchingCondition.getJapaneseNames(), startRow + 1, endRow,
            ConditionSheetItem.MATCHING_CONDITION.getCol(), ConditionSheetItem.MATCHING_CONDITION.getCol());

    setExplicitListConstraint(sheet, NullValueCondition.getJapaneseNames(), startRow + 1, endRow,
            ConditionSheetItem.NULL_VALUE_CONDITION.getCol(), ConditionSheetItem.NULL_VALUE_CONDITION.getCol());

    // ?
    for (int i = 0; i <= maxColumn + 1; i++) {
        sheet.autoSizeColumn(i);
    }
    return sheet;
}

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

private int configureColumns(HSSFSheet sheet, int startRow) {
    assert columnInfos != null;
    int row = startRow;
    int no = 0;//w  w  w. j  a v a 2  s .  c  o m
    for (ColumnInfo info : columnInfos) {
        row++;
        no++;

        HSSFCell noCell = getCell(sheet, row, ConditionSheetItem.NO.getCol());
        noCell.setCellStyle(centerAlignFixedValueStyle);
        noCell.setCellValue(no);

        HSSFCell columnNameCell = getCell(sheet, row, ConditionSheetItem.COLUMN_NAME.getCol());
        columnNameCell.setCellStyle(fixedValueStyle);
        columnNameCell.setCellValue(info.getColumnName());

        HSSFCell columnCommentCell = getCell(sheet, row, ConditionSheetItem.COLUMN_COMMENT.getCol());
        columnCommentCell.setCellStyle(fixedValueStyle);
        columnCommentCell.setCellValue(info.getColumnComment());

        HSSFCell dataTypeCell = getCell(sheet, row, ConditionSheetItem.DATA_TYPE.getCol());
        dataTypeCell.setCellStyle(centerAlignFixedValueStyle);
        dataTypeCell.setCellValue(info.getDataType().getDataTypeString());

        HSSFCell widthCell = getCell(sheet, row, ConditionSheetItem.WIDTH.getCol());
        widthCell.setCellStyle(centerAlignFixedValueStyle);
        switch (info.getDataType()) {
        case CHAR:
        case VARCHAR:
            widthCell.setCellValue(info.getCharacterMaximumLength());
            break;
        case DECIMAL:
            widthCell.setCellValue(info.getNumericPrecision());
            break;
        case DATE:
        case DATETIME:
        case INT:
        case LONG:
        case SMALL_INT:
        case TIMESTAMP:
        case TINY_INT:
            widthCell.setCellValue(CELL_EMPTY);
            break;
        default:
            throw new RuntimeException(
                    MessageFormat.format("Unkonwn data type: {0}", info.getDataType().name()));
        }

        HSSFCell scaleCell = getCell(sheet, row, ConditionSheetItem.SCALE.getCol());
        scaleCell.setCellStyle(centerAlignFixedValueStyle);
        switch (info.getDataType()) {
        case DECIMAL:
            scaleCell.setCellValue(info.getNumericScale());
            break;
        case CHAR:
        case DATE:
        case DATETIME:
        case INT:
        case LONG:
        case SMALL_INT:
        case TIMESTAMP:
        case TINY_INT:
        case VARCHAR:
            scaleCell.setCellValue(CELL_EMPTY);
            break;
        default:
            throw new RuntimeException(
                    MessageFormat.format("Unkonwn data type: {0}", info.getDataType().name()));
        }

        HSSFCell nullableCell = getCell(sheet, row, ConditionSheetItem.NULLABLE.getCol());
        nullableCell.setCellStyle(centerAlignFixedValueStyle);
        if (info.isNullable()) {
            nullableCell.setCellValue(CELL_TRUE);
        } else {
            nullableCell.setCellValue(CELL_FALSE);
        }

        HSSFCell pkCell = getCell(sheet, row, ConditionSheetItem.KEY_FLAG.getCol());
        pkCell.setCellStyle(centerAlignStyle);
        if (info.isKey()) {
            pkCell.setCellValue(CELL_TRUE);
        } else {
            pkCell.setCellValue(CELL_FALSE);
        }

        HSSFCell machingCondtionCell = getCell(sheet, row, ConditionSheetItem.MATCHING_CONDITION.getCol());
        machingCondtionCell.setCellStyle(centerAlignStyle);
        machingCondtionCell.setCellValue(ColumnMatchingCondition.NONE.getJapaneseName());

        HSSFCell nullValueConditionCell = getCell(sheet, row, ConditionSheetItem.NULL_VALUE_CONDITION.getCol());
        nullValueConditionCell.setCellStyle(centerAlignStyle);
        nullValueConditionCell.setCellValue(NullValueCondition.NORMAL.getJapaneseName());

    }
    int endRow = row;
    return endRow;
}

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

/**
 * ???/*from  w ww .j  ava2 s  .  c  om*/
 * @param sheetName ???
 * @return ???
 * @throws SQLException ????????
 */
private HSSFSheet createInputDataSheet(String sheetName) throws SQLException {
    HSSFSheet sheet = workbook.createSheet(sheetName);

    // ??
    HSSFRow row = sheet.createRow(0);
    for (int i = 0; i < columnInfos.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(columnInfos[i].getColumnName());
        cell.setCellStyle(titleStyle);
    }

    // DB?
    PreparedStatement ps = null;
    ResultSet rs = null;

    String sql = "SELECT * FROM " + databaseName + "." + tableName + " limit 0, " + Constants.MAX_ROWS;
    try {
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            row = sheet.createRow(row.getRowNum() + 1);
            for (int i = 0; i < columnInfos.length; i++) {
                ColumnInfo info = columnInfos[i];
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(commonStyle);
                switch (info.getDataType()) {
                case CHAR:
                case VARCHAR:
                    String str = rs.getString(info.getColumnName());
                    if (!rs.wasNull()) {
                        cell.setCellValue(str);
                    }
                    break;
                case DATE:
                    Date date = rs.getDate(info.getColumnName());
                    if (!rs.wasNull()) {
                        cell.setCellValue(new java.util.Date(date.getTime()));
                        cell.setCellStyle(dateStyle);
                    }
                    break;
                case DATETIME:
                case TIMESTAMP:
                    Timestamp ts = rs.getTimestamp(info.getColumnName());
                    if (!rs.wasNull()) {
                        cell.setCellValue(new java.util.Date(ts.getTime()));
                        cell.setCellStyle(dateTimeStyle);
                    }
                    break;
                case DECIMAL:
                    BigDecimal decimal = rs.getBigDecimal(info.getColumnName());
                    if (!rs.wasNull()) {
                        cell.setCellValue(decimal.toPlainString());
                    }
                    break;
                case TINY_INT:
                case SMALL_INT:
                case INT:
                case LONG:
                    long value = rs.getLong(info.getColumnName());
                    if (!rs.wasNull()) {
                        cell.setCellValue(Long.toString(value));
                    }
                    break;
                default:
                    assert false;
                    break;
                }
            }
        }
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    // ?
    for (int i = 0; i < columnInfos.length; i++) {
        sheet.autoSizeColumn(i);
    }
    return sheet;
}

From source file:com.astrocytes.core.statistics.LayerStatistics.java

License:Open Source License

public boolean saveLayerStatisticsToXls(Map<Integer, List<Point>> layers, List<Point> astrocyteCenters,
        List<Point> neuronsCenters, File fileToSave) {
    try {/*  www  . j  a  v  a2 s  .c  o  m*/
        FileOutputStream outputStream = new FileOutputStream(fileToSave);

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(CoreConstants.XLS_SPREADSHEET_TITLE);

        HSSFRow headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue(CoreConstants.XLS_LAYERS_TITLE);

        for (int i = 0; i < layers.size() - 1; i++) {
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell cell = row.createCell(0);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(String.valueOf(i < 1 ? i + 1 : (i == 1 ? "2 - 3" : i + 2)));
        }

        if (astrocyteCenters != null) {
            headerRow.createCell(1).setCellValue(CoreConstants.XLS_ASTROCYTES_TITLE);

            for (Map.Entry<Integer, Integer> count : count(astrocyteCenters, layers).entrySet()) {
                HSSFRow row = sheet.getRow(count.getKey() + 1);
                HSSFCell cell = row.createCell(1);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(count.getValue());
            }
        }

        if (neuronsCenters != null) {
            int colIdx = astrocyteCenters != null ? 2 : 1;
            headerRow.createCell(colIdx).setCellValue(CoreConstants.XLS_NEURONS_TITLE);

            for (Map.Entry<Integer, Integer> count : count(neuronsCenters, layers).entrySet()) {
                HSSFRow row = sheet.getRow(count.getKey() + 1);
                HSSFCell cell = row.createCell(astrocyteCenters != null ? 2 : 1);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(count.getValue());
            }
        }

        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        workbook.write(outputStream);
        outputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
        return false;
    }

    return true;
}