首页 > 代码库 > 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 }
QyggDocuments.java

 

  • 其次,在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 }
ReformSchemeDateAction .java

这是上面用到的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>

 

  最后,由于刚开始撰写技术类博客,因水平有限难免有不足之处,往大家指正。希望这篇博文可以帮助到大家!