這篇文章會給大家實操一個關(guān)于列表導(dǎo)出成excel表格的功能，相信大家在日常工作中也會遇到列表導(dǎo)出的需求，看完本篇文章那么你就可以輕松的去整合列表導(dǎo)出的功能。
本次使用的導(dǎo)出工具是阿里巴巴開源的EasyExcel，關(guān)于EasyExcel我這里簡單的介紹一下：
我們知道Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。
easyexcel重寫了poi對07版Excel的解析，一個3M的excel用POI sax解析依然需要100M左右內(nèi)存，改用easyexcel可以降低到幾M，并且再大的excel也不會出現(xiàn)內(nèi)存溢出；03版依賴POI的sax模式，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡單方便。
ok，下面我們就開始實操整合一下EasyExcel，實現(xiàn)導(dǎo)出功能。
第一步：我們先創(chuàng)建Maven項目，整合成SpringBoot的項目。
- 加入依賴，創(chuàng)建測試接口，確保項目能夠運行。
- pom.xml加入依賴
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
</dependencies>
- 定義主類
/**
* @author lixiang
* @date 2023/5/26 16:02
*/
@SpringBootApplication
public class ExcelApplication {
public static void main(String[] args) {
SpringApplication.run(ExcelApplication.class, args);
}
}
- 創(chuàng)建控制器
/**
* @author lixiang
* @date 2023/5/26 16:00
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
@GetMapping("/create")
public String create(){
String msg = "SUCCESS";
return msg;
}
}
- 測試運行
第二步：引入EasyExcel依賴，封裝工具類
<dependency>
<groupId>com.pig4cloud.excel</groupId>
<artifactId>excel-spring-boot-starter</artifactId>
<version>0.5.0</version>
</dependency>
package com.lixiang.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* 導(dǎo)出excel 表格 工具類
* @author lixiang
* @date 2023/5/26 10:18
*/
public class EasyExcelUtil implements RowWriteHandler {
private int mergeRowIndex;//從哪一行開始合并
private int[] mergeColumnIndex;//excel合并的列
private int[] signNum;//合并的唯一標識
private int total;//總行數(shù)
private int lastRow;
private int firstCol;
private int lastCol;
private int firstRow;
private int mergeCount = 1;
private EasyExcelUtil(){}
private EasyExcelUtil(int mergeRowIndex, int[] mergeColumnIndex, int[] signNum, int total) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.signNum = signNum;
this.total = total;
}
/**
* 導(dǎo)出excel
* @param response
* @param fileName 文件名稱
* @param exportList 導(dǎo)出數(shù)據(jù)
* @param clazz 導(dǎo)出實體bean class對象
* @param <T>
* @throws IOException
*/
public static <T> void createExcel(HttpServletResponse response, String fileName, List<T> exportList,
Class<T> clazz)
throws IOException {
createExcel(response,fileName,exportList,clazz,null);
}
/**
* 導(dǎo)出excel
* @param response
* @param fileName 文件名稱
* @param exportList 導(dǎo)出數(shù)據(jù)
* @param clazz 導(dǎo)出實體bean class對象
* @param cellMerge 單元格合并規(guī)則
* @param <T>
* @throws IOException
*/
public static <T> void createExcel(HttpServletResponse response, String fileName, List<T> exportList,
Class<T> clazz, CellMerge cellMerge)
throws IOException {
// 設(shè)置下載信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//定義ExcelWriterSheetBuilder
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel
.write(response.getOutputStream(), clazz)
.sheet(fileName);
//合并單元格
if (cellMerge != null) {
// 從那一行開始合并
int mergeRowIndex = 1;
EasyExcelUtil
excelMergeRowByRowStrategy = new EasyExcelUtil(mergeRowIndex, cellMerge.getMergeColumIndex(),
cellMerge.getMergeRuleColumIndex(), exportList.size());
excelWriterSheetBuilder.registerWriteHandler(excelMergeRowByRowStrategy);
}
//設(shè)置頭樣式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//設(shè)置內(nèi)容格式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//設(shè)計內(nèi)容居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//設(shè)置內(nèi)容自動換行
contentWriteCellStyle.setWrapped(true);
excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);
//調(diào)用doWrite方法
excelWriterSheetBuilder.doWrite(exportList);
}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
//當(dāng)前行
int curRowIndex = row.getRowNum();
//每一行的最大列數(shù)
short lastCellNum = row.getLastCellNum();
if (curRowIndex == 1) {
//賦初值 第一行
firstRow = curRowIndex;
}
//開始合并位置
if (curRowIndex > mergeRowIndex && !row.getCell(0).getStringCellValue().equals("")) {
for (int i = 0; i < lastCellNum; i++) {
if (i == mergeColumnIndex[i]) {
//當(dāng)前行號 當(dāng)前行對象 合并的標識位
mergeWithPrevAnyRow(writeSheetHolder.getSheet(), curRowIndex, row, signNum);
break;//已經(jīng)進入到合并單元格操作里面了，執(zhí)行一次就行
}
}
}
}
public void mergeWithPrevAnyRow(Sheet sheet, int curRowIndex, Row row, int[] signNum) {
Row preRow = row.getSheet().getRow(curRowIndex - 1);
List<String> rowDataList = new ArrayList<>();
List<String> preDataList = new ArrayList<>();
for (int i : signNum) {
Object currentData =
row.getCell(i).getCellTypeEnum() == CellType.STRING ? row.getCell(i).getStringCellValue() :
row.getCell(i).getNumericCellValue();
Object preData =
preRow.getCell(i).getCellTypeEnum() == CellType.STRING ? preRow.getCell(i).getStringCellValue() :
preRow.getCell(i).getNumericCellValue();
rowDataList.add(String.valueOf(currentData));
preDataList.add(String.valueOf(preData));
}
String rowDataStr = String.join(",", rowDataList);
String preDataStr = String.join(",", preDataList);
//判斷是否合并單元格
boolean curEqualsPre = rowDataStr.equals(preDataStr);
//判斷前一個和后一個相同 并且 標識位相同
if (curEqualsPre) {
lastRow = curRowIndex;
mergeCount++;
}
//excel過程中合并
if (!curEqualsPre && mergeCount > 1) {
mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
mergeCount = 1;
}
//excel結(jié)尾處合并
if (mergeCount > 1 && total == curRowIndex) {
mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
mergeCount = 1;
}
if (!curEqualsPre) {
firstRow = curRowIndex;
}
}
private void mergeSheet(int firstRow, int lastRow, int[] mergeColumnIndex, Sheet sheet) {
for (int colNum : mergeColumnIndex) {
firstCol = colNum;
lastCol = colNum;
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* 單元格合并類
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CellMerge {
private int[] mergeColumIndex;
private int[] mergeRuleColumIndex;
}
/**
* 設(shè)置單元格合并規(guī)則
* @param mergeColumIndex
* @param mergeRuleColumIndex
* @return
*/
public static CellMerge setCellMerge(int[] mergeColumIndex,int[] mergeRuleColumIndex){
EasyExcelUtil excelUtil = new EasyExcelUtil();
CellMerge cellMerge = excelUtil.new CellMerge();
cellMerge.setMergeColumIndex(mergeColumIndex);
cellMerge.setMergeRuleColumIndex(mergeRuleColumIndex);
return cellMerge;
}
}
第三步：模擬測試數(shù)據(jù)
- 創(chuàng)建學(xué)生實體類
/**
* @ColumnWidth(20) 這個是設(shè)置單元格長度的
* @ExcelProperty("") 這個是設(shè)置表格頭部的
* @author lixiang
* @date 2023/5/26 16:30
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Student {
@ExcelProperty("年級")
private String gradeName;
@ExcelProperty("班級")
private String className;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年齡")
private Integer age;
@ExcelProperty("性別")
private String sex;
}
- 模擬學(xué)生數(shù)據(jù)
/**
* @author lixiang
* @date 2023/5/26 16:13
*/
@Service
public class StudentService {
List<String> classNames;
List<String> gradeNames;
{
classNames = new ArrayList<>();
classNames.add("一班");
classNames.add("二班");
classNames.add("三班");
gradeNames = new ArrayList<>();
gradeNames.add("2017級");
gradeNames.add("2018級");
gradeNames.add("2019級");
}
public List<Student> getStudentData(){
List<Student> list = new ArrayList<>();
for (int i = 1; i < 20; i++) {
Student student = new Student();
student.setName("李祥"+i);
student.setClassName(getClassName());
student.setGradeName(getGradeName());
student.setSex("男");
student.setAge(18);
list.add(student);
}
return list;
}
/**
* 獲取班級
* @return
*/
private String getClassName(){
Random rand = new Random();
return classNames.get(rand.nextInt(classNames.size()));
}
/**
* 獲取年級
* @return
*/
private String getGradeName(){
Random rand = new Random();
return gradeNames.get(rand.nextInt(gradeNames.size()));
}
}
/**
* @author lixiang
* @date 2023/5/26 16:00
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private StudentService studentService;
@GetMapping("/create")
public List<Student> create(){
List<Student> studentData = studentService.getStudentData();
return studentData;
}
}
第四步：基于這個數(shù)據(jù)我們進行導(dǎo)出成excel
/**
* @author lixiang
* @date 2023/5/26 16:00
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private StudentService studentService;
@GetMapping("/create")
public void create(HttpServletResponse response) throws Exception{
List<Student> studentData = studentService.getStudentData();
String fileName = "學(xué)生列表";
/**
* 第一個參數(shù)：HttpServletResponse
* 第二個參數(shù)：文件名稱
* 第三個參數(shù)：數(shù)據(jù)集
* 第四個參數(shù)：數(shù)據(jù)集實體class對象
*/
EasyExcelUtil.createExcel(response,fileName,studentData, Student.class);
}
}
Ok，那么現(xiàn)在 我們想要合并單元格，將同年級的同班級的單元格進行合并。
@GetMapping("/create")
public void create(HttpServletResponse response) throws Exception{
List<Student> studentData = studentService.getStudentData();
String fileName = "學(xué)生列表";
/**
* 第一個參數(shù)：HttpServletResponse
* 第二個參數(shù)：文件名稱
* 第三個參數(shù)：數(shù)據(jù)集
* 第四個參數(shù)：數(shù)據(jù)集實體class對象
* 第五個參數(shù)：合并單元格的規(guī)則
* EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
* 第一個參數(shù) 是 我們要合并的那些列，第二個是合并的規(guī)則。
*/
int[] meargeColl = new int[]{0,1};
EasyExcelUtil.CellMerge cellMerge = EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
EasyExcelUtil.createExcel(response,fileName,studentData, Student.class,cellMerge);
}
注意合并的時候，需要將數(shù)據(jù)進行排序，確保年級，班級相同的數(shù)據(jù)都在一起。現(xiàn)在我們先進行數(shù)據(jù)排序。
- 定義中文排序工具
/**
* @author lixiang
* @date 2023/5/26 17:14
*/
public class ChineseNumberUtil {
public static int chineseNumber2Int(String chineseNumber){
int result = 0;
int temp = 1;//存放一個單位的數(shù)字如：十萬
int count = 0;//判斷是否有chArr
char[] cnArr = new char[]{'一','二','三','四','五','六','七','八','九'};
char[] chArr = new char[]{'十','百','千','萬','億'};
for (int i = 0; i < chineseNumber.length(); i++) {
boolean b = true;//判斷是否是chArr
char c = chineseNumber.charAt(i);
for (int j = 0; j < cnArr.length; j++) {//非單位，即數(shù)字
if (c == cnArr[j]) {
if(0 != count){//添加下一個單位之前，先把上一個單位值添加到結(jié)果中
result += temp;
temp = 1;
count = 0;
}
// 下標+1，就是對應(yīng)的值
temp = j + 1;
b = false;
break;
}
}
if(b){//單位{'十','百','千','萬','億'}
for (int j = 0; j < chArr.length; j++) {
if (c == chArr[j]) {
switch (j) {
case 0:
temp *= 10;
break;
case 1:
temp *= 100;
break;
case 2:
temp *= 1000;
break;
case 3:
temp *= 10000;
break;
case 4:
temp *= 100000000;
break;
default:
break;
}
count++;
}
}
}
if (i == chineseNumber.length() - 1) {//遍歷到最后一個字符
result += temp;
}
}
return result;
}
}
- service進行排序
public List<Student> getStudentData(){
List<Student> list = new ArrayList<>();
for (int i = 1; i < 20; i++) {
Student student = new Student();
student.setName("李祥"+i);
student.setClassName(getClassName());
student.setGradeName(getGradeName());
student.setSex("男");
student.setAge(18);
list.add(student);
}
//排序邏輯
list.sort((s1,s2)->{
Integer flag = Integer.parseInt(s1.getGradeName().substring(0,4)) - Integer.parseInt(s2.getGradeName().substring(0,4));
if(flag == 0){
return Integer.compare(ChineseNumberUtil.chineseNumber2Int(s1.getClassName().substring(0,2)),
ChineseNumberUtil.chineseNumber2Int(s2.getClassName().substring(0,2)));
}
return flag;
});
return list;
}
OK，我們排序完成就可以進行合并啦。
int[] meargeColl = new int[]{0,1};
EasyExcelUtil.CellMerge cellMerge = EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
EasyExcelUtil.createExcel(response,fileName,studentData, Student.class,cellMerge);
文章來源：http://www.zghlxwxcb.cn/news/detail-474392.html
我們看到相同的行已經(jīng)被合并啦，ok，excel整合我們就完成啦。文章來源地址http://www.zghlxwxcb.cn/news/detail-474392.html
到了這里，關(guān)于【案例實戰(zhàn)】SpringBoot整合EasyExcel實現(xiàn)列表導(dǎo)出功能的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
