@Override public HSSFDataFormat createDataFormat() { return workbook.createDataFormat(); }
@Override public HSSFDataFormat createDataFormat() { return workbook.createDataFormat(); }
HSSFWorkbook wb = ...; DataFormat format = wb.createDataFormat(); HSSFCellStyle cellStyle = wb.createCellStyle(); style.setDataFormat(format.getFormat("@")); // or "text" cell.setCellStyle(cellStyle);
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 { 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); }
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; }
protected void openWorkbook(OutputStream os) { workbook = new HSSFWorkbook(); emptyCellStyle = workbook.createCellStyle(); emptyCellStyle.setFillForegroundColor((new HSSFColor.WHITE()).getIndex()); emptyCellStyle.setFillPattern(backgroundMode); dataFormat = workbook.createDataFormat(); }
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 void init(HSSFWorkbook workbook) { dataFormat = workbook.createDataFormat(); firstLeftWhite = createStyle(workbook, true, LEFT); firstRightWhite = createStyle(workbook, true, RIGHT); leftWhite = createStyle(workbook, false, LEFT); rightWhite = createStyle(workbook, false, RIGHT); }
HSSFSheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); short rowNum = 0; short colNum = 0;
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){} }
private static HSSFWorkbook workbook; public static void main( String args[] ) throws IOException { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet( "sheet" ); HSSFRow row = sheet.createRow( 0 ); writeDecimal( row, 0.0781013, 0 ); FileOutputStream fos = new FileOutputStream( "workbook.xls", false ); workbook.write( fos ); fos.close(); } private static void writeDecimal( HSSFRow row, Double data, int position ) { String pattern = "#.0000000000"; HSSFCell celda = row.createCell(position); CellStyle styleDecimal = workbook.createCellStyle(); // Font and alignment styleDecimal.setDataFormat(workbook.createDataFormat().getFormat(pattern)); celda.setCellStyle(styleDecimal); celda.setCellType(Cell.CELL_TYPE_NUMERIC); celda.setCellValue(data); }
public static void main(String[] args) throws JDOMException, IOException { File excelFile = new File("C:/temp/test.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("sheetname"); Row r = null; CellStyle cellStyle = workbook.createCellStyle(); // CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd")); for (int i = 0; i < 2; i++) { switch (i) { case 0: r = sheet.createRow(0); r.setRowStyle(cellStyle); break; case 1: r = sheet.createRow(1); r.setRowStyle(cellStyle); break; default: break; } } try (FileOutputStream out = new FileOutputStream(excelFile)) { workbook.write(out); } Desktop.getDesktop().open(excelFile); }
HSSFDataFormat dataFormat = wb.createDataFormat(); currencyStyle.setDataFormat(dataFormat.getFormat("$#,##0.00.000"));
StylesContainer(HSSFWorkbook workbook, FontsContainer fontsContainer) { regularStyle = workbook.createCellStyle(); regularStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle = workbook.createCellStyle(); headerStyle.setFont(fontsContainer.boldFont); headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); headerStyle.setWrapText(true); timeStyle = workbook.createCellStyle(); timeStyle.setDataFormat(workbook.createDataFormat().getFormat("[HH]:MM:SS")); timeBoldStyle = workbook.createCellStyle(); timeBoldStyle.setDataFormat(workbook.createDataFormat().getFormat("[HH]:MM:SS")); timeBoldStyle.setFont(fontsContainer.boldFont); negativeTimeStyle = workbook.createCellStyle(); negativeTimeStyle.setDataFormat(workbook.createDataFormat().getFormat("-[HH]:MM:SS")); numberStyle = workbook.createCellStyle(); numberStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00###")); numberBoldStyle = workbook.createCellStyle(); numberBoldStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00###")); numberBoldStyle.setFont(fontsContainer.boldFont); dateTimeStyle = workbook.createCellStyle(); dateTimeStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); }
private void writeHeaderRow(E bean, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); for (int i = 0; i < beanProperties.size(); i++) { PropFormat prop = beanProperties.get(i); // write column header String componentName = prop.getName(); headerRow.createCell(i).setCellValue(new HSSFRichTextString(componentName)); // apply pattern if (prop.getPattern() != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(prop.getPattern())); sheet.setDefaultColumnStyle(i, columnStyle); } } }
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);
/** * Inserts a single bulleted item into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell. * @param listItem An instance of the String class encapsulating the * items text. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list item * will be written. */ public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) { // A format String must be built to ensure that the contents of the // cell appear as a bulleted item. HSSFDataFormat format = workbook.createDataFormat(); String formatString = InCellLists.BULLET_CHARACTER + " @"; int formatIndex = format.getFormat(formatString); // Construct an HSSFCellStyle and set it's data formt to use the // object created above. HSSFCellStyle bulletStyle = workbook.createCellStyle(); bulletStyle.setDataFormat((short)formatIndex); // Set the cells contents and style. cell.setCellValue(new HSSFRichTextString(listItem)); cell.setCellStyle(bulletStyle); }
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.################")); }
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle dateStyle = workbook.createCellStyle(); HSSFCellStyle datePatternStyle = workbook.createCellStyle(); HSSFDataFormat format1 = workbook.createDataFormat(); HSSFCellStyle dataStyle = workbook.createCellStyle(); HSSFCellStyle numberStyle = workbook.createCellStyle();