一、模板下载：
先将模板放在项目WebRoot下的download文件夹下：
/** * @Title: downloadFile * @Description: 模板下载 (网络地址) * @param @param id * @param @param url * @param @param fileName * @param @param response * @param @param request * @param @throws Exception * @return void * @throws */ @RequestMapping(value = "/downloadFile") public void downloadFile(String url, String fileName, HttpServletResponse response,HttpServletRequest request) throws Exception{ //要对文件名称进行编码 fileName = java.net.URLEncoder.encode(fileName,"utf-8"); response.addHeader("Content-Disposition","attachment;filename=" + fileName+";filename*=utf-8''"+fileName); response.setContentType("application/octet-stream"); //设置文件MIME类型 OutputStream out =null; InputStream in=null; //获取网站部署路径(通过ServletContext对象)，用于确定下载文件位置，从而实现下载 //String path = request.getServletContext().getRealPath("/"); //url = path + "download\" + "我的客户导入模板.xls"; try { URL urlPath = new URL(url);// 创建URL对象 in = urlPath.openStream();// 获取url中的输入流 out = response.getOutputStream(); BufferedInputStream bis = new BufferedInputStream(in); BufferedOutputStream bos = new BufferedOutputStream(out); byte[] buff = new byte[20480]; int b = 0; while (-1 != (b = bis.read(buff))) { bos.write(buff, 0, b); } bis.close(); bos.flush(); bos.close(); }catch(Exception e){ e.printStackTrace(); } finally { if(out!=null)out.close(); if(in!=null)in.close(); } }
/** * * @Title: download * @Description: 下载本地文件 * @param @param path * @param @param response * @param @param request * @return void * @throws */ @RequestMapping(value = "/download") public void download(String type, HttpServletResponse response, HttpServletRequest request) { User u = getUser(request.getSession());//SessionUtils.getUser(request.getSession()); //获取网站部署路径(通过ServletContext对象)，用于确定下载文件位置，从而实现下载 //path = request.getServletContext().getRealPath("/") + "download\" + "我的客户导入模板.xls"; String path = request.getServletContext().getRealPath("/"); List<BasedataResp> list1 = null; List<BasedataResp> list2 = null; QueryBasedataParam param1 = new QueryBasedataParam(); QueryBasedataParam param2 = new QueryBasedataParam(); param1.setCorpId(Long.valueOf(u.getCorpId())); param2.setCorpId(Long.valueOf(u.getCorpId())); String fileName = ""; if("1".equals(type)){ fileName = "我的客户导入模板"; param1.setLabel("custom_status"); //客户状态 param2.setLabel("custom_level"); //客户分级 } else if("2".equals(type)){ fileName = "客户关联联系人导入模板"; param1.setLabel("contacts_role"); //角色关系 param2.setLabel("contacts_relation"); //亲密程度 } String url = path + "download\" + fileName + ".xls"; try { if(param1.getLabel()!=null && !"".equals(param1.getLabel())){ list1 = basedataService.selectBasedataInfo(param1); //查询系统标签 list2 = basedataService.selectBasedataInfo(param2); //查询系统标签 write(url, list1, list2); } ExcelExportUtil.getExcel(url, fileName, response); //下载sheet } catch (Exception e) { System.out.println(e.getMessage()); } } /** * * @Title: write * @Description: 向已存在的Excel写入数据 * @param @param file * @param @param list * @param @param list2 * @param @return * @return String * @throws */ private String write(String file, List<BasedataResp> list, List<BasedataResp> list2) { try { FileInputStream fs = new FileInputStream(file); //获取已有的Excel POIFSFileSystem ps = new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息 HSSFWorkbook wb = new HSSFWorkbook(ps); HSSFSheet sheet1 = wb.getSheetAt(0); //获取第一个工作表，一个excel可能有多个工作表 HSSFSheet sheet2 = wb.getSheetAt(1); //获取第二个sheet HSSFSheet sheet3 = wb.getSheetAt(2); //获取第三个sheet sheet2.removeRow(sheet2.getRow(0)); sheet3.removeRow(sheet3.getRow(0)); FileOutputStream out = new FileOutputStream(file); //向d://test.xls中写数据 HSSFRow row;
//向第二个sheet写入数据（第一个sheet中的下拉选项） for (int i = 0; i < list.size(); i++) { row = sheet2.createRow((short)(i)); //创建行 row.createCell(0).setCellValue(list.get(i).getName()); //设置第一个（从0开始）单元格的数据 }
//向第三个sheet写入数据（第一个sheet中的下拉选项） HSSFRow row2; for (int i = 0; i < list2.size(); i++) { row2 = sheet3.createRow((short)(i)); //创建行 row2.createCell(0).setCellValue(list2.get(i).getName()); //设置第一个（从0开始）单元格的数据 } out.flush(); wb.write(out); out.close(); } catch (Exception e) { System.out.println(e.getMessage()); } return "success"; }
/** * * @Title: getExcel * @Description: 下载指定路径的Excel文件 * @param @param url 文件路径 * @param @param fileName 文件名 * @param @param response * @return void * @throws */ public static void getExcel(String url, String fileName, HttpServletResponse response){ try { //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型 response.setContentType("multipart/form-data"); //2.设置文件头：最后一个参数是设置下载文件名 response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls"); //支持中文文件名 //通过文件路径获得File对象 File file = new File(url); FileInputStream in = new FileInputStream(file); //3.通过response获取OutputStream对象(out) OutputStream out = new BufferedOutputStream(response.getOutputStream()); int b = 0; byte[] buffer = new byte[2048]; while ((b=in.read(buffer)) != -1){ out.write(buffer,0,b); //4.写到输出流(out)中 } in.close(); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } }
模板截图：
二、通过Excel模板导入数据：
/** * * @Title: impExcel * @Description: 批量导入客户信息 * @param @param request * @param @param response * @param @return * @return String * @throws */ @RequestMapping("impExcel") @ResponseBody public String impExcel(MultipartHttpServletRequest request,HttpServletResponse response){ ReturnStandardDataFormat standardData = new ReturnStandardDataFormat(CustomConstants.CUSTOM_SELECT_EXCEPTION,"导入客户信息失败",null); MultipartFile file = request.getFile("file"); ExcelReader er = new ExcelReader(); int count =0; int error =0; int success = 0; List<Custom> list_ = new ArrayList<Custom>(); User u = getUser(request.getSession());//SessionUtils.getUser(request.getSession()); Long corpId = Long.valueOf(u.getCorpId()); Date date = new Date(); String returnMsg = ""; int index = 1; try { List<Map<Integer,String>> list = er.readExcelContentByList(file.getInputStream()); //读取Excel数据内容 count = list.size(); for(Map<Integer,String> map : list){ if(map.get(0)==null || "".equals(map.get(0))){ returnMsg += "第"+index+"行：【客户简称(必填)】列不能为空;"; } else if(map.get(1)==null || "".equals(map.get(1))){ returnMsg += "第"+index+"行：【客户全称(必填)】列不能为空;"; } else { int num = 0; QueryCustomParam params = new QueryCustomParam(); params.setShortName(map.get(0)); params.setCorpId(Long.valueOf(u.getCorpId())); num = customService.checkCustom(params); //查询相同客户 if(num==0){ Custom custom = new Custom(); custom.setId(UUIDUtil.getLongUUID()); custom.setShortName(map.get(0)==null? null : map.get(0)); custom.setName(map.get(1)==null? null : map.get(1)); custom.setNumber(map.get(2)==null? null : map.get(2)); custom.setAddress(map.get(3)==null? null : map.get(3)); custom.setUrl(map.get(4)==null? null : map.get(4)); custom.setDescription(map.get(5)==null? null : map.get(5)); custom.setCustomStatusId(map.get(6)==null? null : basedataService.getLabelId("custom_status", map.get(6), corpId) ); custom.setCustomLevelId(map.get(7)==null? null : basedataService.getLabelId("custom_level", map.get(7), corpId) ); custom.setCreaterId(Long.valueOf(u.getUserId())); custom.setCreateDate(date); custom.setUpdaterId(Long.valueOf(u.getUserId())); custom.setUpdateDate(date); custom.setCorpId(Long.valueOf(u.getCorpId())); list_.add(custom); } else { returnMsg += "第"+index+"行：【客户简称(必填)】列："+ map.get(0)+"已存在;"; } index++; } } int cuccess = customService.batchInsert(list_); //批量导入客户信息 standardData.setReturnCode(0); standardData.setReturnData(null); error = count - success; standardData.setReturnMessage(returnMsg); } catch (Exception e) { log.error("批量导入客户信息异常：" + e.getMessage()); standardData.setReturnMessage(e.getMessage()); } return JsonHelper.encodeObject2Json(standardData, "yyyy-MM-dd HH:mm:ss"); }
读取Excel内容工具类：
/** * 读取Excel数据内容 * @param InputStream * @return List<Map<String, String>> Map的key是列Id(0代表第一列)，值是具体内容 */ public List<Map<Integer, String>> readExcelContentByList(InputStream is) { List<Map<Integer, String>> list = new ArrayList<Map<Integer,String>>(); try { //fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(is); //wb = new XSSFWorkbook(is); } catch (IOException e) { e.printStackTrace(); } sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; Map<Integer,String> map = new HashMap<Integer, String>(); while (j < colNum) { // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据 // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean // str += getStringCellValue(row.getCell((short) j)).trim() + // "-"; map.put(j, getCellFormatValue(row.getCell((short) j)).trim().replaceAll(" ", "")); //str += getCellFormatValue(row.getCell((short) j)).trim() + " "; j++; } list.add(map); } return list; }