本案例为测试easyexcel读取,写入excel
开发工具:idea+maven3+springmvc4+spring4+mybatis3+mysql5.7
easyexcel版本:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
使用的工具类:easyexcel工具类
入参Dto:
@Getter
@Setter
@Accessors(chain = true)
public class ExportDto implements Serializable {
private static final long serialVersionUID = 545300882882790732L;
/**
* 人群洞察类型:1AIPL人群洞察2流转人群洞察3人群包人群洞察
*/
private Integer insightType;
/**
* 品牌编码
*/
private String brandCode;
/**
* 品类编码
*/
private String categoryCode;
/**
* 人群类型:0全部人群 1认知2兴趣3购买4忠诚 5流失 6未知
*/
private String startCrowdType;
/**
* 人群类型:0全部人群 1认知2兴趣3购买4忠诚 5流失 6未知
*/
private String endCrowdType;
/**
*流转人群洞察:1未流转2流转
*/
private String flowType;
/**
* 人群包类型
*/
private String audienceCode;
/**
* 大盘类型:1行业大盘2苏宁大盘,默认为行业大盘
*/
@Min(1)
private Integer grailType = 1;
}
出参Vo:
@Getter
@Setter
public class ExportVo extends BaseRowModel {
/**
* 纬度
*/
@ExcelProperty(value = {"纬度"}, index = 0)
private String dimension;
/**
* 特征
*/
@ExcelProperty(value = {"特征"}, index = 1)
private String feature;
/**
* 占比
*/
@ExcelProperty(value = {"占比"}, index = 2)
private String insightPercent;
/**
* 大盘
*/
@ExcelProperty(value = {"大盘"}, index = 3)
private String grailPercent;
/**
* 对比大盘
*/
@ExcelProperty(value = {"对比大盘"}, index = 4)
private String comparePercent;
}
导出一个sheet:
控制层:(数据量控制在20万以内)
@CrossOrigin
@RestController
@RequestMapping("/mip/export")
public class ExportController extends BaseController {
/**
* 基础属性导出
* @param insightType
* @param brandCode
* @param categoryCode
* @param startCrowdType
* @param endCrowdType
* @param flowType
* @param audienceCode
* @param request
* @return
*/
@GetMapping("/basicAttributeExport")
public void basicAttributeExport(
@RequestParam(value = "insightType")
Integer insightType,
@RequestParam(value = "brandCode")
String brandCode,
@RequestParam(value = "categoryCode")
String categoryCode, String startCrowdType, String endCrowdType, String flowType,
String audienceCode, HttpServletRequest request, HttpServletResponse response) {
ExportDto dto = new ExportDto().setInsightType(insightType).setBrandCode(brandCode)
.setCategoryCode(categoryCode).setStartCrowdType(startCrowdType).setEndCrowdType(endCrowdType)
.setFlowType(flowType).setAudienceCode(audienceCode);
exportService.basicAttributeExport(dto, response);
}
}
业务层:
@Service
public class ExportServiceImpl implements ExportService {
//log日志
private static final Logger LOGGER = LoggerFactory.getLogger(ExportServiceImpl.class);
@Autowired
private DalClient dalClient;
@Autowired
private IExportAssertionService exportAssertionService;
/**
* 基础属性
* @param dto
* @param response
*/
public void basicAttributeExport(ExportDto dto, HttpServletResponse response) {
LOGGER.info("基础属性导出入参为:{}", new Gson().toJson(dto));
//查询结果
List<ExportVo> list = queryBasicAttribute(dto);
String fileName = SNConstant.BASICATTRIBUTE + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");
String sheetName = SNConstant.BASICATTRIBUTE;
//方法一:poi导出
//ExcelUtil.downLoadExcel(response, list, fileName, sheetName, ExportVo.class);
//方法二:easyexcel导出
EasyExcelUtil.writeExcelWithModel(response, list, fileName, sheetName, ExportVo.class, ExcelTypeEnum.XLSX);
}
}
postman测试:
导出效果:
导出多个sheet:
控制层:(数据量控制在20万以内)
/**
* 用户洞察中心导出
* @param insightType
* @param brandCode
* @param categoryCode
* @param startCrowdType
* @param endCrowdType
* @param flowType
* @param audienceCode
* @param request
* @param response
* @return
*/
@GetMapping("/downLoadInsight")
public void downLoadInsight(
@Validated
@NotNull(message = "%custom%人群洞察类型不可为空")
@RequestParam(value = "insightType")
Integer insightType,
@Validated
@NotNull(message = "%custom%品牌编码不可为空")
@RequestParam(value = "brandCode")
String brandCode,
@RequestParam(value = "categoryCode")
String categoryCode, String startCrowdType, String endCrowdType, String flowType,
String audienceCode, HttpServletRequest request, HttpServletResponse response) {
ExportDto dto = new ExportDto().setInsightType(insightType).setBrandCode(brandCode)
.setCategoryCode(categoryCode).setStartCrowdType(startCrowdType).setEndCrowdType(endCrowdType)
.setFlowType(flowType).setAudienceCode(audienceCode);
exportService.downLoadInsight(dto, response);
}
业务层:
/**
* 用户洞察中心导出
* @param dto
* @param response
*/
public void downLoadInsight(ExportDto dto, HttpServletResponse response) {
LOGGER.info("用户洞察中心导出入参为:{}", new Gson().toJson(dto));
long l1 = System.currentTimeMillis() / 1000;
String fileName = SNConstant.USERINSIGHT + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");
String sheetName1 = SNConstant.BASICATTRIBUTE;
List<ExportVo> list1 = queryBasicAttribute(dto);
String sheetName2 = SNConstant.LIVINGCONDITION;
List<ExportVo> list2 = queryLivingCondition(dto);
String sheetName3 = SNConstant.PRODUCTPREFERENCE;
List<ExportVo> list3 = queryProductPreference(dto);
String sheetName4 = SNConstant.MEDIAPREFERENCE;
List<ExportVo> list4 = queryMediaPreference(dto);
Map<String, List<? extends BaseRowModel>> dataMap = new HashMap<>();
dataMap.put(sheetName1, list1);
dataMap.put(sheetName2, list2);
dataMap.put(sheetName3, list3);
dataMap.put(sheetName4, list4);
//方法一:Map存储数据
//EasyExcelUtil.writeExcelWithModelSheet(response, dataMap, fileName, ExcelTypeEnum.XLSX);
//方法二:工厂方法
EasyExcelUtil.writeExcelWithSheets(response, list1, fileName, sheetName1, ExportVo.class, ExcelTypeEnum.XLSX)
.write(list2, sheetName2, ExportVo.class).write(list3, sheetName3, ExportVo.class)
.write(list4, sheetName4, ExportVo.class).finish();
long l2 = System.currentTimeMillis() / 1000;
System.out.println("导出耗时:" + (l2 - l1));
}
导出效果:
easyexcel读取excel(一个sheet)
控制层:(数据量控制在20万以内)
@PostMapping("/readExcelWithModel")
public ResponseEntity<BaseResponse> readExcelWithModel(
@RequestParam(value = "file", required = true)
MultipartFile file) {
try {
String fileName = file.getOriginalFilename();
if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase()
.endsWith(".xlsx"))) {
throw new FieldInvalidException("readExcelWithModel", "文件格式错误!");
}
InputStream inputStream = new BufferedInputStream(file.getInputStream());
List<ExportVo> data=EasyExcelUtil.readExcelWithModel(inputStream, ExportVo.class, ExcelTypeEnum.XLSX);
return ResponseEntity.ok(BaseResponse.success().setResult(data));
} catch (IOException e) {
e.printStackTrace();
}
return ResponseEntity.ok(new BaseResponse("读取文件失败").setResult(null));
}
postman测试效果:
easyexcel读取excel(多个sheet)
控制层:(数据量控制在20万以内)
@PostMapping("/readExcelWithModelSheet")
public ResponseEntity<BaseResponse> readExcelWithModelSheet(
@RequestParam(value = "file", required = true)
MultipartFile file) {
try {
String fileName = file.getOriginalFilename();
if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase()
.endsWith(".xlsx"))) {
throw new FieldInvalidException("readExcelWithModel", "文件格式错误!");
}
InputStream inputStream = new BufferedInputStream(file.getInputStream());
List<ExportVo> data=EasyExcelUtil.readExcelWithModelSheet(inputStream, ExportVo.class, ExcelTypeEnum.XLSX);
return ResponseEntity.ok(BaseResponse.success().setResult(data));
} catch (IOException e) {
e.printStackTrace();
}
return ResponseEntity.ok(new BaseResponse("读取文件失败").setResult(null));
}
postman测试效果:
参考: