Jxl导出Excel实现 public String CreateToExcel(String params) { try { params = java.net.URLDecoder.decode(params, "UTF-8"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } log.info("params:" + params); AssistantAble assistant = new AssistantImpl(); String classID = com.system.Utils.getParameter("classId", params); String listId = Utils.getParameter("listId", params); String queryField = com.system.Utils.getParameter("queryField", params); FormListImpl formList = (FormListImpl) XFormFactory.getXFormModel().getFormList(listId); String condition = assistant.getSQLCondition(params, formList); log.info("queryField:" + queryField); log.info("condition:" + condition); queryField = queryField.substring(0, queryField.lastIndexOf(",")); String[] queryFields = queryField.split(","); int ClassId = Integer.parseInt(classID); SysModel sysmodel = ModelFactory.getSysmodel(); MdpClass mdpClass = sysmodel.getMdpClassByClassID(ClassId); // 获取表名称 String tableName = mdpClass.getName(); // 获取表的描述信息 String description = mdpClass.getDescription(); // 获取各个字段的名称 List<MdpAttributeImpl> list = mdpClass.getAllMdpAttributes(); Map<String,MdpAttributeImpl> map = new HashMap<String, MdpAttributeImpl>(); for(MdpAttributeImpl mdp : list){ map.put(mdp.getName().toUpperCase(), mdp); } Workbook wb = null; WritableWorkbook book = null; WritableSheet sheet = null; HttpServletRequest req =mc.getHttpServletRequest(); String fileName = ""; //String filePath = "C://print//"+description+".xls"; String filePath = req.getRealPath("excels"); try { fileName =UUID.randomUUID().toString()+".xls"; File myFilePath = new File( filePath+"/"+fileName); // 1.首先定义个workbook book = wb.createWorkbook(myFilePath); // 2.定义个sheet sheet = book.createSheet("第一页", 0); // 设置标题 sheet.mergeCells(0, 0, queryFields.length - 1, 0); WritableCellFormat format = getCellFormat(14); format.setWrap(true); //设置自动换行 Label label = new Label(0, 0, description, format); sheet.addCell(label); // 插入标题 for (int i = 0; i < queryFields.length; i++) { WritableCell wc = null; // 以第一行所有的列为模板, wc = addCellWithValue(i, 1, map.get(queryFields[i].toUpperCase()).getDescription(),"string"); sheet.addCell(wc); } String sql = "select " + queryField + " from " + tableName + " where " + condition; log.info("导出查询sql:" + sql); //得到数据 ArrayList<Map<String,String>> resultMap = getGridList(ClassId, sql); // 插入数据. for(int m = 0; m < resultMap.size(); m++){ Map<String,String> data = resultMap.get(m); for(int n = 0; n < queryFields.length; n ++){ String key = queryFields[n].toUpperCase(); WritableCell wc = null; Object fieldValue = data.get(key); String dataType = map.get(key).getDataType(); int referenceType = map.get(key).getReferenceType(); //当为常量的时候 if(referenceType == 2){ String constName = map.get(key).getReference().getReferenceTable(); //log.info("常量键值:key:" + fieldValue); if(fieldValue != null && !"".equals(fieldValue)){ fieldValue = sysmodel.getMdpConstantDisplayString(constName, fieldValue.toString()); } //log.info("常量键值:value:" + fieldValue); } //两表外连接 if(referenceType == 1) { if (map.get(key).getReference() != null) { String referenceTable=map.get(key).getReference().getReferenceTable(); String displayName=map.get(key).getReference().getDisplayName(); String storeName=map.get(key).getReference().getStoreName(); if(fieldValue != null && !"".equals(fieldValue)){ fieldValue = getReferenceShow(fieldValue.toString(),Integer.valueOf(referenceTable),displayName, storeName); } } } wc = addCellWithValue(n, 2 + m, fieldValue, dataType); sheet.addCell(wc); } } book.write(); book.close(); } catch (Exception e) { e.printStackTrace(); } return fileName; } /** * 设置XLS单元格的样式,上下左右居中,带黑色边框 * params ps : 字体大小 * @return 单元格的样式 * 2011-2-21 */ public WritableCellFormat getCellFormat(int ps){ //设置单元格字体为12号字体 WritableFont font = new WritableFont(WritableFont.TIMES, ps); WritableCellFormat format = new WritableCellFormat(font); try { format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中 format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中 format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 } catch (WriteException e) { e.printStackTrace(); } return format; } public WritableCell addCellWithValue(int col, int row, Object value, String dataType) { WritableCell wc = null; WritableCellFormat format = getCellFormat(8); try { format.setWrap(true); //设置自动换行 } catch (WriteException e1) { e1.printStackTrace(); } // 判断数据是否为STRING类型,是用LABLE形式插入,否则用NUMBER形式插入 if (value == null) { wc = new jxl.write.Blank(col, row, format); } else if (dataType instanceof String) { jxl.write.Label label = new jxl.write.Label(col, row, value.toString(),format); wc = label; } else if (value instanceof Date) { jxl.write.DateFormat df = new jxl.write.DateFormat("YYYY-MM-DD hh:mm:ss"); jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df); try { wcfDF.setWrap(true); } catch (WriteException e) { e.printStackTrace(); } jxl.write.DateTime labelDT = new jxl.write.DateTime(col, row,new java.util.Date(), wcfDF); wc = labelDT; } else if (value instanceof Double) { wc = new jxl.write.Number(col, row, new Double(value.toString()).doubleValue()); } else if (value instanceof Number) { jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); try { wcfN.setWrap(true); } catch (WriteException e) { e.printStackTrace(); } jxl.write.Number labelNF = new jxl.write.Number(col, row, new Double(value.toString()), wcfN); wc = labelNF; } return wc; } public ArrayList<Map<String,String>> getGridList(int classid, String sql) { SysModel sysmodel = ModelFactory.getSysmodel(); SysPersistence persistence = PersistenceFactory.getInstance(sysmodel); ArrayList<Map<String,String>> list = null; try { list = persistence.getSearchResultToMap(classid, sql); log.info(list.size()); } catch (Exception e) { log.error("获取数据失败!", e); } return list; } private String getReferenceShow(String value, int classid, String displayName, String storeName) throws PersistenceException { SysModel sysmodel = ModelFactory.getSysmodel(); SysPersistence persistence = PersistenceFactory.getInstance(sysmodel); String sql = "select " + displayName + " from " + sysmodel.getMdpClassByClassID(classid).getName() + " where " + storeName + " = '" + value + "'"; ArrayList<Map<String, String>> list = persistence.getSearchResultToMap( classid, sql); String result = ""; if (list.size() > 0) { result = list.get(0).get(displayName.toUpperCase()); } return result; } }