随笔记录通过easy excel实现导入导出
第一步：导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
第二步：创建导入导出工具类
public class ExcelUtil {
/**
* 导出数据为excel文件
*
* @param filename 文件名称
* @param sheetName sheet名称
* @param dataResult 集合内的bean对象类型要与clazz参数一致
* @param clazz 集合内的bean对象类型要与clazz参数一致
* @param response HttpServlet响应对象
*/
//有实体对象的导出方式
public static void export(String filename,String sheetName, List<?> dataResult, Class<?> clazz, HttpServletResponse response) {
response.setStatus(200);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
OutputStream outputStream = null;
ExcelWriter excelWriter = null;
try {
if (StringUtil.isEmpty(filename)) {
throw new RuntimeException("'filename' 不能为空");
}
String fileName = filename.concat(".xls");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outputStream = response.getOutputStream();
// 根据不同的策略生成不同的ExcelWriter对象
if (dataResult == null){
excelWriter = getTemplateExcelWriter(outputStream);
} else {
excelWriter = getExportExcelWriter(outputStream);
}
//获取表头个数
int last = clazz.getDeclaredFields().length;
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(clazz)
.relativeHeadRowIndex(1)
.registerWriteHandler(new MonthSheetWriteHandler(sheetName,last))//设置大标题名称及其单元格合并
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
.build();
// 写出数据
excelWriter.write(dataResult,writeSheet);
} catch (Exception e) {
log.error("导出excel数据异常：", e);
throw new RuntimeException(e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
if (outputStream != null) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error("导出数据关闭流异常", e);
}
}
}
}
//没有实体对象的导出方式
public static void export(String filename,String sheetName,String headName, List<?> dataResult, List<List<String>> head, HttpServletResponse response) {
response.setStatus(200);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
OutputStream outputStream = null;
ExcelWriter excelWriter = null;
try {
if (StringUtil.isEmpty(filename)) {
throw new RuntimeException("'filename' 不能为空");
}
String fileName = filename.concat(".xls");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outputStream = response.getOutputStream();
// 根据不同的策略生成不同的ExcelWriter对象
if (dataResult == null){
excelWriter = getTemplateExcelWriter(outputStream);
} else {
excelWriter = getExportExcelWriter(outputStream);
}
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head)
.relativeHeadRowIndex(1)
.registerWriteHandler(new MonthSheetWriteHandler(headName,head.size()))//设置大标题名称及其单元格合并
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
.build();
// 写出数据
excelWriter.write(dataResult,writeSheet);
} catch (Exception e) {
log.error("导出excel数据异常：", e);
throw new RuntimeException(e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
if (outputStream != null) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error("导出数据关闭流异常", e);
}
}
}
}
/**
* 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
* @param outputStream 数据输出流
* @return 模板下载ExcelWriter对象
*/
private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){
return EasyExcel.write(outputStream)
//.registerWriteHandler(new CommentWriteHandler()) //增加批注策略
//.registerWriteHandler(new CustomSheetWriteHandler()) //增加下拉框策略
.registerWriteHandler(getStyleStrategy()) //字体居中策略
.build();
}
/**
* 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
* @param outputStream 数据输出流
* @return 数据导出ExcelWriter对象
*/
private static ExcelWriter getExportExcelWriter(OutputStream outputStream){
return EasyExcel.write(outputStream)
.registerWriteHandler(getStyleStrategy()) //字体居中策略
.build();
}
/**
* 设置表格内容居中显示策略
* @return
*/
private static HorizontalCellStyleStrategy getStyleStrategy(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)10);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteFont.setFontName("宋体");
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容策略
WriteCellStyle writeCellStyle = new WriteCellStyle();
// 设置内容水平居中
writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//垂直居中,水平居中
writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
writeCellStyle.setBorderLeft(BorderStyle.THIN);
writeCellStyle.setBorderTop(BorderStyle.THIN);
writeCellStyle.setBorderRight(BorderStyle.THIN);
writeCellStyle.setBorderBottom(BorderStyle.THIN);
writeCellStyle.setWriteFont(headWriteFont);
//设置 自动换行
//contentWriteCellStyle.setWrapped(true);
return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle);
}
/**
* 根据Excel模板，批量导入数据
* @param file 导入的Excel
* @param clazz 解析的类型
* @return 解析完成的数据
*/
public static List<?> importExcel(MultipartFile file, Class<?> clazz){
if (file == null || file.isEmpty()){
throw new RuntimeException("没有文件或者文件内容为空！");
}
List<Object> dataList = null;
BufferedInputStream ipt = null;
try {
InputStream is = file.getInputStream();
// 用缓冲流对数据流进行包装
ipt = new BufferedInputStream(is);
// 数据解析监听器
ExcelListener listener = new ExcelListener();
// 读取数据
EasyExcel.read(ipt, clazz,listener).headRowNumber(2).sheet().doRead();
// 获取去读完成之后的数据
dataList = listener.getDatas();
} catch (Exception e){
log.error(String.valueOf(e));
throw new RuntimeException("数据导入失败！" + e);
}
return dataList;
}
}
第三步：表格头部设置
public class MonthSheetWriteHandler implements SheetWriteHandler {
private String titleName="";
private int last=0;
public MonthSheetWriteHandler(String titleName,int last){
this.titleName=titleName;
this.last=last;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 800);
Cell cell = row1.createCell(0);
//设置标题
cell.setCellValue(titleName+"表");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, last));
}
}
第四步：创建导入的监听器
public class ExcelListener extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
doSomething(o);//根据自己业务做处理
}
private void doSomething(Object object) {
//1、入库调用接口
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// datas.clear();//解析结束销毁不用的资源
}
}
第五步：service层调用导入导出方法
//导出
/**
fileName:文件名称
sheetName:sheet名称
exportList:数据源
HouseExportVO.class:实体类对象，与数据源对应
response:HttpServlet响应对象
*/
//ExcelUtil.export(fileName,sheetName,exportList, HouseExportVO.class, response); 有实体类对象的调用方式
/**
fileName:文件名称
sheetName:sheet名称
headName:头部标题名称
dataList：数据源组装
headList：动态头部列组装
response:HttpServlet响应对象
*/
//ExcelUtil.export(fileName,sheetName,headName,dataList, headList, response);动态列调用方式
//导入
//读取文件，获取数据
/**
multipartFile:导入的文件，由前端传入
HouseImportExcelDTO.class：接受数据所对应的实体对象
读取到的数据源：excelData
*/
//List<?> excelData = ExcelUtil.importExcel(multipartFile, HouseImportExcelDTO.class);
自此通用的easyExcel导入导出就完成了，具体导出的表格样式自行设置！