Java中，解析excel文件信息
解决问题：
- 解决：1.同一合并范围生成不同BOX 2.箱数读取异常 3.合并组标记不一致
一、代码
// 缓存 Excel 中的图片（key: 图片ID，value: 图片字节数组）
private static Map<String, byte[]> excelImageCache = new HashMap<>();
/**
* 核心解析入口
*/
public static LogisticsOrderDTO parseLogisticsExcel(MultipartFile file) throws IOException {
validateExcelFile(file);
InputStream inputStream = file.getInputStream();
XSSFWorkbook workbook = null;
try {
workbook = new XSSFWorkbook(inputStream);
XSSFSheet targetSheet = findTargetSheet(workbook, "单票下单");
if (targetSheet == null) {
throw new IllegalArgumentException("Excel中未找到【单票下单】工作表");
}
// 提前缓存所有图片
cacheExcelImages(workbook);
SheetParseResult parseResult = readSheetData(targetSheet);
LogisticsOrderDTO orderDTO = parseOrderMainInfo(parseResult.getSheetData());
List<P0ProductInfoDTO> productList = parseProductInfo(parseResult);
orderDTO.setProductList(productList);
return orderDTO;
} finally {
if (inputStream != null) {
inputStream.close();
}
if (workbook != null) {
workbook.close();
}
}
}
/**
* 缓存 Excel 中的所有图片
*/
private static void cacheExcelImages(Workbook workbook) {
excelImageCache.clear();
if (!(workbook instanceof XSSFWorkbook)) {
return;
}
XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;
for (int i = 0; i < xssfWorkbook.getNumberOfSheets(); i++) {
Sheet sheet = xssfWorkbook.getSheetAt(i);
if (sheet instanceof XSSFSheet) {
XSSFDrawing drawing = ((XSSFSheet) sheet).getDrawingPatriarch();
if (drawing != null) {
for (XSSFShape shape : drawing.getShapes()) {
if (shape instanceof XSSFPicture) {
XSSFPicture picture = (XSSFPicture) shape;
String imageId = extractImageIdFromPartName(picture.getPictureData().getPackagePart().getPartName().toString());
if (imageId != null) {
excelImageCache.put(imageId, picture.getPictureData().getData());
}
}
}
}
}
}
}
/**
* 从图片路径中提取 ID
*/
private static String extractImageIdFromPartName(String partName) {
if (partName.contains("ID_")) {
return partName.substring(partName.indexOf("ID_"), partName.lastIndexOf("."));
}
return partName.substring(partName.lastIndexOf("/") + 1, partName.lastIndexOf("."));
}
/**
* 从 =DISPIMG("ID_XXX") 中提取图片ID
*/
private static String extractImageIdFromDispImg(String formula) {
Pattern pattern = Pattern.compile("\"(ID_[A-F0-9]+)\"");
Matcher matcher = pattern.matcher(formula);
if (matcher.find()) {
return matcher.group(1);
}
return null;
}
/**
* 安全获取单元格值
*/
private static String getSafeValue(Row row, int colIndex) {
Cell cell = row.getCell(colIndex);
if (cell == null) {
return "";
}
cell.setCellType(CellType.STRING);
return cell.getStringCellValue().trim();
}
/**
* 校验Excel文件有效性
*/
private static void validateExcelFile(MultipartFile file) {
if (file == null || file.isEmpty()) {
throw new IllegalArgumentException("Excel文件不能为空");
}
String fileName = file.getOriginalFilename();
if (fileName == null || !fileName.toLowerCase().endsWith(".xlsx")) {
throw new IllegalArgumentException("仅支持.xlsx格式，当前文件：" + fileName);
}
if (file.getSize() > 10 * 1024 * 1024) {
throw new IllegalArgumentException("Excel文件大小不能超过10MB");
}
}
/**
* 查找目标工作表（忽略大小写）
*/
private static XSSFSheet findTargetSheet(XSSFWorkbook workbook, String targetSheetName) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
if (targetSheetName.equalsIgnoreCase(sheet.getSheetName())) {
return sheet;
}
}
return null;
}
/**
* 读取工作表数据（核心修复：按合并范围分组）
*/
private static SheetParseResult readSheetData(XSSFSheet sheet) {
List<List<String>> sheetData = new ArrayList<>();
List<RowMergeGroup> rowMergeGroups = new ArrayList<>();
int boxSeq = 1;
int productStartExcelRow = 18; // Excel第19行（POI索引18）
// ========== 第一步：收集所有产品行的合并行范围 ==========
Map<Integer, Integer> rowToMergeEndMap = new HashMap<>();
for (int excelRowIdx = productStartExcelRow; excelRowIdx < sheet.getPhysicalNumberOfRows(); excelRowIdx++) {
XSSFRow row = sheet.getRow(excelRowIdx);
if (row == null) {
continue;
}
// 仅处理有SKU的行
XSSFCell skuCell = row.getCell(0);
if (skuCell == null || StringUtils.isBlank(getCellValue(skuCell))) {
continue;
}
// 以长列（列1）为基准获取合并结束行
Integer mergeEndRow = getRowMergeEnd(sheet, excelRowIdx, 1);
if (mergeEndRow == null) {
rowToMergeEndMap.put(excelRowIdx, excelRowIdx);
continue;
}
// 验证宽/高/箱数列合并范围一致
boolean widthMatch = mergeEndRow.equals(getRowMergeEnd(sheet, excelRowIdx, 2));
boolean heightMatch = mergeEndRow.equals(getRowMergeEnd(sheet, excelRowIdx, 3));
boolean boxCountMatch = mergeEndRow.equals(getRowMergeEnd(sheet, excelRowIdx, 4));
if (widthMatch && heightMatch && boxCountMatch) {
rowToMergeEndMap.put(excelRowIdx, mergeEndRow);
// 标记合并范围内所有行
for (int i = excelRowIdx + 1; i <= mergeEndRow; i++) {
rowToMergeEndMap.put(i, mergeEndRow);
}
} else {
rowToMergeEndMap.put(excelRowIdx, excelRowIdx);
}
}
// ========== 第二步：按合并范围去重，生成合并组 ==========
Set<String> mergeRangeSet = new HashSet<>();
for (Map.Entry<Integer, Integer> entry : rowToMergeEndMap.entrySet()) {
int startRow = entry.getKey();
int endRow = entry.getValue();
if (startRow != endRow) { // 仅处理多行合并
mergeRangeSet.add(startRow + "_" + endRow);
}
}
// 生成合并组
for (String mergeRange : mergeRangeSet) {
String[] parts = mergeRange.split("_");
int startRow = Integer.parseInt(parts[0]);
int endRow = Integer.parseInt(parts[1]);
// 读取合并范围内第一个有效箱数
Integer boxCount = getFirstValidBoxCount(sheet, startRow, endRow);
rowMergeGroups.add(new RowMergeGroup(startRow, endRow, boxSeq++, boxCount));
System.out.printf("生成合并组：BOX_%d，行范围[%d-%d]，箱数=%d%n", boxSeq - 1, startRow, endRow, boxCount);
}
// ========== 第三步：读取所有行数据 ==========
int dataRowIdx = 0;
Map<Integer, Integer> excelRowToDataRowMap = new HashMap<>(); // Excel行→数据行映射
for (int excelRowIdx = 0; excelRowIdx < sheet.getPhysicalNumberOfRows(); excelRowIdx++) {
XSSFRow row = sheet.getRow(excelRowIdx);
if (row == null) {
continue;
}
List<String> rowData = readRowCells(sheet, row, excelRowIdx);
if (rowData.stream().allMatch(StringUtils::isBlank)) {
continue;
}
sheetData.add(rowData);
excelRowToDataRowMap.put(excelRowIdx, dataRowIdx++);
}
// ========== 第四步：构建合并区域映射 ==========
Map<Integer, MergeRegion> mergeRegionMap = new HashMap<>();
for (RowMergeGroup group : rowMergeGroups) {
Integer startDataRow = excelRowToDataRowMap.get(group.getStartRow());
if (startDataRow == null) {
continue;
}
// 为合并组内所有行绑定同一合并信息
for (int excelRowIdx = group.getStartRow(); excelRowIdx <= group.getEndRow(); excelRowIdx++) {
Integer dataRow = excelRowToDataRowMap.get(excelRowIdx);
if (dataRow == null) {
continue;
}
mergeRegionMap.put(dataRow, new MergeRegion(
group.getStartRow(), group.getEndRow(),
1, 4,
group.getBoxNo(), group.getMergeSku(),
group.getBoxCount(),
startDataRow
));
}
}
return new SheetParseResult(sheetData, mergeRegionMap);
}
/**
* 获取合并范围内第一个有效箱数
*/
private static Integer getFirstValidBoxCount(XSSFSheet sheet, int startRow, int endRow) {
for (int excelRowIdx = startRow; excelRowIdx <= endRow; excelRowIdx++) {
XSSFRow row = sheet.getRow(excelRowIdx);
if (row == null) {
continue;
}
XSSFCell cell = row.getCell(4); // 箱数列=索引4
if (cell == null || cell.getCellType() == CellType.BLANK.getCode()) {
continue;
}
try {
// 处理公式类型
if (cell.getCellType() == CellType.FORMULA.getCode()) {
if (cell.getCachedFormulaResultType() == CellType.NUMERIC.getCode()) {
return (int) cell.getNumericCellValue();
} else if (cell.getCachedFormulaResultType() == CellType.STRING.getCode()) {
String cleanValue = cell.getStringCellValue().trim().replaceAll("[^0-9]", "");
return cleanValue.isEmpty() ? 0 : Integer.parseInt(cleanValue);
}
}
// 处理数字类型
else if (cell.getCellType() == CellType.NUMERIC.getCode()) {
return (int) cell.getNumericCellValue();
}
// 处理字符串类型
else if (cell.getCellType() == CellType.STRING.getCode()) {
String cleanValue = cell.getStringCellValue().trim().replaceAll("[^0-9]", "");
return cleanValue.isEmpty() ? 0 : Integer.parseInt(cleanValue);
}
} catch (Exception e) {
System.out.println("行" + (excelRowIdx + 1) + "箱数解析失败：" + e.getMessage());
continue;
}
}
return 0; // 兜底
}
/**
* 获取单元格所在合并区域的结束行
*/
private static Integer getRowMergeEnd(XSSFSheet sheet, int excelRowIdx, int colIdx) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i);
if (region.isInRange(excelRowIdx, colIdx)) {
return region.getLastRow();
}
}
return null;
}
/**
* 读取一行单元格值（处理合并单元格）
*/
private static List<String> readRowCells(XSSFSheet sheet, XSSFRow row, int excelRowIdx) {
List<String> rowData = new ArrayList<>();
int lastCellNum = row.getLastCellNum();
for (int cellIdx = 0; cellIdx <= lastCellNum; cellIdx++) {
XSSFCell cell = row.getCell(cellIdx);
// 处理合并单元格
CellRangeAddress mergeRegion = getCellMergeRegion(sheet, excelRowIdx, cellIdx);
if (mergeRegion != null) {
XSSFRow mergeRow = sheet.getRow(mergeRegion.getFirstRow());
cell = mergeRow.getCell(mergeRegion.getFirstColumn());
}
rowData.add(getCellValue(cell));
}
return rowData;
}
/**
* 判断单元格是否在合并区域内
*/
private static CellRangeAddress getCellMergeRegion(XSSFSheet sheet, int rowIdx, int cellIdx) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i);
if (region.isInRange(rowIdx, cellIdx)) {
return region;
}
}
return null;
}
/**
* 获取单元格字符串值（兼容所有类型）
*/
private static String getCellValue(XSSFCell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellTypeEnum()) {
case STRING:
return StringUtils.trim(cell.getStringCellValue());
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return DateUtil.getJavaDate(cell.getNumericCellValue())
.toString().replace("T", " ");
} else {
cell.setCellType(CellType.STRING);
return StringUtils.trim(cell.getStringCellValue());
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
try {
String formula = cell.getCellFormula();
if (formula != null && formula.startsWith("DISPIMG")) {
// 返回公式本身，让后续逻辑处理
return "=" + formula;
}
// 对于其他公式，尝试获取计算结果
if (cell.getCachedFormulaResultType() == CellType.STRING.getCode()) {
return StringUtils.trim(cell.getStringCellValue());
} else if (cell.getCachedFormulaResultType() == CellType.NUMERIC.getCode()) {
return String.valueOf((int) cell.getNumericCellValue());
}
return StringUtils.trim(cell.getStringCellValue());
} catch (Exception e) {
return String.valueOf(cell.getNumericCellValue());
}
default:
return "";
}
}
/**
* 解析订单主信息
*/
private static LogisticsOrderDTO parseOrderMainInfo(List<List<String>> sheetData) {
LogisticsOrderDTO orderDTO = new LogisticsOrderDTO();
int maxRow = Math.min(16, sheetData.size());
for (int rowIdx = 0; rowIdx < maxRow; rowIdx++) {
List<String> rowData = sheetData.get(rowIdx);
if (rowData.isEmpty()) {
continue;
}
String key = getSafeValue(rowData, 0);
if (StringUtils.isBlank(key)) {
continue;
}
switch (key) {
case "客户单号：":
orderDTO.setCustomerOrderNo(getSafeValue(rowData, 1));
orderDTO.setLogisticsChannel(getSafeValue(rowData, 7));
break;
case "*仓库代码：":
orderDTO.setWarehouseCode(getSafeValue(rowData, 1));
orderDTO.setTaxIncluded(getSafeValue(rowData, 7));
break;
case "收件人公司":
orderDTO.setRecipientCompany(getSafeValue(rowData, 1));
orderDTO.setCustomsDeclaration(getSafeValue(rowData, 7));
break;
case "收件人姓名":
orderDTO.setRecipientName(getSafeValue(rowData, 1));
orderDTO.setCustomsClearanceMethod(getSafeValue(rowData, 7));
break;
case "*收件人电话":
orderDTO.setRecipientPhone(getSafeValue(rowData, 1));
break;
case "*收件人地址":
orderDTO.setRecipientAddress(getSafeValue(rowData, 1));
orderDTO.setWithElectricity(getSafeValue(rowData, 7));
break;
case "*收件人城市":
orderDTO.setRecipientCity(getSafeValue(rowData, 1));
orderDTO.setWithMagnetism(getSafeValue(rowData, 7));
break;
case "*收件人省份":
orderDTO.setRecipientProvince(getSafeValue(rowData, 1));
orderDTO.setDeclareCurrency(getSafeValue(rowData, 7));
break;
case "*收件人邮编":
orderDTO.setRecipientPostalCode(getSafeValue(rowData, 1));
orderDTO.setEinCbpNumber(getSafeValue(rowData, 7));
break;
case "*Country国家/区域:":
orderDTO.setCountryRegion(getSafeValue(rowData, 1));
orderDTO.setCompanyName(getSafeValue(rowData, 7));
break;
case "*FBA Shipment ID":
orderDTO.setFbaShipmentId(getSafeValue(rowData, 1));
break;
case "*Amazon Reference ID":
orderDTO.setAmazonReferenceId(getSafeValue(rowData, 1));
break;
case "*所在货站":
orderDTO.setShipmentStation(getSafeValue(rowData, 1));
break;
default:
break;
}
}
validateOrderRequired(orderDTO);
return orderDTO;
}
/**
* 解析产品信息（核心修复：合并组值统一）
*/
private static List<P0ProductInfoDTO> parseProductInfo(SheetParseResult parseResult) {
List<List<String>> sheetData = parseResult.getSheetData();
Map<Integer, MergeRegion> mergeRegionMap = parseResult.getMergeRegionMap();
List<P0ProductInfoDTO> productList = new ArrayList<>();
int productStartDataRow = 18; // 产品从数据行18开始
if (sheetData.size() <= productStartDataRow) {
throw new IllegalArgumentException("Excel中无产品数据（需从第19行开始）");
}
for (int dataRowIdx = productStartDataRow; dataRowIdx < sheetData.size(); dataRowIdx++) {
List<String> rowData = sheetData.get(dataRowIdx);
if (rowData.isEmpty()) {
continue;
}
String sku = getSafeValue(rowData, 0);
if (StringUtils.isBlank(sku)) {
continue;
}
P0ProductInfoDTO product = new P0ProductInfoDTO();
product.setSku(sku);
MergeRegion mergeRegion = mergeRegionMap.get(dataRowIdx);
boolean isBoxMerge = mergeRegion != null;
// 合并组处理：统一BOX编号和箱数
if (isBoxMerge) {
product.setLengthCm(parseBigDecimal(getSafeValue(rowData, 1), "长度"));
product.setWidthCm(parseBigDecimal(getSafeValue(rowData, 2), "宽度"));
product.setHeightCm(parseBigDecimal(getSafeValue(rowData, 3), "高度"));
product.setBoxCount(mergeRegion.getBoxCount()); // 取合并组统一箱数
product.setBoxNo(mergeRegion.getBoxNo());
product.setIsMerge(1);
product.setMergeSku(mergeRegion.getMergeSku());
System.out.printf("产品SKU[%s]：合并标记=1，箱号=%s，箱数=%d%n",
sku, mergeRegion.getBoxNo(), mergeRegion.getBoxCount());
} else {
// 非合并组处理
product.setLengthCm(parseBigDecimal(getSafeValue(rowData, 1), "长度"));
product.setWidthCm(parseBigDecimal(getSafeValue(rowData, 2), "宽度"));
product.setHeightCm(parseBigDecimal(getSafeValue(rowData, 3), "高度"));
product.setBoxCount(parseInteger(getSafeValue(rowData, 4), "箱数"));
product.setBoxNo(sku);
product.setIsMerge(0);
product.setMergeSku(sku);
System.out.printf("产品SKU[%s]：合并标记=0，箱号=%s%n", sku, sku);
}
// 非合并字段解析
product.setSingleGrossWeightKgs(parseBigDecimal(getSafeValue(rowData, 5), "单件毛重"));
product.setDeclareQuantity(parseInteger(getSafeValue(rowData, 6), "申报数量"));
product.setUnitPrice(parseBigDecimal(getSafeValue(rowData, 7), "单价"));
product.setCustomsCode(getSafeValue(rowData, 8));
product.setChineseProductName(getSafeValue(rowData, 9));
product.setEnglishProductName(getSafeValue(rowData, 10));
product.setProductMaterial(getSafeValue(rowData, 11));
product.setProductUsage(getSafeValue(rowData, 12));
product.setBrand(getSafeValue(rowData, 13));
product.setSpecificationModel(getSafeValue(rowData, 14));
// 图片处理部分（修改的地方）
String imageValue = getSafeValue(rowData, 15);
if (StringUtils.isNotBlank(imageValue)) {
if (imageValue.startsWith("http")) {
product.setProductImage(imageValue);
} else if (imageValue.startsWith("=DISPIMG")) {
String imageId = extractImageIdFromDispImg(imageValue);
byte[] imageBytes = excelImageCache.get(imageId);
if (imageBytes != null) {
try {
ShipmentHelper shipmentHelper = new ShipmentHelper();
MultipartFile imageFile = new CustomMultipartFile(imageId + ".png", imageId + ".png", "image/png", imageBytes);
Map<String, Object> uploadResult = shipmentHelper.uploadFile(imageFile);
product.setProductImage((String) uploadResult.get("url"));
} catch (Exception e) {
System.err.println("图片上传失败: " + imageId + ", 错误: " + e.getMessage());
product.setProductImage(null);
}
} else {
System.err.println("未找到ID为 " + imageId + " 的图片数据");
product.setProductImage(null);
}
} else {
try {
ShipmentHelper shipmentHelper = new ShipmentHelper();
Map<String, Object> uploadResult = shipmentHelper.uploadFile(imageValue);
product.setProductImage((String) uploadResult.get("url"));
} catch (Exception e) {
System.err.println("图片上传失败: " + imageValue + ", 错误: " + e.getMessage());
product.setProductImage(null);
}
}
} else {
product.setProductImage(null);
}
validateProductRequired(product, sku);
productList.add(product);
}
if (productList.isEmpty()) {
throw new IllegalArgumentException("Excel中未解析到有效产品数据");
}
return productList;
}
// ========== 工具方法 ==========
private static String getSafeValue(List<String> rowData, int cellIdx) {
if (rowData == null || cellIdx < 0 || cellIdx >= rowData.size()) {
return "";
}
return StringUtils.trim(rowData.get(cellIdx));
}
private static BigDecimal parseBigDecimal(String value, String fieldName) {
if (StringUtils.isBlank(value)) {
throw new IllegalArgumentException(fieldName + "不能为空");
}
try {
return new BigDecimal(value);
} catch (NumberFormatException e) {
throw new IllegalArgumentException(fieldName + "格式错误：" + value);
}
}
private static Integer parseInteger(String value, String fieldName) {
if (StringUtils.isBlank(value)) {
throw new IllegalArgumentException(fieldName + "不能为空");
}
try {
return Integer.parseInt(value);
} catch (NumberFormatException e) {
throw new IllegalArgumentException(fieldName + "格式错误：" + value);
}
}
private static void validateOrderRequired(LogisticsOrderDTO order) {
List<String> missing = new ArrayList<>();
if (StringUtils.isBlank(order.getLogisticsChannel())) {
missing.add("物流渠道");
}
if (StringUtils.isBlank(order.getWarehouseCode())) {
missing.add("仓库代码");
}
// 海运下单/查价下单 如果是 其他邮编下单，需要校验这些东西，如果是 Amazon与Walmart下单，不需要校验
boolean warehouseCodeResult = ShipmentHelper.isPostalCode(order.getWarehouseCode());
if (warehouseCodeResult) {
if (StringUtils.isBlank(order.getRecipientCompany())) {
missing.add("收件人公司");
}
if (StringUtils.isBlank(order.getRecipientName())) {
missing.add("收件人姓名");
}
if (StringUtils.isBlank(order.getRecipientAddress())) {
missing.add("收件人地址");
}
if (StringUtils.isBlank(order.getRecipientCity())) {
missing.add("收件人城市");
}
if (StringUtils.isBlank(order.getRecipientProvince())) {
missing.add("收件人省份");
}
if (StringUtils.isBlank(order.getRecipientPostalCode())) {
missing.add("收件人邮编");
}
if (StringUtils.isBlank(order.getCountryRegion())) {
missing.add("Country国家/区域");
}
if (StringUtils.isBlank(order.getRecipientPhone())) {
missing.add("收件人电话");
}
}
if (!missing.isEmpty()) {
throw new IllegalArgumentException("订单缺失必填字段：" + String.join("、", missing));
}
}
private static void validateProductRequired(P0ProductInfoDTO product, String sku) {
List<String> missing = new ArrayList<>();
if (product.getLengthCm() == null) {
missing.add("长度");
}
if (product.getWidthCm() == null) {
missing.add("宽度");
}
if (product.getHeightCm() == null) {
missing.add("高度");
}
if (product.getBoxCount() == null) {
missing.add("箱数");
}
if (product.getSingleGrossWeightKgs() == null) {
missing.add("单件毛重");
}
if (product.getDeclareQuantity() == null) {
missing.add("申报数量");
}
if (product.getUnitPrice() == null) {
missing.add("单价");
}
if (StringUtils.isBlank(product.getCustomsCode())) {
missing.add("海关编码");
}
if (StringUtils.isBlank(product.getChineseProductName())) {
missing.add("中文品名");
}
if (StringUtils.isBlank(product.getEnglishProductName())) {
missing.add("英文品名");
}
if (StringUtils.isBlank(product.getProductMaterial())) {
missing.add("产品材质");
}
if (StringUtils.isBlank(product.getProductUsage())) {
missing.add("产品用途");
}
if (StringUtils.isBlank(product.getBrand())) {
missing.add("品牌");
}
if (StringUtils.isBlank(product.getSpecificationModel())) {
missing.add("规格/型号");
}
if (!missing.isEmpty()) {
throw new IllegalArgumentException("SKU【" + sku + "】缺失字段：" + String.join("、", missing));
}
}
// ========== 内部封装类 ==========
/**
* 行合并组：标记多行共享的箱组信息
*/
private static class RowMergeGroup {
private final int startRow;
private final int endRow;
private final String boxNo;
private final String mergeSku;
private final Integer boxCount;
public RowMergeGroup(int startRow, int endRow, int boxSeq, Integer boxCount) {
this.startRow = startRow;
this.endRow = endRow;
this.boxNo = "BOX_" + boxSeq;
this.mergeSku = "MERGE_" + this.boxNo;
this.boxCount = boxCount;
}
public int getStartRow() {
return startRow;
}
public int getEndRow() {
return endRow;
}
public String getBoxNo() {
return boxNo;
}
public String getMergeSku() {
return mergeSku;
}
public Integer getBoxCount() {
return boxCount;
}
}
/**
* 合并区域信息
*/
private static class MergeRegion {
private final int firstExcelRow;
private final int lastExcelRow;
private final int firstCol;
private final int lastCol;
private final String boxNo;
private final String mergeSku;
private final Integer boxCount;
private final int firstDataRow;
public MergeRegion(int firstExcelRow, int lastExcelRow, int firstCol, int lastCol,
String boxNo, String mergeSku, Integer boxCount, int firstDataRow) {
this.firstExcelRow = firstExcelRow;
this.lastExcelRow = lastExcelRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
this.boxNo = boxNo;
this.mergeSku = mergeSku;
this.boxCount = boxCount;
this.firstDataRow = firstDataRow;
}
public int getFirstExcelRow() {
return firstExcelRow;
}
public int getLastExcelRow() {
return lastExcelRow;
}
public String getBoxNo() {
return boxNo;
}
public String getMergeSku() {
return mergeSku;
}
public Integer getBoxCount() {
return boxCount;
}
}
/**
* 工作表解析结果
*/
private static class SheetParseResult {
private final List<List<String>> sheetData;
private final Map<Integer, MergeRegion> mergeRegionMap;
public SheetParseResult(List<List<String>> sheetData, Map<Integer, MergeRegion> mergeRegionMap) {
this.sheetData = sheetData;
this.mergeRegionMap = mergeRegionMap;
}
public List<List<String>> getSheetData() {
return sheetData;
}
public Map<Integer, MergeRegion> getMergeRegionMap() {
return mergeRegionMap;
}
}
二、引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
三、代码说明
import com.ship.system.domain.p0Shipment.dto.LogisticsOrderDTO;
import com.ship.system.domain.p0Shipment.dto.P0ProductInfoDTO;
import com.ship.system.service.impl.p0Shipment.helper.CustomMultipartFile;
import com.ship.system.service.impl.p0Shipment.helper.ShipmentHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
这上面的是引入的依赖。
XSSFWorkbook 这个可以解析对应的excel的sheet页文件。
四、对应图片
- excel模板图片
![image]()
浙公网安备 33010602011771号