当前位置： 首页 > article >正文

Java操作Excel导入导出——POI、Hutool、EasyExcel

article 2025/1/20 11:44:11

目录

一、POI导入导出

1.数据库导出为Excel文件

2.将Excel文件导入到数据库中

二、Hutool导入导出

1.数据库导出为Excel文件——属性名是列名 

2.数据库导出为Excel文件——列名起别名 

3.从Excel文件导入数据到数据库——属性名是列名 

4.从Excel文件导入数据到数据库——列名改为属性名

三、EasyExcel

1.数据库导出数据到一个sheet中

2.数据库导出数据到多个sheet中

3.从Excel文件导入数据到数据库

4.读取大数据量Excel文件到数据库中

5.从页面上传Excel写入数据库

6.封装导出数据库到Excel工具类

一、POI导入导出

CREATE TABLE `test_student` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL
);
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.2</version>
</dependency>

1.数据库导出为Excel文件

@Test
void f1() {
    // 数据库导出为Excel文件
    List<TestStudent> list = testStudentService.list();
    // 导出的位置
    String path = "D:\\save\\stu1.xlsx";
    // 工作薄 workbook(Excel本身)->sheet页签工作表 -> 行->单元格
    try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        // 新建 工作薄对象
        // 新建sheet对象
        XSSFSheet sheet = workbook.createSheet("test_stu");
        //创建行
        XSSFRow row0 = sheet.createRow(0);
        // 创建单元格
        row0.createCell(0).setCellValue("学生ID");
        row0.createCell(1).setCellValue("学生姓名");
        row0.createCell(2).setCellValue("学生年龄");
        for (int i = 0; i < list.size(); i++) {
            // 略过首行
            XSSFRow row = sheet.createRow(i + 1);
            TestStudent testStudent = list.get(i);
            row.createCell(0).setCellValue(testStudent.getId());
            row.createCell(1).setCellValue(testStudent.getName());
            row.createCell(2).setCellValue(testStudent.getAge());
        }
        //内容写出去
        workbook.write(new FileOutputStream(path));
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

2.将Excel文件导入到数据库中

@Test
void f2() throws IOException {
    // 将Excel文件导入到数据库中
    ArrayList<TestStudent> stuList = new ArrayList<>();
    // 导入的源文件
    String path = "D:\\save\\stu1.xlsx";
    // 工作薄对象
    Workbook workbook = WorkbookFactory.create(new FileInputStream(path));
    // 工作表 sheet
    Sheet sheet = workbook.getSheetAt(0);
    // 行
    int rows = sheet.getPhysicalNumberOfRows();
    // 行的头信息，第一行，可以不处理
    for (int i = 1; i < rows; i++) {
        Row row = sheet.getRow(i);
        TestStudent student = new TestStudent();
        // 第一个单元格,因为是主键,可以不要
        // 第二个单元格
        student.setName(row.getCell(1).getStringCellValue());
        // 第三个单元格
        student.setAge((int) row.getCell(2).getNumericCellValue());
        // 把组装好的 student对象,存入集合
        stuList.add(student);
        // 不能循环调用数据库
        // testStudentService.save(student);
    }
    // 存数据到 数据库
    // 批量调用
    testStudentService.saveBatch(stuList);
}

 

二、Hutool导入导出

Hutool官网：https://hutool.cn/

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.16</version>
</dependency>

1.数据库导出为Excel文件——属性名是列名 

@Test
void f1() {
    List<TestStudent> list = testStudentService.list();
    String path = "D:\\save\\stu2.xlsx";
    // 属性名就是Excel的列名
    ExcelWriter writer = ExcelUtil.getWriter(path);
    writer.write(list);
    writer.close();
}

2.数据库导出为Excel文件——列名起别名 

@Test
void f2() {
    List<TestStudent> list = testStudentService.list();
    String path = "D:\\save\\stu3.xlsx";
    ExcelWriter writer = ExcelUtil.getWriter(path);
    writer.addHeaderAlias("id", "学生ID");
    writer.addHeaderAlias("name", "学生姓名");
    writer.addHeaderAlias("age", "学生年龄");
    writer.write(list);
    writer.close();
}

3.从Excel文件导入数据到数据库——属性名是列名 

@Test
void f3() {
    String path = "D:\\save\\stu2.xlsx";
    ExcelReader reader = ExcelUtil.getReader(path);
    List<TestStudent> list = reader.readAll(TestStudent.class);
    testStudentService.saveBatch(list);
}

4.从Excel文件导入数据到数据库——列名改为属性名

@Test
void f4() {
    String path = "D:\\save\\stu3.xlsx";
    ExcelReader reader = ExcelUtil.getReader(path);
    // Excel列名信息与 属性不一致时，使用别名的方式读取
    reader.addHeaderAlias("学生ID", "id");
    reader.addHeaderAlias("学生姓名", "name");
    reader.addHeaderAlias("学生年龄", "age");
    List<TestStudent> list = reader.readAll(TestStudent.class);
    testStudentService.saveBatch(list);
}

                      

三、EasyExcel

官网：https://easyexcel.opensource.alibaba.com/ 

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.2.1</version>
</dependency>
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestStudent extends Model<TestStudent> {
    @TableId(type = IdType.AUTO)
    @ExcelProperty("学生ID")
    private Integer id;

    @ExcelProperty("学生姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;
}

1.数据库导出数据到一个sheet中

@Test
void f1() {
    // 数据库导出为Excel文件
    String path = "D:\\save\\stu4.xlsx";
    List<TestStudent> list = testStudentService.list();
    EasyExcel.write(path, TestStudent.class).sheet(0, "学生信息").doWrite(list);
}

2.数据库导出数据到多个sheet中

@Test
void f2() {
    // 数据库导出为Excel文件
    String path = "D:\\save\\stu5.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(path, TestStudent.class).build()) {
        long count = testStudentService.count();
        long num = count % 100 == 0 ? count / 100 : count / 100 + 1;
        for (int i = 0; i < num; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "学生信息" + i).build();
            List<TestStudent> pageList = testStudentService.pageList(i + 1, 100);
            excelWriter.write(pageList, writeSheet);
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

3.从Excel文件导入数据到数据库

@Test
void f3() {
    String path = "D:\\save\\stu4.xlsx";
    EasyExcel.read(path,TestStudent.class,new PageReadListener<TestStudent>(list->{
        // 自带的分页读取，每次 只读取100条数据，防止数据量过大导致内存溢出
        testStudentService.saveBatch(list);
    })).sheet().doRead();
}

4.读取大数据量Excel文件到数据库中

@Test
void f4() {
    String path = "D:\\save\\stu10.xlsx";
    EasyExcel.read(path, TestStudent.class, new ReadListener<TestStudent>() {
        private static final int saveSize = 10000;
        private List<TestStudent> saveList = ListUtils.newArrayListWithCapacity(saveSize);
        // 每次读取一条，执行一次invoke方法
        @Override
        public void invoke(TestStudent testStudent, AnalysisContext analysisContext) {
            saveList.add(testStudent);
            if (saveList.size() >= saveSize) {
                // 保存数据
                saveData();
                // 清空集合，重置集合
                saveList = ListUtils.newArrayListWithCapacity(saveSize);
            }
        }
        // 当所有的数据都读取完成时，会执行invoke方法，但是此时数据还未保存到数据库，所以需要执行doAfterAllAnalysed方法
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            // 执行完了所有的方法，还有一些没凑够saveSize的数据，所以需要执行saveData方法进行存储
            saveData();
        }
        private void saveData() {
            testStudentService.saveBatch(saveList);
        }
    }).sheet().doRead();
}

5.从页面上传Excel写入数据库

后端接口：

@RestController
@RequestMapping("/upload")
public class UploadController {
    @Resource
    TestStudentService testStudentService;

    @PostMapping("/test1")
    public R upload1(MultipartFile file) throws IOException {
        // 该方法每次自动存100条数据到数据库
        EasyExcel.read(
                file.getInputStream(),
                TestStudent.class,
                new PageReadListener<TestStudent>(list -> {
                    testStudentService.saveBatch(list);
                })).sheet().doRead();
        return R.ok("上传成功");
    }
}

前端页面：Student.vue

<template>
  <div class="common-layout">
    <el-container>
      <el-header>
        <Top/>
      </el-header>
      <el-container>
        <el-aside>
          <Menu/>
        </el-aside>
        <el-main>
          <el-row>
            <el-col>
              <el-upload ref="uploadRef" class="upload-demo"
                         action="http://localhost:8081/upload/test1"
                         :auto-upload="false"
                         :with-credentials="true"
                         :on-success="ups">
                <template #trigger> <!--#trigger用于自定义触发上传、弹出对话框或其他交互操作的按钮或元素-->
                  <el-button type="primary">选择文件</el-button>
                </template>
                <el-button class="ml-4" type="success" @click="submitUpload">点击上传</el-button>
                <template #tip> <!--#tip用于插入提示信息或其他额外内容-->
                  <div class="el-upload__tip"></div>
                </template>
              </el-upload>
            </el-col>
          </el-row>
          <el-table :data="stuList" stripe style="width: 100%">
            <el-table-column prop="id" label="学生ID" width="180"/>
            <el-table-column prop="name" label="学生姓名" width="180"/>
            <el-table-column prop="age" label="学生年龄"/>
          </el-table>
        </el-main>
      </el-container>
    </el-container>
  </div>
</template>

<script setup>
import {ref, reactive, onMounted} from "vue";
import axios from '@/plugins/axios.js'
import {ElMessage} from 'element-plus'
import LoginUser from "@/stores/LoginUser.js";
import router from "@/router/index.js";
import Top from "@/components/Top.vue";
import Menu from "@/components/Menu.vue";

const uploadRef = ref([])
let submitUpload = () => {
  uploadRef.value.submit()
}
let ups = (response, file, fileList) => {
  console.log(response)
  if (response.code === 200) {
    ElMessage.success(response.msg)
    query()
  } else {
    ElMessage.error(response.msg)
  }
}
let stuList = ref([])
let query = () => {
  let param = {
    "pageNum": 1,
    "pageSize": 10
  }
  axios.get("/test/stu/page", param)
      .then(result => {
        if (result.code === 200) {
          stuList.value = result.data
        }
      })
}
onMounted(() => {
  query()
})
</script>

<style scoped>

</style>

路由：

import {createRouter, createWebHistory} from 'vue-router'
import HomeView from '../views/HomeView.vue'
import LoginView from '../views/LoginView.vue'
import TaskList from '../views/task/TaskList.vue'
import Student from '../views/test/Student.vue'

const router = createRouter({
    history: createWebHistory(import.meta.env.BASE_URL),
    routes: [
        {
            path: '/',
            name: 'home',
            component: HomeView,
        }, {
            path: '/login',
            name: 'login',
            component: LoginView,
        }, {
            path: '/task/list',
            name: 'taskList',
            component: TaskList
        }, {
            path: '/test',
            name: 'test',
            children: [
                {
                    path: 'student',
                    name: 'student',
                    component: Student,
                }
            ]
        }
    ],
})

export default router

导出数据库到Excel后端接口：

@RestController
@RequestMapping("/export")
public class ExportController {
    @Resource
    HttpServletResponse response;

    @Resource
    private TestStudentService testStudentService;

    @GetMapping("/stu/excel")
    public void exportStuExcel() throws IOException {
        //获取 需要导出的信息
        List<TestStudent> list = testStudentService.list();

        // 设置导出的文件名
        // 这行代码将编码后的字符串中的所有 + 替换为 %20，这样可以确保文件名在下载时不会被错误地解释为空格。
        String fileName = URLEncoder.encode("学生信息表", "UTF-8").replaceAll("\\+", "%20");

        // 设置响应头信息
        // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // response.setCharacterEncoding("utf-8");
        
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        // 使用 filename* 参数可以正确处理包含非 ASCII 字符的文件名。
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8''" + fileName + ".xlsx");

        /* attachment：指示浏览器将文件作为附件下载。
        filename*=utf-8''：使用 filename* 参数来支持非 ASCII 字符，utf-8 表示编码格式，'' 表示语言标签（通常为空）。
        fileName + ".xlsx"：拼接编码后的文件名和文件扩展名。*/
        // response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        // 这行代码使用 EasyExcel 将 list 中的数据写入 Excel 文件，并将其输出到 response.getOutputStream()。
        EasyExcel.write(response.getOutputStream(), TestStudent.class).sheet("数据1").doWrite(list);
        
        // 不需要手动关闭 response.getOutputStream()，EasyExcel 会自动处理。
        // response.getOutputStream().close();
    }
}

前端页面：

<el-col>
  <a href="http://localhost:8081/export/stu/excel">导出全部数据</a>
</el-col>

6.封装导出数据库到Excel工具类

@Component
public class ExportUtil<T> {
    @Resource
    HttpServletResponse response;

    public void expExcel(String fileName, List<T> list, Class<T> tClass){
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            // 设置响应头信息
            // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            // response.setCharacterEncoding("utf-8");

            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            // 使用 filename* 参数可以正确处理包含非 ASCII 字符的文件名。
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8''" + fileName + ".xlsx");

            /* attachment：指示浏览器将文件作为附件下载。
            filename*=utf-8''：使用 filename* 参数来支持非 ASCII 字符，utf-8 表示编码格式，'' 表示语言标签（通常为空）。
            fileName + ".xlsx"：拼接编码后的文件名和文件扩展名。*/
            // response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 这行代码使用 EasyExcel 将 list 中的数据写入 Excel 文件，并将其输出到 response.getOutputStream()。
            EasyExcel.write(response.getOutputStream(), tClass).sheet("数据1").doWrite(list);
        }catch (IOException e){
            throw new RuntimeException(e);
        }
    }
}
/**
 * 使用工具类导出数据库到Excel
 */
@Resource
ExportUtil<TestStudent> exportUtil;
@GetMapping("/stu/excel2")
public void exportStuExcel2(){
    exportUtil.expExcel("学生信息表", testStudentService.list(), TestStudent.class);
}

http://www.kler.cn/a/510626.html

相关文章：

  • 昇腾环境ppstreuct部署问题记录
  • ReactiveSwift 简单使用
  • 机器学习-基本术语
  • Sqlmap入门
  • FastADMIN实现网站启动时执行程序的方法
  • LabVIEW串口通信调试与数据接收问题
  • 【机器学习：三十、异常检测：原理与实践】
  • C#项目生成时提示缺少引用
  • Ghauri -跨平台自动检测和SQL注入
  • 【JAVA项目】基于ssm的【游戏美术外包管理信息系统】
  • Mixly米思齐1.0 2.0 3.0 软件windows版本MAC苹果电脑系统安装使用常见问题与解决
  • AI使优化服务与提升服务
  • 强网杯RS加密签名伪造及PyramidWeb利用栈帧打内存马
  • Vue进阶之旅：核心技术与页面应用实战(路由进阶)
  • [JavaScript] 运算符详解
  • 数据结构与算法面试专题——引入及归并排序
  • 欧拉计划 Project Euler 52（重排的倍数） 题解
  • golang标准库path/filepath使用示例
  • Java开发提速秘籍：巧用Apache Commons Lang工具库
  • 深度学习中超参数
  • 基于微信小程序的安心陪诊管理系统
  • 基础入门-传输加密数据格式编码算法密文存储代码混淆逆向保护安全影响
  • 自由能最小化与逍遥游：从心理预期到心灵自在的和谐旅程
  • react中，使用antd的Upload组件上传zip压缩包文件
  • [JavaScript] 深入理解流程控制结构
  • 开发笔记4