Springboot中excel文件的上传与下载

最近开发了一个小项目，因为有前后端交互，便使用springboot进行了开发，其中涉及到excel的下载与上传，现整理出来，下面直接贴代码。

需要引入的jar包

<!--  poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

1、excel上传jsp页面

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" xmlns="http://www.w3.org/1999/xhtml"
>
<head>
    <meta charset="utf-8"/>
    <title>处罚平台订单管理</title>
</head>
<!-- BEGIN BODY -->
<body class="childBody">
<!-- BEGIN CONTAINER -->
<div >
 <form id="file_form" action="http://localhost:7010/basics/bwList/batchImport"
          enctype="multipart/form-data" method="post">
            <input type="file" name="file" id="file_input">
            <input type="submit" value="导入数据" class="file">
        </div>
    </form> 
</div>
</body>
<!-- END BODY -->
</html>

2、后台处理程序

controller层：

/**
 * 上传excel数据
 * @param file
 */
@RequestMapping(value = "/batchImport",method = {RequestMethod.POST,RequestMethod.GET})
@ResponseBody
public Result batchImport(@RequestParam(value="file") MultipartFile file){
    return bwListService.batchImport(file ,getCurrentUser());
}

service层：

public Result batchImport(MultipartFile mfile ,User user) {
    Result result=new Result();
    //初始化输入流
    InputStream is = null;
    String fileName=mfile.getOriginalFilename();
    try{
        //根据新建的文件实例化输入流
        is = mfile.getInputStream();
        //根据版本选择创建Workbook的方式
        Workbook wb = null;
        //根据文件名判断文件是2003版本还是2007版本
        if(ExcelUtil.isExcel2007(fileName)){
            wb = new XSSFWorkbook(is);
        }else{
            wb = new HSSFWorkbook(is);
        }
        //根据excel里面的内容读取知识库信息
        String msg=readExcelValue(wb,user);
        if(msg.contains("失败")){
            result.setSuccess(false);
            result.setCode(DatabaseConstants.CONFLICT);
        }
        result.setMsg(msg);
    }catch(Exception e){
        e.printStackTrace();
    } finally{
        if(is !=null)
        {
            try{
                is.close();
            }catch(IOException e){
                is = null;
                e.printStackTrace();
            }
        }
    }
    return result;
}

3、excel下载

controller层：

/**
 *下载excel模板
 * @param response
 */
@RequestMapping(value = "/downLoadExcel",method = {RequestMethod.POST,RequestMethod.GET})
@ResponseBody
public void downLoadExcel(HttpServletResponse response){
    bwListService.downLoadExcel(response);
}

service层：

@Override
public void downLoadExcel(HttpServletResponse response) {
    List<String> header= Arrays.asList("业务类型","场景","维度类型","维度值","名单类型");
    String name="黑白名单";
    ExcelUtil.exportExcel(response,header,name);
}

/**
 * 导出excel
 * @param response
 * @param header
 * @param name
 */
public static void exportExcel(HttpServletResponse response,List<String> header,String name){
    response.reset();
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename="+DateUtil.formatLDateTime(new Date())+"_template.xls");
    OutputStream outputStream = null;
    try {
        Workbook workbook=generateWorkbook(name,"XLS",header);
        outputStream = response.getOutputStream();
        workbook.write(response.getOutputStream());
        workbook.close();
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
 * 将数据写入指定path下的Excel文件中
 * @param name sheet名
 * @param style Excel类型
 * @param titles 标题串
 * @return T\F
 */
public static Workbook generateWorkbook(String name, String style, List<String> titles) {
    Workbook workbook;
    if ("XLS".equals(style.toUpperCase())) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }
    // 生成一个表格
    Sheet sheet = workbook.createSheet(name);
    // 设置表格默认列宽度为15个字节
    sheet.setDefaultColumnWidth((short) 36);
    // 生成样式
    Map<String, CellStyle> styles = createStyles(workbook);
    /*
     * 创建标题行
     */
    Row row = sheet.createRow(0);
    for (int i = 0; i < titles.size(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(titles.get(i));
    }

    int totalRows=10000;
    for(int j=0;j<totalRows;j++){
        row = sheet.createRow(j+1);
        for (int k=0;k<titles.size();k++){
            Cell cell = row.createCell(k);
            cell.setCellStyle(styles.get("cell"));
        }
    }
  
    return workbook;
}