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:gda.hrpd.data.ExcelWorkbook.java

License:Open Source License

/**
 * modify if exist, or create if not exist, a cell in the specified row at specified position with the specified
 * value./*from   ww w  .  j a v  a2s  . c o m*/
 * 
 * @param row
 * @param column
 * @param date
 * @throws IOException
 */
public void setCellValue(HSSFRow row, int column, Date date) throws IOException {
    HSSFCell cell = row.getCell(column);
    if (cell == null) {
        if (!writeable) {
            logger.error("Cannot create a new sheet in file {}.", this.filename);
            throw new IOException("Cannot write to file {}." + this.filename);
        }

        // we style the cell as a date (and time). It is important to
        // create a new cell style from the workbook otherwise you can end
        // up modifying the built in style and effecting not only this cell
        // but other cells.
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell = row.createCell((short) column);
        cell.setCellValue(date);
        cell.setCellStyle(cellStyle);
    } else {
        cell.setCellValue(date);
    }
}

From source file:gda.hrpd.data.HSSF.java

License:Apache License

/**
 * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells.
 *
 * @param filename/*from w ww. j av a 2  s .  c  o m*/
 * @param write
 * @exception IOException
 */

public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException {
    short rownum = 0;
    FileOutputStream out = new FileOutputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = (short) 0; rownum < 300; rownum++) {
        r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

        // r.setRowNum(( short ) rownum);
        for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
            c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
            c.setCellValue(new HSSFRichTextString("TEST"));
            s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        } // 50 characters divided by 1/20th of a point
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
        c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);

        // c.setCellValue(0);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
    s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

    // end deleted sheet
    wb.write(out);
    out.close();
}

From source file:gda.hrpd.data.HSSF.java

License:Apache License

/**
 * Method main Given 1 argument takes that as the filename, inputs it and dumps the cell values/types out to sys.out
 * given 2 arguments where the second argument is the word "write" and the first is the filename - writes out a
 * sample (test) spreadsheet (see public HSSF(String filename, boolean write)). given 2 arguments where the first is
 * an input filename and the second an output filename (not write), attempts to fully read in the spreadsheet and
 * fully write it out. given 3 arguments where the first is an input filename and the second an output filename (not
 * write) and the third is "modify1", attempts to read in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at
 * row 39, col 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you take the output from
 * the write test, you'll have a valid scenario.
 *
 * @param args/*w  ww.j a  va2s  .c  om*/
 */

