目录
官方文档:https://alibaba-easyexcel.github.io/index.html
pom依赖
<!-- Excel解析工具 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>
导出:controller
@GetMapping(value = "/export")
@ApiOperation(value = "导出", notes = "")
@ApiImplicitParams({@ApiImplicitParam(paramType = Constants.HEADER, dataType = Constants.STRING, name = Constants.AUTHORIZATION, value = "授权token", required = true)})
public void exportExcel(HttpServletResponse response, QueryDTO query) throws IOException {
//此处为带条件分页查询获取list
QueryPage<ExcelVO> page = service.findList(query);
Assert.notNull(page.getResult(),"导出失败:查询列表为空");
String name = DateUtil.format(LocalDateTime.now(), Constants.DATE_FORMAT) + "文件名" ;
// 这里URLEncoder.encode可以防止中文乱码
String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//单sheet导出
EasyExcel.write(response.getOutputStream(), ExcelVO.class).sheet("导出").doWrite(page.getResult());
}
工具类导出:
@GetMapping("/export")
@CheckPermissions(value = "security:family:export")
@ApiOperation(value = "家庭组导出", notes = "permissions = security:family:export")
@OperationLog(value = logName + "导出", operateType = LogConstants.EXPORT, saveRequestData = false)
@ApiOperationSupport(order = 7)
public void export(HttpServletResponse response, FamilyQuery query) {
List<FamilyExport> list = familyService.findExport(query);
ExcelUtil.writeExcel(response, list, "家庭组_" + DateUtil.format(new Date(), "yyyyMMddHHmmss"), "家庭组", FamilyExport.class);
}
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
/**
* excel工具类
*
* @author: tc
* @date: 2021-02-04 18:11
*/
@Slf4j
public class ExcelUtil {
/**
* excel文件类型
*/
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* 读取Excel(多个sheet可以用同一个实体类解析)
*
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
ExcelListener<T> excelListener = new ExcelListener<>();
ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
/**
* 读取一个sheet
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcelOneSheet(InputStream excelInputStream, String fileName, Class<T> clazz) {
ExcelListener<T> excelListener = new ExcelListener<>();
ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
if (excelReader == null) {
return Lists.newArrayList();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
if(CollectionUtil.isNotEmpty(readSheetList)){
//读取1个
excelReader.read(readSheetList.get(0));
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
/**
* 读取Excel 指定sheet
*
* @param excelInputStream
* @param fileName
* @param clazz
* @param sheetIndex
* @param <T>
* @return
*/
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz, int sheetIndex) {
ExcelListener<T> excelListener = new ExcelListener<>();
ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
excelReader.read(excelReader.excelExecutor().sheetList().get(sheetIndex));
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
/**
* 导出Excel(一个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
OutputStream outputStream = getOutputStream(response, fileName);
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).registerWriteHandler(cellStyle()).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).registerWriteHandler(cellStyle()).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}
/**
* 导出Excel(多个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
OutputStream outputStream = getOutputStream(response, fileName);
WriteWorkbook writeWorkbook = new WriteWorkbook();
writeWorkbook.setOutputStream(outputStream);
writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
ExcelWriterFactory writerFactory = new ExcelWriterFactory(writeWorkbook, outputStream);
WriteSheet writeSheet = EasyExcel.writerSheet(1, sheetName).head(clazz).registerWriteHandler(cellStyle()).build();
writerFactory.write(list, writeSheet);
return writerFactory;
}
/**
* 导出时生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
try {
//创建本地文件
String filePath = fileName + ".xlsx";
File file = new File(filePath);
FileUtil.mkParentDirs(file);
fileName = new String(filePath.getBytes(), StandardCharsets.ISO_8859_1);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
log.error("导出io异常", e);
return null;
}
}
/**
* 返回ExcelReader
*
* @param inputStream
* @param filename
* @param clazz
* @param excelListener
* @param <T>
* @return
*/
private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener<T> excelListener) {
try {
if (StrUtil.isBlank(filename)) {
return null;
}
if (!filename.toLowerCase().endsWith(XLS) && !filename.toLowerCase().endsWith(XLSX)) {
return null;
}
return EasyExcel.read(inputStream, clazz, excelListener).build();
} catch (Exception e) {
log.error("excel读取异常", e);
return null;
} finally {
IoUtil.close(inputStream);
}
}
/**
* EasyExcel异常信息
* @param excelDataConvertException
* @return
*/
public static String getIndexErrorMsg(ExcelDataConvertException excelDataConvertException) {
String cellMsg = "";
CellData cellData = excelDataConvertException.getCellData();
//这里有一个celldatatype的枚举值,用来判断CellData的数据类型
CellDataTypeEnum type = cellData.getType();
if (type.equals(CellDataTypeEnum.NUMBER)) {
cellMsg = cellData.getNumberValue().toString();
} else if (type.equals(CellDataTypeEnum.STRING)) {
cellMsg = cellData.getStringValue();
} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {
cellMsg = cellData.getBooleanValue().toString();
}
return String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex() + 1, cellMsg);
}
/**
* 设置样式
*
* @return
*/
private static HorizontalCellStyleStrategy cellStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
/*// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
// 设置底边框;
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 设置自动换行
headWriteCellStyle.setWrapped(true);
// 设置垂直居中
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置水平居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 15);
headWriteCellStyle.setWriteFont(headWriteFont);*/
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置底边框;
/*contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
contentWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
contentWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
contentWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());*/
// 设置自动换行
contentWriteCellStyle.setWrapped(true);
// 设置垂直居中
/*contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);*/
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
导出:vo
@Data
@ColumnWidth(20)
@HeadRowHeight(30)
@ApiModel("菜品池")
public class CategoryCuisineDishesVO {
@ApiModelProperty(value = "id")
@ExcelIgnore
private Long id;
@ApiModelProperty(value = "菜品编码")
@ColumnWidth(30)
@ExcelProperty(value = "菜品编码", index = 0)
private String code;
@ApiModelProperty(value = "菜品名称")
@ExcelProperty(value = "菜品名称", index = 1)
private String name;
}
导入:controller
@PostMapping(value = "/import")
@ApiOperation(value = "导入", notes = "")
@ApiImplicitParams({
@ApiImplicitParam(paramType = Constants.HEADER, dataType = Constants.STRING, name = Constants.AUTHORIZATION, value = "授权token", required = true),
@ApiImplicitParam(paramType = Constants.FORM, name = Constants.FILE, dataType = Constants.DATE_TYPE_FILE, value = "文件对象", required = true)
})
public Result importExcel(HttpServletRequest request,@RequestParam("file") MultipartFile file){
Assert.notNull(file, "请选择文件");
String name = file.getOriginalFilename().toLowerCase();
Assert.isTrue(name.endsWith(".xls") || name.endsWith(".xlsx"), "格式不支持!");
//解析获取用户登录信息
BaseToken baseToken = TokenUtil.current(request);
try {
//建对应的实体类导入监听器和实体类对应的service
EasyExcel.read(file.getInputStream(), ExcelVO.class,new xxxExcelListener(xxxService,baseToken)).sheet().doRead();
}catch (IllegalArgumentException e){
throw e;
}catch (ExcelAnalysisException e) {
if (e.getCause() instanceof ExcelDataConvertException) {
return Result.fail(ExcelUtil.getIndexErrorMsg((ExcelDataConvertException)e.getCause()));
}
}catch(Exception e){
log.error("[导入失败]",e);
throw new IllegalArgumentException("导入失败");
}
return Result.ok();
}
导入:监听器Listener
public class XxxExcelListener extends AnalysisEventListener<XxxExcelVO> {
private static final Logger LOGGER = LoggerFactory.getLogger(XxxExcelListener .class);
private XxxService xxxService;
private BaseToken baseToken;
private List<XxxExcelVO> list = new ArrayList<>();
public XxxExcelListener (XxxService xxxService, BaseToken baseToken){
this.xxxService= xxxService;
this.baseToken = baseToken;
}
@Override
public void invoke(XxxExcelVOdata, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
//每获取一条数据则存到list
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//所有数据存储完成后调用对应的service去新增list
saveData();
}
private void saveData() {
if (!list.isEmpty()){
//根据自己的业务新增
xxxService.importXXX(list,baseToken);
}
}
}