前言:上文讲述如何上传EXCEL文件,从指定行读取数据,且支持行数据与java属性值相互转换,
entity对象定义方式与上文一致
EXCEL上传指定行读取数据_T浩浩的博客-CSDN博客
前提:需要导出的数据
java">List<ProductImportReq> exportList=Lists.newArrayList();
导出方式一:直接通过流返回给前端
java">public void export(HttpServletResponse response) throws IOException {
List<ProductExportExcelResp> exportList = Lists.newArrayList();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("平台商品主档-"+ LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")), "UTF-8");
// String fileName = "失败文件-"+LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ProductExportExcelResp.class)
.sheet("sheet1")
// 设置字段宽度为自动调整,不太精确
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.doWrite(exportList);
}
导出方式二:将List集合转成excel并上传到OSS
excel信息承载类
java">@AllArgsConstructor
@NoArgsConstructor
@Data
public class EasyExcelParams<T> {
/**
* 文件名称
*/
private String fileName;
/**
* sheet名称
*/
private String sheetName;
/**
* 数据
*/
private List<T> data;
/**
* 数据模型类型
*/
private Class<T> dataModelClazz;
/**
* cell handler
*/
private List<CellWriteHandler> strategies;
public EasyExcelParams(String fileName,String sheetName, List<T> data, Class<T> dataModelClazz) {
this.fileName=fileName;
this.sheetName = sheetName;
this.data = data;
this.dataModelClazz = dataModelClazz;
}
public EasyExcelParams(String fileName,String sheetName, List<T> data, Class<T> dataModelClazz, CellWriteHandler... strategies) {
this(fileName,sheetName, data, dataModelClazz);
this.strategies = Arrays.asList(strategies);
}
public boolean validate() {
return StringUtils.isNotBlank(sheetName)
&& Objects.nonNull(dataModelClazz)
&& Objects.nonNull(data);
}
}
流对象处理工具类
java">public class EasyExcelToStreamUtil {
public static byte[] toInputStream(EasyExcelParams params){
try {
return YLZExcelUtils.getExcelBytes(params);
} catch (ExcelException e) {
throw new RuntimeException(e);
}
}
}
文件上传处理类
java">@RefreshScope
@Component
public class FileUploadUtil<T> {
@Autowired
private RemoteFileService remoteFileService;
@Value(value = "${file.url.prefix}")
private String prefix;
public String uploadExcel(String fileName, String sheetName, List<T> data, Class<T> dataModelClazz){
EasyExcelParams excelParams=new EasyExcelParams(fileName,sheetName,data,dataModelClazz);
byte[] dateByte = EasyExcelToStreamUtil.toInputStream(excelParams);
FileUploadDTO fileUploadDTO=new FileUploadDTO();
fileUploadDTO.setFileName(fileName);
fileUploadDTO.setData(dateByte);
R result = remoteFileService.uploadExcel(fileUploadDTO);
Map<String,String> fileResult= (Map<String, String>) result.getData();
String fileUrl=null!=fileResult.get("url")? prefix+fileResult.get("url") :"";
return fileUrl;
}
}
OSS文件上传实现 上传实现根据项目实际情况选择,实现步骤 1.设置参数 2.将byte[]转成InputStream, 3.上传 保存文件地址
java">@Slf4j
@Service
@AllArgsConstructor
public class SysFileServiceImpl extends ServiceImpl<SysFileMapper, SysFile> implements SysFileService {
//具体上传OSS方式根据项目实际情况选择
private final FileTemplate fileTemplate;
public R uploadExcel(FileUploadDTO fileUploadDTO) {
Map<String, String> resultMap = new HashMap<>(4);
resultMap.put("bucketName", properties.getBucketName());
resultMap.put("fileName", fileUploadDTO.getFileName());
if(null==fileUploadDTO.getData()){
return R.ok("");
}
try (InputStream inputStream = new ByteArrayInputStream(fileUploadDTO.getData())) {
fileTemplate.putObject(properties.getBucketName(),
fileUploadDTO.getFileName(), inputStream, "vnd.ms-excel");
}
catch (Exception e) {
log.error("上传失败", e);
return R.failed(e.getLocalizedMessage());
}
return R.ok(resultMap);
}
}