1.jsp页面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <html> <head> <base href="<%=basePath%>"> <script type="text/javascript" src="js/jquery-1.8.3.js"></script> <script type="text/javascript" src="js/jquery.form.js"></script> <script type="text/javascript" src="js/ajaxfileupload.js"></script> <title>My JSP 'index.jsp' starting page</title> <script type="text/javascript"> //ajax 方式上传文件操作 $(document).ready(function(){ $("#table").hide(); $('#btn').click(function(){ if(checkData()){ // if(checkData()){ $("#table").show(); var file = $("#upfile").val(); //正则表达式获取文件名，不带后缀 /* var filename=file.replace(/^.+?\([^\]+?)(.[^.\]*?)?$/gi,"$1"); */ //正则表达式获取文件名,带后缀 var filename=file.replace(/^.+?\([^\]+?)$/gi,"$1"); //正则表达式后缀 /* var filename=file.replace(/.*(/|\)/, ""); */ //获取文件名后缀 /* var filename = fileDir.substr(0,fileDir.lastIndexOf(".")); */ $("#t2").text(filename); $("#t3").text("正在解析..."); /* $('#form1').ajaxSubmit({ url:'uploadExcel/ajaxUpload.do', type : "POST", dataType: 'json', processData:false, contentType:false, success : function(data) { alert(data); if(data == "true"){ $("#t3").text("<a href='uploadExcel/download_errorexcel.do'>错误信息下载</a>"); }else{ $("#t3").text("导入成功!"); $("#upfile").val(""); } }, error : function(data) { alert("导入excel出错！"); } }); */ /* function resutlMsg(msg){ $("#t3").text("导入成功!"); $("#upfile").val(""); alert(msg); if(msg == "1"){ $("#t3").text("<a href='uploadExcel/download_errorexcel.do'>错误信息下载</a>"); } } function errorMsg(){ alert("导入excel出错！"); } */ /* var form = new FormData(document.getElementById("form1")); $.ajax({ url : "uploadExcel/ajaxUpload.do", type : "POST", data : form, dataType : "json", processData:false, contentType:false, success : function(data) { alert(data); if(data == "true"){ $("#t3").text("<a href='uploadExcel/download_errorexcel.do'>错误信息下载</a>"); }else{ $("#t3").text("导入成功!"); $("#upfile").val(""); } }, error : function(data) { alert("导入excel出错！"); } }); */ $.ajaxFileUpload({ url: 'uploadExcel/ajaxUpload.do', //用于文件上传的服务器端请求地址 type:'POST', secureuri : false, fileElementId: 'upfile', //文件上传域的ID dataType: 'json', //返回值类型 一般设置为json success: function (data){ if(data == "true"){ $("#t3").html("<a href='uploadExcel/download_errorexcel.do'>错误信息下载</a>"); }else{ $("#t3").text("导入成功!"); $("#upfile").val(""); } }, error: function (data){ alert("导入excel出错！"); } } ) } }); }); //JS校验form表单信息 function checkData(){ var fileDir = $("#upfile").val(); var suffix = fileDir.substr(fileDir.lastIndexOf(".")); if("" == fileDir){ alert("选择需要导入的Excel文件！"); return false; } if(".xls" != suffix && ".xlsx" != suffix ){ alert("选择Excel格式的文件导入！"); return false; } return true; } </script> </head> <body> <div> <a href="uploadExcel/download.do">模板下载</a> </div> <div>1.通过简单的form表单提交方式，进行文件的上</br> 2.通过jquery.form.js插件提供的form表单一步提交功能 </div></br> <!-- <form method="POST" enctype="multipart/form-data" id="form1"> --> <table> <tr> <td>上传文件: </td> <td> <input id="upfile" type="file" name="upfile"></td> </tr> <tr> <td><input type="submit" value="提交" onclick="return checkData()"></td> <td><input type="button" value="ajax方式提交" id="btn" name="btn" ></td> </tr> </table> <!-- </form> --> <table id="table"> <tr> <td style=" 200px;">文件名</td> <td>文件状态</td> <td>备注</td> </tr> <tr> <td id="t2" style=" 200px;"></td> <td id="t3"></td> <td id="t4"></td> </tr> </table> </body> </html>
2.后台controller
package com.shenqz.controller; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.PrintWriter; import java.net.URL; import java.net.URLConnection; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.io.FileUtils; import org.apache.log4j.Logger; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import org.springframework.web.multipart.commons.CommonsMultipartFile; import com.alibaba.fastjson.JSONArray; import com.shenqz.util.ImportExcelUtil; import com.shenqz.common.Contents; import com.shenqz.entity.InfoVo; @Controller @RequestMapping("/uploadExcel/*") public class UploadExcelControl { Logger log = Logger.getLogger(UploadExcelControl.class); /** * 描述：通过传统方式form表单提交方式导入excel文件 * @param request * @throws Exception */ /* @RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST}) public String uploadExcel(HttpServletRequest request) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; System.out.println("通过传统方式form表单提交方式导入excel文件！"); InputStream in =null; List<List<Object>> listob = null; MultipartFile file = multipartRequest.getFile("upfile"); if(file.isEmpty()){ throw new Exception("文件不存在！"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); in.close(); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); InfoVo vo = new InfoVo(); System.out.println("打印信息-->机构:"+vo.getCode()+" 名称："+vo.getName()+" 时间："+vo.getDate()+" 资产："+vo.getMoney()); } return "result"; } */ /** * 描述：通过 jquery.form.js 插件提供的ajax方式上传文件 * @param request * @param response * @throws Exception */ @RequestMapping("ajaxUpload.do") @ResponseBody public String ajaxUploadExcel(@RequestParam("upfile") CommonsMultipartFile upfile, HttpServletResponse response,HttpServletRequest request) throws Exception { /*MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile multipartFile = multipartRequest.getFile("fileToUpload"); */ InputStream in =null; List<List<Object>> listob = null; if(upfile.isEmpty()){ throw new Exception("文件不存在！"); } in = upfile.getInputStream(); Workbook workbook = WorkbookFactory.create(in); listob = new ImportExcelUtil().getBankListByExcel(workbook, upfile.getOriginalFilename() ,2 , response,request); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); InfoVo vo = new InfoVo(); for(int j = 0;j < lo.size(); j++){ if(lo.get(j) != null){ if(j == 0){ vo.setOrgancode((String)lo.get(j)); }else if(j == 1){ vo.setOrganname((String)lo.get(j)); }else if(j == 2){ vo.setContact((String)lo.get(j)); }else if(j == 3){ vo.setTelephone((String)lo.get(j)); }else if(j == 4){ vo.setGddh((String)lo.get(j)); }else if(j == 5){ vo.setDate((String)lo.get(j)); }else if(j == 6){ vo.setMoney((String)lo.get(j)); } } } System.out.println(vo); } /*PrintWriter out = null; */ /*response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码 */ /*out = response.getWriter(); out.print("文件导入成功！"); out.flush(); out.close(); */ if(Contents.errorDownLoad == "1"){ return "true"; } return null; } @RequestMapping("download.do") public ResponseEntity<byte[]> download(HttpServletRequest request,HttpServletResponse response, Model model)throws Exception { System.out.println("开始下载"); String downloadFielName = "机构信息表模板.xlsx"; //下载文件路径 String path = "D:\apache-tomcat-7.0.78-dubbo\download"; File file = new File(path + File.separator + downloadFielName); HttpHeaders headers = new HttpHeaders(); downloadFielName = new String(downloadFielName.getBytes("UTF-8"),"iso-8859-1"); //通知浏览器以attachment（下载方式）打开图片(下载的文件名) headers.setContentDispositionFormData("attachment", downloadFielName); //application/octet-stream ： 二进制流数据（最常见的文件下载）。 headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED); } @RequestMapping(value = "download_errorexcel.do") public ResponseEntity<byte[]> downloadExcel(String downloadUrl, HttpServletRequest request, HttpServletResponse response) throws IOException { /*InputStream in = null; //输入流 OutputStream out = null; //输出流 try { //需要下载的文件链接 URL url = new URL(downloadUrl); //建立连接 URLConnection conn = url.openConnection(); //根据链接获得输入流 in = conn.getInputStream(); //文件名 String fileName = Contents.DOWNLOAD_FILENAME; //设置响应 response.setContentType("application/x-excel"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO-8859-1")); //获得输出流 out = response.getOutputStream(); byte[] data = new byte[1024]; int len = 0; while ((len = in.read(data, 0, data.length)) != -1) { out.write(data, 0, len); } } catch (Exception e) { log.error("下载文件异常", e); } finally { if (in != null) { in.close(); } if (out != null) { out.close(); } }*/ String path = request.getServletContext().getRealPath("/error/"); String filename = Contents.DOWNLOAD_FILENAME + ".xlsx"; File file = new File(path + File.separator + filename); HttpHeaders headers = new HttpHeaders(); filename = new String(filename.getBytes("UTF-8"),"iso-8859-1"); //通知浏览器以attachment（下载方式）打开图片(下载的文件名) headers.setContentDispositionFormData("attachment", filename); //application/octet-stream ： 二进制流数据（最常见的文件下载）。 headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file),headers, HttpStatus.CREATED); } }
3.解析工具类
package com.shenqz.util; import java.awt.Color; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.alibaba.fastjson.JSONObject; import com.google.common.collect.Lists; import com.shenqz.common.Contents; import com.shenqz.entity.InfoVoError; import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException; public class ImportExcelUtil { private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel /** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException */ public List<List<Object>> getBankListByExcel(Workbook work,String fileName,Integer num, HttpServletResponse response,HttpServletRequest request) throws Exception{ Contents.errorDownLoad = "0"; /** 有问题的信息列出来 */ List<HashMap<String, String>> errorlist = Lists.newArrayList(); List<List<Object>> list = null; if(null == work){ throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if(sheet==null){continue;} //遍历当前sheet中的所有行 for (int j = num; j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if(row==null||row.getFirstCellNum()==j){continue;} //遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { // 取出一个单元格 cell = row.getCell((int) y); // 重置保存单元格内容的变量为空白 String strValue = ""; int v = 0; // 取出一个单元格内容 if(cell != null){ if (cell.getCellType() == Cell.CELL_TYPE_STRING) { strValue = cell.getStringCellValue().trim(); } if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ Date d=cell.getDateCellValue(); DateFormat formater=new SimpleDateFormat("yyyy/MM/dd"); if(HSSFDateUtil.isCellDateFormatted(cell)){ strValue=formater.format(d); } else{ strValue = String.valueOf(((Double) cell .getNumericCellValue()).longValue()); if(y==5){ try { Date date = formater.parse("1900/01/01"); Calendar a = Calendar.getInstance(); a.setTime(date); a.add(Calendar.DATE, (Integer.valueOf(strValue)-2)); strValue=formater.format(a.getTime()); } catch (NumberFormatException e) { e.printStackTrace(); } catch (ParseException e) { e.printStackTrace(); } } } } } if(y == 0){ if(strValue == null){ errorlist.add(setCheckErrorMap(fileName,"第",String.valueOf(j+1),"行的A列不能为空")); }else{ li.add(strValue); v++; } continue; } if(y == 1){ if(strValue == null){ errorlist.add(setCheckErrorMap(fileName,"第",String.valueOf(j+1),"行的B列不能为空")); }else{ li.add(strValue); v++; } continue; } if(y == 2){ if(strValue == null){ errorlist.add(setCheckErrorMap(fileName,"第",String.valueOf(j+1),"行的C列不能为空")); }else{ li.add(strValue); v++; } continue; } if(y == 3){ if(strValue == null){ errorlist.add(setCheckErrorMap(fileName,"第",String.valueOf(j+1),"行的D列不能为空")); }else{ String regex="1[0-9]{10}"; Pattern p=Pattern.compile(regex); Matcher m=p.matcher(strValue); if(!m.matches()){ errorlist.add(setCheckErrorMap(fileName, "第",String.valueOf(j+1),"行的D列手机号不是11位")); }else{ li.add(strValue); v++; } } continue; } if(y == 4){ li.add(strValue); continue; } if(y == 5){ li.add(strValue); continue; } if(y == 6){ li.add(strValue); } if(v == 4){ list.add(li); } } } } if(errorlist.size() > 0){ Contents.errorDownLoad = "1"; OutputStream os = null; Workbook wb = createExportContent(errorlist); try { /*wb.write(response.getOutputStream());*/ //设置Excel表名 String path = request.getServletContext().getRealPath("/error/"); String filename = Contents.DOWNLOAD_FILENAME + ".xlsx"; File filepath = new File(path,filename); if (!filepath.getParentFile().exists()) { filepath.getParentFile().mkdirs(); } //将上传文件保存到一个目标文件当中 // 设置输入流 FileOutputStream fOut = new FileOutputStream(path+File.separator+filename); // 将模板的内容写到输出文件上 wb.write(fOut); fOut.flush(); wb.close(); // 操作结束，关闭文件 fOut.close(); /*os = response.getOutputStream(); response.reset(); response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode(filename, "UTF-8")); response.setContentType("application/octet-streem");*/ /*ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); ByteArray bytes = os.toByteArray(); response.reset(); response.setContentType("application/msexcel;charset=utf-8"); response.setHeader("Content-disposition", "attachment;filename= "+ fileName); response.getOutputStream().write(bytes.getRawBytes());*/ } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } /*finally{ if (null != os) { try { os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } if (null != wb) { try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } }*/ } return list; } /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */ public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{ Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if(excel2003L.equals(fileType)){ wb = new HSSFWorkbook(inStr); //2003- }else if(excel2007U.equals(fileType)){ wb = new XSSFWorkbook(inStr); //2007+ }else{ throw new Exception("解析的文件格式有误！"); } return wb; } /** * 描述：对表格中数值进行格式化 * @param cell * @return */ @SuppressWarnings("deprecation") public Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if("General".equals(cell.getCellStyle().getDataFormatString())){ value = df.format(cell.getNumericCellValue()); }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){ value = sdf.format(cell.getDateCellValue()); }else{ value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } /** * 判断导入的表格标题行是否正确 * @return * @throws Exception */ public boolean isExistTitles(XSSFWorkbook wb, String[] titles) throws Exception{ //创建Excel工作薄 if(null == wb){ return false; } Sheet sheet = null; Row row = null; Cell cell = null; //遍历Excel中所有的sheet for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); if(sheet==null){continue;} //遍历第二行 row = sheet.getRow(1); for (int y = 0; y < titles.length; y++) { cell = row.getCell(y); if(!cell.getStringCellValue().equals(titles[y])){ return false; } } } wb.close(); return true; } /** * 存放错误信息的map * @param organname * @param item * @param row * @param cell * @param errorInfo * @return */ public static HashMap setCheckErrorMap(String filename,String item, String row,String errorInfo){ HashMap checkmap = new HashMap(); checkmap.put("filename", filename); checkmap.put("ITEM", item); checkmap.put("ROWS", row); checkmap.put("ERROR", errorInfo); return checkmap; } //创建导入失败的excel文件 private XSSFWorkbook createExportContent(List<HashMap<String, String>> errorlist ) { // 创建XSSFWorkbook XSSFWorkbook wb = new XSSFWorkbook(); // 创建表单并设置cell宽度 XSSFSheet currentSheet = wb.createSheet("Sheet1"); currentSheet.setDefaultColumnWidth(20); // 创建表头 createTitle(currentSheet,Contents.errorCoumns); // 创建cellStyle XSSFCellStyle style = wb.createCellStyle(); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); //设置cell底色 style.setFillForegroundColor(new XSSFColor(Color.red)); // 插入表内容 int currentRow = 1; Row row = currentSheet.getRow(0); Cell cell = null; for(int i=0;i<errorlist.size();i++){ HashMap<String,String> map = errorlist.get(i); int cellIndex = 0; // 行 row = currentSheet.createRow(currentRow); // 列 cell = row.createCell(cellIndex++); cell.setCellValue(map.get("filename")); cell = row.createCell(cellIndex++); String str = map.get("ITEM")+map.get("ROWS")+map.get("ERROR"); cell.setCellValue(str); // 最后一个单元格设置样式 cell.setCellStyle(style); currentRow += 1; } return wb; } //创建导入失败的表头 private void createTitle(XSSFSheet sheet, String[] titles) { Row row = sheet.createRow(0); for (int i = 0; i < titles.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(titles[i]); } } //校验文件头部信息是否匹配 private boolean checkExcelTitles(XSSFWorkbook wb, String[] titles , JSONObject result) throws Exception { String title=wb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue().trim(); if(!"机构信息表".equals(title)){ result.put("failMsg", "导入的不是机构信息表!"); return false; } if (!new ImportExcelUtil().isExistTitles(wb, titles)) { result.put("failMsg", "请使用模版进行导入！"); return false; } else { XSSFRow row = wb.getSheetAt(0).getRow(2); //判断第一行是否有数据 if (row == null) { result.put("failMsg", "导入表格为空，请填写完信息后重新导入！"); return false; } //有空格的空白行也视为表格为空 boolean flag = false; for (int i = 0; i < Contents.importInfoVoColumns.length; i++) { if (row.getCell(i) != null && row.getCell(i).getStringCellValue().trim() != null) { flag = true; break; } } if (!flag) { // 判断第一行是否有数据 result.put("failMsg", "导入表格为空，请填写完信息后重新导入！"); return false; } //大于5000条不允许导入 int rowSum = wb.getSheetAt(0).getLastRowNum(); if (rowSum > 5000) { // 判断数据是否大于5000条 result.put("failMsg", "电话信息条数超过5000条，请删减后重新导入！"); return false; } return true; } } }