EasyUI中SpringMVC导入导出功能

SpringMVC导入导出功能

<!-- poi的依赖 -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.13</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.14</version>
</dependency>

1、导入

/**
 * 下载模板
 * @param response
 * @param request
 * @return
 */
 /**
 * 导入的包：import org.apache.commons.io.FileUtils;
 */
@RequestMapping(value = "/downLoadTemplate")
public ResponseEntity<byte[]> downloadTemplate(HttpServletResponse response, HttpServletRequest request) {
   try {

      //获取模版地址
      String path = request.getSession().getServletContext().getRealPath("/templet/网站信息导入模板.xlsx");
      //创建该文件对象
      File file = new File(path);
      //设置响应头
      HttpHeaders headers = new HttpHeaders();
      //通知浏览器以下载的方式打开文件
      headers.setContentDispositionFormData("attachment", URLEncoder.encode("网站信息导入模板.xlsx", "UTF-8"));
      //定义以流的形式下载返回文件数据
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

      //使用springmvc框架的ResponseEntity对象封装返回数据
      return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);
   } catch (IOException e) {
      log.error("模板下载错误");
      return null;
   }
}

//批量导入
   @ResponseBody
   @RequestMapping(value = "/doImport",  method = {RequestMethod.GET,RequestMethod.POST})
   public String doImport(HttpServletResponse response, MultipartHttpServletRequest request){
      JSONObject result = new JSONObject();
      Iterator<String> iterator = ((MultipartHttpServletRequest)request).getFileNames();
      while (iterator.hasNext()) {
         String fileName = iterator.next();
         MultipartFile multipartFile = ((MultipartHttpServletRequest) request).getFile(fileName);
         // 保存上传文件到新的路径
         String classPath = ConfigManager.getAttatchmentsPath() + File.separator + "source" + File.separator
               + multipartFile.getOriginalFilename();
         File upload = new File(classPath);
         if (!upload.exists())
            upload.mkdirs();
         try {
            multipartFile.transferTo(upload);
         } catch (Exception e) {
            log.error("执行出现错误");
         }
         try {
            File excel = new File(classPath);
            Sheet sheet = null;
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在
               String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
               //根据文件后缀（xls/xlsx）进行判断
               if ( "xls".equals(split[1])){
                  try (FileInputStream fis = new FileInputStream(excel);
                      Workbook wb = new HSSFWorkbook(fis)) {
                     sheet = wb.getSheetAt(0);
                  } catch (IOException e) {
                     log.error(e);
                  }
               }else if ("xlsx".equals(split[1])){
                  try (Workbook wb = new XSSFWorkbook(excel)) {
                     sheet = wb.getSheetAt(0);
                  } catch (IOException e) {
                     log.error(e);
                  }
               }else {
                  return null;
               }

           List<WebSiteInfoModel> list = new ArrayList<>();
           //开始解析           upload.delete();
           excel.delete();
           int firstRowIndex = sheet.getFirstRowNum()+1;   //前1行是列名，所以不读
           int lastRowIndex = sheet.getLastRowNum();
           //解析数据  //遍历行
           for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
              Row row = sheet.getRow(rIndex);
              if (row != null) {
                 int firstCellIndex = row.getFirstCellNum();
                 int lastCellIndex = row.getLastCellNum();
                  // 用实体类接收，放到List里面，用getRowData 方法读入excel的内容
                 WebSiteInfoModel model1 = getRowData(row,firstCellIndex,lastCellIndex);
                 list.add(model1);
              }
           }
           //做插入
           webInfoService.insertWebsitInfo(list);
        }
     }catch (Exception e) {
        result.put("flag","failture");
        return result.toJSONString();
     }
  }
  result.put("flag","success");
  return result.toJSONString();

   }

// 对应上面的导入，解析excel中的行做导入处理
private WebSiteInfoModel getRowData(Row row,int firstCellIndex,int lastCellIndex){
   WebSiteInfoModel webSiteInfoModel = new WebSiteInfoModel();
   for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
      Cell cell = row.getCell(cIndex);
      if(cell == null){
         continue;
      }
      //第0列  是序号不处理
      if (cIndex==1){
         webSiteInfoModel.setA(cell.toString());
      }else if (cIndex==2){
         webSiteInfoModel.setB(cell.toString());
      }else if (cIndex==3){
         webSiteInfoModel.setC(cell.toString());
      }else if (cIndex==4){
         webSiteInfoModel.setD(cell.toString());
      }else if (cIndex==5){
         webSiteInfoModel.setE(cell.toString());
      }else if (cIndex==6){
         webSiteInfoModel.setF(cell.toString());
      }else if (cIndex==7){
         webSiteInfoModel.setG(cell.toString());
      }else if (cIndex==8){
         webSiteInfoModel.setH(cell.toString());
      }

   }
   return webSiteInfoModel;
}

/*
* js方法的导入
*/
function upload(url){
    url = '<%=basePath%>' + "websiteinfo/doImport.xhtml";
    $("#fileuploader").uploadFile({
        url:url,
        fileName:"myfile",
        allowedTypes:"xls,xlsx",  //扩展名限制
        multiple:false,    //是否允许选择多个文件
        enctype:"multipart/form-data",
        showProgress:false,    //是否显示进度
        showDelete: false,    //是否显示删除按钮
        maxFileCount:5,          //最大同时上传文件数
        showStatusAfterSuccess: true,//是否显示上传成功状态
        showStatusAfterError: true, //是否显示上传失败状态
        showDownload:false,
        downloadCallback: true,
        showView:false,
        showFileSize:false,
        deleteCallback:function(data,pd)//删除回掉函数
        {
            var filename = pd.filename[0].innerText.split('(')[0];
        },
        onSuccess:function (files, response, xhr, pd)
        {
            alert("上传成功");
        },
        onError: function (files, status, message, pd) {
            alert("上传失败");
        }
    });
}

