导入excel
不上传文件到服务器
1@RequestMapping(value = "intoDatabases") 2@ResponseBody 3public Map<String, Object> intoDatabases( 4 HttpServletRequest request, 5 MultipartFile file) 6 throws Exception { 7 Map<String, Object> map = new HashMap<String, Object>(); 8 if (file.isEmpty()) { 9 map.put("message", "文件错误，无法读取，请重试！"); 10 map.put("status", "error"); 11 map.put("icon", "5"); 12 } else { 13 map = numberService.intoDatabases(file.getInputStream(), 14SessionUtils.getUserSession(request)); 15 } 16 17 return map; 18} 19 20 21public Map<String, Object> intoDatabases(InputStream inputStream, UserSession session) { 22 Map<String, Object> map = new HashMap<String, Object>(); 23 try { 24 HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//接收到的报表对象 25 HSSFSheet sheet = workbook.getSheetAt(0); 26 //获得当前sheet的结束行 27 int lastRowNum = sheet.getLastRowNum(); 28 // System.out.println(lastRowNum); 29 List<Number> numbers = new ArrayList<Number>(); 30 for (int i = 1; i <= lastRowNum; i++) { 31 HSSFRow row = sheet.getRow(i); 32 Number number = new Number(); 33 String city = getStringCellValue(row.getCell(2));//城市 34 numbers.add(number); 35 } 36 37 //存储所有的号码 38 numberMapper.batchInsert(numbers); 39 40 //插入记录表 41 List<ImportRecord> list = new ArrayList<ImportRecord>(); 42 43 for (String key : cityNumber.keySet()) { 44 ImportRecord importRecord = new ImportRecord(); 45 importRecord.setBatch(batch);//批次 46 importRecord.setCityId(key);//城市id 47 importRecord.setCityName(areaMap.get(key));//城市名称 48 importRecord.setCount(cityNumber.get(key));//总数量 49 importRecord.setAdminId(session.getAdminId());//导入人 50 importRecord.setAdminName(session.getAdminName());//管理员名称 51 list.add(importRecord); 52 } 53 54 importRecordMapper.batchInsert(list); 55 56 map.clear(); 57 map.put("message", "操作成功"); 58 map.put("status", "success"); 59 map.put("icon", "6"); 60 } catch (Exception e) { 61 map.put("message", "操作失败"); 62 map.put("status", "error"); 63 map.put("icon", "5"); 64 e.printStackTrace(); 65 } 66 67 return map; 68 }
上传文件到服务器
1@RequestMapping(value = "importMarkerUnsubscribable") 2@ResponseBody 3public Map<String, Object> importMarkerUnsubscribable( 4 HttpServletRequest request, 5 MultipartFile file) 6 throws Exception { 7 Map<String, Object> map = new HashMap<String, Object>(); 8 if (file.isEmpty()) { 9 map.put("message", "文件错误，无法读取，请重试！"); 10 map.put("status", "error"); 11 map.put("icon", "5"); 12 } else { 13String filename = file.getOriginalFilename(); 14String realPath = request.getSession().getServletContext() 15 .getResource("/").getPath(); 16FileUtils.copyInputStreamToFile(file.getInputStream(), 17 new File(realPath, file.getOriginalFilename())); 18UserSession session = SessionUtils.getUserSession(request); 19 20 map = numberService.importMarkerUnsubscribable(realPath + filename, session ); 21 } 22 return map; 23} 24 25public Map<String, Object> importMarkerUnsubscribable(String filePath, UserSession session) { 26 27 Map<String, Object> map = new HashMap<String, Object>(); 28 HSSFWorkbook workbook = null;//接收到的报表对象 29 InputStream inputStream; 30 try { 31 inputStream = new FileInputStream(filePath); 32 workbook = new HSSFWorkbook(inputStream); 33 HSSFSheet sheet = workbook.getSheetAt(0); 34 //获得当前sheet的结束行 35 int lastRowNum = sheet.getLastRowNum(); 36 String[] numbers = new String[lastRowNum]; 37 for (int i = 1; i <= lastRowNum; i++) { 38 HSSFRow row = sheet.getRow(i); 39 numbers[i - 1] = getStringCellValue(row.getCell(0)); 40 } 41 File file = new File(filePath); 42 file.delete(); 43 44 map.put("numbers", numbers); 45 if (numbers.length > 0) { 46 // 47 applyListMapper.markerUnsubscribable(map); 48 49 } 50 map.clear(); 51 map.put("message", "标记可退订成功"); 52 map.put("status", "success"); 53 map.put("icon", "6"); 54 return map; 55 56 } catch (Exception e) { 57 map.put("message", "操作失败"); 58 map.put("status", "error"); 59 map.put("icon", "5"); 60 e.printStackTrace(); 61 } 62 return null; 63}
导出excel
1<a class="btn btn-success" href="${ctx}/number/downloadAllNumber">下载全部号码</a> 2 3location.href = "${ctx}/number/downloadNumbers?id=" + row.id + "&type=1"; 4 5/** 6 * @param req 7 * @param res 8 * @description 下载申请号码 9 * @author GUOPENG 10 * @date 2019.04.17 11 */ 12@RequestMapping(value = "downloadNumbers") 13public void downloadApplyNumbers( 14 HttpServletRequest req, 15 HttpServletResponse res, 16 @RequestParam(value = "id") String id, 17 @RequestParam(value = "type") Integer type) { 18 numberService.downloadNumbers(req, res, id, type); 19} 20 21@Override 22public void downloadNumbers( 23 HttpServletRequest request, 24 HttpServletResponse res, 25 String id, Integer type) { 26 27 OutputStream ouputStream = null; 28 HSSFWorkbook workbook = null;// 接收到的报表对象 29 InputStream inputStream; 30 try { 31 String path = request.getSession().getServletContext() 32 .getResource("/").getPath() + "下载号码-员工列表.xls"; 33 34 inputStream = new FileInputStream(path); 35 workbook = new HSSFWorkbook(inputStream); 36 HSSFSheet sheet = workbook.getSheetAt(0); 37 List<Number> list = new ArrayList<>(); 38 Map<String, Object> map = new HashMap<String, Object>(); 39 String batch = "", cityName =""; 40 if(type == 1){ 41 //员工 42 ImportRecord importRecord = importRecordMapper.findById(id); 43 batch = importRecord.getBatch(); 44 cityName = importRecord.getCityName(); 45 map.put("batch", batch); 46 map.put("cityId", importRecord.getCityId()); 47 list = numberMapper.findByBatchCityId(map); 48 } else if (type == 2){ 49 //渠道商 50 ApplyRecord applyRecord = applyRecordMapper.findById(id); 51 batch = applyRecord.getBatch(); 52 cityName = applyRecord.getCityName(); 53 map.put("applyId", id); 54 map.put("type", 1); 55 list = numberMapper.findByUseApplyId(map); 56 } 57 for (int i = 1; i <= list.size(); i++) { 58 Number number = list.get(i - 1); 59 HSSFRow row = sheet.createRow(i); 60 row.createCell(0).setCellValue(number.getCity());//地区 61 row.createCell(1).setCellValue(number.getNumber());//号码明细 62 row.createCell(2).setCellValue(number.getAreaCode());//区号 63 row.createCell(3).setCellValue(number.getImsi());//IMSI 64 row.createCell(4).setCellValue(number.getSmsc());//短信中心 65 row.createCell(5).setCellValue(number.getRemarks());//备注 66 } 67 res.setContentType("application/vnd.ms-excel;"); 68 res.setHeader("Content-disposition", "attachment;filename=" + new String((batch+"-"+cityName+".xls").getBytes("GB2312"), "ISO8859_1"));// 设定输出文件头 69 ouputStream = res.getOutputStream(); 70 workbook.write(ouputStream); 71 72 } catch (Exception e) { 73 e.printStackTrace(); 74 } finally { 75 try { 76 ouputStream.flush(); 77 ouputStream.close(); 78 } catch (IOException e) { 79 e.printStackTrace(); 80 } 81 82 } 83 84}
此博客仅供参考使用。