SpringBoot操作Excel实现单文件上传、多文件上传、下载、读取内容等功能

原创

刘大猫

修改于 2024-11-08 21:48:07

680

修改于 2024-11-08 21:48:07

文章被收录于专栏：Spring及SpringBoot相关

@toc

项目整体描述

页面图简介：

首页是一个index.html页面，长这样

功能概括说明

方法1：读取指定的Excel
方法2：读取上传的Excel里面的内容
方法3：java单文件导入Excel,保存到target的目录下
方法4：java多文件导入Excel,保存到target的目录下
方法5：java导出Excel

具体功能举例代码介绍

首页index.html

<!DOCTYPE html SYSTEM "http://www.thymeleaf.org/dtd/xhtml1-strict-thymeleaf-4.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>文件上传</title>
</head>
<body>
    <p>单文件上传并保存</p>
    <form method="post" action="/excel/uploadExcel" enctype="multipart/form-data">
        <p><input type="file" name="file00"></p>
        <p><span th:if="${msg}"></span></p>
        <input type="submit" value="提交">
    </form>

    <hr/>
    <p>多文件上传并保存</p>
    <form method="post" enctype="multipart/form-data" action="/excel/uploadBatchExcel">
        <p>文件1：<input type="file" name="file"/></p>
        <p>文件2：<input type="file" name="file"/></p>
        <p><input type="submit" value="上传"/></p>
    </form>

    <hr/>
    <p>excel单文件上传并输出读取内容</p>
    <form method="post" action="/excel/readUploadExcel" enctype="multipart/form-data">
        <p><input type="file" name="fileExcel"></p>
        <p><span th:if="${msg}"></span></p>
        <input type="submit" value="提交">
    </form>
</body>
</html>

操作成功跳转hello.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<p>单文件上传</p>
<p th:text="${msg}"></p>
<hr>
<p>多文件上传</p>
<ul>
    <li th:each="msg1:${msgList}" th:text="${msg1}"></li>
</ul>

</body>
</html>

首页跳转index.html代码

import com.alibaba.excel.EasyExcel;
import com.example.demo.bean.Teacher;
import com.example.demo.bean.TeacherListener;
import com.example.demo.setvice.ImportService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * 操作Excel
 * @Author 211145187
 * @Date 2022/2/22 19:43
 **/
@RequestMapping("/excel")
@Controller
public class OperateExcelController {
    @Autowired
    private ImportService importService;

    //首页
    @GetMapping(value = "")
    public String index() {
        //使用@RestController不能直接返回return "index"，否则不会跳转页面，只会再页面显示index文本而已
        return "index";
    }

方法1：读取指定的Excel

说明：需要用到阿里的第三方库easyexcel进行读取 excel长这样

pom

<!--读取excel文件-->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.2.6</version>
</dependency>

controller

//方法1：读取指定的Excel
    @RequestMapping(value = "/readExcel")
    @ResponseBody
    public List<Teacher> readExcel() {
        String fileName = "C:\\Users\\211145187\\Desktop\\fsdownload\\details (1).xls";
        TeacherListener teacherListener = new TeacherListener();
        EasyExcel.read(fileName, Teacher.class, teacherListener).sheet().doRead();
        return teacherListener.getTeacherList();
    }

Teacher

@Data
public class Teacher {
    //姓名
    private String name;
    //班级
    private String classes;
    //所属学院
    private String college;
    //别名
    private String alias;
}

TeacherListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;

import java.util.ArrayList;
import java.util.List;

/**
 * 教师读取类
 * @Author 211145187
 * @Date 2022/2/23 16:50
 **/
public class TeacherListener extends AnalysisEventListener<Teacher> {

    @Getter
    private List<Teacher> teacherList = new ArrayList<>();

    public TeacherListener() {
        super();
        teacherList.clear();
    }

    /**
     * 每一条数据解析都会调用
     */
    @Override
    public void invoke(Teacher teacher, AnalysisContext context) {
        teacherList.add(teacher);
    }

