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

目录

一、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));// 工作表 sheetSheet 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方法@Overridepublic void invoke(TestStudent testStudent, AnalysisContext analysisContext) {saveList.add(testStudent);if (saveList.size() >= saveSize) {// 保存数据saveData();// 清空集合，重置集合saveList = ListUtils.newArrayListWithCapacity(saveSize);}}// 当所有的数据都读取完成时，会执行invoke方法，但是此时数据还未保存到数据库，所以需要执行doAfterAllAnalysed方法@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 执行完了所有的方法，还有一些没凑够saveSize的数据，所以需要执行saveData方法进行存储saveData();}private void saveData() {testStudentService.saveBatch(saveList);}}).sheet().doRead();
}

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

后端接口：

@RestController
@RequestMapping("/upload")
public class UploadController {@ResourceTestStudentService 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 {@ResourceHttpServletResponse response;@Resourceprivate 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> {@ResourceHttpServletResponse 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);
}