springboot上传excel文件和下载excel（读取mysql表中数据,返回给前端excel文件）

注意事项：上传文件大小限制(不设置的话，文件超过1M会报错)：

spring:
  #数据源
  datasource:
      url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
  servlet:
      multipart:
#      最大文件大小。值可以使用后缀“MB”或“KB”。指示兆字节或千字节大小。
         max-file-size: 20MB
#         # 最大请求大小可以是mb也可以是kb
         max-request-size: 200MB

1.上传excel文件，保存在硬盘中：

    /**
     * 实现文件上传
     * */
    @RequestMapping("fileUpload")
    @ResponseBody 
    public String fileUpload(@RequestParam("fileName") MultipartFile file){
        if(file.isEmpty()){
            return "false";
        }
        String fileName = file.getOriginalFilename();
        int size = (int) file.getSize();
        System.out.println(fileName + "-->" + size);
        
        String path = "F:/test" ;
        File dest = new File(path + "/" + fileName);
        if(!dest.getParentFile().exists()){ //判断文件父目录是否存在
            dest.getParentFile().mkdir();
        }
        try {
            file.transferTo(dest); //保存文件
            return "true";
        } catch (IllegalStateException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "false";
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "false";
        }
    }

2.上传excel文件。保存在硬盘中，并且保证该目录只有一份相同名字的文件。

文件工具类

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * @description: 文件上传下载
 * @author: Administrator
 * @date: 2019-05-31 11:15
 */
public class FileAdminUtils {
    public static Log log = LogFactory.getLog(ExcelUtil.class);

    private static String filePath="G:/test";
    public static String multifileUpload(MultipartFile file,String newFileName){
        if(file.isEmpty()){
            return "false";
        }
//        String fileName = file.getOriginalFilename();
        int size = (int) file.getSize();
        log.info("文件名字=="+newFileName+"==文件大小=="+size);
//        log.info("文件名字=="fileName + "-->" + size);

        String path = filePath;
        File dest = new File(path + "/" + newFileName);
        if(!dest.getParentFile().exists()){ //判断文件父目录是否存在
            dest.getParentFile().mkdir();
        }
        try {
            //保存前先删除重复文件
            String del_result=delFile(newFileName);
            log.info("删除结果=="+del_result);

            file.transferTo(dest); //保存文件
            return "true";
        } catch (IllegalStateException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "false";
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "false";
        }
    }
//    删除文件
    public static String delFile(String fileName) {
        String resultInfo=null;
        String sb=filePath+"/"+fileName;
        File file = new File(sb);
        if (file.exists()) {
            if (file.delete()) {
                resultInfo =  "1-删除成功";
            } else {
                resultInfo =  "0-删除失败";
            }
        } else {
            resultInfo = "文件不存在！";
        }

        return resultInfo;
    }
//文件下载
    public static String  downLoad(HttpServletResponse response,String filename){
        File file = new File(filePath + "/" + filename);
        if(file.exists()){ //判断文件父目录是否存在
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            // response.setContentType("application/force-download");
            try {
                response.setHeader("Content-Disposition", "attachment;fileName=" +   java.net.URLEncoder.encode(filename,"UTF-8"));
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            byte[] buffer = new byte[1024];
            FileInputStream fis = null; //文件输入流
            BufferedInputStream bis = null;

            OutputStream os = null; //输出流
            try {
                os = response.getOutputStream();
                fis = new FileInputStream(file);
                bis = new BufferedInputStream(fis);
                int i = bis.read(buffer);
                while(i != -1){
                    os.write(buffer);
                    i = bis.read(buffer);
                }

            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            System.out.println("----------file download---" + filename);
            try {
                bis.close();
                fis.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return null;
    }

}

上传测试：

//上传excel表
@PostMapping("/upload")
public String upload(MultipartFile file) {

String newfileName="test.xls";//保存的文件名字

String result= FileAdminUtils.multifileUpload(file,newfileName);

System.out.println("保存文件结果=="+result);

return result;
}

3.下载excel文件：

@RequestMapping("/down")
public String downLoad(HttpServletResponse response) throws UnsupportedEncodingException {
    return FileAdminUtils.downLoad(response,"test.xls");
}

web调用下载接口：

window.open("http://xxxx/down?");

4.查询表中的数据，返回给前端excel:

    @RequestMapping("/test")
    public void downAll(HttpServletResponse response,String id) throws IOException {



        List<List<String>> excelData = new ArrayList<>();
        List<String> head = new ArrayList<>();
        head.add("ID");
        head.add("部门");
        head.add("名字");
        excelData.add(head);
        //查询表中的数据
        List<MyData> myDatas=myService.list();
        log.info("==有"+myDatas.size()+"条数据");
        if (myDatas.size()>0){
            for (int i=0;i<myDatas.size();i++){
                MyData  myData=myDatas.get(i);
                List<String> data = new ArrayList<>();
                data.add(myData.getId());
                data.add(myData.getDp());
                data.add(myData.getName());
               
                excelData.add(data);
            }
        }


        String sheetName = "sheet表";
        String fileName = "test.xls";
       // log.info("导出全部excel表");
        ExcelOutUtils.exportExcel(response, excelData, sheetName, fileName, 15);
        log.info("导出全部excel表成功");
    }

excel工具类：

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @description:
 * @author: Administrator
 * @date: 2019-05-29 14:31
 */
public class ExcelOutUtils {
    public static Log log = LogFactory.getLog(ExcelOutUtils.class);
    /**
     * Excel表格导出
     * @param response HttpServletResponse对象
     * @param excelData Excel表格的数据，封装为List<List<String>>
     * @param sheetName sheet的名字
     * @param fileName 导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);

        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);

        //写入List<List<String>>中的数据
        int rowIndex = 0;
        for(List<String> data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
            }
        }

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type","application/vnd.ms-excel");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //刷新缓冲
        response.flushBuffer();
        log.info("workbook将Excel写入到response的输出流中，供页面下载该Excel文件");
        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());
        log.info("关闭workbook");
        //关闭workbook
        workbook.close();
    }

}

web调用：

window.open("http://xxxx/down?id=1");

另一种下载方式：不知道为啥有时候不行，报Resource interpreted as Document but transferred with MIME type application/json ...：

    var $eleForm = $("<form method='get'></form>");
    $eleForm.attr("action",down_all_url);
    //$eleForm.append("<input name='id' type='hidden' value='"+1+"'>");
    //$(document.body).append($eleForm);
    //提交表单，实现下载
    $eleForm.submit();
    console.log("提交表单，实现下载=="); 
    $eleForm.remove();

另外一种导出excel方法（我觉得比较好）:https://blog.csdn.net/l1028386804/article/details/79659605

参考了：https://www.jianshu.com/p/be1af489551c