From c7cf13da4cd96a76771475b8e4c364dfddebf712 Mon Sep 17 00:00:00 2001
From: liyong <18434998025@163.com>
Date: 星期五, 23 一月 2026 11:09:33 +0800
Subject: [PATCH] feat(sales): 库存
---
src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 263 +++++++++++++++++++++++++++++++++++++++++++---------
1 files changed, 215 insertions(+), 48 deletions(-)
diff --git a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
index 427dc0d..cdab562 100644
--- a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -95,6 +95,8 @@
{
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
+ public static final String SEPARATOR = ",";
+
public static final String FORMULA_REGEX_STR = "=|-|\\+|@";
public static final String[] FORMULA_STR = { "=", "-", "+", "@" };
@@ -311,10 +313,22 @@
*/
public List<T> importExcel(InputStream is)
{
+ return importExcel(is, 0);
+ }
+
+ /**
+ * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist
+ *
+ * @param is 杈撳叆娴�
+ * @param titleNum 鏍囬鍗犵敤琛屾暟
+ * @return 杞崲鍚庨泦鍚�
+ */
+ public List<T> importExcel(InputStream is, int titleNum)
+ {
List<T> list = null;
try
{
- list = importExcel(is, 0);
+ list = importExcel(StringUtils.EMPTY, is, titleNum);
}
catch (Exception e)
{
@@ -326,18 +340,6 @@
IOUtils.closeQuietly(is);
}
return list;
- }
-
- /**
- * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist
- *
- * @param is 杈撳叆娴�
- * @param titleNum 鏍囬鍗犵敤琛屾暟
- * @return 杞崲鍚庨泦鍚�
- */
- public List<T> importExcel(InputStream is, int titleNum) throws Exception
- {
- return importExcel(StringUtils.EMPTY, is, titleNum);
}
/**
@@ -360,7 +362,7 @@
throw new IOException("鏂囦欢sheet涓嶅瓨鍦�");
}
boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
- Map<String, PictureData> pictures;
+ Map<String, List<PictureData>> pictures = null;
if (isXSSFWorkbook)
{
pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
@@ -426,7 +428,7 @@
if (String.class == fieldType)
{
String s = Convert.toStr(val);
- if (StringUtils.endsWith(s, ".0"))
+ if (s.matches("^\\d+\\.0$"))
{
val = StringUtils.substringBefore(s, ".0");
}
@@ -504,16 +506,185 @@
}
else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
{
- PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
- if (image == null)
+ StringBuilder propertyString = new StringBuilder();
+ List<PictureData> images = pictures.get(row.getRowNum() + "_" + entry.getKey());
+ for (PictureData picture : images)
{
- val = "";
+ byte[] data = picture.getData();
+ String fileName = FileUtils.writeImportBytes(data);
+ propertyString.append(fileName).append(SEPARATOR);
}
- else
- {
- byte[] data = image.getData();
- val = FileUtils.writeImportBytes(data);
+ val = StringUtils.stripEnd(propertyString.toString(), SEPARATOR);
+ }
+ ReflectUtils.invokeSetter(entity, propertyName, val);
+ }
+ }
+ list.add(entity);
+ }
+ }
+ return list;
+ }
+
+ /**
+ * 鎵╁睍锛氳鍙朎xcel涓涓寚瀹歋heet鐨勬暟鎹�
+ * @param sheetNameList 瑕佽鍙栫殑Sheet鍚嶇О鍒楄〃锛坣ull鍒欒鍙栨墍鏈塖heet锛�
+ * @param is 杈撳叆娴�
+ * @param titleNum 鏍囬鍗犵敤琛屾暟
+ * @return Map<Sheet鍚嶇О, 瀵瑰簲Sheet鐨勬暟鎹垪琛�>
+ */
+ public Map<String, List<T>> importExcelMultiSheet(List<String> sheetNameList, InputStream is, int titleNum) {
+ Map<String, List<T>> resultMap = new HashMap<>();
+ try {
+ this.type = Type.IMPORT;
+ this.wb = WorkbookFactory.create(is);
+
+ // 1. 纭畾瑕佽鍙栫殑Sheet鍒楄〃
+ List<Sheet> sheetsToRead = new ArrayList<>();
+ if (sheetNameList != null && !sheetNameList.isEmpty()) {
+ // 璇诲彇鎸囧畾鍚嶇О鐨凷heet
+ for (String sheetName : sheetNameList) {
+ Sheet sheet = wb.getSheet(sheetName);
+ if (sheet != null) {
+ sheetsToRead.add(sheet);
+ } else {
+ log.warn("鎸囧畾鐨凷heet鍚嶇О涓嶅瓨鍦細{}", sheetName);
+ }
+ }
+ } else {
+ // 璇诲彇鎵�鏈塖heet
+ int sheetCount = wb.getNumberOfSheets();
+ for (int i = 0; i < sheetCount; i++) {
+ sheetsToRead.add(wb.getSheetAt(i));
+ }
+ }
+
+ // 2. 閬嶅巻姣忎釜Sheet锛屽鐢ㄥ師鏈夊鍏ラ�昏緫
+ for (Sheet sheet : sheetsToRead) {
+ String sheetName = wb.getSheetName(wb.getSheetIndex(sheet));
+ // 澶嶇敤鍘熸湁鏍稿績瀵煎叆閫昏緫锛堝叧閿細灏嗗師鏈夋柟娉曟媶鍒嗕负鍙鐢ㄧ殑鍐呴儴鏂规硶锛�
+ List<T> sheetData = importExcelBySheet(sheet, titleNum);
+ resultMap.put(sheetName, sheetData);
+ }
+ } catch (Exception e) {
+ log.error("瀵煎叆澶歋heet Excel寮傚父{}", e.getMessage());
+ throw new UtilException(e.getMessage());
+ } finally {
+ IOUtils.closeQuietly(is);
+ }
+ return resultMap;
+ }
+
+ /**
+ * 鍐呴儴澶嶇敤鏂规硶锛氭牴鎹寚瀹歋heet瀵硅薄璇诲彇鏁版嵁锛堟彁鍙栧師鏈塱mportExcel鐨勬牳蹇冮�昏緫锛�
+ */
+ private List<T> importExcelBySheet(Sheet sheet, int titleNum) throws Exception {
+ List<T> list = new ArrayList<T>();
+ if (sheet == null) {
+ return list;
+ }
+
+ boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
+ Map<String, List<PictureData>> pictures = null;
+ if (isXSSFWorkbook) {
+ pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
+ } else {
+ pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
+ }
+
+ int rows = sheet.getLastRowNum();
+ if (rows > 0) {
+ Map<String, Integer> cellMap = new HashMap<String, Integer>();
+ Row heard = sheet.getRow(titleNum);
+ for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
+ Cell cell = heard.getCell(i);
+ if (StringUtils.isNotNull(cell)) {
+ String value = this.getCellValue(heard, i).toString();
+ cellMap.put(value, i);
+ } else {
+ cellMap.put(null, i);
+ }
+ }
+
+ List<Object[]> fields = this.getFields();
+ Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
+ for (Object[] objects : fields) {
+ Excel attr = (Excel) objects[1];
+ Integer column = cellMap.get(attr.name());
+ if (column != null) {
+ fieldsMap.put(column, objects);
+ }
+ }
+
+ for (int i = titleNum + 1; i <= rows; i++) {
+ Row row = sheet.getRow(i);
+ if (isRowEmpty(row)) {
+ continue;
+ }
+ T entity = null;
+ for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {
+ Object val = this.getCellValue(row, entry.getKey());
+ entity = (entity == null ? clazz.newInstance() : entity);
+ Field field = (Field) entry.getValue()[0];
+ Excel attr = (Excel) entry.getValue()[1];
+ Class<?> fieldType = field.getType();
+
+ // 浠ヤ笅鏄師鏈夋暟鎹被鍨嬭浆鎹€�佸瓧鍏歌В鏋愮瓑閫昏緫锛堝畬鍏ㄥ鐢級
+ if (String.class == fieldType) {
+ String s = Convert.toStr(val);
+ if (s.matches("^\\d+\\.0$")) {
+ val = StringUtils.substringBefore(s, ".0");
+ } else {
+ String dateFormat = field.getAnnotation(Excel.class).dateFormat();
+ if (StringUtils.isNotEmpty(dateFormat)) {
+ val = parseDateToStr(dateFormat, val);
+ } else {
+ val = Convert.toStr(val);
}
+ }
+ } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
+ val = Convert.toInt(val);
+ } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
+ val = Convert.toLong(val);
+ } else if (Double.TYPE == fieldType || Double.class == fieldType) {
+ val = Convert.toDouble(val);
+ } else if (Float.TYPE == fieldType || Float.class == fieldType) {
+ val = Convert.toFloat(val);
+ } else if (BigDecimal.class == fieldType) {
+ val = Convert.toBigDecimal(val);
+ } else if (Date.class == fieldType) {
+ if (val instanceof String) {
+ val = DateUtils.parseDate(val);
+ } else if (val instanceof Double) {
+ val = DateUtil.getJavaDate((Double) val);
+ }
+ } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
+ val = Convert.toBool(val, false);
+ }
+
+ if (StringUtils.isNotNull(fieldType)) {
+ String propertyName = field.getName();
+ if (StringUtils.isNotEmpty(attr.targetAttr())) {
+ propertyName = field.getName() + "." + attr.targetAttr();
+ }
+ if (StringUtils.isNotEmpty(attr.readConverterExp())) {
+ val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
+ } else if (StringUtils.isNotEmpty(attr.dictType())) {
+ if (!sysDictMap.containsKey(attr.dictType() + val)) {
+ String dictValue = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
+ sysDictMap.put(attr.dictType() + val, dictValue);
+ }
+ val = sysDictMap.get(attr.dictType() + val);
+ } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
+ val = dataFormatHandlerAdapter(val, attr, null);
+ } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures)) {
+ StringBuilder propertyString = new StringBuilder();
+ List<PictureData> images = pictures.get(row.getRowNum() + "_" + entry.getKey());
+ for (PictureData picture : images) {
+ byte[] data = picture.getData();
+ String fileName = FileUtils.writeImportBytes(data);
+ propertyString.append(fileName).append(SEPARATOR);
+ }
+ val = StringUtils.stripEnd(propertyString.toString(), SEPARATOR);
}
ReflectUtils.invokeSetter(entity, propertyName, val);
}
@@ -1037,12 +1208,15 @@
else if (ColumnType.IMAGE == attr.cellType())
{
ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
- String imagePath = Convert.toStr(value);
- if (StringUtils.isNotEmpty(imagePath))
+ String propertyValue = Convert.toStr(value);
+ if (StringUtils.isNotEmpty(propertyValue))
{
- byte[] data = ImageUtils.getImage(imagePath);
- getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
- cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
+ List<String> imagePaths = StringUtils.str2List(propertyValue, SEPARATOR);
+ for (String imagePath : imagePaths)
+ {
+ byte[] data = ImageUtils.getImage(imagePath);
+ getDrawingPatriarch(cell.getSheet()).createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
+ }
}
}
}
@@ -1148,6 +1322,7 @@
String dictType = attr.dictType();
if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
{
+ cell.getCellStyle().setDataFormat(this.wb.getCreationHelper().createDataFormat().getFormat(dateFormat));
cell.setCellValue(parseDateToStr(dateFormat, value));
}
else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
@@ -1286,7 +1461,7 @@
public static String convertByExp(String propertyValue, String converterExp, String separator)
{
StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
+ String[] convertSource = converterExp.split(SEPARATOR);
for (String item : convertSource)
{
String[] itemArray = item.split("=");
@@ -1323,7 +1498,7 @@
public static String reverseByExp(String propertyValue, String converterExp, String separator)
{
StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
+ String[] convertSource = converterExp.split(SEPARATOR);
for (String item : convertSource)
{
String[] itemArray = item.split("=");
@@ -1448,8 +1623,7 @@
*/
public String encodingFilename(String filename)
{
- filename = UUID.randomUUID() + "_" + filename + ".xlsx";
- return filename;
+ return UUID.randomUUID() + "_" + filename + ".xlsx";
}
/**
@@ -1750,30 +1924,24 @@
* @param workbook 宸ヤ綔绨垮璞�
* @return Map key:鍥剧墖鍗曞厓鏍肩储寮曪紙1_1锛塖tring锛寁alue:鍥剧墖娴丳ictureData
*/
- public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
+ public static Map<String, List<PictureData>> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
{
- Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
+ Map<String, List<PictureData>> sheetIndexPicMap = new HashMap<>();
List<HSSFPictureData> pictures = workbook.getAllPictures();
- if (!pictures.isEmpty())
+ if (!pictures.isEmpty() && sheet.getDrawingPatriarch() != null)
{
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
{
- HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture)
{
HSSFPicture pic = (HSSFPicture) shape;
- int pictureIndex = pic.getPictureIndex() - 1;
- HSSFPictureData picData = pictures.get(pictureIndex);
+ HSSFClientAnchor anchor = (HSSFClientAnchor) pic.getAnchor();
String picIndex = anchor.getRow1() + "_" + anchor.getCol1();
- sheetIndexPicMap.put(picIndex, picData);
+ sheetIndexPicMap.computeIfAbsent(picIndex, k -> new ArrayList<>()).add(pic.getPictureData());
}
}
- return sheetIndexPicMap;
}
- else
- {
- return sheetIndexPicMap;
- }
+ return sheetIndexPicMap;
}
/**
@@ -1783,16 +1951,15 @@
* @param workbook 宸ヤ綔绨垮璞�
* @return Map key:鍥剧墖鍗曞厓鏍肩储寮曪紙1_1锛塖tring锛寁alue:鍥剧墖娴丳ictureData
*/
- public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
+ public static Map<String, List<PictureData>> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
{
- Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
+ Map<String, List<PictureData>> sheetIndexPicMap = new HashMap<>();
for (POIXMLDocumentPart dr : sheet.getRelations())
{
if (dr instanceof XSSFDrawing)
{
XSSFDrawing drawing = (XSSFDrawing) dr;
- List<XSSFShape> shapes = drawing.getShapes();
- for (XSSFShape shape : shapes)
+ for (XSSFShape shape : drawing.getShapes())
{
if (shape instanceof XSSFPicture)
{
@@ -1800,7 +1967,7 @@
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
- sheetIndexPicMap.put(picIndex, pic.getPictureData());
+ sheetIndexPicMap.computeIfAbsent(picIndex, k -> new ArrayList<>()).add(pic.getPictureData());
}
}
}
--
Gitblit v1.9.3