使用easyexcel
1. pom引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2. Excel文件抽取及写入代码
/**
* 从 excel文件 抽取数据
*/
public List<ExcelCouponDTO> getCheckDataFromExcel(String fileUrl) {
List<ExcelCouponDTO> couponCheckDTOList = new LinkedList<>();
Workbook workbook;
try (InputStream is = getUrl(fileUrl).openStream()) {
// 非excel文件则抛出异常
if (!fileUrl.endsWith(ExcelTypeEnum.XLS.getValue())
&& !fileUrl.endsWith(ExcelTypeEnum.XLSX.getValue())) {
throw new ClientViewException("上传文件不是Excel类型文件，请检查");
}
if (fileUrl.endsWith(ExcelTypeEnum.XLS.getValue())) {
workbook = new HSSFWorkbook(is);
} else {
workbook = new XSSFWorkbook(is);
}
int sheetCount = workbook.getNumberOfSheets();
if (sheetCount == 0) {
throw new com.bilibili.mall.common.exception.ClientViewException("文件为空!");
}
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {
throw new com.bilibili.mall.common.exception.ClientViewException("文件内数据为空！");
}
// 从第一行读取
for (int i = 1; i <= rowCount; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
int cellCount = row.getFirstCellNum();
if (cellCount < 0) {
continue;
}
ExcelCouponDTO couponCheckDTO = new ExcelCouponDTO();
couponCheckDTO.setType(getCellFormatValue(row.getCell(0)));
couponCheckDTOList.add(couponCheckDTO);
}
} catch (IOException e) {
logger.error("读取 excel 文件内容错误", e);
}
return couponCheckDTOList;
}
public URL getUrl(String fileUrl) {
URL url;
try {
fileUrl = fileUrl.startsWith("//") ? "http:" + fileUrl : fileUrl;
url = new URL(fileUrl);
} catch (Exception e) {
throw new com.bilibili.mall.common.exception.ClientViewException(com.bilibili.mall.common.enums.ErrorCode.ILLEGAL_PARAM.getCode(), com.bilibili.mall.common.enums.ErrorCode.ILLEGAL_PARAM.getMessage());
}
return url;
}
public static String getCellFormatValue(Cell cell) {
String cellValue;
if (cell != null) {
// 判断cell类型
switch (cell.getCellTypeEnum()) {
case NUMERIC:
double doubleVal = cell.getNumericCellValue();
long longVal = Math.round(cell.getNumericCellValue());
if (Double.parseDouble(longVal + ".0") == doubleVal) {
cellValue = String.valueOf(longVal);
} else {
cellValue = BigDecimal.valueOf(doubleVal).toPlainString();
}
break;
case FORMULA:
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(cell.getDateCellValue());
} else {
// 数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
cellValue = cell.getRichStringCellValue().getString();
break;
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
/**
* 批量创券文件生成
*/
@Async
public String createMultiCouponFile(List<ExcelCouponDTO> couponCheckDTOList) {
// 1. 构建文件名称
String fileName = "批量优惠券信息-" + System.currentTimeMillis() + ".xlsx";
String fileFullName = localFilePath + File.separator + fileName;
// 2. 构建文件内容
List<ExcelUtils.MultiCouponMode> bodyDataList = new ArrayList<>();
for (ExcelCouponDTO couponCheckDTO : couponCheckDTOList) {
ExcelUtils.MultiCouponMode multiCouponMode = new ExcelUtils.MultiCouponMode();
BeanUtils.copyProperties(couponCheckDTO, multiCouponMode);
if (Boolean.TRUE.equals(couponCheckDTO.getUploadStatus())) {
multiCouponMode.setUploadStatus("上传成功");
} else {
multiCouponMode.setUploadStatus("上传失败");
}
if (couponCheckDTO.getCreateStatus() == null) {
multiCouponMode.setCreateStatus("未创建");
} else if (Boolean.TRUE.equals(couponCheckDTO.getCreateStatus())) {
multiCouponMode.setCreateStatus("创建成功");
} else {
multiCouponMode.setCreateStatus("创建失败");
}
bodyDataList.add(multiCouponMode);
}
// 3. 创建文件
try {
ExcelUtils.createMultiCouponFile(fileFullName, bodyDataList);
} catch (Exception e) {
logger.error(" createMultiCouponFile 文件创建失败", e);
}
// 4. 上传文件
File file = new File(fileFullName);
try {
amazonS3.putObject(bucket, fileName, file);
// 5. 删除本地文件
logger.info(" createMultiCouponFile 删除本地文件是否成功 {} {}", fileName, file.delete());
} catch (SdkClientException e) {
logger.error("createMultiCouponFile 上传文件失败", e);
}
return asyncSendAssetHandler.downloadUrl(fileName);
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public static class MultiCouponMode {
@ExcelProperty(value = "权益/资产类型", index = 0)
private String type;
@ExcelProperty(value = "优惠券名称", index = 1)
private String couponName;
}
public static void createMultiCouponFile(String fileName, List<MultiCouponMode> multiCouponModes) throws Exception {
createIfNecessary(fileName);
ExcelWriter excelWriter = EasyExcelFactory.write()
.head(MultiCouponMode.class)
.file(fileName)
.autoCloseStream(true)
.build();
excelWriter.write(multiCouponModes, EasyExcelFactory.writerSheet(1, "批量创券").build());
excelWriter.finish();
}
private static void createIfNecessary(String fileName) throws Exception {
File file = new File(fileName);
if (!file.exists()) {
file.getParentFile().mkdirs();
if (!file.createNewFile()) {
log.warn("create file failed");
}
}
}
|
