From 11214e3074266a23fe61e8eebbce647fdb7305ef Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期五, 12 六月 2026 18:02:03 +0800
Subject: [PATCH] 报价单修改-优化，增加导入记录，降价历史
---
src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 443 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 441 insertions(+), 2 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 6860e90..167f4b4 100644
--- a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -10,6 +10,7 @@
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.text.DecimalFormat;
+import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
@@ -23,7 +24,7 @@
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
-import javax.servlet.http.HttpServletResponse;
+import jakarta.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
@@ -476,6 +477,22 @@
val = DateUtil.getJavaDate((Double) val);
}
}
+ else if (LocalDate.class == fieldType)
+ {
+ if (val instanceof String)
+ {
+ Date date = DateUtils.parseDate(val);
+ val = StringUtils.isNull(date) ? null : DateUtils.toLocalDate(date);
+ }
+ else if (val instanceof Date)
+ {
+ val = DateUtils.toLocalDate((Date) val);
+ }
+ else if (val instanceof Double)
+ {
+ val = DateUtils.toLocalDate(DateUtil.getJavaDate((Double) val));
+ }
+ }
else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
{
val = Convert.toBool(val, false);
@@ -510,6 +527,185 @@
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);
+ }
+ }
+ 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 (LocalDate.class == fieldType) {
+ if (val instanceof String) {
+ Date date = DateUtils.parseDate(val);
+ val = StringUtils.isNull(date) ? null : DateUtils.toLocalDate(date);
+ } else if (val instanceof Date) {
+ val = DateUtils.toLocalDate((Date) val);
+ } else if (val instanceof Double) {
+ val = DateUtils.toLocalDate(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);
@@ -1873,7 +2069,7 @@
/**
* 鑾峰彇瀵硅薄鐨勫瓙鍒楄〃鏂规硶
- *
+ *
* @param name 鍚嶇О
* @param pojoClass 绫诲璞�
* @return 瀛愬垪琛ㄦ柟娉�
@@ -1894,4 +2090,247 @@
}
return method;
}
+
+ /**
+ * 瀵煎嚭澶歋heet Excel妯℃澘锛堥潤鎬佹柟娉曪級
+ * 鏀寔涓嶅悓绫诲瀷鐨凞TO瀵煎嚭鍒颁笉鍚岀殑Sheet
+ *
+ * @param response HTTP鍝嶅簲
+ * @param sheetDataMap Map<Sheet鍚嶇О, SheetData>锛孲heetData鍖呭惈鏁版嵁鍒楄〃鍜屽搴旂殑Class绫诲瀷
+ * @param fileName 鏂囦欢鍚�
+ */
+ @SuppressWarnings("unchecked")
+ public static void exportExcelMultiSheet(HttpServletResponse response,
+ Map<String, SheetData<?>> sheetDataMap, String fileName)
+ {
+ try (SXSSFWorkbook workbook = new SXSSFWorkbook())
+ {
+ // 鍒涘缓鏍峰紡
+ CellStyle headerStyle = createHeaderStyle(workbook);
+ CellStyle dataStyle = createDataStyle(workbook);
+
+ // 閬嶅巻姣忎釜Sheet
+ for (Map.Entry<String, SheetData<?>> entry : sheetDataMap.entrySet())
+ {
+ String sheetName = entry.getKey();
+ SheetData<?> sheetData = entry.getValue();
+ List<?> dataList = sheetData.getDataList();
+ Class<?> clazz = sheetData.getClazz();
+
+ // 鍒涘缓Sheet
+ Sheet sheet = workbook.createSheet(sheetName);
+
+ // 鑾峰彇瀛楁淇℃伅
+ List<Object[]> fields = getFieldsByClass(clazz, Type.IMPORT);
+ if (fields.isEmpty())
+ {
+ continue;
+ }
+
+ // 鍒涘缓琛ㄥご
+ Row headerRow = sheet.createRow(0);
+ int colIndex = 0;
+ for (Object[] fieldObj : fields)
+ {
+ Field field = (Field) fieldObj[0];
+ Excel excel = (Excel) fieldObj[1];
+ Cell cell = headerRow.createCell(colIndex);
+ cell.setCellValue(excel.name());
+ cell.setCellStyle(headerStyle);
+ // 璁剧疆鍒楀
+ sheet.setColumnWidth(colIndex, (int) ((excel.width() + 0.72) * 256));
+ colIndex++;
+ }
+
+ // 鍐欏叆鏁版嵁
+ if (dataList != null && !dataList.isEmpty())
+ {
+ int rowIndex = 1;
+ for (Object data : dataList)
+ {
+ Row dataRow = sheet.createRow(rowIndex);
+ colIndex = 0;
+ for (Object[] fieldObj : fields)
+ {
+ Field field = (Field) fieldObj[0];
+ Excel excel = (Excel) fieldObj[1];
+ field.setAccessible(true);
+ Object value = field.get(data);
+ Cell cell = dataRow.createCell(colIndex);
+ setCellValueByType(cell, value, excel, dataStyle, workbook);
+ colIndex++;
+ }
+ rowIndex++;
+ }
+ }
+ }
+
+ // 杈撳嚭鍒板搷搴�
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ String encodedFileName = new String(fileName.getBytes("GBK"), "ISO8859_1") + ".xlsx";
+ response.addHeader("Content-Disposition", "attachment;filename=" + encodedFileName);
+ workbook.write(response.getOutputStream());
+ }
+ catch (Exception e)
+ {
+ log.error("瀵煎嚭澶歋heet Excel寮傚父: {}", e.getMessage());
+ throw new UtilException("瀵煎嚭Excel澶辫触: " + e.getMessage());
+ }
+ }
+
+ /**
+ * Sheet鏁版嵁灏佽绫�
+ */
+ public static class SheetData<T>
+ {
+ private List<T> dataList;
+ private Class<T> clazz;
+
+ public SheetData(List<T> dataList, Class<T> clazz)
+ {
+ this.dataList = dataList;
+ this.clazz = clazz;
+ }
+
+ public List<T> getDataList()
+ {
+ return dataList;
+ }
+
+ public Class<T> getClazz()
+ {
+ return clazz;
+ }
+ }
+
+ /**
+ * 鏍规嵁绫昏幏鍙栧瓧娈靛垪琛紙闈欐�佹柟娉曪級
+ */
+ private static List<Object[]> getFieldsByClass(Class<?> clazz, Type type)
+ {
+ List<Object[]> fields = new ArrayList<>();
+ List<Field> tempFields = new ArrayList<>();
+ tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
+ tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
+
+ for (Field field : tempFields)
+ {
+ if (field.isAnnotationPresent(Excel.class))
+ {
+ Excel excel = field.getAnnotation(Excel.class);
+ if (excel != null && (excel.type() == Type.ALL || excel.type() == type))
+ {
+ fields.add(new Object[] { field, excel });
+ }
+ }
+ }
+
+ // 鎸塻ort鎺掑簭
+ fields.sort(Comparator.comparing(objects -> ((Excel) objects[1]).sort()));
+ return fields;
+ }
+
+ /**
+ * 鍒涘缓琛ㄥご鏍峰紡
+ */
+ private static CellStyle createHeaderStyle(Workbook workbook)
+ {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.THIN);
+ style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+
+ Font font = workbook.createFont();
+ font.setFontName("Arial");
+ font.setFontHeightInPoints((short) 10);
+ font.setBold(true);
+ style.setFont(font);
+
+ DataFormat dataFormat = workbook.createDataFormat();
+ style.setDataFormat(dataFormat.getFormat("@"));
+
+ return style;
+ }
+
+ /**
+ * 鍒涘缓鏁版嵁鏍峰紡
+ */
+ private static CellStyle createDataStyle(Workbook workbook)
+ {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.THIN);
+ style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
+
+ Font font = workbook.createFont();
+ font.setFontName("Arial");
+ font.setFontHeightInPoints((short) 10);
+ style.setFont(font);
+
+ return style;
+ }
+
+ /**
+ * 璁剧疆鍗曞厓鏍煎��
+ */
+ private static void setCellValueByType(Cell cell, Object value, Excel excel, CellStyle dataStyle, Workbook workbook)
+ {
+ cell.setCellStyle(dataStyle);
+
+ if (value == null)
+ {
+ cell.setCellValue("");
+ return;
+ }
+
+ String dateFormat = excel.dateFormat();
+
+ if (StringUtils.isNotEmpty(dateFormat) && value instanceof Date)
+ {
+ cell.setCellValue(new SimpleDateFormat(dateFormat).format((Date) value));
+ }
+ else if (value instanceof Date)
+ {
+ cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format((Date) value));
+ }
+ else if (value instanceof BigDecimal)
+ {
+ cell.setCellValue(((BigDecimal) value).doubleValue());
+ }
+ else if (value instanceof Number)
+ {
+ cell.setCellValue(((Number) value).doubleValue());
+ }
+ else if (value instanceof Boolean)
+ {
+ cell.setCellValue((Boolean) value);
+ }
+ else
+ {
+ String strValue = Convert.toStr(value);
+ // 闃叉CSV娉ㄥ叆
+ if (StringUtils.startsWithAny(strValue, FORMULA_STR))
+ {
+ strValue = RegExUtils.replaceFirst(strValue, FORMULA_REGEX_STR, "\t$0");
+ }
+ cell.setCellValue(strValue);
+ }
+ }
}
--
Gitblit v1.9.3