1、实体对象VO
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class PrizeLogImportExcelVO {
@ExcelProperty("订单编号")
private String prizeSn;
@ExcelProperty("快递公司")
private String expressName;
@ExcelProperty("快递单号")
private String expressSn;
@ExcelProperty("快递编码")
private String expressCode;
@ExcelProperty("快递ID")
private Integer expressId;
}
2、接口类
public interface IJmPrizeLogService {
/**
* 导入
*
* @param list
*/
void importExcel(List<PrizeLogImportExcelVO> list);
}
3、接口实现类
@Service
public class JmPrizeLogServiceImpl extends ServiceImpl<JmPrizeLogMapper, JmPrizeLog> implements IJmPrizeLogService {
public static List<String> errorPrizeSns = new ArrayList<>();
@Resource
private JmPrizeLogMapper prizeLogMapper;
@Resource
private JmExpressMapper expressMapper;
private static final ExecutorService POOL = Executors.newCachedThreadPool();
@Override
public void importExcel(List<PrizeLogImportExcelVO> list) {
errorPrizeSns.clear();
if (CollectionUtil.isEmpty(list)) {
errorPrizeSns.add("excel中无数据,无法导入发货!");
return;
}
List<PrizeLogImportExcelVO> result = new ArrayList<>();
for (PrizeLogImportExcelVO importExcelVO : list) {
String prizeSn = importExcelVO.getPrizeSn();
if (StringUtils.isEmpty(prizeSn)) {
continue;
}
if (StringUtils.isBlank(importExcelVO.getExpressName())) {
errorPrizeSns.add(prizeSn + " 快递公司不能为空!");
continue;
}
if (StringUtils.isBlank(importExcelVO.getExpressSn())) {
errorPrizeSns.add(prizeSn + " 快递单号不能为空!");
continue;
}
QueryWrapper<JmExpress> expressQueryWrapper = new QueryWrapper<>();
expressQueryWrapper.eq("name", importExcelVO.getExpressName());
JmExpress jmExpress = expressMapper.selectOne(expressQueryWrapper);
if (!ObjectUtil.isNotEmpty(jmExpress)) {
errorPrizeSns.add(prizeSn + " 快递公司有误!");
continue;
} else {
importExcelVO.setExpressCode(jmExpress.getCode());
importExcelVO.setExpressId(jmExpress.getId().intValue());
}
result.add(importExcelVO);
}
if (CollectionUtil.isEmpty(errorPrizeSns)) {
POOL.execute(() -> {
prizeLogMapper.batchExpressInfo(result);
});
}
}
}
4、 Mapper接口
public interface JmPrizeLogMapper extends BaseMapper<JmPrizeLog> {
/**
* 批量导入快递信息
*
* @param list
*/
@Update("<script><foreach collection='list' item='o' index='index' separator=';' >UPDATE `jm_prize_log` SET express_name =#{o.expressName}, express_sn = #{o.expressSn}, express_code = #{o.expressCode}, express_id = #{o.expressId} WHERE prize_sn = #{o.prizeSn}</foreach></script>")
void batchExpressInfo(@Param("list") List<PrizeLogImportExcelVO> list);
}
5、定义一个ExcelHander工具类继承AnalysisEventListener 最重要的是重写invoke方法,去执行读EXCEL逻辑。
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jumi.microservice.sale.entity.vo.PrizeLogImportExcelVO;
import com.jumi.microservice.sale.service.IJmPrizeLogService;
import java.util.ArrayList;
import java.util.List;
public class PrizeLogImportListener extends AnalysisEventListener<PrizeLogImportExcelVO> {
private IJmPrizeLogService prizeLogService;
private List<PrizeLogImportExcelVO> list = new ArrayList<>();
@Override
public void invoke(PrizeLogImportExcelVO data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
prizeLogService.importExcel(list);
}
public PrizeLogImportListener(IJmPrizeLogService prizeLogService) {
this.prizeLogService = prizeLogService;
}
public List<PrizeLogImportExcelVO> getList() {
return list;
}
}
6、调用逻辑
@Resource
private IJmPrizeLogService prizeLogService;
// @PostMapping("/import/excel")
// @ApiOperation("导入excel")
// public ResponseResult<List<String>> importExcel(@RequestParam(value = "multipartFile") MultipartFile request) throws IOException {
// PrizeLogImportListener listener = new PrizeLogImportListener(prizeLogService);
// EasyExcel.read(request.getInputStream(), PrizeLogImportExcelVO.class, listener).sheet().doRead();
// return ResponseResult.success(JmPrizeLogServiceImpl.errorPrizeSns);
// }
@PostMapping("/import/excel")
@ApiOperation("导入excel")
public ResponseResult<List<String>> importExcel(@RequestParam(value = "multipartFile") MultipartFile file) throws IOException {
if (file == null || file.isEmpty() || ObjectUtils.isEmpty(file.getOriginalFilename())) {
throw new BaseException(500, "文件不能为空");
}
String fileName = file.getOriginalFilename();
if (!(fileName.endsWith(".xlsx") || fileName.endsWith(".xls") || fileName.endsWith(".csv"))) {
throw new BaseException(500, "文件类型错误,只支持:xlsx、xls、csv");
}
try {
InputStream inputStream = file.getInputStream();
if (fileName.endsWith(".csv")) {
inputStream = CsvToXlsxUtil.csvStream2xlsxStream(file.getInputStream(), fileName);
}
PrizeLogImportListener listener = new PrizeLogImportListener(prizeLogService);
EasyExcel.read(inputStream, PrizeLogImportExcelVO.class, listener).sheet().doRead();
} catch (ExcelAnalysisException | ExcelCommonException | IOException e) {
System.out.println(e);
throw new BaseException(500, "文件异常,请检查确认");
}
return ResponseResult.success(JmPrizeLogServiceImpl.errorPrizeSns);
}
7、CsvToXlsxUtil工具类
import cn.hutool.core.util.ObjectUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jumpmind.symmetric.csv.CsvReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
/**
* Csv 转 Xlsx 工具类
*/
public class CsvToXlsxUtil {
private static final Logger log = LoggerFactory.getLogger(CsvToXlsxUtil.class);
/**
* CSV常用分隔符,如需动态扩展设置成配置项
*/
private static final char[] DELIMITERS = {
',',
';',
'\001',
' ',
'\t',
'|',
'#',
'&'
};
/**
* 读取CSV文件并写入到XLSX文件中,默认编码
*
* @param csvFileAddress 文件地址
*/
public static String csvToXlsx(String csvFileAddress) {
return csvToXlsx(csvFileAddress, "UTF-8");
}
/**
* @param inputStream 输入流
*/
public static InputStream csv2xlsx(InputStream inputStream, String fileName) {
return csvStream2xlsxStream(inputStream, fileName);
}
/**
* 读取CSV文件并写入到XLSX文件中,指定CSV文件编码
*
* @param csvFileAddress 文件地址
* @param charset 编码
*/
public static String csvToXlsx(String csvFileAddress, String charset) {
String xlsxFileAddress = "";
FileOutputStream fileOutputStream = null;
try {
char delimiter = getDelimiter(csvFileAddress);
//xlsx file address
xlsxFileAddress = csvFileAddress.replace("csv", "xlsx");
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = workBook.createSheet(getSheetName(csvFileAddress));
int rowNum = -1;
CsvReader csvReader = new CsvReader(csvFileAddress, delimiter, Charset.forName(charset));
while (csvReader.readRecord()) {
rowNum++;
XSSFRow currentRow = sheet.createRow(rowNum);
for (int i = 0; i < csvReader.getColumnCount(); i++) {
currentRow.createCell(i).setCellValue(csvReader.get(i));
}
}
fileOutputStream = new FileOutputStream(xlsxFileAddress);
workBook.write(fileOutputStream);
return getFileName(xlsxFileAddress);
} catch (Exception e) {
log.error("CsvToXlsxUtil exception :", e);
} finally {
try {
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
}
}
return getFileName(xlsxFileAddress);
}
/**
* @param inputStream 输入流
*/
public static InputStream csvStream2xlsxStream(InputStream inputStream, String fileName) {
FileOutputStream fileOutputStream = null;
try {
fileName = fileName.replace(".csv", ".xlsx");
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = workBook.createSheet("sheet1");
int rowNum = -1;
CsvReader csvReader = new CsvReader(inputStream, StandardCharsets.UTF_8);
while (csvReader.readRecord()) {
rowNum++;
XSSFRow currentRow = sheet.createRow(rowNum);
for (int i = 0; i < csvReader.getColumnCount(); i++) {
currentRow.createCell(i).setCellValue(csvReader.get(i));
}
}
File file = new File("/" + fileName);
fileOutputStream = new FileOutputStream(file);
workBook.write(fileOutputStream);
InputStream input = new FileInputStream(file);
file.delete();
return input;
} catch (Exception e) {
log.error("CsvToXlsxUtil exception :", e);
} finally {
try {
if (ObjectUtil.isNotNull(fileOutputStream)) {
assert fileOutputStream != null;
fileOutputStream.close();
}
} catch (IOException e) {
log.error("CsvToXlsxUtil close FileOutputStream exception :", e);
}
}
return null;
}
/**
* 设置excel文件的sheet名称
* 获取CSV文件名作为Excel文件的sheet名称
*
* @param path 资源路径
*/
private static String getSheetName(String path) {
try {
String[] file = getFileName(path).split("\\.");
return file[0];
} catch (Exception e) {
log.error("CsvToXlsxUtil get sheet name exception : ", e);
return "Sheet";
}
}
/**
* 根据资源路径切割获取文件名
*
* @param path 资源路径
*/
private static String getFileName(String path) {
String[] paths = path.contains("\\") ? path.split("\\\\") : path.split("/");
return paths[paths.length - 1];
}
/**
* 常用CSV分隔符数组遍历资源第一行,分隔的字段数多的为资源分隔符
* 异常情况下默认用’,‘作为分隔符
*
* @param path 资源路径
*/
private static char getDelimiter(String path) {
BufferedReader br = null;
char delimiter = ',';
try {
br = new BufferedReader(new FileReader(path));
String line = br.readLine();
CsvReader csvReader;
int columCount = 0;
for (char delimiterTest : DELIMITERS) {
csvReader = new CsvReader(getStringStream(line), delimiterTest, StandardCharsets.UTF_8);
if (csvReader.readRecord()) {
int newColumnCount = csvReader.getColumnCount();
if (newColumnCount > columCount) {
columCount = newColumnCount;
delimiter = delimiterTest;
}
}
}
} catch (Exception e) {
log.error("CsvToXlsxUtil get delimiter exception :", e);
} finally {
try {
assert br != null;
br.close();
} catch (IOException e) {
log.error("CsvToXlsxUtil get delimiter close BufferedReader exception :", e);
}
}
return delimiter;
}
/**
* 字符串转输入流
* 把CSV文件第一行数据转成输入流
*
* @param sInputString 字符串
*/
private static InputStream getStringStream(String sInputString) {
if (null != sInputString && !"".equals(sInputString)) {
try {
return new ByteArrayInputStream(sInputString.getBytes());
} catch (Exception e) {
log.error("CsvToXlsxUtil get StringStream exception :", e);
}
}
return null;
}
}