List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:com.b510.excel.client.LoanCalculator.java
License:Apache License
/** * cell styles used for formatting calendar sheets */// w ww. j a v a 2s . c om private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("formula_i", style); return styles; }
From source file:com.b510.excel.client.TimesheetDemo.java
License:Apache License
/** * Create a library of cell styles//from www. j a v a2 s . co m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:com.bc.util.XlsxExporter.java
public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) { try {/*from w w w . j av a 2 s . c o m*/ if (output.exists()) { log.info(output.getName() + " exists. Deleting"); output.delete(); log.info("Deleted " + output.getName()); } log.info("Creating xlsx file..."); FileOutputStream fos = new FileOutputStream(output); XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet("Order"); CellStyle style = workBook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.ALIGN_FILL); Font font = workBook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date", "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped", "Discount", "Extended Price" }; log.info("Creating header row & columns"); Row row = sheet.createRow(0); for (int i = 0; i < columnHeaders.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnHeaders[i]); cell.setCellStyle(style); sheet.setColumnWidth(i, 4500); } sheet.setColumnWidth(9, 13500); log.info("Writing " + items.size() + " records"); XSSFDataFormat decimalFormat = workBook.createDataFormat(); CellStyle dstyle = workBook.createCellStyle(); dstyle.setDataFormat(decimalFormat.getFormat("0.00")); int i = 1; for (CustomerOrderItem orderItem : items) { Row drow = sheet.createRow(i++); Hibernate.initialize(order.getCustomerOrderItems()); String strValue; Float floatValue; Integer intVal; Cell cInvoice = drow.createCell(0); strValue = order.getInvoiceNumber(); if (strValue == null) strValue = ""; cInvoice.setCellValue(order.getInvoiceNumber()); Cell cSalesman = drow.createCell(1); strValue = order.getSalesman(); if (strValue == null) strValue = ""; cSalesman.setCellValue(strValue); Cell cCustomerName = drow.createCell(2); strValue = order.getCustomer().getCompanyName(); if (strValue == null) strValue = ""; cCustomerName.setCellValue(strValue); Cell cCustomerCode = drow.createCell(3); strValue = order.getCustomerCode(); if (strValue == null) strValue = ""; cCustomerCode.setCellValue(strValue); Cell cPo = drow.createCell(4); strValue = order.getPoNumber(); if (strValue == null) strValue = ""; cPo.setCellValue(strValue); Cell cShipDate = drow.createCell(5); Date d = order.getShipDate(); if (d == null) cShipDate.setCellValue(""); else cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Cell cPostDate = drow.createCell(6); d = order.getPostDate(); if (d == null) cPostDate.setCellValue(""); else cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Hibernate.initialize(orderItem.getInventoryItem()); InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem(); if (item != null) { Cell cIsbn = drow.createCell(7); strValue = item.getIsbn(); if (strValue == null) strValue = ""; cIsbn.setCellValue(strValue); Cell cIsbn13 = drow.createCell(8); strValue = item.getIsbn13(); if (strValue == null) strValue = ""; cIsbn13.setCellValue(strValue); Cell cTitle = drow.createCell(9); strValue = item.getTitle(); if (strValue == null) strValue = ""; cTitle.setCellValue(strValue); Cell cListPrice = drow.createCell(10); floatValue = item.getListPrice(); cListPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cListPrice.setCellValue(floatValue); Cell cPrice = drow.createCell(11); floatValue = item.getSellingPrice(); cPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cPrice.setCellValue(floatValue); } Cell cQuantity = drow.createCell(12); intVal = orderItem.getQuantity(); log.info("Quantity : " + intVal); if (intVal == null) intVal = 0; cQuantity.setCellValue(intVal); Cell cShipped = drow.createCell(13); intVal = orderItem.getFilled(); log.info("Shipped QTY : " + intVal); if (intVal == null) intVal = 0; cShipped.setCellValue(intVal); Cell cDiscount = drow.createCell(14); cDiscount.setCellStyle(dstyle); floatValue = orderItem.getDiscount(); if (floatValue == null) floatValue = 0.0f; cDiscount.setCellValue(floatValue); Cell cExtendedPrice = drow.createCell(15); cExtendedPrice.setCellStyle(dstyle); BigDecimal dValue = orderItem.getTotalPrice(); if (dValue == null) dValue = BigDecimal.ZERO; cExtendedPrice.setCellValue(dValue.doubleValue()); } workBook.write(fos); log.info("Finished writing data, closing..."); fos.close(); log.info("Completed exporting data to " + output.getAbsolutePath()); } catch (Exception ex) { Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java
License:Apache License
private void populateHyperlinkCell(Record record, FieldDef fieldDef, Cell cell) throws Exception { String cellValue = record.getHyperlinkFieldValue(fieldDef.getName()).getDisplayText(); cell.setCellValue(cellValue);//from w ww. j a va 2 s .c o m // cell style for hyperlinks // by default hyperlinks are blue and underlined CellStyle hlink_style = workbook.createCellStyle(); Font hlink_font = workbook.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // Make it a hyperlink CreationHelper createHelper = workbook.getCreationHelper(); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(record.getHyperlinkFieldValue(fieldDef.getName()).getHyperlinkText()); cell.setHyperlink(link); cell.setCellStyle(hlink_style); }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloProveedor(Workbook wb) { CellStyle estiloCelda = wb.createCellStyle(); Font fuente = wb.createFont(); fuente.setFontName("Calibri"); fuente.setBold(true);//w w w. j av a 2s .c o m fuente.setFontHeightInPoints((short) 32); fuente.setColor(IndexedColors.ROYAL_BLUE.getIndex()); estiloCelda.setFont(fuente); estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); estiloCelda.setAlignment(HorizontalAlignment.CENTER); return estiloCelda; }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloColumnas(Workbook wb, int tipo) { CellStyle estiloCelda = wb.createCellStyle(); Font fuente = wb.createFont(); if (tipo == COLUMNA) { estiloCelda.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); fuente.setBold(true);//ww w.j a v a 2 s. c om } if (tipo == SURTIDO) { fuente.setColor(IndexedColors.WHITE.getIndex()); estiloCelda.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex()); estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); } fuente.setFontName("Calibri"); fuente.setFontHeightInPoints((short) 11); estiloCelda.setFont(fuente); estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); estiloCelda.setAlignment(HorizontalAlignment.CENTER); if (tipo == COLUMNA) { estiloCelda.setBorderBottom(BorderStyle.MEDIUM); estiloCelda.setBorderTop(BorderStyle.MEDIUM); estiloCelda.setBorderLeft(BorderStyle.MEDIUM); estiloCelda.setBorderRight(BorderStyle.MEDIUM); } else { estiloCelda.setBorderBottom(BorderStyle.THIN); estiloCelda.setBorderTop(BorderStyle.THIN); estiloCelda.setBorderLeft(BorderStyle.THIN); estiloCelda.setBorderRight(BorderStyle.THIN); } return estiloCelda; }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloTotales(Workbook wb) { CellStyle estiloCelda = wb.createCellStyle(); Font fuente = wb.createFont(); estiloCelda.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex()); estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); fuente.setFontName("Calibri"); fuente.setBold(true);/*ww w . j a v a 2 s . c om*/ fuente.setFontHeightInPoints((short) 14); fuente.setColor(IndexedColors.WHITE.getIndex()); estiloCelda.setFont(fuente); estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); estiloCelda.setAlignment(HorizontalAlignment.CENTER); estiloCelda.setBorderBottom(BorderStyle.MEDIUM); estiloCelda.setBorderTop(BorderStyle.MEDIUM); estiloCelda.setBorderLeft(BorderStyle.MEDIUM); estiloCelda.setBorderRight(BorderStyle.MEDIUM); return estiloCelda; }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloCuadro(Workbook wb, int tipo) { CellStyle estiloCelda = wb.createCellStyle(); Font fuente = wb.createFont(); if (tipo == AMARILLO) { estiloCelda.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); }//from www . java2s. c om fuente.setFontName("Calibri"); fuente.setBold(true); fuente.setFontHeightInPoints((short) 12); estiloCelda.setFont(fuente); estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); estiloCelda.setAlignment(HorizontalAlignment.CENTER); estiloCelda.setBorderBottom(BorderStyle.THIN); estiloCelda.setBorderTop(BorderStyle.THIN); estiloCelda.setBorderLeft(BorderStyle.THIN); estiloCelda.setBorderRight(BorderStyle.THIN); return estiloCelda; }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloHeader(Workbook wb, int tipo) { Font fuente = wb.createFont(); CreationHelper createHelper = wb.getCreationHelper(); fuente.setFontName("Calibri"); fuente.setBold(true);/*from ww w .j a va 2 s.co m*/ if (tipo == TITULO || tipo == SUCURSAL || tipo == LABEL || tipo == FECHA) { fuente.setFontHeightInPoints((short) 16); } else if (tipo == USUARIO) { fuente.setFontHeightInPoints((short) 20); } CellStyle estiloCelda = wb.createCellStyle(); estiloCelda.setFont(fuente); if (tipo == TITULO || tipo == USUARIO || tipo == LABEL || tipo == FECHA) { estiloCelda.setFillForegroundColor(IndexedColors.WHITE.getIndex()); } else if (tipo == SUCURSAL) { estiloCelda.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); } estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); if (tipo == LABEL || tipo == FECHA) { estiloCelda.setWrapText(false); } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) { estiloCelda.setWrapText(true); } estiloCelda.setAlignment(HorizontalAlignment.CENTER); if (tipo == LABEL || tipo == FECHA) { estiloCelda.setVerticalAlignment(VerticalAlignment.BOTTOM); } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) { estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); } if (tipo == SUCURSAL) { estiloCelda.setBorderBottom(BorderStyle.MEDIUM); estiloCelda.setBorderTop(BorderStyle.MEDIUM); estiloCelda.setBorderLeft(BorderStyle.MEDIUM); estiloCelda.setBorderRight(BorderStyle.MEDIUM); } if (tipo == FECHA) { estiloCelda.setDataFormat(createHelper.createDataFormat().getFormat("dd \"de\" mmmm \"de\" yyyy")); } return estiloCelda; }
From source file:com.company.et.service.XlsService.java
private static void setCellStyle(Workbook wb, Sheet s, Cell cell) { CellStyle cellStyle = wb.createCellStyle(); // if (cell.getStringCellValue().equals("??")) { // s.autoSizeColumn(0,true); // }/*from w ww .j av a 2s .c om*/ cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); Font font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); }