首页 > 代码库 > MVC模式下基于SSH三大框架的java web项目excel表格的导出(不依赖另外的jar包)
MVC模式下基于SSH三大框架的java web项目excel表格的导出(不依赖另外的jar包)
最近工作中碰到了需要将web页面的表格内容导出到excel表格并下载到本地的需求。以下是在利用网上资源、与同事探讨下，完成的代码。
- 首先我们需要有定义好的实体类。以下是截取了项目中用到的部分代码。
1 public class QyggDocuments implements java.io.Serializable { 2 3 private static final long serialVersionUID = -2543382529255041149L; 4 5 private String id; //主键id 6 private String fileName; //文件名称 7 private String fileContentType; //文件类型 8 private String filePath; //文件路径 9 private String fileSize; //文件大小 10 private String commonId; //外键(总任务主键或者企业任务主键) 11 private String reportUserCode; //填报人编码 12 private String reportUserName; //填报人名称 13 private String entName; //企业名称 14 15 // Constructors 16 17 /** default constructor */ 18 public QyggDocuments() { 19 } 20 21 /** minimal constructor */ 22 public QyggDocuments(String id) { 23 this.id = id; 24 } 25 26 /** full constructor */ 27 public QyggDocuments(String id, String fileName, String fileContentType, String filePath, String fileSize, String commonId, 28 String reportUserCode, String reportUserName, String entName) { 29 this.id = id; 30 this.fileName = fileName; 31 this.fileContentType = fileContentType; 32 this.filePath = filePath; 33 this.fileSize = fileSize; 34 this.commonId = commonId; 35 this.reportUserCode = reportUserCode; 36 this.reportUserName = reportUserName; 37 this.entName = entName; 38 } 39 40 // Property accessors 41 42 public String getId() { 43 return this.id; 44 } 45 46 public void setId(String id) { 47 this.id = id; 48 } 49 50 public String getFileName() { 51 return this.fileName; 52 } 53 54 public void setFileName(String fileName) { 55 this.fileName = fileName; 56 } 57 58 public String getFileContentType() { 59 return this.fileContentType; 60 } 61 62 public void setFileContentType(String fileContentType) { 63 this.fileContentType = fileContentType; 64 } 65 66 public String getFilePath() { 67 return this.filePath; 68 } 69 70 public void setFilePath(String filePath) { 71 this.filePath = filePath; 72 } 73 74 public String getFileSize() { 75 return this.fileSize; 76 } 77 78 public void setFileSize(String fileSize) { 79 this.fileSize = fileSize; 80 } 81 82 public String getCommonId() { 83 return this.commonId; 84 } 85 86 public void setCommonId(String commonId) { 87 this.commonId = commonId; 88 } 89 90 public String getReportUserCode() { 91 return this.reportUserCode; 92 } 93 94 public void setReportUserCode(String reportUserCode) { 95 this.reportUserCode = reportUserCode; 96 } 97 98 public String getReportUserName() { 99 return this.reportUserName; 100 } 101 102 public void setReportUserName(String reportUserName) { 103 this.reportUserName = reportUserName; 104 } 105 106 public String getEntName() { 107 return this.entName; 108 } 109 110 public void setEntName(String entName) { 111 this.entName = entName; 112 } 113 114 }
- 其次，在dao、daoImpl、service、serviceImpl各层加入相应的获取对象的方法。本文只列举出service层代码，dao层请自行编写。
service
1 public interface QyggReportService { 2 /** 3 * 查询附件 4 * @param commonId 5 * @return 6 */ 7 public QyggDocuments queryDocuments(String commonId); 8 }
serviceImpl
1 public class QyggReportServiceImpl implements QyggReportService { 2 @Autowired 3 private IBaseDAO baseDAO; 4 /** 5 * 查询附件 6 * @param commonId 7 * @return 8 */ 9 @SuppressWarnings("unchecked") 10 public QyggDocuments queryDocuments(String commonId) { 11 StringBuffer hql = new StringBuffer(); 12 hql.append(" from QyggDocuments A where A.commonId = ? "); 13 List<QyggDocuments> list = baseDAO.getObjectByCondtions(hql.toString(), new Object[]{commonId}, "A", QyggDocuments.class); 14 if(!CommonUtil.isNullOrSizeZero(list)){ 15 return list.get(0); 16 } 17 return new QyggDocuments(); 18 } 19 }
下面是以上代码用到的CommonUtil的源码
1 public class CommonUtil { 2 /** 3 * 判断集合是否为空,或者长度为0 4 * 5 * @param coll 6 * @return 集合为Null，或者长度为0,返回 true;否则返回false 7 */ 8 @SuppressWarnings("unchecked") 9 public static boolean isNullOrSizeZero(Collection coll) { 10 if (coll == null || coll.size() == 0) 11 return true; 12 return false; 13 } 14 }
- 接下来是Action源码
1 public class ReformSchemeDateAction extends ActionSupport { 2 private static final long serialVersionUID = 8360690879427965177L; 3 private static Logger log = Logger.getLogger(ReformSchemeDateAction.class); 4 5 @Autowired 6 private QyggReportService qyggReportService; 7 8 //-------- 9 private QyggDocuments documents; 10 private String fileName; 11 private InputStream inputStream; 12 private String taskSn; 13 14 public String expData() throws UnsupportedEncodingException{ 15 setFileName(URLEncoder.encode("企业改制填报表.xls", "UTF-8")); 16 17 // 单行数据源 18 Map<String, Map<String, String>> olines = new HashMap<String, Map<String, String>>(); 19 Map<String, List<Map<String, String>>> mlines = new HashMap<String, List<Map<String, String>>>(); 20 try{ 21 documents = qyggReportService.queryDocuments(taskSn); 22 }catch(Exception e1){ 23 e1.printStackTrace(); 24 return "error"; 25 } 26 27 olines.put("QyggDocuments", XlsExportHelper.transBean2Map(documents, new String[] { "createTime", "updateTime" })); 28 29 // -----------------------------------------测试数据 30 31 try { 32 TSheet cis = new TSheetImpl(); 33 // String newFilePath = cis.copyWorkbook(filePath, null); 34 35 log.debug("企业改制填报数据导出, 读取模板...."); 36 InputStream is = ServletActionContext.getServletContext().getResourceAsStream("/WEB-INF/xlsTemplate/qygg/reformScheme_01.xls"); 37 38 log.debug("企业改制填报数据导出, 打开文件...."); 39 // 打开文件 40 cis.openWorkbook(is); 41 42 // 设置数据源 43 cis.setDataSource(olines, mlines); 44 45 // 处理模板数据 46 cis.fillSheet(); 47 48 log.debug("企业改制填报数据导出, 数据输出...."); 49 // 数据输出 50 ByteArrayOutputStream output = new ByteArrayOutputStream(); 51 cis.saveWorkbook(output); 52 log.debug("企业改制填报数据导出, 文件成功输出"); 53 54 byte[] ba = output.toByteArray(); 55 inputStream = new ByteArrayInputStream(ba); 56 output.flush(); 57 output.close(); 58 59 return "success"; 60 61 } catch (TSheetException e) { 62 log.debug("企业改制填报数据导出, 出现异常!!!"); 63 e.printStackTrace(); 64 } catch (IOException e) { 65 log.debug("企业改制填报数据导出, 出现异常, 数据读写异常!!!"); 66 e.printStackTrace(); 67 } 68 69 return "error"; 70 } 71 72 /*getter and setter*/ 73 public QyggDocuments getDocuments() { 74 return documents; 75 } 76 public void setDocuments(QyggDocuments documents) { 77 this.documents = documents; 78 } 79 public String getFileName() { 80 return fileName; 81 } 82 public void setFileName(String fileName) { 83 this.fileName = fileName; 84 } 85 public InputStream getInputStream() { 86 return inputStream; 87 } 88 public void setInputStream(InputStream inputStream) { 89 this.inputStream = inputStream; 90 } 91 92 public String getTaskSn() { 93 return taskSn; 94 } 95 96 public void setTaskSn(String taskSn) { 97 this.taskSn = taskSn; 98 } 99 }
这是上面用到的XlsExportHelper文件源码
1 /** 2 * xls导出辅助 3 * @author yep 4 * 5 */ 6 public class XlsExportHelper { 7 private static final long serialVersionUID = 5081817158639576516L; 8 private static Logger log = Logger.getLogger(BigPrejectDateAction.class); 9 /** 10 * Bean --> Map 11 * 12 * @param obj 13 * @param ignore 14 * 等于null将没有忽略 15 * @return 16 */ 17 public static Map<String, String> transBean2Map(Object obj, String[] ignore) { 18 19 if (obj == null) { 20 return null; 21 } 22 Map<String, String> map = new HashMap<String, String>(); 23 try { 24 BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass()); 25 PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors(); 26 27 Field[] fields = obj.getClass().getDeclaredFields(); 28 for (Field field : fields) { 29 field.setAccessible(true); //修改访问权限 30 if (field.getType() == String.class) { 31 Object value =http://www.mamicode.com/ field.get(obj); 32 String v =""; 33 if (value!=null) v = value.toString(); 34 map.put(field.getName(), v); 35 36 } else if (field.getType() == Date.class) { 37 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 38 Object value =http://www.mamicode.com/ field.get(obj); 39 String v = ""; 40 if (value!=null) v =sdf.format(value); 41 map.put(field.getName(), v); 42 43 } else if (field.getType() == Integer.class 44 || field.getType() == Long.class 45 || field.getType() == Float.class 46 || field.getType() == Double.class 47 || field.getType() == Character.class 48 || field.getType() == Short.class ) { 49 Object value =http://www.mamicode.com/ field.get(obj); 50 String v = String.valueOf(value); 51 map.put(field.getName(), v); 52 53 } else { 54 log.error("transBean2Map: 类型暂时不支持, " + field.getType().getName()); 55 } 56 } 57 } catch (Exception e) { 58 log.debug("transBean2Map Error " + e); 59 } 60 61 return map; 62 63 } 64 }
- 前台页面部分的源码就不需要展示给大家了，大家添加好导出按钮访问对应的action即可。以下是struts与spring的xml文件配置
struts配置
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN" "http://struts.apache.org/dtds/struts-2.0.dtd"> 3 <struts> 4 <!-- xls导出 --> 5 <package name="ReformSchemeDateAction" namespace="/qygg/report" 6 extends="root-struts"> 7 <action name="ReformSchemeDateAction!*" class="ReformSchemeDateAction" 8 method="{1}"> 9 <result name="success" type="stream"> 10 <param name="inputName">inputStream</param> 11 <param name="bufferSize">4096</param> 12 <param name="contentType">application/vnd.ms-excel</param> 13 <param name="contentDisposition">attachment;filename="${fileName}"</param> 14 </result> 15 </action> 16 </package> 17 </struts>
xml配置
1 <beans xmlns="http://www.springframework.org/schema/beans" 2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 3 xmlns:aop="http://www.springframework.org/schema/aop" 4 xmlns:tx="http://www.springframework.org/schema/tx" 5 xmlns:util="http://www.springframework.org/schema/util" 6 xsi:schemaLocation=" 7 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd 8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd 9 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd 10 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.5.xsd 11 " 12 default-lazy-init="false"> 13 14 <!-- 表导出 --> 15 <bean id="ReformSchemeDateAction" class="gov.bjsasac.commons.export.action.ReformSchemeDateAction" scope="prototype"></bean> 16 </beans>
最后,由于刚开始撰写技术类博客，因水平有限难免有不足之处，往大家指正。希望这篇博文可以帮助到大家！
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。