package com.doumee.core.annotation.excel;
|
|
import com.doumee.dao.business.model.GoodsImportAsync;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.function.Consumer;
|
|
/**
|
* 商品异步导入：按表头名称解析列，避免列顺序变更导致字段错位。
|
*/
|
public class AsyncImportExcelSupport {
|
|
private static final int DEFAULT_COL_PARAM = 6;
|
private static final int DEFAULT_COL_SUB_CATEGORY = 7;
|
private static final int DEFAULT_COL_MAIN_IMAGE = 8;
|
private static final int DEFAULT_COL_MULTI_IMAGE = 9;
|
|
private AsyncImportExcelSupport() {
|
}
|
|
public static ColumnMapping readAndApplyTextFields(String filePath, int sheetIndex, int headerRowIndex,
|
List<GoodsImportAsync> dataList) throws IOException {
|
try (InputStream in = new FileInputStream(filePath)) {
|
return readAndApplyTextFields(in, sheetIndex, headerRowIndex, dataList);
|
}
|
}
|
|
public static ColumnMapping readAndApplyTextFields(InputStream in, int sheetIndex, int headerRowIndex,
|
List<GoodsImportAsync> dataList) throws IOException {
|
try (Workbook workbook = WorkbookFactory.create(in)) {
|
Sheet sheet = workbook.getSheetAt(sheetIndex);
|
Row headerRow = sheet.getRow(headerRowIndex);
|
Map<String, Integer> headerCols = buildHeaderColumnMap(headerRow);
|
ColumnMapping mapping = new ColumnMapping();
|
mapping.paramCol = resolveCol(headerCols, DEFAULT_COL_PARAM, "产品参数");
|
mapping.subCategoryCol = resolveCol(headerCols, DEFAULT_COL_SUB_CATEGORY, "商品二级类别");
|
mapping.mainImageCol = resolveCol(headerCols, DEFAULT_COL_MAIN_IMAGE, "商品主图");
|
mapping.multiImageCol = resolveCol(headerCols, DEFAULT_COL_MULTI_IMAGE, "商品多图");
|
applyTextFields(sheet, headerRowIndex, headerCols, mapping, dataList);
|
return mapping;
|
}
|
}
|
|
public static ColumnMapping readColumnMapping(String filePath, int sheetIndex, int headerRowIndex) throws IOException {
|
try (InputStream in = new FileInputStream(filePath)) {
|
return readColumnMapping(in, sheetIndex, headerRowIndex);
|
}
|
}
|
|
public static ColumnMapping readColumnMapping(InputStream in, int sheetIndex, int headerRowIndex) throws IOException {
|
try (Workbook workbook = WorkbookFactory.create(in)) {
|
Sheet sheet = workbook.getSheetAt(sheetIndex);
|
Row headerRow = sheet.getRow(headerRowIndex);
|
Map<String, Integer> headerCols = buildHeaderColumnMap(headerRow);
|
ColumnMapping mapping = new ColumnMapping();
|
mapping.paramCol = resolveCol(headerCols, DEFAULT_COL_PARAM, "产品参数");
|
mapping.subCategoryCol = resolveCol(headerCols, DEFAULT_COL_SUB_CATEGORY, "商品二级类别");
|
mapping.mainImageCol = resolveCol(headerCols, DEFAULT_COL_MAIN_IMAGE, "商品主图");
|
mapping.multiImageCol = resolveCol(headerCols, DEFAULT_COL_MULTI_IMAGE, "商品多图");
|
return mapping;
|
}
|
}
|
|
private static void applyTextFields(Sheet sheet, int headerRowIndex, Map<String, Integer> headerCols,
|
ColumnMapping mapping, List<GoodsImportAsync> dataList) {
|
if (sheet == null || dataList == null || dataList.isEmpty()) {
|
return;
|
}
|
DataFormatter formatter = new DataFormatter();
|
int dataStartRow = headerRowIndex + 1;
|
for (int i = 0; i < dataList.size(); i++) {
|
Row row = sheet.getRow(dataStartRow + i);
|
if (row == null) {
|
continue;
|
}
|
GoodsImportAsync item = dataList.get(i);
|
setIfPresent(headerCols, row, formatter, "商品文件夹名称", item::setId);
|
setIfPresent(headerCols, row, formatter, "商品名称", item::setName);
|
setIfPresent(headerCols, row, formatter, "商品类别", item::setCategory);
|
setIfPresent(headerCols, row, formatter, "商品品牌", item::setBrand);
|
setIfPresent(headerCols, row, formatter, "指导价", item::setZdPrice);
|
setIfPresent(headerCols, row, formatter, "入手价", item::setPrice);
|
if (mapping.paramCol != null) {
|
item.setParamStr(readCellString(row, mapping.paramCol, formatter));
|
}
|
if (mapping.subCategoryCol != null) {
|
item.setSubCategory(readCellString(row, mapping.subCategoryCol, formatter));
|
}
|
}
|
}
|
|
private static void setIfPresent(Map<String, Integer> headerCols, Row row, DataFormatter formatter,
|
String header, Consumer<String> setter) {
|
Integer col = headerCols.get(header);
|
if (col != null) {
|
setter.accept(readCellString(row, col, formatter));
|
}
|
}
|
|
private static Map<String, Integer> buildHeaderColumnMap(Row headerRow) {
|
Map<String, Integer> map = new HashMap<>();
|
if (headerRow == null) {
|
return map;
|
}
|
DataFormatter formatter = new DataFormatter();
|
short lastCell = headerRow.getLastCellNum();
|
for (int c = 0; c < lastCell; c++) {
|
Cell cell = headerRow.getCell(c);
|
if (cell == null) {
|
continue;
|
}
|
String header = normalizeHeader(formatter.formatCellValue(cell));
|
if (StringUtils.isNotBlank(header)) {
|
map.put(header, c);
|
}
|
}
|
return map;
|
}
|
|
private static Integer resolveCol(Map<String, Integer> headerCols, int defaultCol, String headerName) {
|
Integer col = headerCols.get(headerName);
|
return col != null ? col : defaultCol;
|
}
|
|
private static String normalizeHeader(String header) {
|
if (header == null) {
|
return "";
|
}
|
return header.trim().replace('\u00A0', ' ');
|
}
|
|
static String readCellString(Row row, int col, DataFormatter formatter) {
|
if (row == null || col < 0) {
|
return "";
|
}
|
Cell cell = row.getCell(col);
|
if (cell == null) {
|
return "";
|
}
|
String value = formatter.formatCellValue(cell);
|
if (value == null) {
|
return "";
|
}
|
value = value.trim();
|
if (StringUtils.endsWith(value, ".0") && value.matches("^\\d+\\.0$")) {
|
value = StringUtils.substringBefore(value, ".0");
|
}
|
return value;
|
}
|
|
public static class ColumnMapping {
|
private Integer paramCol;
|
private Integer subCategoryCol;
|
private Integer mainImageCol;
|
private Integer multiImageCol;
|
|
public int getMainImageCol() {
|
return mainImageCol != null ? mainImageCol : DEFAULT_COL_MAIN_IMAGE;
|
}
|
|
public int getMultiImageCol() {
|
return multiImageCol != null ? multiImageCol : DEFAULT_COL_MULTI_IMAGE;
|
}
|
}
|
}
|
