目录
1、添加EasyExcel依赖
<!--alibaba easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version> <!--1.1.2-beta5-->
</dependency>2、测试类、读取本地excel、写入本地excel
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.util.List;
import java.util.Map;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 14:15
*/
@Slf4j
public class ExcelRead {
public static void main1(String[] args) {
String userPath = "D:/123.xls";
String logPath = "D:/log.xls";
EasyExcel.read(userPath, User.class, new UserExcelListener()).sheet().doRead();
EasyExcel.read(logPath, Log.class, new UserExcelListener()).sheet().doRead();
}
public static void main(String[] args) {
String fileName = "D:/log.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
for (Log data : list) {
log.info("读取到数据:{}", JSON.toJSONString(data));
}
// String fileName1 = addFile("D:/", "test.xls");
EasyExcel.write("D:/test.xls", Log.class).sheet().doWrite(list);
// 这里 也可以不指定class，返回一个list，然后读取第一个sheet 同步读取会自动finish
String path = "D:/123.xls";
List<Map<Integer, String>> listMap = EasyExcel.read(path).sheet().doReadSync();
for (Map<Integer, String> data : listMap) {
// 返回每条数据的键值对 表示所在的列 和所在列的值
log.info("读取到数据:{}", JSON.toJSONString(data));
}
}
public static String addFile(String filePath, String fileName){
File file = new File(filePath + fileName);
if (!file.exists()) {
file.mkdirs();// 能创建多级目录
}
return file.getPath();
}
}
2.1 实体类
2.1.1 用户实体类-User
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 14:09
*/
@Data
public class User {
/**
* 姓名
*/
@ExcelProperty(index = 0)
private String name;
/**
* 年龄
*/
@ExcelProperty(index = 1)
private String age;
}
2.1.2 用户实体类-Log
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.1.3 UserExcelListener
package com.shucha.deveiface.biz.test;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 14:14
*/
@Slf4j
public class UserExcelListener<T> extends AnalysisEventListener<T> {
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 2;
List<T> list = new ArrayList<>();
@Override
public void invoke(T t, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(t));
list.add(t);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("所有数据解析完成！");
}
private void saveData(){
log.info("{}条数据，开始存储数据库！", list.size());
log.info("存储数据库成功！");
}
}
3、实际的业务处理控制器
package com.shucha.deveiface.web.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.sdy.common.utils.StringUtil;
import com.shucha.deveiface.biz.test.Log;
import com.shucha.deveiface.biz.test.User;
import com.shucha.deveiface.biz.test.UserExcelListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.List;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-18 14:32
*/
@Slf4j
@RestController
@SuppressWarnings("unchecked")
@RequestMapping("/excel")
public class TestController {
/**
* 测试上传excel文件读取文件内容
* @param file
* @throws IOException
*/
@PostMapping("/readExcel")
public void readExcel(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener()).sheet().doRead();
List<User> list = EasyExcel.read(file.getInputStream()).head(User.class).sheet().doReadSync();
// System.out.println(list);
}
/**
* 下载excel文件
* @param response
* @throws IOException
*/
@GetMapping("/downLoadResItem")
public void downLoadResItem(HttpServletResponse response) throws IOException {
String fileName = "D:/log.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName1 = URLEncoder.encode("生成的excel文件", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName1 + ".xls");
ServletOutputStream output = response.getOutputStream();
// ExcelWriter writer = new ExcelWriter(output, ExcelTypeEnum.XLS, true);
//Sheet sheet = new Sheet(1, 0, ResItemExcelVo.class);
// sheet.setSheetName("第一页");
EasyExcel.write(response.getOutputStream(), Log.class)
.sheet("sheet1")
.doWrite(list);
// writer.finish();
output.flush();
}
/**
* 生成的excel，写入多个sheet
* @param response
* @throws IOException
*/
@GetMapping("/downLoadTwo")
public void downLoadTwo(HttpServletResponse response) throws IOException {
String fileName = "D:/log.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> logList = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
// 读取用户信息excel
String userPath = "D:/123.xls";
List<User> userList = EasyExcel.read(userPath).head(User.class).doReadAllSync();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName1 = URLEncoder.encode("生成多个sheet文件", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName1 + ".xls");
ServletOutputStream output = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(output).build();
//这里 需要指定写用哪个class去写
WriteSheet writeSheet = EasyExcel.writerSheet(0, "用户信息").head(User.class).build();
excelWriter.write(userList, writeSheet);
writeSheet = EasyExcel.writerSheet(1, "查询日志信息").head(Log.class).build();
excelWriter.write(logList, writeSheet);
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
output.flush();
}
}
4、读取合并数据分类
| 日志名称 | 日志时间 | 字段名 | 字段描述 | 字段值 |
| 查询用户信息 | 2021-01-11 | userName | 用户名 | tqf_123 |
| age | 年龄 | 25 | ||
| sex | 性别(0-男，1-女，2-未知) | 0 | ||
| 获取日志列表 | 2021-01-12 | logName | 日志名称 | 查询 |
| logTime | 日志时间 | 2021-10-01 14:25:14 | ||
| logUser | 查询人姓名 | 王杰 | ||
| 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 查询最新数据 | 2021-01-15 | time | 时间 | 2021-10-01 14:25:18 |
| total | 数据条数 | 35 |
public static void main(String[] args) {
String fileName = "D:/log.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
for (Log data : list) {
log.info("读取到数据:{}", JSON.toJSONString(data));
}
List<LogDTO> dtoList = new ArrayList<>();
int i = 0;
for (Log data : list) {
// 判断日志名称不为空就是主数据
LogDTO dto = new LogDTO();
i++;
if(StringUtil.isNotBlank(data.getLogName())) {
dto.setLogName(data.getLogName());
dto.setDate(data.getDate());
// 获取子类
List<Log> sonDataList = new ArrayList<>();
Log sonLog = new Log();
sonLog.setFieldName(data.getFieldName());
sonLog.setFieldDesc(data.getFieldDesc());
sonLog.setFieldValue(data.getFieldValue());
sonDataList.add(sonLog);
for (int j = i;j<list.size();j++) {
if(StringUtil.isNotBlank(list.get(j).getLogName())) {
break;
}
if(StringUtil.isBlank(list.get(j).getLogName())) {
Log sonLog1 = new Log();
sonLog1.setFieldName(list.get(j).getFieldName());
sonLog1.setFieldDesc(list.get(j).getFieldDesc());
sonLog1.setFieldValue(list.get(j).getFieldValue());
sonDataList.add(sonLog1);
}
}
dto.setSonDataList(sonDataList);
dtoList.add(dto);
}
}
System.out.println(JsonUtil.toJson(dtoList));
}读取的数据结构：
[{
"logName": "查询用户信息",
"date": "2021-01-11",
"sonDataList": [{
"logName": null,
"date": null,
"fieldName": "userName",
"fieldDesc": "用户名",
"fieldValue": "tqf_123"
}, {
"logName": null,
"date": null,
"fieldName": "age",
"fieldDesc": "年龄",
"fieldValue": "25"
}, {
"logName": null,
"date": null,
"fieldName": "sex",
"fieldDesc": "性别(0-男，1-女，2-未知)",
"fieldValue": "0"
}]
}, {
"logName": "获取日志列表",
"date": "2021-01-12",
"sonDataList": [{
"logName": null,
"date": null,
"fieldName": "logName",
"fieldDesc": "日志名称",
"fieldValue": "查询"
}, {
"logName": null,
"date": null,
"fieldName": "logTime",
"fieldDesc": "日志时间",
"fieldValue": "2021-10-01 14:25:14"
}, {
"logName": null,
"date": null,
"fieldName": "logUser",
"fieldDesc": "查询人姓名",
"fieldValue": "王杰"
}]
}, {
"logName": "根据ID查询详情",
"date": "2021-01-13",
"sonDataList": [{
"logName": null,
"date": null,
"fieldName": "total",
"fieldDesc": "总数",
"fieldValue": "25"
}]
}, {
"logName": "查询最新数据",
"date": "2021-01-15",
"sonDataList": [{
"logName": null,
"date": null,
"fieldName": "time",
"fieldDesc": "时间",
"fieldValue": "2021-10-01 14:25:18"
}, {
"logName": null,
"date": null,
"fieldName": "total",
"fieldDesc": "数据条数",
"fieldValue": "35"
}]
}]LogDTO实体类
package com.shucha.deveiface.biz.test;
import lombok.Data;
import java.util.List;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-01-25 09:57
*/
@Data
public class LogDTO {
/**
* 日志名称
*/
private String logName;
/**
* 日志时间
*/
private String date;
/**
* 对应的子类列表数据
*/
private List<Log> sonDataList;
}
5、 导出excel合并单元格
5.1 要导出的数据2022.xls文件内容
| 日志名称 | 日志时间 | 字段名 | 字段描述 | 字段值 |
| 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 根据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 |
5.2 执行代码调试导入文件
public void mergeExcel(){
String fileName = "D:/2022.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
//需要合并的列
int[] mergeColumeIndex = {0,1};
// 从第二行后开始合并
int mergeRowIndex = 1;
EasyExcel.write("D:/test.xls", Log.class).sheet().registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex)).doWrite(list);
}导出的excel文件格式
| 日志名称 | 日志时间 | 字段名 | 字段描述 | 字段值 |
| 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| total | 总数 | 25 | ||
| total | 总数 | 25 | ||
| 获取日志列表 | 2021-01-12 | logName | 日志名称 | 查询 |
| logTime | 日志时间 | 2021-10-01 14:25:14 | ||
| logUser | 查询人姓名 | 王杰 | ||
| 查询用户信息 | 2021-01-11 | userName | 用户名 | tqf_123 |
| age | 年龄 | 25 | ||
| sex | 性别(0-男，1-女，2-未知) | 0 |
5.3 合并指定列的工具类
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 Jamin
* @date 2020/11/9 11:35
*/
@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.4 导出的实体类
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;
}
5.5 实际的控制层调用代码
/**
* 下载合并单元格
* @param response
* @throws IOException
*/
@GetMapping("/downLoadhb")
public void downLoadhb(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName1 = URLEncoder.encode("生成的excel文件,合并单元格", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName1 + ".xls");
ServletOutputStream output = response.getOutputStream();
// ExcelWriter writer = new ExcelWriter(output, ExcelTypeEnum.XLS, true);
// Sheet sheet = new Sheet(1, 0, ResItemExcelVo.class);
// sheet.setSheetName("第一页");
String fileName = "D:/2022.xls";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
List<Log> list = EasyExcel.read(fileName).head(Log.class).sheet().doReadSync();
for (Log data : list) {
log.info("读取到数据:{}", JSON.toJSONString(data));
}
List<LogDTO> dtoList = new ArrayList<>();
int i = 0;
for (Log data : list) {
// 判断日志名称不为空就是主数据
LogDTO dto = new LogDTO();
i++;
if(StringUtil.isNotBlank(data.getLogName())) {
dto.setLogName(data.getLogName());
dto.setDate(data.getDate());
// 获取子类
List<Log> sonDataList = new ArrayList<>();
Log sonLog = new Log();
sonLog.setFieldName(data.getFieldName());
sonLog.setFieldDesc(data.getFieldDesc());
sonLog.setFieldValue(data.getFieldValue());
sonDataList.add(sonLog);
for (int j = i;j<list.size();j++) {
if(StringUtil.isNotBlank(list.get(j).getLogName())) {
break;
}
if(StringUtil.isBlank(list.get(j).getLogName())) {
Log sonLog1 = new Log();
sonLog1.setFieldName(list.get(j).getFieldName());
sonLog1.setFieldDesc(list.get(j).getFieldDesc());
sonLog1.setFieldValue(list.get(j).getFieldValue());
sonDataList.add(sonLog1);
}
}
dto.setSonDataList(sonDataList);
dtoList.add(dto);
}
}
//需要合并的列
int[] mergeColumeIndex = {0,1};
// 从第二行后开始合并
int mergeRowIndex = 1;
EasyExcel.write(response.getOutputStream(),Log.class).sheet().registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex)).doWrite(list);
output.flush();
}
扫一扫
2895
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