    /**
     * 所有数据解析完成都会调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        teacherList.forEach(System.out::println);
    }
}

最终展示结果

方法2：读取上传的Excel里面的内容

pom

<!--文件上传-->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.3</version>
</dependency>

controller//方法2：读取上传的Excel里面的内容 @RequestMapping(value = "/readUploadExcel") @ResponseBody public String readUploadExcel(HttpServletRequest request) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile file = multipartRequest.getFile("fileExcel"); if (file.isEmpty()) { return "文件不能为空"; } InputStream inputStream = file.getInputStream(); List<List<Object>> list = importService.getBankListByExcel(inputStream, file.getOriginalFilename()); inputStream.close(); list.forEach(System.out::println); return "上传成功并输出内容"; }service

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 211145187
 * @Date 2022/2/24 09:33
 **/
@Service
public class ImportService {

    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public List getBankListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }

                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }

}

最终展示结果

方法3：java单文件导入Excel,保存到target的目录下

controller

//方法3：java单文件导入Excel,保存到target的目录下
    @PostMapping(value = "/uploadExcel")
    public String uploadExcel(@RequestParam("file00") MultipartFile file, Model model) throws IOException {
        try {
            if(file.isEmpty()){
                model.addAttribute("msg","上传失败，请选择文件！");
                return "index";
            }
            String filename = file.getOriginalFilename();
            //filePath获取的是编译后的路径，而不是项目看到的路径，filePath=/E:/WorkSpace/demo/target/classes/
            String filePath = ResourceUtils.getURL("classpath:").getPath()+"static/oneFile/";
            //避免文件重复覆盖
            String uuid= UUID.randomUUID().toString().replaceAll("-", "");
            //时间戳分类文件
            String time = new SimpleDateFormat("YYYY-MM").format(new Date());
            String realPath = filePath + time + "/" + uuid + "-" + filename;
            System.out.println("realPath:" + realPath);
            //最后保存的路径在这里：target/classes/static/oneFile/2022-02/548881060e3d417a91d87b0a10959077-sop.sql
            File dest = new File(realPath);
            //检测是否存在目录，无，则创建
            if(!dest.getParentFile().exists()){
                dest.getParentFile().mkdirs();//新建文件夹 多级目录
            }
            file.transferTo(dest);//文件写入
        } catch (IOException e) {
            e.printStackTrace();
        }
        model.addAttribute("msg","文件上传成功！");
        return "hello";
    }

最终展示结果

方法4：java多文件导入Excel,保存到target的目录下

controller

