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.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

/**
 * Write the specified dataset to the specified Excel document.
 *//*  w ww.jav  a2  s . c  o m*/
public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException {
    logger.debug("write(dataSet={}, out={}) - start", dataSet, out);

    HSSFWorkbook workbook = new HSSFWorkbook();

    this.dateCellStyle = createDateCellStyle(workbook);

    int index = 0;
    ITableIterator iterator = dataSet.iterator();
    while (iterator.next()) {
        // create the table i.e. sheet
        ITable table = iterator.getTable();
        ITableMetaData metaData = table.getTableMetaData();
        HSSFSheet sheet = workbook.createSheet(metaData.getTableName());

        // write table metadata i.e. first row in sheet
        workbook.setSheetName(index, metaData.getTableName());

        HSSFRow headerRow = sheet.createRow(0);
        Column[] columns = metaData.getColumns();
        for (int j = 0; j < columns.length; j++) {
            Column column = columns[j];
            HSSFCell cell = headerRow.createCell(j);
            cell.setCellValue(new HSSFRichTextString(column.getColumnName()));
        }

        // write table data
        for (int j = 0; j < table.getRowCount(); j++) {
            HSSFRow row = sheet.createRow(j + 1);
            for (int k = 0; k < columns.length; k++) {
                Column column = columns[k];
                Object value = table.getValue(j, column.getColumnName());
                if (value != null) {
                    HSSFCell cell = row.createCell(k);
                    if (value instanceof Date) {
                        setDateCell(cell, (Date) value, workbook);
                    } else if (value instanceof BigDecimal) {
                        setNumericCell(cell, (BigDecimal) value, workbook);
                    } else if (value instanceof Long) {
                        setDateCell(cell, new Date(((Long) value).longValue()), workbook);
                    } else {
                        cell.setCellValue(new HSSFRichTextString(DataType.asString(value)));
                    }
                }
            }
        }

        index++;
    }

    // write xls document
    workbook.write(out);
    out.flush();
}

From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected void setDateCell(HSSFCell cell, Date value, HSSFWorkbook workbook) {
    // double excelDateValue = HSSFDateUtil.getExcelDate(value);
    // cell.setCellValue(excelDateValue);
    // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

    long timeMillis = value.getTime();
    cell.setCellValue((double) timeMillis);
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellStyle(this.dateCellStyle);

    // System.out.println(HSSFDataFormat.getBuiltinFormats());
    // TODO Find out correct cell styles for date objects
    // HSSFCellStyle cellStyleDate = workbook.createCellStyle();
    // cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    ///*  w ww .  java2 s .c  o m*/
    // HSSFCellStyle cellStyleDateTimeWithSeconds =
    // workbook.createCellStyle();
    // cellStyleDateTimeWithSeconds.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
    //
    // HSSFDataFormat dataFormat = workbook.createDataFormat();
    // HSSFCellStyle cellStyle = workbook.createCellStyle();
    // cellStyle.setDataFormat(dataFormat.getFormat("dd/mm/yyyy hh:mm:ss"));
    //
    // SimpleDateFormat formatter = new
    // SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    // SimpleDateFormat formatter2 = new SimpleDateFormat("dd/MM/yyyy");
    // SimpleDateFormat formatter3 = new SimpleDateFormat("HH:mm:ss.SSS");
    //
    //
    // Date dateValue = (Date)value;
    // Calendar cal = null;
    //
    // // If it is a date value that does not have seconds
    // if(dateValue.getTime() % 60000 == 0){
    // // cellStyle = cellStyleDate;
    // cal=Calendar.getInstance();
    // cal.setTimeInMillis(dateValue.getTime());
    //
    // cell.setCellValue(cal);
    // cell.setCellStyle(cellStyle);
    // // cell.setCellValue(cal);
    // }
    // else {
    // // HSSFDataFormatter formatter = new HSSFDataFormatter();
    //
    // // If we have seconds assume that it is only h:mm:ss without date
    // // TODO Clean implementation where user can control date formats
    // would be nice
    // // double dateDouble = dateValue.getTime() % (24*60*60*1000);
    // cal = get1900Cal(dateValue);
    //
    // String formatted = formatter3.format(dateValue);
    // //TODO Format ...
    // // cellStyle = cellStyleDateTimeWithSeconds;
    // System.out.println("date formatted:"+formatted);
    // // HSSFRichTextString s = new HSSFRichTextString(formatted);
    // // cell.setCellValue(s);
    // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    // cell.setCellValue((double)dateValue.getTime());
    // cell.setCellStyle(cellStyleDateTimeWithSeconds);
    // }

}

From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook) {
    if (logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start",
                new Object[] { cell, value, workbook });

    cell.setCellValue(((BigDecimal) value).doubleValue());

    HSSFDataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal) value).scale();
    short format;
    if (scale <= 0) {
        format = df.getFormat("####");
    } else {/*from  ww  w.j a  v a  2  s . c o  m*/
        String zeros = createZeros(((BigDecimal) value).scale());
        format = df.getFormat("####." + zeros);
    }
    if (logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);

    HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
    cellStyleNumber.setDataFormat(format);
    cell.setCellStyle(cellStyleNumber);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value.//from  w  w w  .j  av  a2 s  .com
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param date
 *            the date
 */
private void setCellValue(final HSSFRow row, final int col, final Date date) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(date);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value.//from w w  w .j a va2  s.  c  om
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final int value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value./*from ww  w .jav  a2s  . c om*/
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final double value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value./*from   w w  w. j av  a2s  . com*/
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final String value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) {
    try {/*from w w  w .  j av a2  s  .co  m*/
        String formatV = "";
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
            if (format.length >= col) {
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col]));
                //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellA1.setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRow(String sheetName, int row, String[] value) {
    try {//from ww  w  .  jav  a2  s .co m
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addHeaderRow(String sheetName, String[] columnNames) {
    try {/*from   w  w  w .j a va2  s. c  om*/
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        HSSFCellStyle cellStyle = setHeaderStyle(workbook);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(0);

        for (int col = 0; col < columnNames.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(columnNames[col]);
            cellA1.setCellStyle(cellStyle);
        }

        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    }

}