SpringBoot+thymeleaf模板引擎实现Excel表格的导入导出

Eternal-xin

于 2024-06-26 11:56:50 发布

阅读量250

点赞数 4

分类专栏： SpringBoot 文章标签： spring boot excel 后端

本文链接：https://blog.csdn.net/m0_53402965/article/details/139985472

版权

SpringBoot 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一：环境

//父工程项目依赖2.7.14  jdk版本1.8
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.14</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

1.导入依赖

<!--thymeleaf模板引擎-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>

用于操作Microsoft Office文档
<!-- Apache POI dependencies -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version> <!-- 使用时请检查最新版本 -->
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version> <!-- 使用时请检查最新版本 -->
</dependency>
用于读取、写入和操作Excel文件（特别是较旧的.xls格式）
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

2.前端页面

前端代码：

//使用form表单发送post请求   style="display: none"(隐藏form表单)
<button onclick="uploadExcel.click()">导入</button>
<form th:action="@{/insertExcel}" method="post" style="display: none" enctype="multipart/form-data">
    <input type="file" id="uploadExcel" name="file" value="表格上传" onchange="changepic(this)" />
    <input type="submit" id="clickButton" class="btn btn-primary">上传文件</input>
</form>
<script>
    function changepic(input) {
        var file = input.value;
        if(file != null){
            document.getElementById("clickButton").click();
        }
    }
</script>
<button onclick="clickButton1.click()">导出</button>
<form th:action="@{/exportCarByExcel}" method="post" style="display: none" >
    <button type="submit" id="clickButton1" class="btn btn-primary"></button>
</form>

二：后端代码：

1.实体类（Student）

@Data
public class User {
  private Integer id;
  private String userName;
  private String password;
  private String nickName;
  private String phone;
  private String email;
  private long roleId;
}

2.Controller

/**
 * 导入excel表格
 */
@PostMapping("/insertExcel")
public String insertStudent(@RequestParam("file") MultipartFile file) throws IOException {
    UploadImageController uploadImageController = new UploadImageController();
    String upload = uploadImageController.uploadExcel(file);
    insertExecel insertExecel = new insertExecel();
    List<Student> studentList = insertExecel.readExcel(0,upload);
    int i = studentService.insertStudent(studentList);
    return "redirect:/selectStudentList";
}
/**
 * 导出文件表格
 */
@PostMapping("/exportStudentByExcel")
public void exportCarByExcel(HttpServletResponse response) throws IOException {
    //调用工具类中的导出表格的方法
    insertExecel  = new insertExecel();
    List<Student> list = studentService.getStudentList();
    //将从数据库中查询到的list集合传入exportExcel方法中
    String excelPath = insertExecel.exportExcel(list);
    excelPath = "\\excel" + excelPath;
    //返回生成excel文件的路径   浏览器下载excel表格
    insertExecel.fileDownload(excelPath,response);
}

3.工具类（insertExecel）

导入的excel文件后缀需为（.xls）导出的文件格式后缀也为（.xls）

