实现功能：
1、Excel模板下载
2、导入excel
一、jsp效果和代码
<form id="uploadForm" target="frameFile" class="bs-docs-example form-horizontal" method="post" action="<%=path %>/webCenter.do" enctype="multipart/form-data">
<input type="hidden" id="conId" name="conId" value="<%=conId%>">
<input type="hidden" id="code" name="code" value="<%=code%>">
<input type="hidden" name="method" value="insertUserInfo">
<table cellpadding="0" cellspacing="0" border="0" style="width:600px;margin:20px auto;text-algin:left;">
<tr><td colspan="4"><input id="dyId" type="hidden"/></td></tr>
<tr><td colspan="3"><a href="/center/file/userInfoModel.xlsx" id="downloadModel" name="downloadModel" style="margin-left:7px;"><u>点击下载人员模板</u></a></td></tr>
<tr><td colspan="4"><br/></td></tr>
<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; -->
<tr><td colspan="4"><br/></td></tr>
<tr>
<td colspan="4" style="text-align: center;">
<hr style="width:720px;border-width: 0.3px;margin-left:-10px;">
<button id="tiJiao" type="button" class="czbtn" style="width:100px;font-size: 16px;">导入</button>
</td>
</tr>
</table>
</form>
二、js代码
$("#tiJiao").click(function(){
if($("#files").val() == ""){
alert("请选择要上传的文件");
}else{
CommonPerson.Base.LoadingPic.FullScreenShow();
$("#tkDiv").hide();
$("#tk1").hide();
$("#uploadForm").submit();
}
})
三、action处理
/**
* 人员信息导入
* @param conId
* @param code
* @param request
* @param response
*/
@RequestMapping(params="method=insertUserInfo",method=RequestMethod.POST)
public void insertUserInfo(Integer conId,String code,HttpServletRequest request,HttpServletResponse response){
try {
String msg = "";
Integer state = 0;
String fileUrl = "/files/excel/";
HttpSession session = this.getSession(request);
Adminuser adminUser = session.getAttribute("centerAdminUser") == null?null:(Adminuser) session.getAttribute("centerAdminUser");
if(adminUser == null){
try {
response.sendRedirect(request.getContextPath()+"/center/index.jsp");
} catch (Exception e) {
e.printStackTrace();
}
}else{
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile multipartFile = multipartRequest.getFile("files");
InputStream is = multipartFile.getInputStream();
if(is!=null){
Workbook wb = WorkbookFactory.create(is);
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
List<UserInfo> userInfoList = new ArrayList<UserInfo>();
int rowCount = 0;
boolean temp = true;
try {
Sheet st = wb.getSheetAt(0);
int rowNum = st.getLastRowNum(); //获取Excel最后一行索引，从零开始，所以获取到的是表中最后一行行数减一
int colNum = st.getRow(0).getLastCellNum();//获取Excel列数
for(int r=1;r<=rowNum;r++){//读取每一行，第一行为标题，从第二行开始
rowCount = r;
Row row = st.getRow(r);
UserInfo userInfo = new UserInfo();
for(int l=0;l<colNum;l++){//读取每一行的每一列
Cell cell = row.getCell(l);
if(cell != null){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if(l != 8 && l != 9){//第9列和第10列(列数是从0开始遍历)分别是身份证号码和工作背景，这两项为选填，其余项全为必填
if(cell != null && !"".equals(cell.toString().trim())){
System.out.print(cell + "\t");
}else{
System.out.print("该项不能为空" + "\t");
temp = false;
//给Excel中为空格的必填项添加背景色
Cell newCell = row.createCell(l);
newCell.setCellStyle(style);
}
}else{//身份证号和工作背景
System.out.print(cell + "\t");
}
if(temp){
switch (l) {
case 0: userInfo.setEmail(cell.getStringCellValue()); break;
case 1: userInfo.setMobilePhone(cell.getStringCellValue()); break;
case 2: userInfo.setPassword(cell.getStringCellValue()); break;
case 3: userInfo.setTrueName(cell.getStringCellValue()); break;
case 4: userInfo.setXingPingyin(cell.getStringCellValue()); break;
case 5: userInfo.setMingPingyin(cell.getStringCellValue()); break;
case 6: userInfo.setSex(cell.getStringCellValue()); break;
case 7: userInfo.setBirthday(cell.getStringCellValue()); break;
case 8: userInfo.setIdCard(cell.getStringCellValue()); break;
case 9: userInfo.setBeijin(cell.getStringCellValue()); break;
case 10: userInfo.setXueli(cell.getStringCellValue()); break;
case 11:
userInfo.setProvinceName(cell.getStringCellValue());
Hospital provinceId = hospitalService.getHospitalByProvince(cell.getStringCellValue());
if(provinceId != null){
userInfo.setProvince(provinceId.getHospitalId()+"");
}
break;
case 12:
userInfo.setCityName(cell.getStringCellValue());
Hospital cityId = hospitalService.getHospitalByCity(cell.getStringCellValue());
if(cityId != null){
userInfo.setCity(cityId.getHospitalId()+"");
}
break;
case 13:
userInfo.setDanwei(cell.getStringCellValue());
break;
case 14: userInfo.setKs(cell.getStringCellValue()); break;
case 15: userInfo.setZhicheng(cell.getStringCellValue()); break;
case 16: userInfo.setZhiwei(cell.getStringCellValue()); break;
case 17: userInfo.setAddress(cell.getStringCellValue()); break;
case 18: userInfo.setZip(cell.getStringCellValue()); break;
case 19: userInfo.setTelphone(cell.getStringCellValue()); break;
}
userInfo.setConferencesId(conId);
userInfo.setFromWhere(code);
userInfo.setCreateTime(new Date());
}
}
System.out.println();
userInfoList.add(userInfo);
}
if(temp){//Excel完全没有问题
webService.saveOrUpdateAll(userInfoList);
state = 1;
msg = "导入成功";
}else{//Excel存在必填项为空的情况
state = 2;
msg = "Excel数据格式有问题，请下载表格，并将其中标红色的部分填写完整";
String filePath = request.getSession().getServletContext().getRealPath("files/excel");
String fileName = DateTime.getDateString(new Date(), "yyyy_MM_dd")+String.valueOf(System.currentTimeMillis()/1000)+".xlsx";
OutputStream out = new FileOutputStream(new File(filePath + "/" + fileName));
wb.write(out);
out.close();
fileUrl = fileUrl + fileName;
}
}catch (Exception e) {
System.out.println("第"+rowCount+"行出错");
msg = "第"+rowCount+"行出错";
e.printStackTrace();
}
}
is.close();
JSONObject result = new JSONObject();
result.accumulate("state",state);
result.accumulate("remark",msg);
result.accumulate("fileUrl",fileUrl);
String urlString = "<script type='text/javascript'>window.parent.insertResult('"+result.toString()+"')</script>";
PrintWriter out = response.getWriter();
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
out.write(urlString);
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
try {
JSONObject result = new JSONObject();
result.accumulate("state",0);
result.accumulate("remark","excel数据格式有问题，导入失败");
String urlString ="<script type='text/javascript'>window.parent.insertResult('"+result.toString()+"')</script>";
PrintWriter out = response.getWriter();
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
out.write(urlString);
out.flush();
out.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}