List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:org.projectforge.excel.XlsContentProvider.java
License:Open Source License
@Override public XlsContentProvider updateCellStyle(final ExportCell cell) { final CellFormat format = cell.ensureAndGetCellFormat(); CellStyle cellStyle = reusableCellFormats.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); reusableCellFormats.put(format, cellStyle); format.copyToCellStyle(cellStyle); if (format.getFillForegroundColor() != null) { cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); }//from w w w . j av a 2 s. c om cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); cellStyle.setWrapText(true); final String dataFormat = format.getDataFormat(); if (dataFormat != null) { final short df = workbook.getDataFormat(format.getDataFormat()); cellStyle.setDataFormat(df); } } cell.setCellStyle(cellStyle); return this; }
From source file:org.projectforge.export.XlsContentProvider.java
License:Open Source License
public void updateCellStyle(final ExportCell cell) { final CellFormat format = cell.ensureAndGetCellFormat(); CellStyle cellStyle = reusableCellFormats.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); reusableCellFormats.put(format, cellStyle); format.copyToCellStyle(cellStyle); if (format.getFillForegroundColor() != null) { cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); }//from w w w. j av a2s .c o m cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); cellStyle.setWrapText(true); final String dataFormat = format.getDataFormat(); if (dataFormat != null) { final short df = workbook.getDataFormat(format.getDataFormat()); cellStyle.setDataFormat(df); } } cell.setCellStyle(cellStyle); }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;/*from ww w . j av a2s . co m*/ try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet 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, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //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); //create 50 cells for (short cellnum = (short) 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.riflemansd.businessprofit.excel.MyExcelDocument.java
License:Open Source License
public void setDate(int nsheet, int nrow, int ncolumn, Date value) { org.apache.poi.ss.usermodel.Cell cell = getCell(nsheet, nrow, ncolumn); CellStyle style = workbook.createCellStyle(); CreationHelper helper = workbook.getCreationHelper(); style.setDataFormat(helper.createDataFormat().getFormat("dd/mm/yyy")); cell.setCellValue(value);/*from w ww .j a v a2s . c o m*/ cell.setCellStyle(style); }
From source file:org.seedstack.io.jasper.fixtures.CustomXlsRenderer.java
License:Mozilla Public License
@Override public void render(OutputStream outputStream, Object model, String mimeType, Map<String, Object> parameters) { Validate.isTrue(StringUtils.equals(mimeType, "application/xls")); try {//from w w w . ja va2 s. co m Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); CreationHelper createHelper = wb.getCreationHelper(); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); // Or do it on one line. CustomerBean bean = (CustomerBean) model; row.createCell(1).setCellValue(bean.getCustomerNo()); row.createCell(2).setCellValue(createHelper.createRichTextString(bean.getFirstName())); row.createCell(3).setCellValue(createHelper.createRichTextString(bean.getLastName())); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); Cell cell = row.createCell(4); cell.setCellValue(bean.getBirthDate()); cell.setCellStyle(cellStyle); row.createCell(5).setCellValue(bean.getMailingAddress()); row.createCell(6).setCellValue(bean.getMarried()); row.createCell(7).setCellValue(bean.getNumberOfKids()); row.createCell(8).setCellValue(bean.getFavouriteQuote()); row.createCell(9).setCellValue(bean.getEmail()); row.createCell(10).setCellValue(bean.getLoyaltyPoints()); wb.write(outputStream); outputStream.close(); } catch (Exception e) { throw new RuntimeException(e); } }
From source file:org.unitime.timetable.export.XLSPrinter.java
License:Apache License
protected CellStyle getStyle(A f, boolean dashed, String format) { String styleId = (dashed ? "D" : "") + (f.has(F.BOLD) ? "b" : "") + (f.has(F.ITALIC) ? "i" : "") + (f.has(F.UNDERLINE) ? "u" : "") + (f.has(F.RIGHT) ? "R" : f.has(F.CENTER) ? "C" : "L") + (f.hasColor() ? "#" + Integer.toHexString(f.getColor().getRGB()) : "") + (format == null ? "" : "|" + format); CellStyle style = iStyles.get(styleId); if (style == null) { style = iWorkbook.createCellStyle(); if (dashed) { style.setBorderTop(BorderStyle.DASHED); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); }/* ww w .j a va 2s . com*/ style.setAlignment(f.has(F.RIGHT) ? HorizontalAlignment.RIGHT : f.has(F.CENTER) ? HorizontalAlignment.CENTER : HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(f.has(F.BOLD), f.has(F.ITALIC), f.has(F.UNDERLINE), f.getColor())); style.setWrapText(true); if (format != null) style.setDataFormat(iWorkbook.createDataFormat().getFormat(format)); iStyles.put(styleId, style); } return style; }
From source file:org.wurtele.ifttt.watchers.WorkTimesWatcher.java
License:Open Source License
private void processFile(Path input) { logger.info("Updating " + output); try (Workbook wb = new XSSFWorkbook(); OutputStream out = Files.newOutputStream(output, StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING)) { Sheet sheet = wb.createSheet("Time Sheet"); List<WorkDay> days = new ArrayList<>(); DateFormat df = new SimpleDateFormat("MMMM dd, yyyy 'at' hh:mma"); for (String line : Files.readAllLines(input)) { String[] data = line.split(";"); LocationType type = LocationType.valueOf(data[0].toUpperCase()); Date time = df.parse(data[1]); Date day = DateUtils.truncate(time, Calendar.DATE); WorkDay wd = new WorkDay(day); if (days.contains(wd)) wd = days.get(days.indexOf(wd)); else/*from w ww.j a va 2 s . co m*/ days.add(wd); wd.getTimes().add(new WorkTime(time, type)); } CreationHelper helper = wb.getCreationHelper(); Font bold = wb.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("MMMM d, yyyy")); CellStyle timeStyle = wb.createCellStyle(); timeStyle.setDataFormat(helper.createDataFormat().getFormat("h:mm AM/PM")); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); headerStyle.setFont(bold); CellStyle totalStyle = wb.createCellStyle(); totalStyle.setAlignment(CellStyle.ALIGN_RIGHT); Row header = sheet.createRow(0); header.createCell(0).setCellValue("DATE"); header.getCell(0).setCellStyle(headerStyle); Collections.sort(days); for (int r = 0; r < days.size(); r++) { WorkDay day = days.get(r); Row row = sheet.createRow(r + 1); row.createCell(0).setCellValue(day.getDate()); row.getCell(0).setCellStyle(dateStyle); Collections.sort(day.getTimes()); for (int c = 0; c < day.getTimes().size(); c++) { WorkTime time = day.getTimes().get(c); if (sheet.getRow(0).getCell(c + 1) != null && !sheet.getRow(0).getCell(c + 1).getStringCellValue().equals(time.getType().name())) { throw new Exception("Invalid data"); } else if (sheet.getRow(0).getCell(c + 1) == null) { sheet.getRow(0).createCell(c + 1).setCellValue(time.getType().name()); sheet.getRow(0).getCell(c + 1).setCellStyle(headerStyle); } row.createCell(c + 1).setCellValue(time.getTime()); row.getCell(c + 1).setCellStyle(timeStyle); } } int totalCol = header.getLastCellNum(); header.createCell(totalCol).setCellValue("TOTAL"); header.getCell(totalCol).setCellStyle(headerStyle); for (int r = 0; r < days.size(); r++) { sheet.getRow(r + 1).createCell(totalCol).setCellValue(days.get(r).getTotal()); sheet.getRow(r + 1).getCell(totalCol).setCellStyle(totalStyle); } for (int c = 0; c <= totalCol; c++) { sheet.autoSizeColumn(c); } wb.write(out); } catch (Exception e) { logger.error("Failed to update " + output, e); } }
From source file:org.zafritech.zidingorms.io.excel.ExcelFunctions.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CreationHelper creationHelper = wb.getCreationHelper(); CellStyle style; // Header Font Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBold(true);/*from ww w . j a v a2s . c o m*/ headerFont.setColor(IndexedColors.WHITE.getIndex()); // Header Left Aligned Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("HeaderLeftAlign", style); // Header Center Aligned Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("HeaderCenterAlign", style); // Body Left Aligned Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); styles.put("BodyLeftAlign", style); // Body Center Aligned Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); styles.put("BodyCenterAlign", style); // Body Left Aligned WrapText Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setWrapText(true); styles.put("BodyLeftAlignWrapText", style); // Body Left Aligned Date Format Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); styles.put("BodyLeftAlignDate", style); // Body Center Aligned Date Format Style style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); styles.put("BodyCenterAlignDate", style); return styles; }
From source file:org.zephyrsoft.sdb2.StatisticsController.java
License:Open Source License
public void exportStatisticsAll(SongsModel songs, File targetExcelFile) { // collect basic data Map<String, Song> songsByUUID = new HashMap<>(); for (Song song : songs) { songsByUUID.put(song.getUUID(), song); }//from w ww . ja v a2s.c o m List<String> months = statistics.getUsedMonths(); // create a new workbook Workbook workbook = new HSSFWorkbook(); // define formats CellStyle integerStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); integerStyle.setDataFormat(df.getFormat("0")); CellStyle textStyle = workbook.createCellStyle(); textStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); CellStyle textBoldStyle = workbook.createCellStyle(); textBoldStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); org.apache.poi.ss.usermodel.Font font = workbook.createFont(); font.setColor(org.apache.poi.ss.usermodel.Font.COLOR_RED); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); textBoldStyle.setFont(font); for (String month : months) { Map<String, Integer> monthStatsByUUID = statistics.getStatisticsForMonth(month); Map<Song, Integer> monthStatsBySong = new TreeMap<>(); for (String uuid : monthStatsByUUID.keySet()) { Song song = songs.getByUUID(uuid); if (song != null) { monthStatsBySong.put(song, monthStatsByUUID.get(uuid)); } else { LOG.info("no song found in database for UUID {}", uuid); } } Sheet sheet = workbook.createSheet(month); Row row = null; int rownum = 0; row = sheet.createRow(rownum); int cellnum = 0; addTextCell(row, cellnum++, textBoldStyle, "Presentation Count"); addTextCell(row, cellnum++, textBoldStyle, "Song Title"); addTextCell(row, cellnum++, textBoldStyle, "Composer (Music)"); addTextCell(row, cellnum++, textBoldStyle, "Author (Text)"); addTextCell(row, cellnum++, textBoldStyle, "Publisher"); addTextCell(row, cellnum++, textBoldStyle, "Copyright Notes"); addTextCell(row, cellnum++, textBoldStyle, "Song Lyrics"); rownum++; for (Song song : monthStatsBySong.keySet()) { row = sheet.createRow(rownum); cellnum = 0; addIntegerCell(row, cellnum++, integerStyle, monthStatsBySong.get(song)); addTextCell(row, cellnum++, textStyle, song.getTitle()); addTextCell(row, cellnum++, textStyle, song.getComposer()); addTextCell(row, cellnum++, textStyle, song.getAuthorText()); addTextCell(row, cellnum++, textStyle, song.getPublisher()); addTextCell(row, cellnum++, textStyle, song.getAdditionalCopyrightNotes()); addTextCell(row, cellnum++, textStyle, song.getLyrics()); rownum++; } for (int i = 0; i < cellnum; i++) { sheet.autoSizeColumn(i); } sheet.createFreezePane(0, 1); } try (FileOutputStream out = new FileOutputStream(targetExcelFile)) { workbook.write(out); out.close(); LOG.info("all statistics exported"); } catch (IOException e) { ErrorDialog.openDialog(null, "Could not export the statistics to:\n" + targetExcelFile.getAbsolutePath() + "\n\nPlease verify that you have write access and the file is not opened by any other program!"); LOG.warn("could not write statistics to file", e); } }
From source file:output.ExcelM3Upgrad.java
/** * create a library of cell styles/*from ww w. ja v a2 s . c om*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); DataFormat df = wb.createDataFormat(); Font font1 = wb.createFont(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(false); styles.put("cell_centered", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(true); styles.put("cell_centered_locked", style); // style = createBorderedStyle(wb); // style.setAlignment(CellStyle.ALIGN_CENTER); // style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); // style.setFillPattern(CellStyle.SOLID_FOREGROUND); // style.setFont(headerFont); // style.setDataFormat(df.getFormat("d-mmm")); // styles.put("header_date", style); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(false); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(true); styles.put("cell_b_centered_locked", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }