java上传excel文件及解析
CreateTime--2018年3月5日16:25:14
Author:Marydon
一、准备工作
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.关于上面为什么要抛出运行时异常？
在往数据库中批量插入数据时，如果中间插入失败，需要进行回滚。