.....

private static final String FILE_NAME = “Customer.xls”;
@GetMapping(“/downloadCustomerImportTemplate”)
@ApiOperation(“下载客户导入Excel模板”)
public void expertCustomer(HttpServletResponse response) {
String fileName = “CustomerImportTemplate.xls”;
InputStream is = null;
try {
// 设置响应头
response.setCharacterEncoding(“UTF-8”);
response.setHeader(“content-Type”, “application/vnd.ms-excel”);
response.setHeader(“Content-Disposition”, “attachment;filename=” +
URLEncoder.encode(“CustomerImportTemplate.xls”, “UTF-8”));
is = CustomerController.class.getClassLoader()
.getResourceAsStream(“excel-template/” + fileName);
IOUtils.copy(is, response.getOutputStream());
} catch (IOException e) {
log.error(“客户导入模板下载失败，”, e);
}finally {
try {
is.close();
} catch (IOException e) {
log.error(“流对象资源释放失败!”, e);
}
}
}

@PostMapping("/imp

ortCustomer")
@ApiOperation(“导入客户信息”)
public WebResult importCustomer(@RequestParam(“file”)MultipartFile file){
if (!EasyPoiUtil.isImportFileFormat(file)) {
return WebResult.paramFail(“导入失败，文件格式有误!”);
}
List customerReqVO = EasyPoiUtil.importExcel(file, CustomerImportReqVO.class);
return WebResult.ok(customerService.batchSaveImport(customerReqVO));
}

@GetMapping("/exportCustomer")
@ApiOperation("导出客户信息")
public void exportCustomer(CustomerConditionQuery query, HttpServletResponse response){

    List list = customerService.exportCustomer(query);
    EasyPoiUtil.exportExcel(list, CustomerExportRespVO.class, FILE_NAME, response);
}
1
2
3
4
5
6
7

4.0.0


cn.afterturn
easypoi-base

EasyPoiUtil

/**
* 导入Excel
* @param file 导入的excel文件
* @param pojoClass 导入的excel对应的类
* @return 返回得到结果集信息对象
*/
public static List importExcel(MultipartFile file, Class pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
List list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
throw new ServiceException(“excel文件不能为空”);
} catch (RuntimeException e) {
log.error(“导入失败，导入内容有误!”, e);
if (e.getMessage().contains(ARGUMENT_TYPE_ERROR)) {
throw new ServiceException(“导入失败，员工信息类型错误!”);
}
} catch (Exception e) {
log.error(“导入失败，文件读取错误!”, e);
}
return list;
}

/**
* 校验是否为支持导入的文件格式，xls、xlsx
*
* @authro JayPdd
* @date 2020/2/20 13:37
* @param file 上传的文件
* @return 文件格式正确：true，错误：false
*/
public static Boolean isImportFileFormat(MultipartFile file) {
int initSize = 4;
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName) || fileName.length() < initSize) {
return false;
}
String suffix = fileName.substring(fileName.length() - initSize).toLowerCase();
if (!EXPERT_SUPPORT_FILE_FORMAT.contains(suffix)) {
return false;
}
return true;
}

/**
* 导入支持的文件格式
*/
private static final Set EXPERT_SUPPORT_FILE_FORMAT = new HashSet<>(Arrays.asList(“.xls”, “xlsx”));

@GetMapping(“/exportCustomer”)
@ApiOperation(“导出客户信息”)
public void exportCustomer(CustomerConditionQuery query, HttpServletResponse response){

    List list = customerService.exportCustomer(query);
    EasyPoiUtil.exportExcel(list, CustomerExportRespVO.class, FILE_NAME, response);
}
1
2
3

/**
* 导出Excel不支持自定义createHandler
* @param list 导出的数据
* @param pojoClass 导出的数据类型class对象
* @param fileName 文件名称
* @param response http响应对象
*/
public static void exportExcel(List list, Class pojoClass, String fileName,
HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(“0”, “Sheet1”));
}

/**
* 原始默认导出，该参数信息可参考其他到处，大致相似
* @param list
* @param pojoClass
* @param fileName
* @param response
* @param exportParams
*/
private static void defaultExport(List list, Class pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams) {
exportParams.setStyle(ExcelStyleUtil.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null){
downLoadExcel(fileName, response, workbook);
}
}

excel-template

@Slf4j
@EnableSwagger2Doc
@EnableEurekaClient
@SpringBootApplication
@ComponentScan(basePackages = {“com”})
@MapperScan(“com.mapper”)
@EnableFeignClients(basePackages = {“com.api”, “com.feign”})
public class CustomerApplication {
public static void main(String[] args) {
SpringApplication.run(CustomerApplication.class,args);
}
}

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class CustomerImportReqVO extends CustomerDTO {
@TableId(type = IdType.AUTO)
private Long customerId;
@ApiModelProperty(value = “客户编号”, dataType = “String”)
private String customerNumber;
@ApiModelProperty(value = “客户姓名”, dataType = “String”)
@Excel(name = “客户姓名*”, orderNum = “1”, width = 10)
private String customerName;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class CustomerReqVO extends CustomerDTO {
@TableId(type = IdType.AUTO)
private Long customerId;
@ApiModelProperty(value = “客户编号”, dataType = “String”)
@Excel(name = “客户编号”, orderNum = “1”, width = 10)
private String customerNumber;
@ApiModelProperty(value = “客户姓名”, dataType = “String”)
@Excel(name = “客户姓名*”, orderNum = “2”, width = 10)
private String customerName;

客户姓名*