List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormat
public static String getBuiltinFormat(short index)
From source file:gr.abiss.calipso.util.ExcelUtils.java
License:Open Source License
public ExcelUtils(List items, ItemSearch itemSearch, Component callerComponent) { this.wb = new HSSFWorkbook(); this.sheet = wb.createSheet("calipso"); this.sheet.setDefaultColumnWidth((short) 12); HSSFFont fBold = wb.createFont();/*from w w w .j av a 2 s . c om*/ fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); this.csBold = wb.createCellStyle(); this.csBold.setFont(fBold); this.csDate = wb.createCellStyle(); this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); this.items = items; this.itemSearch = itemSearch; this.callerComponent = callerComponent; }
From source file:gregchen.Simulation.java
License:Open Source License
private void initializeExcelFile() { //short rownum; //Create a Data folder if it does not already exist File theDir = new File("Data"); //if the directory does not exist, create it if(!theDir.exists()) {//from ww w . j a v a2 s . co m theDir.mkdir(); } // create a new file if(fileCount == 0) { File file = null; do { fileCount++; file = new File("Data/Data " + fileCount + ".xls"); }while(file.exists()); } try { excelOut = new FileOutputStream("Data/Data " + fileCount + ".xls"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } fileCount++; // create a new workbook wb = new HSSFWorkbook(); // create a new sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor( (short)0xc ); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor( (short)Font.COLOR_RED ); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout( true ); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "Greg Chen"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // Make header cells r = s.createRow(0); c = r.createCell(7); c.setCellStyle(cs3); c.setCellValue("Initial number: " + initialNumber + " Number per release: " + numPerRelease + " Number Releases: " + numReleases + " Release Interval: " + releaseInterval + " Female lethal gene: " + fsRIDL); currentRow = 2; for (short cellnum = (short) 0; cellnum < 7; cellnum ++) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals String heading = null; switch(cellnum) { case 0: heading = "Day"; break; case 1: heading = "Total Population"; break; case 2: heading = "Male Population"; break; case 3: heading = "Female Population"; break; case 4: heading = "FF"; break; case 5: heading = "Ff"; break; case 6: heading = "ff"; break; } c.setCellValue(heading); // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" // make this column a bit wider if(cellnum > 0) { s.setColumnWidth((short) (cellnum), (short) ((300) / ((double) 1 / 20))); } r.setHeight((short) 800); } /* int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); r.setHeight((short) 0x249); // } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); */ // write the workbook to the output stream // close our file (don't blow out our file handles) }
From source file:info.jtrac.service.ExcelUtils.java
License:Apache License
public ExcelUtils(List<Item> items, ItemSearch itemSearch) { this.wb = new HSSFWorkbook(); this.sheet = wb.createSheet("jtrac"); this.sheet.setDefaultColumnWidth((short) 12); HSSFFont fBold = wb.createFont();/*from w ww . ja v a2 s . c o m*/ fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); this.csBold = wb.createCellStyle(); this.csBold.setFont(fBold); this.csDate = wb.createCellStyle(); this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); this.items = items; this.itemSearch = itemSearch; }
From source file:info.jtrac.util.ExcelUtils.java
License:Apache License
public ExcelUtils(List items, ItemSearch itemSearch) { this.wb = new HSSFWorkbook(); this.sheet = wb.createSheet("jtrac"); this.sheet.setDefaultColumnWidth((short) 12); HSSFFont fBold = wb.createFont();/*www . j a v a 2 s . co m*/ fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); this.csBold = wb.createCellStyle(); this.csBold.setFont(fBold); this.csDate = wb.createCellStyle(); this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); this.items = items; this.itemSearch = itemSearch; }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private void initHssfStyle(final HSSFWorkbook workbook) { // default://w w w .ja v a 2s .c om final HSSFCellStyle oddCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenCellStyle = createRowCellStyle(workbook, true); oddHssfStyleCache.put(DataType.Boolean, oddCellStyle); oddHssfStyleCache.put(DataType.String, oddCellStyle); evenHssfStyleCache.put(DataType.Boolean, evenCellStyle); evenHssfStyleCache.put(DataType.String, evenCellStyle); // Nombre sans dcimal final HSSFCellStyle oddLongCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenLongCellStyle = createRowCellStyle(workbook, true); oddLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); evenLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); oddHssfStyleCache.put(DataType.Long, oddLongCellStyle); oddHssfStyleCache.put(DataType.Integer, oddLongCellStyle); evenHssfStyleCache.put(DataType.Long, evenLongCellStyle); evenHssfStyleCache.put(DataType.Integer, evenLongCellStyle); // Nombre a dcimal final HSSFCellStyle oddDoubleCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenDoubleCellStyle = createRowCellStyle(workbook, true); oddDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); evenDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); oddHssfStyleCache.put(DataType.Double, oddDoubleCellStyle); oddHssfStyleCache.put(DataType.BigDecimal, oddDoubleCellStyle); evenHssfStyleCache.put(DataType.Double, evenDoubleCellStyle); evenHssfStyleCache.put(DataType.BigDecimal, evenDoubleCellStyle); // Date final HSSFCellStyle oddDateCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenDateCellStyle = createRowCellStyle(workbook, true); oddDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */)); evenDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */)); oddHssfStyleCache.put(DataType.Date, oddDateCellStyle); evenHssfStyleCache.put(DataType.Date, evenDateCellStyle); }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
private void initHssfStyle(final HSSFWorkbook workbook) { // default://w ww .j a v a 2 s. c o m final HSSFCellStyle oddCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenCellStyle = createRowCellStyle(workbook, true); oddHssfStyleCache.put(DataType.Boolean, oddCellStyle); oddHssfStyleCache.put(DataType.String, oddCellStyle); evenHssfStyleCache.put(DataType.Boolean, evenCellStyle); evenHssfStyleCache.put(DataType.String, evenCellStyle); // Nombre sans dcimal final HSSFCellStyle oddLongCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenLongCellStyle = createRowCellStyle(workbook, true); oddLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); evenLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); oddHssfStyleCache.put(DataType.Long, oddLongCellStyle); oddHssfStyleCache.put(DataType.Integer, oddLongCellStyle); evenHssfStyleCache.put(DataType.Long, evenLongCellStyle); evenHssfStyleCache.put(DataType.Integer, evenLongCellStyle); // Nombre a dcimal final HSSFCellStyle oddDoubleCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenDoubleCellStyle = createRowCellStyle(workbook, true); oddDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); evenDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); oddHssfStyleCache.put(DataType.Double, oddDoubleCellStyle); oddHssfStyleCache.put(DataType.BigDecimal, oddDoubleCellStyle); evenHssfStyleCache.put(DataType.Double, evenDoubleCellStyle); evenHssfStyleCache.put(DataType.BigDecimal, evenDoubleCellStyle); // Date final HSSFCellStyle oddDateCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenDateCellStyle = createRowCellStyle(workbook, true); oddDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */)); evenDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */)); oddHssfStyleCache.put(DataType.LocalDate, oddDateCellStyle); evenHssfStyleCache.put(DataType.LocalDate, evenDateCellStyle); // Instant final HSSFCellStyle oddDateTimeCellStyle = createRowCellStyle(workbook, true); final HSSFCellStyle evenDateTimeCellStyle = createRowCellStyle(workbook, true); oddDateTimeCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); evenDateTimeCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); oddHssfStyleCache.put(DataType.Instant, oddDateTimeCellStyle); evenHssfStyleCache.put(DataType.Instant, evenDateTimeCellStyle); }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
public CellStyle[] fillSheetHeader(Sheet sheet, Workbook wb, CreationHelper createHelper, int beginRowHeaderData, int beginColumnHeaderData) { CellStyle hCellStyle = buildHeaderCellStyle(sheet); int colnum = extractedFieldsMetaData.size(); Row row = sheet.getRow(beginRowHeaderData); CellStyle[] cellTypes = new CellStyle[colnum]; // array for numbers patterns storage for (int j = 0; j < colnum; j++) { Cell cell = row.createCell(j + beginColumnHeaderData); cell.setCellType(HSSFCell.CELL_TYPE_STRING); IFieldMetaData fieldMetaData = extractedFieldsMetaData.get(j); String fieldName = fieldMetaData.getName(); String format = (String) fieldMetaData.getProperty("format"); String alias = (String) fieldMetaData.getAlias(); Boolean visible = (Boolean) fieldMetaData.getProperty("visible"); if (extractedFields != null && extractedFields.get(j) != null) { Object f = extractedFields.get(j); logger.debug("Extracted field " + fieldName + " is instance of " + f.getClass().getName()); if (f instanceof Field) { Field field = (Field) f; fieldName = field.getName(); if (field.getPattern() != null) { format = field.getPattern(); }//w w w .j a v a 2s .c o m } } CellStyle aCellStyle = wb.createCellStyle(); if (format != null) { short formatInt = HSSFDataFormat.getBuiltinFormat(format); aCellStyle.setDataFormat(formatInt); cellTypes[j] = aCellStyle; } if (visible != null && visible.booleanValue() == true) { if (alias != null && !alias.equals("")) { cell.setCellValue(createHelper.createRichTextString(alias)); } else { cell.setCellValue(createHelper.createRichTextString(fieldName)); } cell.setCellStyle(hCellStyle); } } return cellTypes; }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes, int beginRowData, int beginColumnData) { CellStyle dCellStyle = buildDataCellStyle(sheet); int rownum = beginRowData; short formatIndexInt = HSSFDataFormat.getBuiltinFormat("#,##0"); CellStyle cellStyleInt = wb.createCellStyle(); // cellStyleInt is the default cell style for integers cellStyleInt.cloneStyleFrom(dCellStyle); cellStyleInt.setDataFormat(formatIndexInt); short formatIndexDoub = HSSFDataFormat.getBuiltinFormat("#,##0.00"); CellStyle cellStyleDoub = wb.createCellStyle(); // cellStyleDoub is the default cell style for doubles cellStyleDoub.cloneStyleFrom(dCellStyle); cellStyleDoub.setDataFormat(formatIndexDoub); CellStyle cellStyleDate = wb.createCellStyle(); // cellStyleDate is the default cell style for dates cellStyleDate.cloneStyleFrom(dCellStyle); //cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm")); cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d")); for (int i = 0; i < numberOfRows; i++) { Row rowVal = sheet.getRow(rownum); IRecord record = (IRecord) dataStore.getRecordAt(i); List fields = record.getFields(); int length = extractedFieldsMetaData.size(); for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) { IFieldMetaData metaField = extractedFieldsMetaData.get(fieldIndex); IField f = (IField) record.getFieldAt((Integer) metaField.getProperty("index")); if (f != null && f.getValue() != null) { Boolean visible = (Boolean) metaField.getProperty("visible"); if (visible) { Class c = metaField.getType(); logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]"); if (rowVal == null) { rowVal = sheet.createRow(rownum); }/*from w w w.j a va 2 s .co m*/ Cell cell = rowVal.createCell(fieldIndex + beginColumnData); cell.setCellStyle(dCellStyle); if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]"); Number val = (Number) f.getValue(); cell.setCellValue(val.intValue()); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle( (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt); } else if (Number.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]"); Number val = (Number) f.getValue(); cell.setCellValue(val.doubleValue()); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle( (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleDoub); } else if (String.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]"); String val = (String) f.getValue(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } else if (Boolean.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]"); Boolean val = (Boolean) f.getValue(); cell.setCellValue(val.booleanValue()); cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else if (Date.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]"); Date val = (Date) f.getValue(); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String dtString = df.format(val); cell.setCellValue(dtString); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } else { logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]"); String val = f.getValue().toString(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } } } } rownum++; } }
From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java
License:Mozilla Public License
protected short getBuiltinFormat(String formatStr) { short format = HSSFDataFormat.getBuiltinFormat(formatStr); return format; }
From source file:joinery.impl.Serialization.java
License:Open Source License
private static final void writeCell(final Cell cell, final Object value) { if (value instanceof Number) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Number.class.cast(value).doubleValue()); } else if (value instanceof Date) { final CellStyle style = cell.getSheet().getWorkbook().createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell.setCellStyle(style);// w w w. j av a2s . c o m cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Date.class.cast(value)); } else if (value instanceof Boolean) { cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value != null ? String.valueOf(value) : ""); } }