java实现导入excel功能

java实现导⼊excel功能
实现功能：
1、Excel模板下载
2、导⼊excel
⼀、jsp效果和代码
1<form id="uploadForm" target="frameFile" class="bs-docs-example form-horizontal" method="post" action="<%=path %>/webCenter.do" enctype="multipart/form-data">
2<input type="hidden" id="conId" name="conId" value="<%=conId%>">
3<input type="hidden" id="code" name="code" value="<%=code%>">
4<input type="hidden" name="method" value="insertUserInfo">
5<table cellpadding="0" cellspacing="0" border="0" style="width:600px;margin:20px auto;text-algin:left;">
6<tr><td colspan="4"><input id="dyId" type="hidden"/></td></tr>
7<tr><td colspan="3"><a href="/center/file/userInfoModel.xlsx" id="downloadModel" name="downloadModel" style="margin-left:7px;"><u>点击下载⼈员模板</u></a></td></tr> 8<tr><td colspan="4"><br/></td></tr>
9<tr><td colspan="3"><input id="files" name="files" type="file" style="width:200px;"/></td></tr><!-- background:url('/center/images/uploadImg.png') no-repeat 0px 10px; -->
10<tr><td colspan="4"><br/></td></tr>
11<tr>
12<td colspan="4" style="text-align: center;">
13<hr style="width:720px;border-width: 0.3px;margin-left:-10px;">
14<button id="tiJiao" type="button" class="czbtn" style="width:100px;font-size: 16px;">导⼊</button>
15</td>
16</tr>
17</table>
18</form>
View Code
⼆、js代码
1 $("#tiJiao").click(function(){
2 if($("#files").val() == ""){
3 alert("请选择要上传的⽂件");
4 }else{
5 CommonPerson.Base.LoadingPic.FullScreenShow();
6 $("#tkDiv").hide();
7 $("#tk1").hide();
8 $("#uploadForm").submit();
9 }
10 })
View Code
三、action处理
1 /**
2 * ⼈员信息导⼊
3 * @param conId
4 * @param code
5 * @param request
6 * @param response
7 */
8 @RequestMapping(params="method=insertUserInfo",method=RequestMethod.POST)
9 public void insertUserInfo(Integer conId,String code,HttpServletRequest request,HttpServletResponse response){
10 try {
11 String msg = "";
12 Integer state = 0;
13 String fileUrl = "/files/excel/";
14 HttpSession session = this.getSession(request);
15 Adminuser adminUser = session.getAttribute("centerAdminUser") == null?null:(Adminuser) session.getAttribute("centerAdminUser");
16 if(adminUser == null){
17 try {
18 response.sendRedirect(request.getContextPath()+"/center/index.jsp");
19 } catch (Exception e) {
20 e.printStackTrace();
21 }
22 }else{
23 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
24 MultipartFile multipartFile = multipartRequest.getFile("files");
25 InputStream is = multipartFile.getInputStream();
26 if(is!=null){
27 Workbook wb = WorkbookFactory.create(is);
28 CellStyle style = wb.createCellStyle();
29 style.setFillForegroundColor(IndexedColors.RED.getIndex());
30 style.setFillPattern(CellStyle.SOLID_FOREGROUND);
31 List<UserInfo> userInfoList = new ArrayList<UserInfo>();
32 int rowCount = 0;
33 boolean temp = true;
34 try {
35 Sheet st = wb.getSheetAt(0);
36 int rowNum = st.getLastRowNum(); //获取Excel最后⼀⾏索引，从零开始，所以获取到的是表中最后⼀⾏⾏数减⼀
37 int colNum = st.getRow(0).getLastCellNum();//获取Excel列数
38 for(int r=1;r<=rowNum;r++){//读取每⼀⾏，第⼀⾏为标题，从第⼆⾏开始
39 rowCount = r;
40Row row = st.getRow(r);
41UserInfo userInfo = new UserInfo();
42 for(int l=0;l<colNum;l++){//读取每⼀⾏的每⼀列
43Cell cell = row.getCell(l);
44if(cell != null){
45cell.setCellType(Cell.CELL_TYPE_STRING);
46 }
47 if(l != 8 && l != 9){//第9列和第10列(列数是从0开始遍历)分别是⾝份证号码和⼯作背景，这两项为选填，其余项全为必填
48if(cell != null && !"".equals(cell.toString().trim())){
49 System.out.print(cell + "\t");
50 }else{
51 System.out.print("该项不能为空" + "\t");
52 temp = false;
53//给Excel中为空格的必填项添加背景⾊
54 Cell newCell = row.createCell(l);
55newCell.setCellStyle(style);
56 }
57 }else{//⾝份证号和⼯作背景
58 System.out.print(cell + "\t");
59 }
60 if(temp){
61 switch (l) {
62 case 0: userInfo.setEmail(cell.getStringCellValue()); break;
63 case 1: userInfo.setMobilePhone(cell.getStringCellValue()); break;
64 case 2: userInfo.setPassword(cell.getStringCellValue()); break;
65 case 3: userInfo.setTrueName(cell.getStringCellValue()); break;
66 case 4: userInfo.setXingPingyin(cell.getStringCellValue()); break;
67 case 5: userInfo.setMingPingyin(cell.getStringCellValue()); break;
68 case 6: userInfo.setSex(cell.getStringCellValue()); break;
69 case 7: userInfo.setBirthday(cell.getStringCellValue()); break;
70 case 8: userInfo.setIdCard(cell.getStringCellValue()); break;
71 case 9: userInfo.setBeijin(cell.getStringCellValue()); break;
72 case 10: userInfo.setXueli(cell.getStringCellValue()); break;
73 case 11:
74 userInfo.setProvinceName(cell.getStringCellValue());
75 Hospital provinceId = hospitalService.getHospitalByProvince(cell.getStringCellValue());
76if(provinceId != null){
77userInfo.setProvince(provinceId.getHospitalId()+"");
78 }
79 break;
80 case 12:
81 userInfo.setCityName(cell.getStringCellValue());
82 Hospital cityId = hospitalService.getHospitalByCity(cell.getStringCellValue());
83if(cityId != null){
84userInfo.setCity(cityId.getHospitalId()+"");
85 }
86 break;
87 case 13:
88 userInfo.setDanwei(cell.getStringCellValue());
89 break;
90 case 14: userInfo.setKs(cell.getStringCellValue()); break;
91 case 15: userInfo.setZhicheng(cell.getStringCellValue()); break;
92 case 16: userInfo.setZhiwei(cell.getStringCellValue()); break;
93 case 17: userInfo.setAddress(cell.getStringCellValue()); break;
94 case 18: userInfo.setZip(cell.getStringCellValue()); break;
95 case 19: userInfo.setTelphone(cell.getStringCellValue()); break;
96 }
97 userInfo.setConferencesId(conId);
98 userInfo.setFromWhere(code);
99 userInfo.setCreateTime(new Date());
100 }
101 }
102 System.out.println();
103 userInfoList.add(userInfo);
104 }
105 if(temp){//Excel完全没有问题
106 webService.saveOrUpdateAll(userInfoList);
107 state = 1;
108msg = "导⼊成功";
109 }else{//Excel存在必填项为空的情况
110 state = 2;
111msg = "Excel数据格式有问题，请下载表格，并将其中标红⾊的部分填写完整";
112 String filePath = request.getSession().getServletContext().getRealPath("files/excel");
113String fileName = DateTime.getDateString(new Date(), "yyyy_MM_dd")+String.valueOf(System.currentTimeMillis()/1000)+".xlsx"; 114 OutputStream out = new FileOutputStream(new File(filePath + "/" + fileName));
115 wb.write(out);
116 out.close();
117 fileUrl = fileUrl + fileName;
118 }
119 }catch (Exception e) {
120 System.out.println("第"+rowCount+"⾏出错");
121 msg = "第"+rowCount+"⾏出错";
122 e.printStackTrace();
123 }
124 }
125 is.close();
126 JSONObject result = new JSONObject();
127 result.accumulate("state",state);
128 result.accumulate("remark",msg);
129 result.accumulate("fileUrl",fileUrl);
130 String urlString = "<script type='text/javascript'>window.parent.insertResult('"+result.toString()+"')</script>";
131 PrintWriter out = response.getWriter();
132 response.setCharacterEncoding("utf-8");
133 response.setContentType("text/html;charset=UTF-8");
134 out.write(urlString);
135 out.flush();
136 out.close();
137 }
138 } catch (Exception e) {
139 e.printStackTrace();
140 try {
141 JSONObject result = new JSONObject();
142 result.accumulate("state",0);
143 result.accumulate("remark","excel数据格式有问题，导⼊失败");
144 String urlString ="<script type='text/javascript'>window.parent.insertResult('"+result.toString()+"')</script>"; 145 PrintWriter out = response.getWriter();
146 response.setCharacterEncoding("utf-8");
147 response.setContentType("text/html;charset=UTF-8");
148 out.write(urlString);
149 out.flush();
150 out.close();
151 } catch (Exception e2) {
152 e2.printStackTrace();
153 }
154 }
155 }
View Code。