List of usage examples for org.apache.poi.ss.usermodel DataFormat getFormat
String getFormat(short index);
From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java
License:Open Source License
/** * @param workbook the workbook of the cell * @param strikeOut true, if cell should be striked out * @return the highlighted cell style/*from ww w . j a v a 2 s . co m*/ */ public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) { // create highlight style for type cells CellStyle highlightStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); // set thin border around the cell highlightStyle.setBorderBottom(CellStyle.BORDER_THIN); highlightStyle.setBorderLeft(CellStyle.BORDER_THIN); highlightStyle.setBorderRight(CellStyle.BORDER_THIN); // set cell data format to text highlightStyle.setDataFormat(df.getFormat("@")); // display multiple lines highlightStyle.setWrapText(true); highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); if (strikeOut) { Font disabledTypeFont = workbook.createFont(); disabledTypeFont.setStrikeout(true); disabledTypeFont.setColor(IndexedColors.BLACK.getIndex()); highlightStyle.setFont(disabledTypeFont); } return highlightStyle; }
From source file:Excel.ExportarProducto.java
private void createCellStyles() { // FORMAT STYLE DataFormat format = workbook.createDataFormat(); style1 = workbook.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2 = workbook.createCellStyle(); style2.setDataFormat(format.getFormat("#,##0")); style3 = workbook.createCellStyle(); style3.setDataFormat(workbook.createDataFormat().getFormat("0.0%")); //END FORMAT STYLE }
From source file:fr.amapj.service.engine.generator.excel.ExcelGeneratorTool.java
License:Open Source License
/** * On utilise en tout 12 styles/* www.j ava 2s .com*/ */ private void initializeStyle() { // Cration des styles grasGaucheNonWrappe = wb.createCellStyle(); grasGaucheNonWrappe.setAlignment(CellStyle.ALIGN_LEFT); grasGaucheNonWrappe.setVerticalAlignment(CellStyle.VERTICAL_CENTER); grasGaucheNonWrappe.setFont(fontGras); grasGaucheNonWrappe.setWrapText(false); beWhite(grasGaucheNonWrappe); grasGaucheNonWrappeColor = wb.createCellStyle(); grasGaucheNonWrappeColor.cloneStyleFrom(grasGaucheNonWrappe); beOrange(grasGaucheNonWrappeColor); grasGaucheNonWrappeBordure = wb.createCellStyle(); grasGaucheNonWrappeBordure.setAlignment(CellStyle.ALIGN_LEFT); grasGaucheNonWrappeBordure.setVerticalAlignment(CellStyle.VERTICAL_CENTER); grasGaucheNonWrappeBordure.setFont(fontGras); grasGaucheNonWrappeBordure.setWrapText(false); addBorderedStyle(grasGaucheNonWrappeBordure); beWhite(grasGaucheNonWrappeBordure); grasGaucheNonWrappeBordureGray = duplicate(grasGaucheNonWrappeBordure); beGray(grasGaucheNonWrappeBordureGray); // grasGaucheWrappe = duplicate(grasGaucheNonWrappe); grasGaucheWrappe.setWrapText(true); grasGaucheWrappeColor = duplicate(grasGaucheNonWrappeColor); grasGaucheWrappeColor.setWrapText(true); grasGaucheWrappeBordure = duplicate(grasGaucheNonWrappeBordure); grasGaucheWrappeBordure.setWrapText(true); grasGaucheWrappeBordureGray = duplicate(grasGaucheNonWrappeBordureGray); grasGaucheWrappeBordureGray.setWrapText(true); // grasCentre = wb.createCellStyle(); grasCentre.setAlignment(CellStyle.ALIGN_CENTER); grasCentre.setVerticalAlignment(CellStyle.VERTICAL_CENTER); grasCentre.setFont(fontGras); grasCentre.setWrapText(true); beWhite(grasCentre); grasCentreBordure = duplicate(grasCentre); addBorderedStyle(grasCentreBordure); grasCentreBordureColor = duplicate(grasCentreBordure); beOrange(grasCentreBordureColor); grasCentreBordureColorPetit = duplicate(grasCentreBordureColor); grasCentreBordureColorPetit.setFont(fontGrasPetit); grasCentreBordureGray = duplicate(grasCentreBordure); beGray(grasCentreBordureGray); nonGrasCentreBordure = wb.createCellStyle(); nonGrasCentreBordure.setAlignment(CellStyle.ALIGN_CENTER); nonGrasCentreBordure.setVerticalAlignment(CellStyle.VERTICAL_CENTER); nonGrasCentreBordure.setFont(fontNonGras); addBorderedStyle(nonGrasCentreBordure); nonGrasCentreBordure.setWrapText(true); beWhite(nonGrasCentreBordure); nonGrasCentreBordureDiagonal = duplicate(nonGrasCentreBordure); addDiagonalBorder(nonGrasCentreBordureDiagonal); nonGrasCentreBordureDiagonalColor = duplicate(nonGrasCentreBordureDiagonal); beOrange(nonGrasCentreBordureDiagonalColor); nonGrasCentreBordureColor = duplicate(nonGrasCentreBordure); beOrange(nonGrasCentreBordureColor); nonGrasCentreBordureGray = duplicate(nonGrasCentreBordure); beGray(nonGrasCentreBordureGray); nonGrasGaucheBordure = wb.createCellStyle(); nonGrasGaucheBordure.setAlignment(CellStyle.ALIGN_LEFT); nonGrasGaucheBordure.setVerticalAlignment(CellStyle.VERTICAL_CENTER); nonGrasGaucheBordure.setFont(fontNonGras); addBorderedStyle(nonGrasGaucheBordure); nonGrasGaucheBordure.setWrapText(true); beWhite(nonGrasGaucheBordure); nonGrasGaucheBordureGray = duplicate(nonGrasGaucheBordure); beGray(nonGrasGaucheBordureGray); prixCentreBordure = wb.createCellStyle(); prixCentreBordure.setAlignment(CellStyle.ALIGN_CENTER); prixCentreBordure.setFont(fontGrasBlue); addBorderedStyle(prixCentreBordure); prixCentreBordure.setWrapText(true); DataFormat df = wb.createDataFormat(); prixCentreBordure.setDataFormat(df.getFormat("#,##0.00")); beWhite(prixCentreBordure); prixCentreBordureColor = duplicate(prixCentreBordure); beOrange(prixCentreBordureColor); titre = wb.createCellStyle(); titre.setAlignment(CellStyle.ALIGN_CENTER); titre.setFont(fontGrasHaut); titre.setWrapText(false); beWhite(titre); nongrasGaucheWrappe = wb.createCellStyle(); nongrasGaucheWrappe.setAlignment(CellStyle.ALIGN_LEFT); nongrasGaucheWrappe.setVerticalAlignment(CellStyle.VERTICAL_CENTER); nongrasGaucheWrappe.setFont(fontNonGras); nongrasGaucheWrappe.setWrapText(true); beWhite(nongrasGaucheWrappe); nonGrasGaucheNonWrappe = duplicate(nongrasGaucheWrappe); nonGrasGaucheNonWrappe.setWrapText(false); }
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 w w w . java2 s . c om 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:Import.Utils.XSSFConvert.java
public void convert() throws IOException { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int i = 0; i < wbs.length; i++) { Workbook wb = wbs[i];//from w w w .j a v a2s . co m CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet org.apache.poi.ss.usermodel.Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 2 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = 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, blue and bold f.setFontHeightInPoints((short) 12); f.setColor(IndexedColors.RED.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set font 2 to 10 point type, red and bold f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.RED.getIndex()); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set cell style and formatting cs.setFont(f); cs.setDataFormat(df.getFormat("#,##0.0")); // Set the other cell style and formatting cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat(df.getFormat("text")); cs2.setFont(f2); // Define a few rows for (int rownum = 0; rownum < 30; rownum++) { r = s.createRow(rownum); for (int cellnum = 0; cellnum < 10; cellnum += 2) { c = r.createCell(cellnum); Cell c2 = r.createCell(cellnum + 1); c.setCellValue((double) rownum + (cellnum / 10)); c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum)); } } // Save String filename = "workbook.xls"; if (wb instanceof XSSFWorkbook) { filename = filename + "x"; } FileOutputStream out = null; try { out = new FileOutputStream(filename); } catch (FileNotFoundException ex) { Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex); } wb.write(out); out.close(); } }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
public CellStyle getNumberFormat(int j, Map<Integer, CellStyle> decimalFormats, Sheet sheet, CreationHelper createHelper, CellType celltype) { int mapPosition = j; if (celltype.equals(CellType.TOTAL)) { mapPosition = j + 90000;//from w ww.j a va2s. c om } else if (celltype.equals(CellType.SUBTOTAL)) { mapPosition = j + 80000; } else if (celltype.equals(CellType.CF)) { mapPosition = j + 60000; } if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition); if (celltype.equals(CellType.CF)) { j = this.getCalculatedFieldDecimals(); } String decimals = ""; for (int i = 0; i < j; i++) { decimals += "0"; } CellStyle cellStyle = this.buildDataCellStyle(sheet); DataFormat df = createHelper.createDataFormat(); String format = "#,##0"; if (decimals.length() > 0) { format += "." + decimals; } cellStyle.setDataFormat(df.getFormat(format)); if (celltype.equals(CellType.TOTAL)) { cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); } if (celltype.equals(CellType.CF)) { cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex()); } if (celltype.equals(CellType.SUBTOTAL)) { cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); } decimalFormats.put(mapPosition, cellStyle); return cellStyle; }
From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java
License:Mozilla Public License
private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {//w w w .j ava2 s.c o m if (decimalFormats.get(j) != null) return decimalFormats.get(j); String decimals = ""; for (int i = 0; i < j; i++) { decimals += "0"; } CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles cellStyleDoub.cloneStyleFrom(dCellStyle); DataFormat df = createHelper.createDataFormat(); String format = "#,##0"; if (decimals.length() > 0) { format += "." + decimals; } cellStyleDoub.setDataFormat(df.getFormat(format)); decimalFormats.put(j, cellStyleDoub); return cellStyleDoub; }
From source file:it.inspired.exporter.ExcelExporter.java
License:Open Source License
@Override public void init() { super.init(); // 2 rows are left for the header super.currentRow = 0; if (super.isEnabledHeader()) { super.currentRow = 2; }/*from ww w . ja v a 2 s .c o m*/ workbook = new HSSFWorkbook(); sheet = workbook.createSheet("export"); // BigDecimal Style DataFormat formatbd = workbook.createDataFormat(); bigDecimalStyle = workbook.createCellStyle(); bigDecimalStyle.setDataFormat(formatbd.getFormat("#,##0.0000")); // Double Style DataFormat formatdb = workbook.createDataFormat(); doubleStyle = workbook.createCellStyle(); doubleStyle.setDataFormat(formatdb.getFormat("0.00")); // Date Style dateStyle = workbook.createCellStyle(); CreationHelper helper = workbook.getCreationHelper(); dateStyle.setDataFormat(helper.createDataFormat().getFormat(dateFormat)); // Integer Style DataFormat formatint = workbook.createDataFormat(); integerStyle = workbook.createCellStyle(); integerStyle.setDataFormat(formatint.getFormat("0")); }
From source file:it.redev.parco.ext.ExportableModelEntityQuery.java
License:Open Source License
private void setCell(Cell cell, Object obj) { if (obj == null) { cell.setCellValue(""); } else if (obj instanceof Date) { cell.setCellValue((Date) obj); CellStyle style = workbook.createCellStyle(); CreationHelper helper = workbook.getCreationHelper(); style.setDataFormat(helper.createDataFormat().getFormat(dateFormat)); cell.setCellStyle(style);/*from w ww . jav a 2 s . co m*/ } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Integer) { cell.setCellValue(Double.parseDouble(obj.toString())); DataFormat format = workbook.createDataFormat(); CellStyle style = workbook.createCellStyle(); style.setDataFormat(format.getFormat("0")); cell.setCellStyle(style); } else if (obj instanceof Double) { cell.setCellValue(Double.parseDouble(obj.toString())); DataFormat format = workbook.createDataFormat(); CellStyle style = workbook.createCellStyle(); style.setDataFormat(format.getFormat("0.00")); cell.setCellStyle(style); } else if (obj instanceof BigDecimal) { cell.setCellValue(Double.parseDouble(obj.toString())); DataFormat format = workbook.createDataFormat(); CellStyle style = workbook.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); } else { CreationHelper helper = workbook.getCreationHelper(); cell.setCellValue(helper.createRichTextString(obj.toString())); } }
From source file:javacommon.excel.ExcelWriter.java
/** * ?/*from w ww . j a v a 2s . c om*/ * @param row * @param value * @param index */ public void writeCell(Row row, Object value, int index) { if (value != null) { Cell cell = row.createCell(index); if (value instanceof Date) { CellStyle style = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellStyle(style); cell.setCellValue((Date) value); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); CellStyle style = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellStyle(style); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else if (value instanceof Short) { cell.setCellValue((Short) value); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { cell.setCellValue((Float) value); } else if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof StringBuilder) { cell.setCellValue(value.toString()); } else if (value instanceof BigDecimal) { cell.setCellValue(((BigDecimal) value).doubleValue()); } } }