工具类-----------------------------------------------------
public class ExcelUtil extends EasyExcelFactory {
/**
* 批量导入
* @param inputStream
* @param head
* @param readListener
*/
public static void importExcel(InputStream inputStream, Class head, ReadListener readListener){
read(inputStream,head,readListener).sheet().doRead();
}
/**
* 单sheet导出
* @param response
* @param clazz
* @param fileName
* @param list
*/
public static void exportExcel(HttpServletResponse response, Class clazz, String fileName, List<?> list){
try {
String filename = URLEncoder.encode(DateUtil.getDate(new Date(),"yyyyMMddHHmmss")+fileName+".xlsx", "UTF-8");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置文本内省
response.setContentType("application/vnd.ms-excel");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename="+filename);
ExcelWriter excelWriter = write(response.getOutputStream()).build();
WriteSheet writeSheet = writerSheet(0,fileName).head(clazz).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}catch (Exception e){
throw new ServiceException("导出"+fileName+"失败");
}
}
/**
* 多sheet
* @param response
* @param fileName
* @return
*/
public static ExcelWriter exportExcels(HttpServletResponse response, String fileName){
try {
String filename = URLEncoder.encode(DateUtil.getDate(new Date(),"yyyyMMddHHmmss")+fileName+".xlsx", "UTF-8");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置文本内省
response.setContentType("application/vnd.ms-excel");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename="+filename);
return write(response.getOutputStream()).build();
}catch (Exception e){
throw new ServiceException("导出"+fileName+"失败");
}
}
/**
* 多sheet
* @param sheetNo
* @param sheetName
* @return
*/
public static WriteSheet createSheet(Integer sheetNo, String sheetName, Class clazz){
return writerSheet(sheetNo,sheetName).head(clazz).build();
}
}
@ExcelIgnoreUnannotated // 没加导出注解的字段，不导出（注解在类上）
@ExcelProperty(value = "发布方式", converter = BillPublishTypeConverter.class)
//注解在属性上
k v类型转换器 （）
public class BillPublishTypeConverter implements Converter<Integer> {
@Override
public Class<?> supportJavaTypeKey() {
return null;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String name = BillTypeEnum.getNameByCode(value);
return new WriteCellData<>(name);
}
}
批量导入---------------------------------------------------------------------------------------------------------------------
ExcelUtil.importExcel(multipartFile.getInputStream(), BillImportDto.class, new AnalysisEventListener<BillImportDto>() {
List<BillImportDto> billImportList = new ArrayList<>();
@Override
public void invoke(BillImportDto billImportDto, AnalysisContext analysisContext) {
String goodsUnitStr = billImportDto.getGoodsUnitStr();
billImportDto.setGoodsUnit(GoodsUnitEnum.getCodeByName(goodsUnitStr));
String isHideMoneyStr = billImportDto.getIsHideMoneyStr();
billImportDto.setIsHideMoney(FlagEnum.getCodeByName(isHideMoneyStr));
billImportList.add(billImportDto);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
BillImportHandleDto handleDto = new BillImportHandleDto();
handleDto.setList(billImportList);
handleDto.setCompanyId(authUser.getCompanyId());
handleDto.setFileName(fileName);
handleDto.setUserId(authUser.getUserId());
handleDto.setUserName(authUser.getUserName());
billImportService.importBill(handleDto);
}