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.dp2345.ExcelView.java

License:Open Source License

/**
 * ?Excel/*from w  ww  . j a  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" + " " + "D" + "P" + "2" + "3" + "4" + "5"));
                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.dv.util.DVExcelIO.java

License:Open Source License

public static boolean exportIntoExcel(String fullExcelFileName, String sheetName, Vector cols, Vector rows) {
    boolean isExportFine = true;

    HSSFWorkbook hsswb = null;//from  w  w  w  . j  a v  a  2s  .  co m
    HSSFSheet hsssh = null;
    HSSFRow row = null;

    try {
        File excel = new File(fullExcelFileName);
        if (!excel.exists()) {
            hsswb = new HSSFWorkbook();
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);

        } else {
            hsswb = new HSSFWorkbook(new FileInputStream(excel));
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);
        }

        row = hsssh.createRow((short) 2);
        HSSFCellStyle style = hsswb.createCellStyle();
        style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        for (int i = 0; i < cols.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(cols.get(i).toString());
            cell.setCellStyle(style);
        }

        HSSFCellStyle style1 = hsswb.createCellStyle();

        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style1.setBottomBorderColor(HSSFColor.BLACK.index);
        style1.setLeftBorderColor(HSSFColor.BLACK.index);
        style1.setTopBorderColor(HSSFColor.BLACK.index);
        style1.setRightBorderColor(HSSFColor.BLACK.index);

        for (int i = 3; i <= rows.size() + 2; i++) {
            row = hsssh.createRow((short) i);

            for (int j = 0; j < cols.size(); j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(((Vector) rows.elementAt(i - 3)).get(j).toString());
                cell.setCellStyle(style1);
            }
        }

        FileOutputStream fOut = new FileOutputStream(excel);
        hsswb.write(fOut);
        fOut.flush();
        fOut.close();

    } catch (IOException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (IllegalArgumentException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (Exception e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    }

    return isExportFine;
}

From source file:com.dv.util.DVExcelIO.java

License:Open Source License

public static boolean exportBatchResultIntoExcel(String fullExcelFileName, String sheetName,
        HashMap<String, Vector> colsMap, HashMap<String, Vector> rowsMap) {
    HSSFWorkbook hsswb = null;/*w ww . j a  va2 s .  c  o m*/
    HSSFSheet hsssh = null;
    HSSFRow row = null;
    Vector cols = new Vector();
    Vector rows = new Vector();

    try {
        File excel = new File(fullExcelFileName);
        if (!excel.exists()) {
            hsswb = new HSSFWorkbook();
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);

        } else {
            hsswb = new HSSFWorkbook(new FileInputStream(excel));
            hsssh = hsswb.createSheet(sheetName);
            hsssh.setDefaultRowHeight((short) 10);
            hsssh.setDefaultColumnWidth(20);
        }

        int rowCount = 1;

        for (int k = 0; k < colsMap.size(); k++) {

            cols = colsMap.get(String.valueOf(k));
            rows = rowsMap.get(String.valueOf(k));

            rowCount = rowCount + 1;

            row = hsssh.createRow((short) (rowCount));
            HSSFCellStyle style = hsswb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setTopBorderColor(HSSFColor.BLACK.index);
            style.setRightBorderColor(HSSFColor.BLACK.index);

            for (int i = 0; i < cols.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(cols.get(i).toString());
                cell.setCellStyle(style);
            }

            HSSFCellStyle style1 = hsswb.createCellStyle();

            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);

            style1.setBottomBorderColor(HSSFColor.BLACK.index);
            style1.setLeftBorderColor(HSSFColor.BLACK.index);
            style1.setTopBorderColor(HSSFColor.BLACK.index);
            style1.setRightBorderColor(HSSFColor.BLACK.index);

            int loop = rowCount;

            for (int i = 1 + loop; i <= rows.size() + loop; i++) {
                row = hsssh.createRow((short) i);
                for (int j = 0; j < cols.size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(((Vector) rows.elementAt(i - (1 + loop))).get(j).toString());
                    cell.setCellStyle(style1);
                }
                rowCount++;
            }
        }
        FileOutputStream fOut = new FileOutputStream(excel);
        hsswb.write(fOut);
        fOut.flush();
        fOut.close();

    } catch (IOException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (IllegalArgumentException e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    } catch (Exception e) {
        DVLOG.setErrorLog(DVExcelIO.class.getName(), e);
        return false;
    }
    return true;
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeStudyDetailRow(HSSFWorkbook xlsBook, HSSFSheet xlsSheet, int currentRowNum, String label,
        String value) {//from  w  ww  . java  2s. c om
    Locale locale = LocaleContextHolder.getLocale();
    HSSFRow row = xlsSheet.createRow(currentRowNum);
    HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, 153, 51, 0));
    cell.setCellValue(messageSource.getMessage(label, null, locale));
    cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(value);
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeSectionHeader(HSSFWorkbook xlsBook, HSSFSheet xlsSheet, int currentRowNum, String typeLabel,
        int c1, int c2, int c3) {
    Locale locale = LocaleContextHolder.getLocale();
    HSSFRow row = xlsSheet.createRow(currentRowNum);

    HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage(typeLabel, null, locale));

    cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.description", null, locale));

    cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.property", null, locale));

    cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.scale", null, locale));

    cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.method", null, locale));

    cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.datatype", null, locale));

    cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.value", null, locale));

    cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
    cell.setCellStyle(getHeaderStyle(xlsBook, c1, c2, c3));
    cell.setCellValue(messageSource.getMessage("export.study.description.column.label", null, locale));
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeSectionRow(int currentRowNum, HSSFSheet xlsSheet, MeasurementVariable variable) {
    HSSFRow row = xlsSheet.createRow(currentRowNum);

    HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getName());

    cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getDescription());

    cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getProperty());

    cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getScale());

    cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getMethod());

    cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getDataType());

    cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getValue());

    cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(variable.getLabel());
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeObservationHeader(int currentRowNum, HSSFWorkbook xlsBook, HSSFSheet xlsSheet,
        List<MeasurementVariable> variables) {
    if (variables != null && !variables.isEmpty()) {
        int currentColNum = 0;
        HSSFRow row = xlsSheet.createRow(currentRowNum++);
        for (MeasurementVariable variable : variables) {
            HSSFCell cell = row.createCell(currentColNum++);
            if (variable.isFactor()) {
                cell.setCellStyle(getHeaderStyle(xlsBook, 51, 153, 102));
            } else {
                cell.setCellStyle(getHeaderStyle(xlsBook, 51, 51, 153));
            }/*from   www .  ja v a2 s  .  co  m*/
            cell.setCellValue(variable.getName());
        }

    }
}

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java

