Java中使用easyexcel导入导出数据
工作中常常遇到导入导出Excel数据，还需要设置表格边框、合并单元格、字体居中等等各种样式，尝试了多种方式觉得阿里的easyexcel挺好使，记录一下使用、表格样式如下：
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
创建导出实体类
@HeadRowHeight(38) //设置标题行高
@Data
public class SwhcBdcVo implements Serializable {
@ExcelProperty(value = { "基本信息", "序号" }, index = 0) //需要在表格显示的列
@ColumnWidth(6) //每列占用的宽度，列宽
private String xh;
@ExcelProperty(value = { "基本信息", "姓名" }, index = 1)
@ColumnWidth(8)
private String qlrmc;
@ExcelProperty(value = { "基本信息", "性别" }, index = 2)
@ColumnWidth(6)
private String xb;
@ExcelProperty(value = { "基本信息", "身份证号" }, index = 3)
@ColumnWidth(16)
private String qlrzjh;
@ExcelProperty(value = {"不动产信息", "房产来源（去向）" }, index = 4)
@ColumnWidth(30)
private String qx;
@ExcelProperty(value = {"不动产信息", "具体地址" }, index = 5)
@ColumnWidth(30)
private String dz;
@ExcelProperty(value = {"不动产信息", "建筑面积（m²）" }, index = 6)
@ColumnWidth(11)
private BigDecimal jzmj;
@ExcelProperty(value = {"不动产信息", "产权性质" }, index = 7)
@ColumnWidth(11)
private String cqxz;
@ExcelProperty(value = {"不动产信息", "登记时间(年月)" }, index = 8)
@ColumnWidth(11)
private String djsj;
@ExcelProperty(value = {"不动产信息", "交易价格(万元)" }, index = 9)
@ColumnWidth(11)
private BigDecimal jyjg;
@ExcelProperty(value = "备注", index = 10)
@ColumnWidth(24)
private String bz;
@ExcelIgnore //不需要在表格展示的字段
private String ywh;
@ExcelIgnore
private Date compareDjsj;
}
生成Excel模板
public void generateTemplate(HttpServletResponse response) {
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = URLEncoder.encode(String.format("%s.xlsx", sdf2.format(new Date())),
StandardCharsets.UTF_8.toString());
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// 这里 需要指定写用哪个class去写
ExcelWriter writer = new ExcelWriterBuilder()
.autoCloseStream(true)
.excelType(ExcelTypeEnum.XLSX)
.file(response.getOutputStream())
.head(SwhcBdcVo.class)
// 核心代码：表头和正文的样式在此
.registerWriteHandler(setConfigure())
.build();
WriteSheet writeSheetfw = new WriteSheet();
writeSheetfw.setSheetName("Sheet1");
writer.write(null, writeSheetfw);
// 关闭流
writer.finish();
}
数据导入读取
public R dr(MultipartFile file) {
if (!StrUtil.endWithAny(file.getOriginalFilename(), ".xls", ".xlsx")) {
return R.failed("上传的文件不是excel");
}
List<SwhcBdcVo> drList = EasyExcelUtil.readExcel(file, "Sheet1", SwhcBdcVo.class, 2); //2为表格标题占用的行数，读取跳过标题
if (CollUtil.isEmpty(drList )) {
return R.failed("未获取到导入表格数据");
}
}
EasyExcelUtil工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import org.springframework.web.multipart.MultipartFile;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class EasyExcelUtil {
public static <T> List<T> readExcel(MultipartFile excel, String sheetName, Class<T> clazz, int headLineNum)
throws IOException {
ExcelReaderBuilder readerBuilder = getReader(excel);
if (readerBuilder == null) {
return null;
}
ExcelReaderSheetBuilder sheet = readerBuilder.sheet(sheetName);
if (sheet == null) {
return null;
}
sheet.head(clazz).headRowNumber(headLineNum);
return sheet.doReadSync();
}
private static ExcelReaderBuilder getReader(MultipartFile excel) throws IOException {
String filename = excel.getOriginalFilename();
if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
InputStream is = new BufferedInputStream(excel.getInputStream());
return EasyExcel.read(is);
} else {
return null;
}
}
}
数据生成到表格
log.info("-------开始数据写表操作------------------");
//查询到的数据写入Excel表格，表格文件传入minio
ByteArrayOutputStream out = new ByteArrayOutputStream();
//需要合并的列
int[] mergeColumeIndex = {0,1,2,3};
//从第三行后开始合并
int mergeRowIndex = 2;
// 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex);
EasyExcel.write(out, SwhcBdcVo.class)
.autoCloseStream(true)
.excelType(ExcelTypeEnum.XLSX)
.head(SwhcBdcVo.class)
// 核心代码：表头和正文的样式在此
.registerWriteHandler(setConfigure())
.registerWriteHandler(excelFillCellMergeStrategy)
.sheet("Sheet1")
.doWrite(totalPersonBdcList);
byte[] bs = out.toByteArray();
out.close();
try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bs));
OutputStream out1 = new FileOutputStream("./测试文件.xlsx")) {
workbook.write(out1);
log.info("-----------写入文件结束------------");
}
设置标题样式和内容样setConfigure()方法
//配置字体，表头背景等
private static HorizontalCellStyleStrategy setConfigure() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex()); // 白色背景
WriteFont headWriteFont = new WriteFont();
// 加粗
// headWriteFont.setBold(true);
// headWriteFont.setFontHeightInPoints((short) 14); // 设置行高，不重要
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
// contentWriteFont.setFontHeightInPoints((short) 14);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//导出数据垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//导出数据水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置边框
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//设置 自动换行
//contentWriteCellStyle.setWrapped(true);
//设置
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
设置内容合并策略，指定列的内容相同，单元格合并
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;
@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;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
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);
}
}
}
}
