批量发货模板导出导入（excel）
在电商系统中，客户需求：订单可以进行批量发货，之前是单独发货的功能
批量发货：要求：先导出订单的模板（excel）,在模板中填写订单对应的物流公司和物流单号，最后再把填好的模板导入，达到批量发货的目的
一、相关依赖
1.导出依赖：
<!--excel poi导出相关依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2.0</version>
</dependency>
2.导入依赖：
<!--.apache.poi excel导入相关依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
二、导出
1.excel导出工具类--ExcelReportUtils
package com.astronaut.auction.common.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
/**
* excel导出工具类
*/
public class ExcelReportUtils {
/**
* Excel导出
*
* @param response response
* @param fileName 文件名
* @param list 数据List
* @param pojoClass 对象Class
*/
public static void exportExcel(HttpServletResponse response, String fileName, String title, Collection<?> list,
Class<?> pojoClass) throws IOException {
if(StringUtils.isBlank(fileName)){
//当前日期
fileName = DateUtils.format(new Date());
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, "Sheet0"), pojoClass, list);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
}
/**
* Excel导出，先sourceList转换成List<targetClass>，再导出
*
* @param response response
* @param fileName 文件名
* @param title 标题
* @param sourceList 原数据List
* @param targetClass 目标对象Class
*/
public static void exportExcelToTarget(HttpServletResponse response, String fileName, String title, Collection<?> sourceList,
Class<?> targetClass) throws Exception {
List targetList = new ArrayList<>(sourceList.size());
for(Object source : sourceList){
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
exportExcel(response, fileName, title, targetList, targetClass);
}
}
2.导出模板--TemplateExcel
package com.astronaut.auction.modules.order.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
/**
* @Classname ExcelTemplateVO
* @Description: 批量发货模板excel
* @Date: 2023-06-30 16:01
* @AUTHOR: 无泪之城
* @Version 1.0
*/
@Data
public class TemplateExcel {
@Excel(name = "订单号" , width = 15)
private String code;
@Excel(name = "物流公司名称" , width = 15)
private String name;
@Excel(name = "物流单号" , width = 15)
private String expressCode;
}
3.导出用到的VO--ExcelTemplateVO
package com.astronaut.auction.modules.order.vo;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* @Classname ExcelTemplateVO
* @Description: 批量发货模板数据
* @Date: 2023-06-30 16:01
* @AUTHOR: 无泪之城
* @Version 1.0
*/
@Data
public class ExcelTemplateVO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "订单号")
private String code;
@ApiModelProperty(value = "物流公司名称")
private String name;
@ApiModelProperty(value = "物流单号")
private String expressCode;
}
4、导出接口
@PostMapping("/export")
@ApiOperation("【批量发货模板-导出】-【新增功能2.23.7.1】")
public void export(@RequestBody List<String> ids, HttpServletResponse response) throws Exception{
String fileName ="批量发货订单信息" + DateUtils.format(new Date());
String title ="批量发货订单信息";
List<ExcelTemplateVO> list = new ArrayList<>();
if (ids!=null){
for (String id:ids) {
ExcelTemplateVO excelTemplateVO = new ExcelTemplateVO();
excelTemplateVO.setCode(id);
excelTemplateVO.setName("");
excelTemplateVO.setExpressCode("");
list.add(excelTemplateVO);
}
}
ExcelReportUtils.exportExcelToTarget(response,fileName,title,list, TemplateExcel.class);
}
三、导入（批量发货）
1.导入工具类：ReadPatientExcelUtil
package com.astronaut.auction.common.utils.ExcelUtils;
import com.astronaut.auction.modules.order.vo.ExcelTemplateVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @Classname ReadPatientExcelUtils
* @Description:数据导入解析工具类
* @Date: 2023-07-01 10:20
* @AUTHOR: 无泪之城
* @Version 1.0
*/
@Slf4j
public class ReadPatientExcelUtil {
//总行数
private static int totalRows = 0;
//总条数
private static int totalCells = 0;
//错误信息接收器
private static String errorMsg;
/**
* 读EXCEL文件，获取信息集合
* @return
*/
//
public static AllRecordDTO getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();//获取文件名
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
AllRecordDTO dto = createExcel(mFile.getInputStream(), isExcel2003);
return dto;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据excel里面的内容读取客户信息
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public static AllRecordDTO createExcel(InputStream is, boolean isExcel2003) {
try{
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
AllRecordDTO dto = readExcelValue(wb);// 读取Excel里面客户的信息
return dto;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读取Excel里面客户的信息
* @param wb
* @return
*/
private static AllRecordDTO readExcelValue(Workbook wb) {
List<ExcelTemplateVO> voList=new ArrayList<>();
AllRecordDTO dto=new AllRecordDTO();
//【解析sheet1-档案资料】###########################################################################################
//默认会跳过第一行标题
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
totalRows = sheet.getPhysicalNumberOfRows();
System.out.println("资料行数"+totalRows);
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
System.out.println("资料列数"+totalCells);
// 循环Excel行数 从第三行开始读取【第一行：标题，第二行：列名】
for (int r = 2; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
ExcelTemplateVO excelTemplateVO = new ExcelTemplateVO();
// 循环Excel的列
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) { //第一列
//如果是纯数字,将单元格类型转为String
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
excelTemplateVO.setCode(cell.getStringCellValue());//将单元格数据赋值给user
}
else if (c == 1){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
excelTemplateVO.setName(cell.getStringCellValue());
}
else if (c == 2){
if(cell.getCellTypeEnum() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}
excelTemplateVO.setExpressCode(cell.getStringCellValue());
}
}
}
voList.add(excelTemplateVO);
dto.setVoList(voList);
}
// log.info("解析结果：{}",dto);
return dto;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
2.导入用到的DTO--AllRecordDTO
package com.astronaut.auction.common.utils.ExcelUtils;
import com.astronaut.auction.modules.order.vo.ExcelTemplateVO;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
* @Classname AllRecordDTO
* @Description:把解析excel的数据放在这里
* @Date: 2023-07-01 10:24
* @AUTHOR: 无泪之城
* @Version 1.0
*/
@Data
public class AllRecordDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "模板数据list")
private List<ExcelTemplateVO> voList;
}
3.导入接口
@PostMapping("/delivery")
@ApiOperation("【批量发货】-【新增功能2.23.7.1】")
public Result<String> delivery(@RequestBody MultipartFile mFile){
if (mFile.isEmpty()){
throw new RenException("请上传有效excel文件！");
}
String msg=activityOrderService.delivery(mFile);
return new Result<String>().ok(msg);
}
4.实现类：
@Override
@Transactional
public String delivery(MultipartFile mFile) {
AllRecordDTO recordDTO = ReadPatientExcelUtil.getExcelInfo(mFile);
if (recordDTO.getVoList()!=null && !recordDTO.getVoList().isEmpty()){
List<ExcelTemplateVO> voList=recordDTO.getVoList();
log.info("数据为：{}",voList);
//遍历集合，修改订单状态为2待收货【lkt_order、lkt_order_details】、 查询快递公司id+物流单号放在订单详情表中【lkt_order_details】
for (ExcelTemplateVO vo:voList) {
//修改订单状态为2待收货【lkt_order】
activityOrderDao.updateOrderStatus(vo.getCode());
//修改订单状态为2待收货、快递公司id+物流单号放在订单详情表中【lkt_order_details】
Integer express_id=activityOrderDao.getKdId(vo.getName());
if (express_id!=null){
activityOrderDao.updateOrderDetail(express_id,vo.getExpressCode(),vo.getCode());
}
}
}
return "批量发货完成！";
}
本文来自博客园，作者：青喺半掩眉砂，转载请注明原文链接：https://www.cnblogs.com/xiaoguo-java/p/17546779.html
