excel上传与下载

pom

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

 

 

一、excel下载

1.前台向后台发送数据。

因为ajax无法实现下载，所以的用的是from表单提交

//将from表单写在方法中
  $(".daochu").click(function(){
    var data=getQueryUtility();
//方式1
{
   //向后台传送的数据
    var form= $("<form action='提交路径"+"' method='post'></form>")
    form.append("<input type='hidden' name='date1' value='"+data.date1+"'>");
    form.append("<input type='hidden' name='date2' value='"+data.date2+"'>");
    form.append("<input type='hidden' name='selectModel' value='"+data.selectModel+"'>");
    form.append("<input type='hidden' name='number' value='"+data.number+"'>");
    $(document.body).append(form);
    form.submit();
}
//方式2
{
 window.location.href='[[@{/portal/WhiteListsController/download/}]]';//用这种方式也可以
}


  });

2.后台

a.后台接受数据

  /**
     * 
     *queryUtility 接受数据自动封装为一个对象
     * @return 是否下载成功
     */
    @RequestMapping(value="/downNetSafety", method = {RequestMethod.POST})
    public String downNetSafety( QueryUtility queryUtility, HttpServletRequest request, HttpServletResponse response){
       //数据库获取的数据
        List<Map<String,String>> listsMap=getNetSafetyData(queryUtility);

        String sheetName[]={};//表头
        String excelname=;//excel表单名
        String [] keys=[];keys,通过key获得value
        String [] headerName=[];表头
       //获取excel表格
        HSSFWorkbook workbook=SetNetSafetyExcel(keys,headerName,listsMap,excelname);
       //excel表单
        String fileName=excelname+".xls";
       //将数据下载
        downExcel(fileName,workbook,request,response);
        return null;
    }

b.写成一个excel文档

 /**
     *
     * @param keys 获取数据key
     * @param headerName 表头
     * @param listsMap  数据
     * @param sheetname sheet名
     * @return 返回excel文件
     */
    private  HSSFWorkbook SetNetSafetyExcel(  String [] keys, String [] headerName, List<Map<String,String>> listsMap,String sheetname){
        HSSFWorkbook workbook = new HSSFWorkbook();

        //设置excel表格
        HSSFSheet sheet = workbook.createSheet(sheetname);
        HSSFRow row = sheet.createRow(0);
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth(i, 20*256);
        }
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
     //设置excel第一行表头
        HSSFCell cell;
        for(int i=0;i<headerName.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(headerName[i]);
            cell.setCellStyle(style);
        }
     //将数据写入excel
        for(int i=0,k=listsMap.size();i<k;i++){
            HSSFRow row1 = sheet.createRow(i+1);
            Map<String,String> map=listsMap.get(i);
            for(int j=0,g1=keys.length;j<g1;j++){
                HSSFCell cell1=row1.createCell(j);
                cell1.setCellValue(map.get(keys[j]));
            }
        }
        return  workbook;
    }

c.,下载

  /**
     *
     * @param fileName   文件名
     * @param workbook   文件
     * @param request
     * @param response  
     */
    private  void downExcel(  String fileName,HSSFWorkbook workbook,HttpServletRequest request, HttpServletResponse response){
        try {
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" +  
             java.net.URLEncoder.encode(fileName, "UTF-8"));
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
        }catch (Exception e){
            System.out.println(e);
        }
    }

二、excel上传

1.前台

 //前台上传
    function  updataWhiteList(){
        //上传的文件
        var formData = new FormData();
        formData.append("file",$("#file").prop("files")[0]);
        $.ajax({
            type: "POST",
            url: "",
            data: formData,
            async: false,
            cache: false,
            contentType: false,
            processData: false,
            success: function (data) {
                
            }, error: function (data) {
                alert("请求失败");
            }
        })

    }

2.后台

a.后台接受

    /**
     * file为输入文件
     */
    @RequestMapping("/updataWhiteList")
    @ResponseBody
    public String updata(@RequestParam(value = "file") MultipartFile excelFile, HttpServletRequest req, HttpServletResponse resp) {
       String result="";
        try {
         //判断是不是excel文件
           result= isExcelFile(excelFile);
         //获取excel文件
            Workbook woek=getWorkBook(excelFile);
         //读取数据并存入数据库
            result=CheckExcel(woek);
        } catch (Exception e){
            System.out.println(e);
        }
return result;
    }

b.判断是不是excel文件

/**
     * 检查是不是为excel文件
     * @param excelFile
     * @throws IOException
     */
    private String isExcelFile(MultipartFile excelFile) throws IOException{
        if(null == excelFile){
            return "文件不存在";
        }
        String fileName = excelFile.getOriginalFilename();
        if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
            return "不是excel文件";
        }
        return "格式正确";
    }

c.获取excel文件

    /**
     *
     * 获取不同版本的excel
     * @param formFile
     * @return
     */
    private Workbook  getWorkBook(MultipartFile formFile){
        String fileName = formFile.getOriginalFilename();
        Workbook workbook = null;
        try {
            //获得excel文件的io流
            InputStream is = formFile.getInputStream();

            //根据文件后缀名不同（xls和xlsx）获得不同的workbook实现类对象
            if(fileName.endsWith("xls")){
                //2003
                workbook = new HSSFWorkbook(is);

            }else if(fileName.endsWith("xlsx")){
                //2007

                 workbook = new XSSFWorkbook(is);

            }else {

            }
        } catch (IOException e) {

        }

        return workbook;
    }

d.将表格种每一格获取的数据转为字符串

 //将表格中数据转换为字符串
    private String getValue(Cell cell) {
        if (cell == null || "".equals(cell.toString())) {
            return "";
        }
        String value = "";
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            case NUMERIC:
                value = (new DecimalFormat("0")).format(cell.getNumericCellValue());
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return value;
    }

e.将数据读取出来存入数据库

/*
*
* 检查上传白名单是否符合要求
*
* */
private String CheckExcel(Workbook workbook){
    Sheet sheet=workbook.getSheetAt(0);
    //获取sheet行数
    int num=sheet.getPhysicalNumberOfRows();
    List<WhiteList> lists= new ArrayList<>();
//第一行为表头，直接从第二行开始获取
    for(int i=1;i<num;i++){
      //获取的数据
        WhiteList list= new WhiteList();
       
        Cell cell=row.getCell(0);
        String decive_number=getValue(cell);
        list.setDevice_number(decive_number);
      
        lists.add(list);
    }

    int size=lists.size();
    int page;
    if(size>500){
        page=size/500;
        int index=0;
 do {
//存入数据库，一次存入500条
whiteListService.insertList(lists.subList(0,500));
//清除500数据
lists.subList(0,500).clear();

     index++;
 }while (index<page);
    }
    if(lists.size()>0) {
        whiteListService.insertList(lists);
    }
        return "上传成功," +
                "成功上传了"+size+"条";
}