该博客介绍了如何使用EasyExcel库在Java中处理Excel数据，包括读取和写入操作。示例涵盖了操作日志和用户信息实体类的定义，以及如何在写入时合并单元格。此外，还提供了测试数据和调用方的代码示例，展示了如何创建包含多个sheet的Excel文件。
目录
1、添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>2、实体类
2.1 操作日志
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 15:03
*/
@Data
public class Log {
/**
* 日志名称
*/
@ColumnWidth(20)
@ExcelProperty(value = "日志名称", index = 0)
// @ExcelProperty(value = {"资源基本信息", "序号"},index = 0)
private String logName;
/**
* 日志时间
*/
@ColumnWidth(20)
@ExcelProperty(value = "日志时间", index = 1)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private String date;
/**
* 字段名
*/
@ColumnWidth(20)
@ExcelProperty(value = "字段名", index = 2)
private String fieldName;
/**
* 字段描述
*/
@ColumnWidth(20)
@ExcelProperty(value = "字段描述", index = 3)
private String fieldDesc;
/**
* 字段值
*/
@ColumnWidth(20)
@ExcelProperty(value = "字段值", index = 4)
private String fieldValue;
}2.2 用户信息
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import lombok.Data;
import org.apache.poi.ss.usermodel.FillPatternType;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 14:09
*/
@Data
public class User {
/**
* 姓名
*/
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex() 设置标题的背景颜色
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
@ExcelProperty(value = "姓名", index = 0)
private String name;
/**
* 年龄
*/
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex() 设置标题的背景颜色
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
@ExcelProperty(value = "年龄", index = 1)
private String age;
}3、测试的excel数据文件
3.1 用户信息excel
| 姓名 | 年龄 |
| 王海 | 35 |
| 谭杰 | 12 |
| 王娟 | 25 |
3.2 操作日志excel
| 日志名称 | 日志时间 | 字段名 | 字段描述 | 字段值 |
| 根据ID查询详情 | 2021-01-13 | size | 条数 | 25 |
| 根据ID查询详情 | 2021-01-13 | page | 页码 | 21 |
| 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 获取日志列表 | 2021-01-12 | logName | 日志名称 | 查询 |
| 获取日志列表 | 2021-01-12 | logTime | 日志时间 | 2021-10-01 14:25:14 |
| 获取日志列表 | 2021-01-12 | logUser | 查询人姓名 | 王杰 |
| 查询用户信息 | 2021-01-11 | userName | 用户名 | tqf_123 |
| 查询用户信息 | 2021-01-11 | age | 年龄 | 25 |
| 查询用户信息 | 2021-01-11 | sex | 性别(0-男，1-女，2-未知) | 0 |
4、excel列合并工具类
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* 单元格合并
*
* @author
* @date
*/
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
/**
* 合并字段的下标
*/
private int[] mergeColumnIndex;
/**
* 合并几行
*/
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
//
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}5、调用方
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.ServletOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* @author tqf
* @Description 生成excel文件，多个sheet写入数据
* @Version 1.0
* @since 2022-07-11 10:16
*/
@Slf4j
public class MoreExcelSheet {
// 生成的文件路径和名称
private static File file = new File("D:/Excel多个sheet.xls");
public static void main(String[] args) throws IOException {
// 日志列表数据
List<Log> dtoList = getData();
// 用户信息列表数据
List<User> userList = getUserData();
String sheetName[] = {"操作日志","用户信息"};
// sheet的对象class
List<Class> classArray = new ArrayList<>();
classArray.add(Log.class);
classArray.add(User.class);
//需要合并的列
int[] mergeColumeIndex = {0,1};
// 从第二行后开始合并
int mergeRowIndex = 1;
//不存在则新增
if(!file.getParentFile().exists()){
file.getParentFile().mkdirs();
}
if(!file.exists()){
file.createNewFile();
}
FileOutputStream output = new FileOutputStream(file);
// ServletOutputStream output = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(output).build();
for (int i=0;i<sheetName.length;i++) {
WriteSheet writeSheet;
if(i == 0 ){
writeSheet = EasyExcel.writerSheet(i, sheetName[i])
// 指定合并列
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
.head(classArray.get(i))
.build();
excelWriter.write(dtoList, writeSheet);
} else {
writeSheet = EasyExcel.writerSheet(i, sheetName[i])
.head(classArray.get(i))
.build();
excelWriter.write(userList, writeSheet);
}
}
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
output.flush();
}
public static List<Log> getData(){
String fileName = "D:/log1.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
return list;
}
public static List<User> getUserData(){
String userPath = "D:/123.xls";
List<User> userList = EasyExcel.read(userPath).head(User.class).sheet().doReadSync();
return userList;
}
/**
* 1万条数据写入多个sheet
* @throws IOException
*/
public static void moreSheet() throws IOException {
List<User> userList = getUserData();
FileOutputStream output = new FileOutputStream(file);
// ServletOutputStream output = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(output).build();
// 每个sheet写入的数据量
int len = 2000;
// 总的数据量
int size = userList.size();
// 多少个sheet
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<User> dataList = userList.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
WriteSheet writeSheet;
writeSheet = EasyExcel.writerSheet(i, "sheet"+(i+1)+"")
.head(User.class)
.build();
excelWriter.write(dataList, writeSheet);
}
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
output.flush();
}
}生成文件截图如下：
扫一扫
4757
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
