HSSFRow row = sheet.createRow(sheet.getLastRowNum()); HSSFCell cell = row.createCell(0); HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); cell.setCellStyle(style); cell.setCellValue(Float.parseFloat("21.5"));
public void cloneStyleFrom(HSSFCellStyle source) { // First we need to clone the extended format // record _format.cloneStyleFrom(source._format); // Handle matching things if we cross workbooks if(_workbook != source._workbook) { lastDateFormat.set(Short.MIN_VALUE); lastFormats.set(null); getDataFormatStringCache.set(null); // Then we need to clone the format string, // and update the format record for this short fmt = (short)_workbook.createFormat(source.getDataFormatString() ); setDataFormat(fmt); // Finally we need to clone the font, // and update the format record for this FontRecord fr = _workbook.createNewFont(); fr.cloneStyleFrom( source._workbook.getFontRecordAt( source.getFontIndexAsInt() ) ); HSSFFont font = new HSSFFont( (short)_workbook.getFontIndex(fr), fr ); setFont(font); } }
HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short) 0); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); SimpleDateFormat datetemp = new SimpleDateFormat("yyyy-MM-dd"); Date cellValue = datetemp.parse("1994-01-01 12:00"); cell.setCellValue(cellValue); //binds the style you need to the cell. HSSFCellStyle dateCellStyle = wb.createCellStyle(); short df = wb.createDataFormat().getFormat("dd-mmm"); dateCellStyle.setDataFormat(df); cell.setCellStyle(dateCellStyle);
HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("#")); Double d = 10000000000.0; thecell.setCellValue(d); thecell.setCellType(Cell.CELL_TYPE_NUMERIC); thecell.setCellStyle(style);
HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
HSSFCellStyle style = book.createCellStyle(); style.setDataFormat(BuiltInFormats.getBuiltInFormat("text"));
HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook.createDataFormat(); cs.setDataFormat(df.getFormat("#,##0.0")); //or cs.setDataFormat((short)7); cell.setCellValue(2655.32); cell.setCellStyle(cs);
HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFDataFormat hssfDataFormat = wb.createDataFormat(); String cellVal = "2500"; cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.000")); newCell.setCellStyle(cellStyle); newCell.setCellValue(new Double(cellVal)); newCell.setCellType(Cell.CELL_TYPE_NUMERIC);
HSSFCellStyle cell = yourWorkBook.createCellStyle(); CreationHelper ch = yourWorkBook.getCreationHelper(); cell.setDataFormat(ch.createDataFormat().getFormat("#,##0.00;\\-#,##0.00"));
public void setStyleFormat(int dataType, HSSFCellStyle styleRow, Workbook wb) { HSSFDataFormat df = (HSSFDataFormat) wb.createDataFormat(); switch (dataType) { case Types.DATE: case Types.TIME: case Types.TIMESTAMP: styleRow.setDataFormat(df.getFormat("m/d/yy")); break; case Types.INTEGER: case Types.NUMERIC: case Types.DECIMAL: styleRow.setDataFormat(df.getFormat("0")); break; default: styleRow.setDataFormat(df.getFormat("@")); break; } }
protected void createFormats() { timeFormatCellStyle = wb.createCellStyle(); timeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm")); dateFormatCellStyle = wb.createCellStyle(); dateFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); dateTimeFormatCellStyle = wb.createCellStyle(); dateTimeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); integerFormatCellStyle = wb.createCellStyle(); integerFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); DataFormat format = wb.createDataFormat(); doubleFormatCellStyle = wb.createCellStyle(); doubleFormatCellStyle.setDataFormat(format.getFormat("#,##0.################")); }
/** * Instantiates a new Poi cell style. * * @param workBook the work book */ public PoiCellStyle(PoiWorkBook workBook) { this.stringCs = this.getDefaultExcelCellStyle(workBook); this.numberCs = this.getDefaultExcelCellStyle(workBook); this.numberCs.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); this.numberCs.setAlignment(HorizontalAlignment.RIGHT); this.dateCs = this.getDefaultExcelCellStyle(workBook); this.dateCs.setDataFormat(DATE_FORMAT_VALUE); }
public void cloneStyleRelationsFrom(HSSFCellStyle source) { // First we need to clone the extended format // record _format.cloneStyleFrom(source._format); // Handle matching things if we cross workbooks if(_workbook != source._workbook) { // Then we need to clone the format string, // and update the format record for this short fmt = (short)_workbook.getFormat(source.getDataFormatString(), true); setDataFormat(fmt); } }
protected static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook) { HSSFDataFormat format = workbook.createDataFormat(); short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT); HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormatCode); return dateCellStyle; }
FileOutputStream out = new FileOutputStream("dateFormat.xls"); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("new sheet"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook.createDataFormat(); cs.setDataFormat(df.getFormat("#,##0.0")); for(int i=0 ;i <100 ; i++ ) { double value = new Random(i).nextGaussian(); HSSFRow row = sheet.createRow((short) i); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(value); if(value>=0 && value<=1) cell.setCellStyle(cs); } hssfworkbook.write(out); out.close();
private HSSFCellStyle createStyle(final HSSFWorkbook workbook, boolean isFirst, short align) { HSSFCellStyle style = workbook.createCellStyle(); if (isFirst) { style.setBorderTop(HSSFCellStyle.BORDER_THIN); } if (align == RIGHT) { style.setAlignment(RIGHT); style.setDataFormat(dataFormat.getFormat("[HH]:MM:SS")); } return style; }
try{ FileOutputStream out = new FileOutputStream ("dateFormat.xls"); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet ("new sheet"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook. createDataFormat(); cs.setDataFormat(df.getFormat("#,##0.0")); HSSFRow row = sheet.createRow((short)0); HSSFCell cell = row.createCell((short)0); cell.setCellValue(11111.1); cell.setCellStyle(cs); hssfworkbook.write(out); out.close(); }catch(Exception e){} }
public static void cloneStyleRelations(HSSFCellStyle source, HSSFCellStyle target) { //First we need to clone the extended format record getFormatFromStyle(target).cloneStyleFrom(getFormatFromStyle(source)); //Handle matching things if we cross workbooks InternalWorkbook sourceWorkbook = getWorkbookFromStyle(source); InternalWorkbook targetWorkbook = getWorkbookFromStyle(target); if (targetWorkbook != sourceWorkbook) { //Then we need to clone the format string, and update the format record for this short fmt = sourceWorkbook.getFormat(source.getDataFormatString(), true); target.setDataFormat(fmt); } }
public static void cloneStyleRelations(HSSFCellStyle source, HSSFCellStyle target) { //First we need to clone the extended format record getFormatFromStyle(target).cloneStyleFrom(getFormatFromStyle(source)); //Handle matching things if we cross workbooks InternalWorkbook sourceWorkbook = getWorkbookFromStyle(source); InternalWorkbook targetWorkbook = getWorkbookFromStyle(target); if (targetWorkbook != sourceWorkbook) { //Then we need to clone the format string, and update the format record for this short fmt = targetWorkbook.getFormat(source.getDataFormatString(), true); target.setDataFormat(fmt); } }
FileOutputStream out = new FileOutputStream("dateFormat.xls"); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("new sheet"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook.createDataFormat(); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(hssfworkbook); cs.setDataFormat(df.getFormat("h:mm:ss")); HSSFRow row = sheet.createRow((short)0); HSSFCell cell = row.createCell((short)0); cell.setCellFormula("TIME(0,3,24)");//this method only sets the formula string and does not calculate the formula value cell.setCellType(Cell.CELL_TYPE_FORMULA);//Set the cells type (numeric, formula or string) evaluator.evaluateFormulaCell(cell);// it evaluates the formula, and saves the result of the formula cell.setCellStyle(cs); HSSFRow row2 = sheet.createRow((short)1); HSSFCell cell2 = row2.createCell((short)0); cell2.setCellFormula("TIME(0,9,54)"); cell2.setCellType(Cell.CELL_TYPE_FORMULA); evaluator.evaluateFormulaCell(cell2); cell2.setCellStyle(cs);