一、生成模板,下载文件
1.固定数据生成表头
先弄一个导入数据的实体类
@Data
@ColumnWidth(20)
public class UserAccountBO {
@ExcelProperty("学号")
private String sid;
@ExcelProperty("姓名")
private String name;
@ExcelProperty(value = "性别",converter = SexConvert.class)
private Integer sex;
@ExcelProperty("生日")
private String birthday;
@ExcelProperty("备注")
private String note;
}
@ExcelProperty:核心注解,value
属性可用来设置表头名称,converter
属性可以用来设置类型转换器;
@ColumnWidth:用于设置表格列的宽度;
@DateTimeFormat:用于设置日期转换格式。
在EasyExcel中,如果你想实现枚举类型到字符串的转换(比如gender属性中,0->男
,1->女
),需要自定义转换器,下面为自定义的SexConvert
代码实现;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* @Desc excel性别数据转换
**/
public class SexConvert implements Converter<Integer> {
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return "男".equals(cellData.getStringValue()) ? 1 : 2;
}
}
2.根据动态数据生成表头
public void downLoadModel(@Valid @RequestBody DynamicDataTypeParam.Id param, HttpServletResponse response) {
DynamicDataEntity dynamicDataEntity = dynamicDataTypeService.findById(param.getId());
try (OutputStream outputStream = response.getOutputStream()) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(dynamicDataEntity.getName() + "导入模板"+ ".xlsx", "utf-8"); //用与转码
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
// 导出的表头
List<List<String>> nameList = new ArrayList<>(dynamicDataEntity.getDataOne().stream().map(map -> {
List<String> name = new ArrayList<>();
name.add(map.getName());
return name;
}).toList());
dynamicDataEntity.getDataTwo().forEach(dataEntity->{
List<String> name = new ArrayList<>();
name.add(dataEntity.getName());
nameList.add(name);
});
EasyExcel.write(outputStream)
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.head(nameList)
.sheet(dynamicDataEntity.getName()).doWrite(Collections.EMPTY_LIST);
//这种是读sheet页,可以用于多个sheet
ExcelWriter excelWriter = EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
WriteSheet sheet = EasyExcel.writerSheet("Sheet1").head(nameList).build();
} catch (IOException e) {
throw new RuntimeException("下载导入模板失败", e);
}
}
二、导入文件,读文件
1.读文件所需类MyAnalysisEventListener
可以自定义方法,把读到的数据变成想要的格式在进行处理
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.lang.reflect.Field;
import java.util.*;
public class MyAnalysisEventListener<T> extends AnalysisEventListener<T> {
List<T> list = new ArrayList<>();
Set<String> set = new HashSet<>();
List<String> headerRow = new ArrayList<>();
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// 处理表头行数据
for (int i = 0; i < headMap.size(); i++) {
String header = headMap.get(i);
headerRow.add(header);
}
}
@Override
public void invoke(T t, AnalysisContext analysisContext) {
list.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List<String> getHeaderRow(){
return headerRow;
}
public List<T> getDataList(){
//自定义方法
List<T> resultList = new ArrayList<>();
resultList.addAll(list);
list.clear();
set.clear();
return resultList;
}
}
2.校验表头和动态数据写入
public ApiResponse<?> importData(@RequestPart("id") String id, @RequestPart("file") MultipartFile file) {
if (StringUtils.isSpace(id)) {
return ApiResponse.fail("id不能为空");
}
//文件校验
String filename = file.getOriginalFilename();
if (file.isEmpty() || filename == null) {
return ApiResponse.fail("请选择文件");
}
String suffix = filename.substring(filename.lastIndexOf(".") + 1);
if (!suffix.equalsIgnoreCase("xls") && !suffix.equalsIgnoreCase("xlsx")) {
return ApiResponse.fail("文件格式不正确");
}
List<DynamicDataEntity> dynamicDataEntityList = new ArrayList<>();
try (InputStream inputStream = file.getInputStream()) {
MyAnalysisEventListener<Object> listener = new MyAnalysisEventListener<>();
EasyExcel.read(inputStream).sheet().registerReadListener(listener).doRead();
DynamicDataTypeEntity dynamicDataTypeEntity = dynamicDataTypeService.findById(id);
// 检验表头
List<String> headerRow = listener.getHeaderRow();
List<String> nameDataOnes = dynamicDataTypeEntity.getDataOne().stream().map(DataDataOneEntity::getName).toList();
List<String> nameDataTwos = new ArrayList<>(dynamicDataTypeEntity.getDataTwo().stream().map(DataDataOneEntity::getName).toList());
int i1 = nameDataTwos.size() - 1;
nameDataTwos.remove(i1);
List<String> nameDataOneHeadList = headerRow.subList(0, nameDataOnes.size());
if (!nameDataOnes.equals(nameDataOneHeadList)) {
return ApiResponse.fail("表头不匹配,请按照模板列名填写");
}
// DataTwo有几个 DataTwo是多个的形式
int mulHead = (headerRow.size() - nameDataOnes.size()) / nameDataTwos.size();
int f = nameDataOnes.size();
int g = f;
for (int i = 1; i <= mulHead; i++) {
g += nameDataTwos.size();
List<String> nameDataTwoHeadList = headerRow.subList(f, g);
Set<String> nameDataTwoHeadSet = new HashSet<>(nameDataTwoHeadList);
if (!nameDataTwoHeadSet.isEmpty() && nameDataTwoHeadSet.size() != 1 && !nameDataTwoHeadSet.contains(null)) {
if (!nameDataTwos.equals(nameDataTwoHeadList)) {
return ApiResponse.fail("第" + i + "列不匹配,请按照模板列名填写");
}
}
f = g;
}
// 数据处理
List<Object> dataList = listener.getDataList();
// 。。。。。
} catch (IOException e) {
return ApiResponse.fail("导入文件失败");
}
dynamicDataService.saveAll(dynamicDataEntityList);
return ApiResponse.success("导入文件保存成功");
}
三、导出多个excel,导出压缩包
public void downLoadApplyTeacherData(@Valid @RequestBody DynamicDataTypeParam.Id param, HttpServletResponse response) throws UnsupportedEncodingException {
List<DynamicDataEntity> dynamicDataEntityList = dynamicDataService.findById(param.getId());
if (dynamicDataEntityList == null || dynamicDataEntityList.isEmpty()) {
String errMessage = URLEncoder.encode("导出失败,没有数据!", "utf-8");
response.setHeader("Access-Control-Expose-Headers", "X-Error-Message");
response.setHeader("X-Error-Message", new String(errMessage.getBytes("UTF-8"), "ISO-8859-1"));
}else {
Set<DynamicDataEntity> dynamicDataEntitySet = new HashSet<>(dynamicDataEntityList);
Map<DynamicDataTypeEntity, List<DynamicDataEntity>> categorizedData = dynamicDataEntitySet.stream()
.collect(Collectors.groupingBy(DynamicDataEntity::getDynamicDataTypeEntity));
File tempDir = new File(CommonUtils.getJarFilePath() + File.separator + "\\temp");
tempDir.mkdirs();
// zip导出响应处理
response.setContentType("application/zip");
String fileName = URLEncoder.encode("数据导出" + ".zip", "utf-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
try (ServletOutputStream outputStream = response.getOutputStream();
ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
categorizedData.forEach((dynamicDataTypeEntity, dynamicDataEntitys1) -> {
List<Object> dynamicDataEntitys = new ArrayList<>();
// 处理数据 dynamicDataEntitys1 。。。。
// 处理后数据dynamicDataEntitys
try {
// 创建 Excel 文件,写入数据
File excelFile = new File(tempDir, dynamicDataTypeEntity.getName() + ".xlsx");
try (OutputStream outputStream1 = new FileOutputStream(excelFile)) {
// 使用 EasyExcel 写入数据到 outputStream
List<List<String>> nameList = new ArrayList<>(dynamicDataTypeEntity.getDataOne().stream().map(map -> {
List<String> name = new ArrayList<>();
name.add(map.getName());
return name;
}).toList());
EasyExcel.write(outputStream1)
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.head(nameList)
.sheet(dynamicDataTypeEntity.getName()).doWrite(dynamicDataEntitys);
}
FileInputStream fis = new FileInputStream(excelFile);
// 创建一个字节数组,大小等于文件的长度
byte[] excelData = new byte[(int) excelFile.length()];
// 读取文件内容到字节数组
fis.read(excelData);
fis.close();
ZipEntry zipEntry = new ZipEntry(dynamicDataTypeEntity.getName() + ".xlsx");
zipOutputStream.putNextEntry(zipEntry);
zipOutputStream.write(excelData);
zipOutputStream.closeEntry();
} catch (IOException e) {
throw new RuntimeException("下载导入模板失败", e);
}
});
} catch (Exception e) {
throw new RuntimeException("下载导入模板失败", e);
}
// 删除临时文件
deleteTempFiles(tempDir);
File[] files = tempDir.listFiles();
if (files != null) {
for (File file : files) {
if (file.isDirectory()) {
deleteTempFiles(file);
} else {
file.delete();
}
}
}
tempDir.delete();
}
}
四、导出表格需合并单元格
表头使用List<List<String>> 的类型,相当于二维数组
合并单元格使用的是POI的CellRangeAddress
合并策略有4个参数,分别是开始行,结束行,开始列,结束列。下标从0开始
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
最终效果
我只合并一行数据用了Collections.singletonList(new CellRangeAddress(0, 0, 0, dataList.get(0).size()-1)))转了一下
如果多个直接放List<CellRangeAddress>就行
List<List<String>> headList = new ArrayList<>();
headList.add(List.of(name,"序号"));
headList.add(List.of(name,"学号"));
headList.add(List.of(name,"姓名"));
headList.add(List.of(name,"总得分"));
// 数据 是动态所以用List<Object>
List<List<Object>> dataList = new ArrayList<>();
EasyExcel.write(outputStream)
.registerWriteHandler(
new CommonMergeStrategy(
Collections.singletonList(new CellRangeAddress(0, 0, 0, dataList.get(0).size()-1))))
.head(headList).sheet("比赛结果").doWrite(dataList);
CommonMergeStrategy 类
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* 公共的合并单元格类,支持自定义合并策略
*/
public class CommonMergeStrategy extends AbstractMergeStrategy {
// 合并策略list
private List<CellRangeAddress> cellRangeAddresssList;
// 通过有参数构造方法,设置合并策略
public CommonMergeStrategy(List<CellRangeAddress> list) {
this.cellRangeAddresssList = list;
}
/**
* 重写合并策略方法
* @param sheet sheet
* @param cell cell
* @param head head
* @param relativeRowIndex relativeRowIndex
*/
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
// 将自定义合并策略假如excel
if (CollectionUtils.isNotEmpty(cellRangeAddresssList)) {
// if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
for (CellRangeAddress item : cellRangeAddresssList) {
sheet.addMergedRegionUnsafe(item);
}
// }
}
}
}
五、本地写测试代码时读写数据
读
String filePath = "C:\\Users\\AA\\Desktop\\数据.xlsx";
MyAnalysisEventListener<Object> listener = new MyAnalysisEventListener<>();
EasyExcel.read(filePath, listener).head(AA.class).doReadAll();
// 数据处理
List<Object> dataList = listener.getDataList();
for (Object o : dataList) {}
写
EasyExcel.write(fileTeacherPath1, DataTeacher.Teacher.class)
.sheet("Sheet1")
.doWrite(dataList);
六、当写文件时,某条件不符合,不能给导出文件,返回给前端的处理方式
1.返回到响应头里
// 设置 HTTP 响应状态为 400
// response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
String errMessage = URLEncoder.encode("导出失败,没有数据!", "utf-8");
response.setHeader("Access-Control-Expose-Headers", "X-Error-Message");
response.setHeader("X-Error-Message", new String(errMessage.getBytes("UTF-8"), "ISO-8859-1"));
2.给一个空文件
try (ServletOutputStream outputStream = response.getOutputStream();
ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
// zip导出响应处理
response.setContentType("application/zip");
String fileName = URLEncoder.encode(+"没有数据" + ".zip", "utf-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
// 创建一个 ByteArrayOutputStream 来存储空文件的内容
ByteArrayOutputStream emptyFileContent = new ByteArrayOutputStream();
// 创建一个空文件
String emptyFileName = "没有数据.txt";
emptyFileContent.write("没有数据".getBytes("UTF-8"));
// 将空文件添加到zip中
ZipEntry entry = new ZipEntry(emptyFileName);
zipOutputStream.putNextEntry(entry);
zipOutputStream.write(emptyFileContent.toByteArray());
zipOutputStream.closeEntry();
// 清理空文件内容
emptyFileContent.close();
} catch (IOException e) {
throw new RuntimeException("下载导入模板失败", e);
}