java上传excel文件及解析
一、准备工作
1.1 文件上传插件：swfupload；
1.2 文件上传所需jar包：commons-fileupload-1.3.1.jar和commons-io-2.2.jar；
1.3 解析excel所需jar包：dom4j-1.6.1.jar,poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-ooxml-schemas-3.8-20120326.jar和xmlbeans-2.3.0.jar
1.4目录结构
二、代码展示
2.1 客户端代码设计
JSP部分
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
2 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
3 <html>
4 <head>
5 <title>excel导入演示</title>
6 <%@ include file="commons/jsp/include.jsp" %>
7 <script type="text/javascript" src="<c:url value="/commons/js/swfupload/swfupload.js" />"></script>
8 <script type="text/javascript" src="index.js"></script>
9 </head>
10 <body>
11 <table>
12 <tbody>
13 <tr>
14 <td align="right">导入排班信息</td>
15 <td>
16 <input id="FILENAME" maxlength="256" type="text" class="" readonly/>
17 <input id="saveFileName" type="hidden"/>
18 </td>
19 <td>
20 <span id="ButtonPlaceholder"></span>
21 <input onclick="readExcel();" type="button" value="导入"/>
22 </td>
23 </tr>
24 </tbody>
25 </table>
26 </body>
27 </html>js文件
1 var uploadItem;
2 // 页面加载
3 $(function() {
4 // 必须是页面加载完毕后，再实例化该对象
5 uploadItem = new UploadItem();
6 });
7
8 /**
9 * 导入Excel
10 * @returns
11 */
12 function readExcel() {
13 var FILENAME = $("#saveFileName").val();// 上传文件
14 if(FILENAME == ""){
15 Dialog.Alert('消息提示',"请点击浏览按钮选择EXCEL文件！",null,null,100);
16 return;
17 }
18
19 var param = "FILENAME=" + FILENAME;//文件名字
20 $.ajax({
21 type : 'POST',
22 url : baseUrl + "/readExcel.do",
23 data : param,
24 success : function(result) {
25 var result = eval("(" + result + ")");
26 $get('FILENAME').value = "";
27 $get('saveFileName').value = "";
28 // 返回执行结果
29 var returnMsg = result.msg;
30 if ("数据导入成功！" != result.msg) {
31 returnMsg = result.expMsg;
32 }
33 alert(returnMsg);
34 }
35 });
36 }
37 /*
38 * SWFUpload 浏览按钮：上传文件到文件夹
39 */
40 function UploadItem() {
41 var object = this;
42
43 this.settings_object = {
44 flash_url : baseUrl + "/commons/js/swfupload/swfupload.swf",
45 upload_url : baseUrl + "/uploadExcel.do",
46 file_post_name : "uploadFile",
47 post_params:{"test":"测试参数传递"},
48 file_size_limit : "20 MB",
49 file_types : "*.xls;*.xlsx",
50 file_types_description : "excel File",
51 file_upload_limit : "0",
52
53 file_queued_handler : fileQueued,// 指定文件上传事件
54 upload_error_handler : uploadError,// 指定上传异常处理事件
55 file_queue_error_handler : fileQueueError,//文件上传校验事件异常处理
56 upload_success_handler : uploadSuccess,// 指定上传成功事件
57
58 button_image_url : baseUrl + "/commons/images/browser.gif",
59 button_placeholder_id : "ButtonPlaceholder",// 根据ID绑定浏览按钮及事件
60 button_width : 69,
61 button_height : 21,
62
63 debug : false
64 };
65
66 this.swfu = new SWFUpload(object.settings_object);
67
68 /**
69 * 开始上传
70 */
71 this.startUpload = function () {
72 object.swfu.startUpload();
73 };
74
75 }
76
77 function fileQueued(file) {
78 uploadItem.startUpload();
79 };
80
81 /**
82 * 上传成功
83 * @param file
84 * @param serverData
85 * @returns
86 */
87 function uploadSuccess(file, result) {
88 var result = eval("(" + result + ")");
89 $get("FILENAME").value = result.oldFileName;
90 $get("saveFileName").value = result.saveFileName;
91 }
92
93 function fileQueueError(file, errorCode, message) {
94 switch (errorCode) {
95 case -100:
96 message = "您上传的文件过大！";// QUEUE_LIMIT_EXCEEDED
97 break;
98 case -110:
99 message = "您上传的文件过大！";// FILE_EXCEEDS_SIZE_LIMIT
100 break;
101 case -120:
102 message = "您上传的文件类型不正确！";// ZERO_BYTE_FILE
103 break;
104 case -130:
105 message = "您上传的文件类型格式错误！";// INVALID_FILETYPE
106 break;
107 default:
108 break;
109 }
110
111 alert(result.msg);
112 $get('FILENAME').focus();
113 };
114
115 /**
116 * 上传失败
117 * @param file
118 * @param errorCode
119 * @returns
120 */
121 function uploadError(file, errorCode) {
122 var result = eval("(" + errorCode + ")");
123 alert(result.msg);
124 };2.2 服务器端代码设计
文件上传代码
1 package controller;
2
3 import java.io.File;
4 import java.io.IOException;
5 import java.io.PrintWriter;
6 import java.util.Calendar;
7 import java.util.Iterator;
8 import java.util.List;
9 import javax.servlet.ServletException;
10 import javax.servlet.annotation.WebServlet;
11 import javax.servlet.http.HttpServlet;
12 import javax.servlet.http.HttpServletRequest;
13 import javax.servlet.http.HttpServletResponse;
14 import org.apache.commons.fileupload.FileItem;
15 import org.apache.commons.fileupload.FileItemFactory;
16 import org.apache.commons.fileupload.disk.DiskFileItemFactory;
17 import org.apache.commons.fileupload.servlet.ServletFileUpload;
18
19 /**
20 * Servlet implementation class UploadExcel
21 */
22 @WebServlet("/uploadExcel.do")
23 public class UploadExcelController extends HttpServlet {
24 private static final long serialVersionUID = 1L;
25
26 protected void doGet(HttpServletRequest request, HttpServletResponse response)
27 throws ServletException, IOException {
28 this.doPost(request, response);
29 }
30
31 protected void doPost(HttpServletRequest request, HttpServletResponse response)
32 throws ServletException, IOException {
33 // 指定保存路径
34 String fileSavePath = "/upload";
35 String rootPath = this.getServletContext().getRealPath("");
36 fileSavePath = rootPath + fileSavePath;
37 // 获取前台传参
38 String param = request.getParameter("test");
39 System.out.println("获取前台参数：" + param);
40
41 // 上传操作
42 FileItemFactory factory = new DiskFileItemFactory();
43 ServletFileUpload upload = new ServletFileUpload(factory);
44 upload.setHeaderEncoding("UTF-8");
45 String saveFileName = "";
46 String oldFileName = "";
47 try {
48 List items = upload.parseRequest(request);
49 if (null != items) {
50 Iterator itr = items.iterator();
51 while (itr.hasNext()) {
52 FileItem item = (FileItem) itr.next();
53 if (!item.isFormField()) {// 文件格式
54 // 以当前精确到秒的日期为上传的文件的文件名
55 saveFileName = this.getServerSysDateAndTimeAsCode();
56 oldFileName = item.getName();
57 String fileType = oldFileName.substring(oldFileName.lastIndexOf("."));
58 saveFileName += fileType;
59 // 空文件对象路径+文件名
60 File savedFile = new File(fileSavePath, saveFileName);
61 // 写入
62 item.write(savedFile);
63 }
64 }
65 }
66
67 StringBuffer sb = new StringBuffer();
68 // key和value两边都必须带""
69 sb.append("{").append("\"oldFileName\"").append(":").append("\"").append(oldFileName).append("\"")
70 .append(",").append("\"saveFileName\"").append(":").append("\"").append(saveFileName).append("\"").append("}");
71 // json字符串：文件名称及文件路径
72 String returnMsg = sb.toString();
73 System.out.println(returnMsg);
74 // 返回信息
75 response.setContentType("text/html; charset=UTF-8");
76 PrintWriter out = response.getWriter();
77
78 // 返回页面
79 out.print(returnMsg);
80
81 } catch (Exception e) {
82 e.printStackTrace();
83 }
84
85 }
86
87 /**
88 * 获得当前日期【long型】作为文件名称
89 * @return
90 */
91 public String getServerSysDateAndTimeAsCode() {
92 String result = null;
93 long currentTimeInMilis = Calendar.getInstance().getTimeInMillis();
94 result = String.valueOf(currentTimeInMilis);
95 return result;
96 }
97 }
98 解析excel
99
100 package controller;
101
102 import java.io.File;
103 import java.io.IOException;
104 import java.io.PrintWriter;
105 import javax.servlet.ServletException;
106 import javax.servlet.annotation.WebServlet;
107 import javax.servlet.http.HttpServlet;
108 import javax.servlet.http.HttpServletRequest;
109 import javax.servlet.http.HttpServletResponse;
110 import bo.BoExcelImpl;
111 import bo.IBoExcel;
112
113 /**
114 * Servlet implementation class AnalyzeExcelController
115 */
116 @WebServlet("/readExcel.do")
117 public class AnalyzeExcelController extends HttpServlet {
118 private static final long serialVersionUID = 1L;
119
120 protected void doGet(HttpServletRequest request, HttpServletResponse response)
121 throws ServletException, IOException {
122 this.doPost(request, response);
123 }
124
125 protected void doPost(HttpServletRequest request, HttpServletResponse response)
126 throws ServletException, IOException {
127 String FILENAME = request.getParameter("FILENAME");
128 String msg = "";
129 String expMsg = "";
130 IBoExcel boExcel = new BoExcelImpl();
131 try {
132 String path = this.getServletContext().getRealPath("/upload");
133 // 上传excel的绝对路径
134 path += File.separator + FILENAME;
135 // 解析excel数据
136 boolean isSuccess = boExcel.readExcel(path);
137
138 if (isSuccess) {
139 msg = "数据导入成功！";
140 } else {
141 msg = "数据导入失败！";
142 }
143
144 } catch (Exception e) {
145 expMsg = e.getMessage();
146 } finally {
147 StringBuffer sb = new StringBuffer();
148 sb.append("{").append("\"msg\"").append(":").append("\"").append(msg).append("\"").append(",")
149 .append("\"expMsg\"").append(":").append("\"").append(expMsg).append("\"").append("}");
150 // json字符串：文件名称及文件路径
151 String returnMsg = sb.toString();
152 System.out.println(returnMsg);
153
154 // 返回信息
155 response.setContentType("text/html; charset=UTF-8");
156 PrintWriter out = response.getWriter();
157
158 // 返回页面
159 out.print(returnMsg);
160 }
161
162 }
163
164 }业务层
1 package bo;
2
3 import java.io.File;
4 import java.util.ArrayList;
5 import java.util.List;
6 import java.util.Map;
7 import org.apache.log4j.Logger;
8 import tools.ReadExcelUtils;
9
10 /**
11 *
12 * @author Marydon
13 * @createTime 2018年3月2日下午8:01:07
14 * @updateTime
15 * @Email:Marydon20170307@163.com
16 * @version:1.0.0
17 */
18 public class BoExcelImpl implements IBoExcel {
19 private Logger log = Logger.getLogger(this.getClass());
20
21 @Override
22 public boolean readExcel(String filePath) throws Exception {
23 try {
24 boolean isSuccess = false;
25
26 ReadExcelUtils excelReader = new ReadExcelUtils(filePath);
27
28 List<String> columnsList = new ArrayList<String>();
29 columnsList.add("ORG_ID");
30 columnsList.add("DEPENT_NAME");
31 columnsList.add("DOCTOR_NAME");
32 columnsList.add("DOCTOR_PHONE");
33 columnsList.add("SCHEDULE_DATE");
34 columnsList.add("WEEK_TXT");
35 columnsList.add("WB_TYPE");
36 columnsList.add("CLOSE_TZ");
37 columnsList.add("REPLACE_TZ");
38
39 // 1.对读取Excel表格内容
40 List<Map> scheduleList = excelReader.readExcelContent(columnsList);
41 System.out.println(scheduleList);
42
43 isSuccess = true;
44
45 // 删除该上传的文件
46 File excelFile = new File(filePath);
47 if (excelFile.exists()) {
48 excelFile.delete();
49 }
50 return isSuccess;
51 } catch (Exception e) {
52 log.error(e.getMessage());
53 throw new RuntimeException(e.getMessage(), e);
54 }
55 }
56 }
效果展示：
excel文件
上传成功
刷新upload目录
导入成功
注意：
1.文章中的Dialog和$get()是自定义封装的方法，无需理会；
2.其中，excel中代表数值的字段，需要改成文本格式，否则解析出来后面会带".0"；
3.关于上面为什么要抛出运行时异常？
在往数据库中批量插入数据时，如果中间插入失败，需要进行回滚。
本文来自博客园，作者：Marydon，转载请注明原文链接：https://www.cnblogs.com/Marydon20170307/p/8509685.html
