目錄
一、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);
}