2、导出

$('#export').click(function(){
    // 获取datagrid中选中的行
    var checkedItems = $('#tt').datagrid('getChecked');
    // 定义数组准备传值
    var chrids = [];
    $.each(checkedItems, function(index, item){
        // 将选中的行的chrid传入，传给后端
        chrids.push(item.chrid);
    });
    if(chrids.length==0){
        alert("请勾选需要导出的网站！");
    }else{
        window.location.href="<c:out value='${pageContext.request.contextPath}'/>/websiteinfo/dexportExcel.xhtml?chrids="+chrids.join(",");
    }
}

// 批量导出
   @RequestMapping(value = "/dexportExcel", method = {RequestMethod.GET,RequestMethod.POST})
   public void  dexportExcel(HttpServletRequest request, HttpServletResponse response) {
      String[] chrids = request.getParameter("chrids").split(",");
      XSSFWorkbook workbook = null;
      try {
         workbook = new XSSFWorkbook();
         workbook.createSheet("Sheet1");
         XSSFFont font1 = workbook.createFont();
         font1.setFontName("宋体");// 设置字体
         font1.setFontHeightInPoints((short) 14);// 字体大小
         font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
         //设置表头样式
         XSSFCellStyle titleStyle = workbook.createCellStyle();
         titleStyle.setFont(font1);//将样式加到title里
         titleStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
         titleStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
         titleStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
         titleStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
         titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
         titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中对齐
         //titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); 此处显示高亮ORANGE
         titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);//此处显示白色
         titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
         titleStyle.setWrapText(true); // 指定单元格自动换行

         //设置数据列样式
         XSSFCellStyle dataStyle = workbook.createCellStyle();
         dataStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
         dataStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
         dataStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
         dataStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
         //dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);//此处显示高亮green
         dataStyle.setFillForegroundColor(HSSFColor.WHITE.index);//此处显示白色
         dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
         //dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

         //rowList是每一个sheet空间里的数据。。。rowList里存放的是Map  Map单位里就是具体的数据title和数据list
         XSSFSheet sheet = workbook.getSheetAt(0);
         sheet.setDefaultColumnWidth((short) 20);
         final int initRow = 1;
         final int initCol = 1;
         XSSFRow rTitle = sheet.createRow(initRow - 1);//创建一行
         //此处得到Map值，遍历title文件，给excel文件添加title ~~~~~begin~~~~~~~~~~~~~~~~~~
         Map titles = new HashMap();//是标题行的数据
         titles.put("XH","序号");
         titles.put("QYMC", "标题1");
         titles.put("TYSHXYDM", "标题2");
         titles.put("ICPNAME", "标题3");
         titles.put("ICPTIME", "标题4");
         titles.put("WZMC", "标题5");
         titles.put("WZ", "标题5");
         int titleFlag = initCol;
         String[] arr = arr = new String[titles.size()];
         arr[0] = "XH";
         arr[1] = "QYMC";
         arr[2] = "TYSHXYDM";
         arr[3] = "ICPNAME";
         arr[4] = "ICPTIME";
         arr[5] = "WZMC";
         arr[6] = "WZ";
         for (int j = 0; j < arr.length; j++) {
            XSSFCell cell = rTitle.createCell(j);//创建一列
            cell.setCellStyle(titleStyle);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(titles.get(arr[j])));  //保存value值
            titleFlag++;
         }
         rTitle.setHeight((short)( 45*20));
         XSSFCell cell = rTitle.createCell((short) (titleFlag)); //创建一行
         Map<String,Object> mapResult = new HashMap<>();
         List<Map<String,Object>> listResult = new ArrayList<>();
         // 循环从jsp得到的chrids数据
         for(int i=0;i<chrids.length;i++){
            Map mapChrid = new HashMap();
            mapChrid.put("chrid",chrids[i]);
            mapResult = service.selectAll();   //走后台接口，获取我所需要的数据，返回值就是标题行所需要的数据
            mapResult.put("XH",i+1);
            listResult.add(mapResult);
         }
         int listFlag = initRow;
            //对于每一个数据值进行便利结果集，对于每一个需要填值的地方进行创建行列空间
         for (Iterator it = listResult.iterator(); it.hasNext();) {
            XSSFRow row = sheet.createRow(listFlag);//创建一行
            row.setHeight((short)(30*20));
            //遍历数组，从数组中后去当前下表的值，获取Map中的key值，得到value
            Map<String, Object> listMap = (Map<String, Object>) it.next();
            for (int m = 0; m < arr.length; m++) {
               //arr[m] 这里解释了方法：ExcelDate.getTitleKey()中的备注：此key值应于数据结果集中的key值相呼应
               String value = listMap.get(arr[m]) != null ? listMap.get(arr[m]).toString() : "";
               cell = row.createCell(m);
               cell.setCellStyle(dataStyle);
               cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
               cell.setCellValue(value);
            }
            listFlag++;
         }
         OutputStream outputStream = null;
         try {
            //设置Http响应头告诉浏览器下载这个附件
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode("网站信息导出模板.xlsx","UTF-8"));
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
         } catch (Exception ex) {
            ex.printStackTrace();
         }finally {
            try {
               outputStream.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }
      } catch (Exception e) {
         log.error("执行出现错误");
      }finally {
         if (workbook != null){
            try {
               workbook.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }
      }


   }