List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createDataFormat
@Override
public HSSFDataFormat createDataFormat()
From source file:org.anyframe.logmanager.web.LogManagerController.java
License:Apache License
/** * log data export for excel file type//from w w w .j ava 2 s. c o m * * @param searchCondition * @param model * @param request * @return * @throws Exception */ @RequestMapping(params = "method=xlsExport") public void xlsExport(LogSearchCondition searchCondition, Model model, HttpServletRequest request, HttpServletResponse response) throws Exception { searchCondition.setPageIndex(-1); searchCondition.setCollection(searchCondition.getRepositoryName()); String fileName = null; String sDate = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss", new Locale("ko_KR")); sDate = sdf.format(new Date()); StringBuffer sb = new StringBuffer(); sb.append(searchCondition.getAppName().substring(searchCondition.getAppName().lastIndexOf("/") + 1)); sb.append("_").append(searchCondition.getCollection()).append("_").append(sDate).append(".xls"); fileName = sb.toString(); SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-ddHHmm"); logger.debug("from:{}", searchCondition.getFromDate() + searchCondition.getFromHour() + searchCondition.getFromMinute()); logger.debug("to:{}", searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute()); if (searchCondition.isUseFromDate()) searchCondition.setFromDateTime(dateTimeFormat.parse(searchCondition.getFromDate() + searchCondition.getFromHour() + searchCondition.getFromMinute())); if (searchCondition.isUseToDate()) searchCondition.setToDateTime(dateTimeFormat.parse( searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute())); List<LogDataMap> resultList = service.searchAnalysisLog(searchCondition); response.reset(); response.setContentType("application/x-msexcel;charset=MS949"); // response.setContentType("application/octet-stream"); String userAgent = request.getHeader("User-Agent"); if (userAgent.indexOf("MSIE 5.5") > -1) { response.setHeader("Content-Disposition", "filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\";"); } else if (userAgent.indexOf("MSIE") > -1) { response.setHeader("Content-Disposition", "attachment; filename=\"" + java.net.URLEncoder.encode(fileName, "UTF-8") + "\";"); } else { response.setHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("euc-kr"), "latin1") + "\";"); } response.setHeader("Content-Description", "JSP Generated Data"); response.setHeader("Content-Transfer-Encoding", "binary;"); response.setHeader("Pragma", "no-cache;"); response.setHeader("Expires", "-1;"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(fileName); OutputStream fileOut = null; try { fileOut = response.getOutputStream(); HSSFRow row = null; HSSFRow headerRow = null; HSSFDataFormat df = workbook.createDataFormat(); HSSFCellStyle headerStyle = workbook.createCellStyle(); HSSFFont boldFont = workbook.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(boldFont); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(df.getFormat("yyyy-mm-dd h:mm:ss.000")); dateStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); dateStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle messageStyle = workbook.createCellStyle(); messageStyle.setWrapText(true); messageStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); messageStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell cell; HSSFCell headerCell; short width = 265; Iterator<String> j = null; String key = null; int cellIndex = 0; int listSize = 0; String level = null; Date timestamp = null; String message = null; if (resultList != null) { listSize = resultList.size(); for (int i = 0; i < listSize; i++) { LogDataMap log = (LogDataMap) resultList.get(i); if (i == 0) { headerRow = sheet.createRow(i); // level header sheet.setColumnWidth(0, 7 * width); headerCell = headerRow.createCell(0); HSSFRichTextString headerValue = new HSSFRichTextString("level"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); headerCell = headerRow.createCell(1); // time stamp header sheet.setColumnWidth(1, 24 * width); headerValue = new HSSFRichTextString("timestamp"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); headerCell = headerRow.createCell(2); // message header sheet.setColumnWidth(2, 70 * width); headerValue = new HSSFRichTextString("message"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); } row = sheet.createRow(i + 1); // level level = (String) log.get("level"); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(level); // timestamp timestamp = (Date) log.get("timestamp"); cell = row.createCell(1); cell.setCellStyle(dateStyle); cell.setCellValue(timestamp); // message message = (String) log.get("message"); HSSFRichTextString messageValue = new HSSFRichTextString(message); cell = row.createCell(2); cell.setCellStyle(messageStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(messageValue); cellIndex = 3; j = log.keySet().iterator(); while (j.hasNext()) { key = j.next(); if ("_id".equals(key) || "message".equals(key) || "timestamp".equals(key) || "level".equals(key)) { continue; } //logger.debug("key=" + key); if (i == 0) { sheet.setColumnWidth(cellIndex, 20 * width); headerCell = headerRow.createCell(cellIndex); HSSFRichTextString headerValue = new HSSFRichTextString(key); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); } cell = row.createCell(cellIndex); Object value = log.get(key); if (value instanceof Date) { cell.setCellStyle(dateStyle); cell.setCellValue((Date) value); } else { cell.setCellStyle(style); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((String) log.get(key)); } cellIndex++; } } workbook.write(fileOut); } } catch (Exception e) { throw e; } finally { try { if (fileOut != null) { fileOut.flush(); fileOut.close(); } } catch (IOException ex) { logger.warn(ex.getMessage(), ex); } } }
From source file:org.beangle.commons.transfer.excel.PoiTest.java
License:Open Source License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(0);/*from www . j a v a 2 s . com*/ // Create a cell and put a date value in it. The first cell is not // styled as a date. HSSFCell cell = row.createCell(0); cell.setCellValue(new Date()); // we style the second cell as a date (and time). It is important to // create a new cell style from the workbook // otherwise you can end up modifying the built in style and effecting // not only this cell but other cells. HSSFCellStyle cellStyle = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); cellStyle.setDataFormat(df.getFormat("YYYY-MM-DD HH:MM:SS")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("src/test/resources/workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {//from www .j av a 2 s .c o m monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1)); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.extremecomponents.table.view.ExtendXlsView.java
License:Apache License
private Map initStyles(HSSFWorkbook wb, short fontHeight) { Map result = new HashMap(); HSSFCellStyle titleStyle = wb.createCellStyle(); HSSFCellStyle textStyle = wb.createCellStyle(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFCellStyle numericStyle = wb.createCellStyle(); HSSFCellStyle numericStyleBold = wb.createCellStyle(); HSSFCellStyle moneyStyle = wb.createCellStyle(); HSSFCellStyle moneyStyleBold = wb.createCellStyle(); HSSFCellStyle percentStyle = wb.createCellStyle(); HSSFCellStyle percentStyleBold = wb.createCellStyle(); result.put("titleStyle", titleStyle); result.put("textStyle", textStyle); result.put("boldStyle", boldStyle); result.put("numericStyle", numericStyle); result.put("numericStyleBold", numericStyleBold); result.put("moneyStyle", moneyStyle); result.put("moneyStyleBold", moneyStyleBold); result.put("percentStyle", percentStyle); result.put("percentStyleBold", percentStyleBold); HSSFDataFormat format = wb.createDataFormat(); // Global fonts HSSFFont font = wb.createFont();//from w w w. j a v a 2 s . c om font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setColor(HSSFColor.BLACK.index); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints(fontHeight); HSSFFont fontBold = wb.createFont(); fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontBold.setColor(HSSFColor.BLACK.index); fontBold.setFontName(HSSFFont.FONT_ARIAL); fontBold.setFontHeightInPoints(fontHeight); // Money Style moneyStyle.setFont(font); moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle.setDataFormat(format.getFormat(moneyFormat)); // Money Style Bold moneyStyleBold.setFont(fontBold); moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyleBold.setDataFormat(format.getFormat(moneyFormat)); // Percent Style percentStyle.setFont(font); percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle.setDataFormat(format.getFormat(percentFormat)); // Percent Style Bold percentStyleBold.setFont(fontBold); percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyleBold.setDataFormat(format.getFormat(percentFormat)); // Standard Numeric Style numericStyle.setFont(font); numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Standard Numeric Style Bold numericStyleBold.setFont(fontBold); numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Title Style titleStyle.setFont(font); titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBottomBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setLeftBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setRightBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setTopBorderColor(HSSFColor.BLACK.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Standard Text Style textStyle.setFont(font); textStyle.setWrapText(true); // Standard Text Style boldStyle.setFont(fontBold); boldStyle.setWrapText(true); return result; }
From source file:org.mili.core.text.transformation.ExcelTransformator.java
License:Apache License
/** * Transforms./* www.j a v a 2 s .c o m*/ * * @param from from table * @param params the params * @return the HSSF workbook */ public HSSFWorkbook transform(Table from, Object... params) { if (from.getRowSize() == 0) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFRow r = sheet.createRow(0); HSSFCell c = r.createCell((short) (0)); c.setCellValue("Keine Daten vorhanden !"); return wb; } String fsInt = "#,###,##0"; String fsFloat = "#,###,##0.000"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFFont headFont = wb.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFont(headFont); HSSFCellStyle numberStyle = wb.createCellStyle(); HSSFRow headerRow = sheet.createRow(0); short z = 0; for (int i = 0, n = from.getColSize(); i < n; i++) { Col col = from.getCol(i); HSSFCell headerCell = headerRow.createCell((short) (z)); headerCell.setCellStyle(headStyle); headerCell.setCellValue(col.getName()); z++; } for (int i = 0, n = from.getRowSize(); i < n; i++) { Row row = from.getRow(i); HSSFRow contentRow = sheet.createRow(i + 1); short j = 0; for (int ii = 0, nn = from.getColSize(); ii < nn; ii++) { Col col = from.getCol(ii); Object o = row.getValue(ii); HSSFCell contentCell = contentRow.createCell((short) (j)); String value = o == null ? "" : String.valueOf(o); if (o instanceof Number) { if (o instanceof Integer) { numberStyle.setDataFormat(format.getFormat(fsInt)); contentCell.setCellValue(Integer.parseInt(value)); contentCell.setCellStyle(numberStyle); } else if (o instanceof Float) { numberStyle.setDataFormat(format.getFormat(fsFloat)); contentCell.setCellValue(Float.parseFloat(value)); contentCell.setCellStyle(numberStyle); } } else { contentCell.setCellValue(value); } j++; } } return wb; }
From source file:org.openscada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {/*w w w . j a va 2 s . co m*/ monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } workbook.setRepeatingRowsAndColumns(0, -1, -1, 0, 1); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGenericComponent(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, ComponentExportOptions opt) throws Throwable { Object[] row = null;// w w w .j a v a2s.com Object obj = null; HSSFRow r = null; HSSFCell c = null; HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); if (opt.getCellsContent() != null) for (int i = 0; i < opt.getCellsContent().length; i++) { row = opt.getCellsContent()[i]; r = s.createRow(rownum); for (short j = 0; j < row.length; j++) { c = r.createCell(j); obj = row[j]; if (obj != null) { if (obj instanceof String) { try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(obj.toString()); c.setCellStyle(csText); } else if (obj instanceof BigDecimal || obj instanceof Double || obj instanceof Float || obj.getClass() == Double.TYPE || obj.getClass() == Float.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csDecNum); } else if (obj instanceof Integer || obj instanceof Short || obj instanceof Long || obj.getClass() == Integer.TYPE || obj.getClass() == Short.TYPE || obj.getClass() == Long.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csIntNum); } else if (obj instanceof Boolean) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj.getClass().equals(boolean.class)) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj instanceof Date || obj instanceof java.util.Date || obj instanceof java.sql.Timestamp) { c.setCellValue((java.util.Date) obj); c.setCellStyle(csDateTime); } } else { c.setCellValue(""); c.setCellStyle(csText); } } rownum++; } return rownum; }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGrid(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, GridExportOptions opt) throws Throwable { // declare a row object reference HSSFRow r = null;//w w w. j a v a 2s. com // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"/*opt.getDateFormat()*/)); csDate.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDate.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDate.setBorderRight(HSSFCellStyle.BORDER_THIN); csDate.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTime = wb.createCellStyle(); csTime.setDataFormat(HSSFDataFormat .getBuiltinFormat(exportOptions.getTimeFormat().equals("HH:mm") ? "h:mm" : "h:mm AM/PM")); csTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csTime.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); // prepare vo getters methods... String methodName = null; String attributeName = null; Hashtable gettersMethods = new Hashtable(); Method[] voMethods = opt.getValueObjectType().getMethods(); for (int i = 0; i < voMethods.length; i++) { methodName = voMethods[i].getName(); if (methodName.startsWith("get")) { attributeName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4); if (opt.getExportAttrColumns().contains(attributeName)) gettersMethods.put(attributeName, voMethods[i]); } } Response response = null; int start = 0; Object value = null; Object vo = null; int type; boolean firstRow = true; if (opt.getTitle() != null && !opt.getTitle().equals("")) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getTitle()); c.setCellStyle(csTitle); rownum++; rownum++; } String[] filters = opt.getFilteringConditions(); if (filters != null) { for (int i = 0; i < filters.length; i++) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(filters[i]); rownum++; } rownum++; } do { response = opt.getGridDataLocator().loadData(GridParams.NEXT_BLOCK_ACTION, start, opt.getFilteredColumns(), opt.getCurrentSortedColumns(), opt.getCurrentSortedVersusColumns(), opt.getValueObjectType(), opt.getOtherGridParams()); if (response.isError()) throw new Exception(response.getErrorMessage()); for (int j = 0; j < ((VOListResponse) response).getRows().size(); j++) { if (firstRow) { firstRow = false; // create the first row... r = s.createRow(rownum++); for (short i = 0; i < opt.getExportColumns().size(); i++) { c = r.createCell(i); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getExportColumns().get(i).toString()); c.setCellStyle(csTitle); } for (int k = 0; k < opt.getTopRows().size(); k++) { // create a row for each top rows... vo = opt.getTopRows().get(k); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 0); } } // create a row vo = ((VOListResponse) response).getRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 1); } start = start + ((VOListResponse) response).getRows().size(); if (!((VOListResponse) response).isMoreRows()) break; } while (rownum < opt.getMaxRows()); for (int j = 0; j < opt.getBottomRows().size(); j++) { // create a row for each bottom rows... vo = opt.getBottomRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 2); } return rownum; }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Creates an Excel document with a given column name list, and column data list. * The String objects in the column name list are used as Map keys to look up the corresponding * column header and data. The column data to be exported is a List of Map objects where * the first Map element contains column headers, and the rest has all the column data. * @param workBookName a String object as Excel file name * @param workSheetName a String object as the name of the Excel sheet * @param columnNameList a List of String objects as column names, they usually correspond to entity field names * @param data a List of Map objects to be exported where the first Map element contains column headers, * and the rest has all the column data. * @throws IOException if an error occurs *///from ww w. ja v a2 s . c o m public static void saveToExcel(final String workBookName, final String workSheetName, final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException { if (StringUtils.isEmpty(workBookName)) { throw new IllegalArgumentException("Argument workBookName can't be empty"); } if (StringUtils.isEmpty(workSheetName)) { throw new IllegalArgumentException("Argument workSheetName can't be empty"); } if (columnNameList == null || columnNameList.isEmpty()) { throw new IllegalArgumentException("Argument columnNameList can't be empty"); } // the data list should have at least one element for the column headers if (data == null || data.isEmpty()) { throw new IllegalArgumentException("Argument data can't be empty"); } FileOutputStream fileOut = new FileOutputStream(new File(workBookName)); assert fileOut != null; HSSFWorkbook workBook = new HSSFWorkbook(); assert workBook != null; HSSFSheet workSheet = workBook.createSheet(workSheetName); assert workSheet != null; // create the header row HSSFRow headerRow = workSheet.createRow(0); assert workSheet != null; HSSFFont headerFont = workBook.createFont(); assert headerFont != null; headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.BLACK.index); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); assert headerCellStyle != null; headerCellStyle.setFont(headerFont); // the first data list element should always be the column header map Map<String, Object> columnHeaderMap = data.get(0); if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = headerRow.createCell(i); assert cell != null; cell.setCellStyle(headerCellStyle); Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i)); if (columnHeaderTitle != null) { cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString())); } } } // create data rows // column data starts from the second element if (data.size() > 1) { // Create the style used for dates. HSSFCellStyle dateCellStyle = workBook.createCellStyle(); String dateFormat = "mm/dd/yyyy hh:mm:ss"; HSSFDataFormat hsfDateFormat = workBook.createDataFormat(); short dateFormatIdx = hsfDateFormat.getFormat(dateFormat); if (dateFormatIdx == -1) { Debug.logWarning("Date format [" + dateFormat + "] could be found or created, try one of the pre-built instead:" + HSSFDataFormat.getBuiltinFormats(), MODULE); } dateCellStyle.setDataFormat(dateFormatIdx); for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) { Map<String, Object> rowDataMap = data.get(dataRowIndex); if (rowDataMap == null) { continue; } HSSFRow dataRow = workSheet.createRow(dataRowIndex); assert dataRow != null; for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = dataRow.createCell(i); assert cell != null; Object cellData = rowDataMap.get(columnNameList.get(i)); if (cellData != null) { // Note: dates are actually numeric values in Excel and so the cell need to have // a special style set so it actually displays as a date if (cellData instanceof Calendar) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Calendar) cellData); } else if (cellData instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) cellData); } else if (cellData instanceof BigDecimal) { cell.setCellValue(((BigDecimal) cellData).doubleValue()); } else if (cellData instanceof Double) { cell.setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Integer) { cell.setCellValue(((Integer) cellData).doubleValue()); } else if (cellData instanceof BigInteger) { cell.setCellValue(((BigInteger) cellData).doubleValue()); } else { cell.setCellValue(new HSSFRichTextString(cellData.toString())); } } } } } // auto size the column width if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { workSheet.autoSizeColumn(i); } } // create the Excel file workBook.write(fileOut); fileOut.close(); }
From source file:org.orbeon.oxf.processor.converter.FromXLSConverter.java
License:Open Source License
public ProcessorOutput createOutput(String name) { ProcessorOutput output = new ProcessorOutputImpl(FromXLSConverter.this, name) { public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) { try { // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); // Generate XML from Excel file final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); final org.dom4j.Document d = extractFromXLS(bais); final DOMGenerator domGenerator = new DOMGenerator(d, "from xls output", DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext); domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver); } catch (IOException e) { throw new OXFException(e); }/*w ww.ja va2 s .c om*/ } private Document extractFromXLS(InputStream inputStream) throws IOException { // Create workbook HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); // Create document final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook"); final NonLazyUserDataDocument resultDocument = new NonLazyUserDataDocument(root); // Add elements for each sheet for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); final Element element = new NonLazyUserDataElement("sheet"); resultDocument.getRootElement().add(element); // Go though each cell XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (targetXPath != null) { int cellType = cell.getCellType(); String value = null; switch (cellType) { case HSSFCell.CELL_TYPE_STRING: case HSSFCell.CELL_TYPE_BLANK: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: double doubleValue = cell.getNumericCellValue(); if (((double) ((int) doubleValue)) == doubleValue) { // This is an integer value = Integer.toString((int) doubleValue); } else { // This is a floating point number value = XMLUtils.removeScientificNotation(doubleValue); } break; } if (value == null) throw new OXFException("Unkown cell type " + cellType + " for XPath expression '" + targetXPath + "'"); addToElement(element, targetXPath, value); } } }); } return resultDocument; } private void addToElement(Element element, String xpath, String value) { StringTokenizer elements = new StringTokenizer(xpath, "/"); while (elements.hasMoreTokens()) { String name = elements.nextToken(); if (elements.hasMoreTokens()) { // Not the last: try to find sub element, otherwise create Element child = element.element(name); if (child == null) { child = new NonLazyUserDataElement(name); element.add(child); } element = child; } else { // Last: add element, set content to value Element child = new NonLazyUserDataElement(name); child.add(Dom4jUtils.createText(value)); element.add(child); } } } }; addOutput(name, output); return output; }