List of usage examples for org.apache.poi.ss.usermodel Workbook createFont
Font createFont();
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>//from www.j a v a 2 s .c o m * 1. : POI UTIL * 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> * </pre> * * @method Name : createWorkBook * @param workbook, sheetName, list, colNames * @return Workbook * @throws Exception * */ @SuppressWarnings("deprecation") public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list, List<Map<String, Object>> colNames) throws Exception { Row row; Cell cell; /* ? ? START */ /* Workbook workbook = new Workbook(); */ CellStyle titleStyle = workbook.createCellStyle(); CellStyle cellStyle = workbook.createCellStyle(); CellStyle contentStyle = workbook.createCellStyle(); CellStyle contentStyle_2 = workbook.createCellStyle(); /* ? */ /* ? ? */ Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 13); titleFont.setFontName("? "); /* ? */ Font colNameFont = workbook.createFont(); colNameFont.setFontHeightInPoints((short) 10); colNameFont.setFontName("? "); /* ? */ Font contentFont = workbook.createFont(); /* ? ? ? */ titleStyle.setFont(titleFont); /* ? / ? ? */ cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */ cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setFont(colNameFont); /* ? / ? */ contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); /* ? / ? */ contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT); contentStyle_2.setFont(contentFont); /* ? */ Sheet sheet = workbook.createSheet(sheetName); /* ?? */ int sheet1_row = 0; List<Map<String, Object>> colList = colNames; /* ? START */ row = sheet.createRow(sheet1_row); for (int i = 0; i < colList.size(); i++) { cell = row.createCell(i); cell.setCellValue(String.valueOf(colList.get(i).get("item" + i))); cell.setCellStyle(cellStyle); } sheet1_row++; /* ? END */ for (Map<String, Object> map : list) { row = sheet.createRow(sheet1_row); for (int j = 0; j < colList.size(); j++) { cell = row.createCell(j); cell.setCellStyle(contentStyle); if (null != map.get(colList.get(j).get("item" + j))) { cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j)))); } else { cell.setCellValue(""); } /* ? ?? */ sheet.autoSizeColumn((short) j); sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312); } sheet1_row++; } return workbook; }
From source file:com.ocs.dynamo.ui.composite.table.export.TableExportActionHandlerTest.java
License:Apache License
@Test public void testExportWithCustomCellStyle() throws IOException { List<EntityModel<?>> models = new ArrayList<>(); models.add(entityModelFactory.getModel(Person.class)); handler = new TableExportActionHandler(ui, entityModelFactory, models, messageService, REPORT_TITLE, columnIds, true, new CustomCellStyleGenerator() { private CellStyle cellStyle; private CellStyle bdStyle; @Override//ww w .j a v a 2 s . c o m public CellStyle getCustomCellStyle(Workbook workbook, Object propId, Object value, AttributeModel attributeModel) { if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); Font font = workbook.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); cellStyle.setFont(font); } if (bdStyle == null) { DataFormat format = workbook.createDataFormat(); bdStyle = workbook.createCellStyle(); bdStyle.setAlignment(CellStyle.ALIGN_RIGHT); bdStyle.setBorderBottom(CellStyle.BORDER_THIN); bdStyle.setBorderTop(CellStyle.BORDER_THIN); bdStyle.setBorderLeft(CellStyle.BORDER_THIN); bdStyle.setBorderRight(CellStyle.BORDER_THIN); bdStyle.setDataFormat(format.getFormat("#,##0.00")); Font font = workbook.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); bdStyle.setFont(font); } if ("name".equals(propId)) { return cellStyle; } else if ("percentage".equals(propId)) { return bdStyle; } return null; } }); handler.handleAction(handler.getActions(null, null)[0], getTable(), null); byte[] bytes = captureSave(); Workbook wb = importer.createWorkbook(bytes); Assert.assertEquals("Bas, Bob", wb.getSheetAt(0).getRow(2).getCell(0).getStringCellValue()); Font font = wb.getFontAt(wb.getSheetAt(0).getRow(2).getCell(0).getCellStyle().getFontIndex()); Assert.assertEquals(IndexedColors.BLUE.getIndex(), font.getColor()); Assert.assertEquals("Patrick", wb.getSheetAt(0).getRow(3).getCell(0).getStringCellValue()); Assert.assertEquals(35, wb.getSheetAt(0).getRow(2).getCell(1).getNumericCellValue(), 0.001); Assert.assertEquals(44, wb.getSheetAt(0).getRow(3).getCell(1).getNumericCellValue(), 0.001); // totals must be summed up Assert.assertEquals(79, wb.getSheetAt(0).getRow(4).getCell(1).getNumericCellValue(), 0.001); // percentage Assert.assertEquals(0.12, wb.getSheetAt(0).getRow(2).getCell(3).getNumericCellValue(), 0.001); Assert.assertEquals(0.15, wb.getSheetAt(0).getRow(3).getCell(3).getNumericCellValue(), 0.001); }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
private void setCellBorder(Workbook wb, Cell cell) { CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // Create a new font and alter it. Font font = wb.createFont(); font.setFontHeightInPoints((short) 9); font.setFontName("Georgia"); style.setFont(font);/*w w w .j av a 2 s . co m*/ style.setWrapText(true); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_LEFT); cell.setCellStyle(style); cell.setCellType(Cell.CELL_TYPE_STRING); }
From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body.//w w w . ja v a 2s. co m */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle(dictionaryService) != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(dictionaryService); descriptions[i] = pd.getDescription(dictionaryService); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else if ("odf".equals(format) || "ods".equals(format)) { try { SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument(); // Add the header row Table sheet = odf.appendSheet("Export"); org.odftoolkit.simple.table.Row hr = sheet.appendRow(); // TODO // Have the contents populated // TODO // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); odf.save(baos); model.put(MODEL_ODF, baos.toByteArray()); } catch (Exception e) { throw new WebScriptException("Error creating ODF file", e); } } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); sheet.createFreezePane(0, 1); Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:com.runwaysdk.dataaccess.io.ExcelExporter.java
License:Open Source License
/** * Simple constructor that sets up the workbook *///w w w. j a v a 2 s .c o m public ExcelExporter(Workbook workbook) { this.workbook = workbook; this.listeners = new LinkedList<ExcelExportListener>(); this.sheets = new LinkedList<ExcelExportSheet>(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); this.boldStyle = workbook.createCellStyle(); this.boldStyle.setFont(font); }
From source file:com.saba.CalendarDemo.java
License:Apache License
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;/*ww w.j a va2s. c o m*/ Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setFont(titleFont); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("title", style); Font itemFontLeft = wb.createFont(); itemFontLeft.setFontHeightInPoints((short) 11); itemFontLeft.setFontName("Trebuchet MS"); itemFontLeft.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(itemFontLeft); styles.put("item_left", style); Font itemFontRight = wb.createFont(); itemFontRight.setFontHeightInPoints((short) 10); itemFontRight.setFontName("Trebuchet MS"); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(itemFontRight); styles.put("item_right", style); return styles; }
From source file:com.saba.CalendarDemo.java
License:Apache License
/** * createHeaderStyle : /*from www. ja v a 2 s.c o m*/ * Header row setting for sheet * @param wb * @return */ private static CellStyle createHeaderStyleForAward(Workbook wb) { CellStyle headerStyle; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle = createBorderedStyle(wb); headerStyle.setAlignment(CellStyle.ALIGN_LEFT); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); return headerStyle; }
From source file:com.seer.datacruncher.profiler.spring.ExporterController.java
License:Open Source License
public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String type = CommonUtil.notNullValue(request.getParameter("exportaction")); String columns = CommonUtil.notNullValue(request.getParameter("exportcolumns")); String data = CommonUtil.notNullValue(request.getParameter("exportdata")); if (type.equals("csv")) { PrintWriter out = response.getWriter(); response.setContentType("application/csv"); response.setHeader("content-disposition", "attachment;filename=analysis_data.csv"); // set the file // name to // whatever // required.. out.println(columns.replace("&&&&&", ",")); for (String strData : data.split("@@@@@")) { out.println(strData.replace("&&&&&", ",")); }//from w w w. jav a 2 s . c o m out.flush(); out.close(); } else if (type.equals("xml")) { PrintWriter out = response.getWriter(); response.setContentType("text/xml"); response.setHeader("content-disposition", "attachment;filename=analysis_data.xml"); // set the file // name to // whatever // required.. try { StringBuffer xml = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); xml.append("<table><header>"); String colArr[] = columns.split("&&&&&"); for (String col : colArr) { xml.append("<columnName>" + col + "</columnName>"); } xml.append("</header>"); for (String strData : data.split("@@@@@")) { xml.append("<row>"); int ind = 0; for (String val : strData.split("&&&&&")) { xml.append("<" + colArr[ind] + ">" + val + "</" + colArr[ind] + "/>"); ind++; } xml.append("</row>"); } xml.append("</table>"); out.print(xml.toString()); } catch (Exception e) { e.printStackTrace(); } out.flush(); out.close(); } else if (type.equals("excel")) { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=analysis_data.xls"); Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); String colArr[] = columns.split("&&&&&"); short ind = 0; CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); Row row = sheet.createRow(0); for (String col : colArr) { Cell cell = row.createCell(ind); cell.setCellValue(col); cell.setCellStyle(style); ind++; } ind = 1; for (String strData : data.split("@@@@@")) { Row valRow = sheet.createRow(ind); short cellInd = 0; for (String val : strData.split("&&&&&")) { valRow.createCell(cellInd).setCellValue(val); cellInd++; } ind++; } // Write the output to a file OutputStream resOout = response.getOutputStream(); wb.write(resOout); resOout.close(); } return null; }
From source file:com.shiyq.poi.HSSFTest.java
public static boolean createExcel(String excelName) { boolean created = false; Workbook wb = new HSSFWorkbook(); Font font = wb.createFont(); font.setBold(true);/*from w w w . j a v a2 s . c om*/ CellStyle headStyle = wb.createCellStyle(); headStyle.setFont(font); Sheet sheet = wb.createSheet("20165???"); String[] head = { "??", "?", "??", "???", "????", "????", "?", "??", "", "", "??" }; String[] code = { "card_no", "card_type", "spread_time", "spread_emp_no", "spread_emp_name", "owner_name", "plate", "blance", "start_time", "end_time", "month_money" }; List<Map<String, Object>> list = setList(); setSheet(sheet, list, head, headStyle, code); // int startRow = 4; int endRow = 8; int startColumn = head.length + 4; int endColumn = head.length + 8; String describe = "1?" + (new Date().toString()) + "\n"; describe += "2.\n"; describe += "3.?2016-4-42016-5-4"; CellStyle descStyle = wb.createCellStyle(); descStyle.setAlignment(CellStyle.ALIGN_LEFT); descStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP); setSheet(sheet, startRow, endRow, startColumn, endColumn, describe, descStyle); Sheet sheet1 = wb.createSheet("20166???"); setSheet(sheet1, list, head, headStyle, code); setSheet(sheet1, startRow, endRow, startColumn, endColumn, describe, descStyle); try { try (FileOutputStream fileOut = new FileOutputStream(excelName)) { wb.write(fileOut); created = true; } } catch (FileNotFoundException ex) { Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex); } return created; }
From source file:com.siemens.sw360.exporter.ExcelExporter.java
License:Open Source License
/** * Create header style, same has cell style but with bold font */// www . ja v a 2 s . c o m private static CellStyle createHeaderStyle(Workbook workbook) { CellStyle headerCellStyle = createCellStyte(workbook); Font font = workbook.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(font); return headerCellStyle; }