public class insertExecel {
    /**
     * 导入表格
     * @param sheetIndex（sheetIndex 就是用来标识这个特定工作表的）
     * @param filePath （前端传入的文件的名字）
     * @return
     * @throws IOException
     */
    public List<Student> readExcel( int sheetIndex,String filePath) throws IOException {
        
        List<Student> dataList = new ArrayList<>();
        
        File file = new File(filePath);
        
        FileInputStream fis = new FileInputStream(file);

        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook(fis);
        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    // 如果是标题行，可以跳过或者处理标题
                    continue;
                }
                Student data = new Student();
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            if(cell.getColumnIndex() == 0){
                                data.setId((Integer.parseInt(cell.getStringCellValue()+"")));
                            }
                            // 假设第一列存储到cell1，第二列存储到cell2等
                            else if (cell.getColumnIndex() == 1) {
                                data.setName(cell.getStringCellValue());
                            }
                            else if (cell.getColumnIndex() == 2) {
                                data.setSex(cell.getStringCellValue());
                            }
                            else if (cell.getColumnIndex() == 3) {
                                data.setAge(Integer.parseInt(cell.getStringCellValue()+""));
                            }
                            else if (cell.getColumnIndex() == 4) {
                                data.setPhone(cell.getStringCellValue());
                            }
                            else if (cell.getColumnIndex() == 5) {
                                data.setEmail(cell.getStringCellValue());
                            }
                            else if (cell.getColumnIndex() == 6) {
                                data.setScore(Double.valueOf(cell.getStringCellValue()+""));
                            }
                            else if (cell.getColumnIndex() == 7) {
                                data.setClassId(Integer.parseInt(cell.getStringCellValue()+""));
                            }
                            else if (cell.getColumnIndex() == 8) {
                                data.setPhoto(cell.getStringCellValue());
                            }
                            else if (cell.getColumnIndex() == 9) {
                                data.setMoreImage(cell.getStringCellValue());
                            }
                            break;
                        // 处理其他类型的单元格，如NUMERIC, BOOLEAN, FORMULA等
                    }
                }
                dataList.add(data);
            }
            workbook.close();
        }
        fis.close();
        return dataList;
    }
    //这是我项目的excel存放文件夹的根路径 需要更换成自己的路径
    String fileName = "D:\\IDEA2022.3\\SpringBoot\\springboot-02\\src\\main\\resources\\static\\upload\\excel";
    public String exportExcel(List list) {

        try {
            WritableWorkbook wwb = null;
            // 创建可写入的Excel工作簿
            SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmss");
            String dname = sdf.format(new Date());

            fileName = fileName + "\\" + dname + "-student.xls";
            File file=new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            //以fileName为文件名来创建一个Workbook
            wwb = Workbook.createWorkbook(file);

            // 创建工作表
            WritableSheet ws = wwb.createSheet("Test Shee 1", 0);

            //要插入到的Excel表格的行号，默认从0开始
            Label labelId = new Label(0, 0, "编号(id)");//表示第
            Label labelName = new Label(1, 0, "姓名(name)");
            Label labelSex = new Label(2, 0, "性别(sex)");
            Label labelAge = new Label(3, 0, "年龄(age)");
            Label labelPhone = new Label(4, 0, "手机号(phone)");
            Label labelEmail = new Label(5, 0, "邮箱(email)");
            Label labelScore = new Label(6, 0, "分数(score)");
            Label labelClassId = new Label(7, 0, "班级(classId)");
            Label labelPhoto = new Label(8, 0, "头像(photo)");
            Label labelMoreImage = new Label(9, 0, "生活照(moreImage)");

            ws.addCell(labelId);
            ws.addCell(labelName);
            ws.addCell(labelSex);
            ws.addCell(labelAge);
            ws.addCell(labelPhone);
            ws.addCell(labelEmail);
            ws.addCell(labelScore);
            ws.addCell(labelClassId);
            ws.addCell(labelPhoto);
            ws.addCell(labelMoreImage);

            List<Student> studentList = list;

            for (int i = 0; i < studentList.size(); i++) {

                Label labelId_i = new Label(0, i + 1, studentList.get(i).getId() + "");
                Label labelName_i = new Label(1, i + 1, studentList.get(i).getName());
                Label labelSex_i = new Label(2, i + 1, studentList.get(i).getSex());
                Label labelAge_i = new Label(3, i + 1, studentList.get(i).getAge() + "");
                Label labelPhone_i = new Label(4, i + 1, studentList.get(i).getPhone());
                Label labelEmail_i = new Label(5, i + 1, studentList.get(i).getEmail());
                Label labelScore_i = new Label(6, i + 1, studentList.get(i).getScore() + "");
                Label labelClassId_i = new Label(7, i + 1, studentList.get(i).getClassgrade().getId()+"");
                Label labelPhoto_i = new Label(8, i + 1, studentList.get(i).getPhoto());
                Label labelMoreImage_i = new Label(9, i + 1, studentList.get(i).getMoreImage());

                ws.addCell(labelId_i);
                ws.addCell(labelName_i);
                ws.addCell(labelSex_i);
                ws.addCell(labelAge_i);
                ws.addCell(labelPhone_i);
                ws.addCell(labelEmail_i);
                ws.addCell(labelScore_i);
                ws.addCell(labelClassId_i);
                ws.addCell(labelPhoto_i);
                ws.addCell(labelMoreImage_i);
            }

            //写进文档
            wwb.write();
            // 关闭Excel工作簿对象
            System.out.println("表格数据导出成功!");
            wwb.close();
            return  "\\" + dname + "-student.xls";

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "";
        }
    }

    private String Path = "D:\\IDEA2022.3\\SpringBoot\\springboot-02\\src\\main\\resources\\static\\upload";
    //文件下载
    public void fileDownload(String filePath, HttpServletResponse response) throws IOException {
        // 创建输入流，读取文件
        FileInputStream fis = new FileInputStream(Path + "\\" + filePath);
        // 设置响应头信息
        response.setHeader("Content-Disposition", "attachement;filename=" +
                filePath.substring(filePath.lastIndexOf("/") + 1));
        // 获取输出流
        ServletOutputStream out = response.getOutputStream();
        // 拷贝
        IOUtils.copy(fis, out);
    }
}

4.Mapper(sql语句循环遍历添加)

<insert id="insertStudent">
    insert into student
    values
    <foreach collection="list" separator="," item="item">
        (null,#{item.name},#{item.sex},#{item.age},#{item.phone},#{item.email},
         #{item.score},#{item.classId},#{item.photo},#{item.moreImage})
    </foreach>
</insert>

三：效果展示