public static void main(String[] args) {
    if (args.length < 2) {

        /*
         * try { HSSF hssf = new HSSF(args[ 0 ]); System.out.println("Data dump:\n"); HSSFWorkbook wb =
         * hssf.hssfworkbook; for (int k = 0; k < wb.getNumberOfSheets(); k++) { System.out.println("Sheet " + k);
         * HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows;
         * r++) { HSSFRow row = sheet.getPhysicalRowAt(r); int cells = row.getPhysicalNumberOfCells();
         * System.out.println("ROW " + row.getRowNum()); for (int c = 0; c < cells; c++) { HSSFCell cell =
         * row.getPhysicalCellAt(c); String value = null; switch (cell.getCellType()) { case
         * HSSFCell.CELL_TYPE_FORMULA : value = "FORMULA "; break; case HSSFCell.CELL_TYPE_NUMERIC : value =
         * "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING : value = "STRING
         * value=" + cell.getStringCellValue(); break; default : } System.out.println("CELL col=" +
         * cell.getCellNum() + " VALUE=" + value); } } } } catch (Exception e) { e.printStackTrace(); }
         */
    } else if (args.length == 2) {
        if (args[1].toLowerCase().equals("write")) {
            System.out.println("Write mode");
            try {
                long time = System.currentTimeMillis();
                // HSSF hssf = new HSSF(args[ 0 ], true);

                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("readwrite test");
            try {
                HSSF hssf = new HSSF(args[0]);

                // HSSFStream hssfstream = hssf.hssfstream;
                HSSFWorkbook wb = hssf.hssfworkbook;
                FileOutputStream stream = new FileOutputStream(args[1]);

                // HSSFCell cell = new HSSFCell();
                // cell.setCellNum((short)3);
                // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                // cell.setCellValue(-8009.999);
                // hssfstream.modifyCell(cell,0,(short)6);
                wb.write(stream);
                stream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    } else if ((args.length == 3) && args[2].toLowerCase().equals("modify1")) {
        try // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
        {
            HSSF hssf = new HSSF(args[0]);

            // HSSFStream hssfstream = hssf.hssfstream;
            HSSFWorkbook wb = hssf.hssfworkbook;
            FileOutputStream stream = new FileOutputStream(args[1]);
            HSSFSheet sheet = wb.getSheetAt(0);

            for (int k = 0; k < 25; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            for (int k = 74; k < 100; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            HSSFRow row = sheet.getRow(39);
            HSSFCell cell = row.getCell((short) 3);

            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString("MODIFIED CELL!!!!!"));

            // HSSFCell cell = new HSSFCell();
            // cell.setCellNum((short)3);
            // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            // cell.setCellValue(-8009.999);
            // hssfstream.modifyCell(cell,0,(short)6);
            wb.write(stream);
            stream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:gda.hrpd.data.SampleExperimentSummary.java

License:Open Source License

/**
 * @param sampleNo/*w  w w  . j  a v  a  2  s .  co m*/
 */
public void saveExperimentInfo(int sampleNo) {
    HSSFRow row = sheet.getRow(sampleNo + rowOffset);
    HSSFCell cell = row.getCell((short) 1);
    if (cell == null)
        cell = row.createCell((short) 1);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(runNumber));
    cell = row.getCell((short) 2);
    if (cell == null)
        cell = row.createCell((short) 2);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(date));
    cell = row.getCell((short) 3);
    if (cell == null)
        cell = row.createCell((short) 3);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(time));
    cell = row.getCell((short) 4);
    if (cell == null)
        cell = row.createCell((short) 4);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(beamline));
    cell = row.getCell((short) 5);
    if (cell == null)
        cell = row.createCell((short) 5);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(project));
    cell = row.getCell((short) 6);
    if (cell == null)
        cell = row.createCell((short) 6);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(experiment));
    cell = row.getCell((short) 7);
    if (cell == null)
        cell = row.createCell((short) 7);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(new HSSFRichTextString(accumulationTime));
}

From source file:gov.guilin.ExcelView.java

License:Open Source License

/**
 * ?Excel/*from   w  ww.ja  va2  s  .  c o m*/
 * 
 * @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" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                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:gov.nih.nci.caadapter.common.csv.CSVMetaReportGenerator.java

License:BSD License

private void printHeading(HSSFSheet worksheet) {
    HSSFRow row = worksheet.createRow(0);
    HSSFCell segmentcell = row.createCell((short) 0);
    segmentcell.setCellValue("Segment Name");
    for (int i = 1; i <= maxfields; i++) {
        HSSFCell fieldcell = row.createCell((short) (i));
        fieldcell.setCellValue("Field " + i);
    }//w  ww  .j  a  v a  2 s.  co  m
}

From source file:gov.nih.nci.caadapter.common.csv.CSVMetaReportGenerator.java

License:BSD License

private void processSegment(CSVSegmentMeta segment, HSSFSheet worksheet, int currentRow, int indent) {
    HSSFRow row = worksheet.createRow(currentRow++);
    HSSFCell segmentcell = row.createCell((short) 0);
    segmentcell.setCellValue(getIndent(indent) + segment.getName());

    List<CSVFieldMeta> fields = segment.getFields();
    if (fields.size() > maxfields)
        maxfields = fields.size();/*w w w  . j  a va2 s  . c  o  m*/

    for (int i = 0; i < fields.size(); i++) {
        CSVFieldMeta csvFieldMeta = fields.get(i);
        HSSFCell fieldcell = row.createCell((short) (i + 1));
        fieldcell.setCellValue(csvFieldMeta.getName());
    }

    List<CSVSegmentMeta> childSegments = segment.getChildSegments();
    for (int i = 0; i < childSegments.size(); i++) {
        CSVSegmentMeta csvSegmentMeta = childSegments.get(i);
        processSegment(csvSegmentMeta, worksheet, currentRow++, indent + 1);
    }
}

From source file:gov.nih.nci.caadapter.hl7.report.MapReportGenerator.java

License:BSD License

/**
 * Do not generate unmapped function parameter here, since the structure is different.
 *//*from   ww  w  .ja v a  2s .  c o  m*/
private void generateUnmappedReport(Mapping mappingMeta) {
    HSSFSheet csvWorksheet = workbook.createSheet(UNMAPPED_SOURCE_TITLE);
    printHeading(csvWorksheet, UNMAPPED_SOURCE_TITLE, false);
    int rowCount = sheetRowCount.get(UNMAPPED_SOURCE_TITLE).intValue();
    HSSFRow row = null;
    for (String string : allCSVElement) {
        if (!visitiedCSVElement.contains(string)) {
            row = csvWorksheet.createRow(rowCount);
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(string);
            rowCount++;
        }
    }

    HSSFSheet h3sWorksheet = workbook.createSheet(UNMAPPED_TARGET_TITLE);
    printHeading(h3sWorksheet, UNMAPPED_TARGET_TITLE, false);
    rowCount = sheetRowCount.get(UNMAPPED_TARGET_TITLE).intValue();
    row = null;
    for (String string : allMifElement) {
        if (!visitiedMifElement.contains(string)) {
            row = h3sWorksheet.createRow(rowCount);
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(string);
            rowCount++;
        }
    }
}

From source file:gov.nih.nci.caadapter.hl7.report.MapReportGenerator.java

License:BSD License

/**
 * Print once and only once when the worksheet is created.
 * @param worksheet/*from w ww .ja va2s . c  o m*/
 */
private void printHeading(HSSFSheet worksheet, String firstRowText, boolean mappedHeading) {
    HSSFRow row = worksheet.createRow(0);
    HSSFCell cell = null;
    cell = row.createCell((short) 0);
    cell.setCellValue(firstRowText);
    if (mappedHeading) {
        row = worksheet.createRow(1);
        cell = row.createCell((short) 0);
        cell.setCellValue("Path of Origination");
        cell = row.createCell((short) 1);
        cell.setCellValue("Path of Destination");
        sheetRowCount.put(firstRowText, new Integer(2));
    } else {
        sheetRowCount.put(firstRowText, new Integer(1));
    }
}

From source file:gov.nih.nci.caadapter.hl7.report.MapReportGenerator.java

License:BSD License

private void printMappedContent(HSSFSheet worksheet, String source, String target, String keyToRowCount) {

    Integer rowCount = sheetRowCount.get(keyToRowCount);
    HSSFRow row = worksheet.createRow(rowCount.intValue());
    HSSFCell cell = null;
    cell = row.createCell((short) 0);
    cell.setCellValue(source);
    cell = row.createCell((short) 1);
    cell.setCellValue(target);/*  w w w. j a v  a2  s.c  o  m*/

    //update the row count
    Integer updatedRowCount = new Integer(rowCount.intValue() + 1);
    sheetRowCount.put(keyToRowCount, updatedRowCount);
}