//方法4：java多文件导入Excel,保存到target的目录下
    @PostMapping(value = "/uploadBatchExcel")
    public String uploadBatchExcel(HttpServletRequest request, Model model) throws IOException {
        MultipartRequest request1 = (MultipartRequest)request;
        //猜测 file为 input 类型为 file
        List<MultipartFile> fileList = request1.getFiles("file");
        List<String> msgList = new ArrayList<>();
        try {
            String filePath = ResourceUtils.getURL("classpath:").getPath()+"static/multiFile/";
            for (int i = 1; i <= fileList.size(); i++){
                MultipartFile file = fileList.get(i - 1);
                if (file.isEmpty()){
                    msgList.add("上传第"+i+"个文件失败");
                    model.addAttribute("msgList",msgList);
                    continue;
                }
                String filename = file.getOriginalFilename();
                //避免文件重复覆盖
                String uuid= UUID.randomUUID().toString().replaceAll("-", "");
                //时间戳分类文件
                String time = new SimpleDateFormat("YYYY-MM").format(new Date());
                String realPath = filePath + time + "/" + uuid + "-" + filename;
                System.out.println("realPath:" + realPath);
                File dest = new File(realPath);
                //System.out.println("realPath"+realPath);
                //检测是否存在目录，无，则创建
                if(!dest.getParentFile().exists()){
                    dest.getParentFile().mkdirs();//新建文件夹 多级目录
                }
                msgList.add("第"+i+"个文件，上传成功!");
                file.transferTo(dest);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        model.addAttribute("msgList",msgList);
        return "hello";
    }

最终展示结果

方法5：java导出Excel

pom

<!--操作Excel-->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.17</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>3.17</version>
 </dependency>

controller

//方法5：java导出Excel
    @RequestMapping("/exportExcel")
    public void createExcel(HttpServletResponse response) throws IOException {

        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        buildScoreSheet(wb);
        buildTeacherSheet(wb);
        //.....省略部分代码

        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/octet-stream;charset=iso8859-1");
        wb.write(output);
        output.close();
    }
    /**
     * 设置样式
     * @Author 211145187
     * @Date 2022/2/22 20:15
     * @Param wb wb
     * @Return CellStyle
     **/
    private CellStyle getHeadStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }
    /**
     * 构建参数
     * @Author 211145187
     * @Date 2022/2/22 20:20
     * @Param wb wb
     **/
    private void buildScoreSheet(HSSFWorkbook wb) {
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("成绩表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("学员考试成绩一览表");
        cell.setCellStyle(getHeadStyle(wb));
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

        //在sheet里创建第二行
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("笔试成绩");
        row2.createCell(3).setCellValue("机试成绩");
        //在sheet里创建第三行
        HSSFRow row3=sheet.createRow(2);
        row3.createCell(0).setCellValue("李明");
        row3.createCell(1).setCellValue("As178");
        row3.createCell(2).setCellValue(87);
        row3.createCell(3).setCellValue(78);
    }
    /**
     * 构建参数
     * @Author 211145187
     * @Date 2022/2/22 20:20
     * @Param wb wb
     **/
    private void buildTeacherSheet(HSSFWorkbook wb) {
        String[] titleData = new String[4];
        titleData[0] = "姓名";
        titleData[1] = "班级";
        titleData[2] = "所属学院";
        titleData[3] = "别名";
        List<Teacher> teacherList = new ArrayList<>();
        Teacher teacher1 = new Teacher();
        teacher1.setName("周杰伦");
        teacher1.setClasses("三年二班");
        teacher1.setCollege("魔法学院");
        teacher1.setAlias("Jay Chou");
        teacherList.add(teacher1);
        Teacher teacher2 = new Teacher();
        teacher2.setName("陈奕迅");
        teacher2.setClasses("三年二班");
        teacher2.setCollege("魔法学院");
        teacher2.setAlias("Eason");
        teacherList.add(teacher2);

        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("教师表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("教师一览表");
        cell.setCellStyle(getHeadStyle(wb));
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

        //在sheet里创建第二行
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        for (int i = 0; i < titleData.length; i++) {
            HSSFCell cell2 = row2.createCell(i);
            cell2.setCellValue(titleData[i]);
            cell2.setCellStyle(getHeadStyle(wb));
        }
        //在sheet里创建第三行
        for (int j = 0; j < teacherList.size(); j++) {
            Teacher teacher = teacherList.get(j);
            HSSFRow row3 = sheet.createRow(j + 2);
            for (int k = 0; k < titleData.length; k++) {
                HSSFCell cell3 = row3.createCell(k);
                if (k == 0) {
                    cell3.setCellValue(teacher.getName());
                } else if (k == 1) {
                    cell3.setCellValue(teacher.getClasses());
                } else if (k == 2) {
                    cell3.setCellValue(teacher.getCollege());
                } else {
                    cell3.setCellValue(teacher.getAlias());
                }
            }
        }
    }

最终展示效果包含2张表

可能出错场景说明

场景1：springboot配合thymeleaf，调用接口不跳转页面只显示文本

springboot配合thymeleaf，调用接口不跳转页面只显示文本

场景2：org.springframework.web.multipart.MultipartException: Current request is not a multipart request

org.springframework.web.multipart.MultipartException: Current request is not a multipart request

场景3：Error resolving template excel/readExcel, template might not exist or might not be accessible by

Error resolving template [excel/readExcel], template might not exist or might not be accessible by

场景4：springboot实现上传，前端必须指定enctype="multipart/form-data"，后端请求方式必须是post，且方法中添加注解或类型转换

前端

<hr/>
    <p>多文件上传并保存</p>
    <form method="post" enctype="multipart/form-data" action="/excel/uploadBatchExcel">
        <p>文件1：<input type="file" name="file"/></p>
        <p>文件2：<input type="file" name="file"/></p>
        <p><input type="submit" value="上传"/></p>
    </form>

后端

要么使用注解标注@RequestParam("file00") MultipartFile file
public String uploadExcel(@RequestParam("file00") MultipartFile file, Model model)

或者

要么request类型转换MultipartRequest request1 = (MultipartRequest)request
public String uploadBatchExcel(HttpServletRequest request, Model model) throws IOException {
        MultipartRequest request1 = (MultipartRequest)request;

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

金融