License:Open Source License

private void writeObservationRow(int currentRowNum, HSSFSheet xlsSheet, MeasurementRow dataRow,
        HSSFWorkbook xlsBook) {/*w w  w .  j a v a  2  s  .com*/
    HSSFRow row = xlsSheet.createRow(currentRowNum);
    int currentColNum = 0;
    CellStyle style = xlsBook.createCellStyle();
    DataFormat format = xlsBook.createDataFormat();
    style.setDataFormat(format.getFormat("0.#"));
    for (MeasurementData dataCell : dataRow.getDataList()) {
        HSSFCell cell = row.createCell(currentColNum++);
        /*
        if(AppConstants.NUMERIC_DATA_TYPE.getString().equalsIgnoreCase(dataCell.getDataType())){
           cell.setCellType(Cell.CELL_TYPE_BLANK);
           cell.setCellType(Cell.CELL_TYPE_NUMERIC);            
        }*/
        cell.setCellValue(dataCell.getValue());

    }
}

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) {
    if (src == null) {
        dest.setCellType(HSSFCell.CELL_TYPE_BLANK);
        return;/*from w  ww .j  av  a 2 s .  c  o m*/
    }

    if (src.getCellComment() != null)
        dest.setCellComment(src.getCellComment());
    if (src.getCellStyle() != null) {
        HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb);
        if (nstyle == null) {
            nstyle = destwb.createCellStyle();
            copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle());
        }
        dest.setCellStyle(nstyle);
    }
    dest.setCellType(src.getCellType());

    switch (src.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:

        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        dest.setCellValue(src.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        dest.setCellFormula(src.getCellFormula());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        dest.setCellErrorValue(src.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        dest.setCellValue(src.getNumericCellValue());
        break;
    default:
        dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString()));
        break;
    }
}

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * /*from w  w  w . j av  a2s  . c  o m*/
 * @param
 * @return void
 */
private void generateColumn(HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum,
        HSSFCellStyle headerstyle) {
    HSSFRow row = sheet.getRow(rownum);
    if (row == null)
        row = sheet.createRow(rownum);

    HSSFCell cell = row.createCell(colnum);
    cell.setCellValue(tc.getDisplay());

    if (headerstyle != null)
        cell.setCellStyle(headerstyle);
    if (tc.isComplex()) {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);

        int cn = colnum;
        for (int i = 0; i < tc.getChildren().size(); i++) {
            if (i != 0) {
                cn = cn + tc.getChildren().get(i - 1).getLength();
            }
            generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle);
        }
    } else {
        CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum);
        sheet.addMergedRegion(address);
        fillMergedRegion(sheet, address, headerstyle);
    }
    sheet.autoSizeColumn(colnum, true);
}