报价单修改-优化，增加导入记录，降价历史

云

7 天以前 11214e3074266a23fe61e8eebbce647fdb7305ef

 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;

@@ -2089,4 +2090,247 @@
        }

        return method;

    }



    /**

     * 导出多Sheet Excel模板（静态方法）

     * 支持不同类型的DTO导出到不同的Sheet

     *

     * @param response HTTP响应

     * @param sheetDataMap Map<Sheet名称, SheetData>，SheetData包含数据列表和对应的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("导出多Sheet 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 });

                }

            }

        }



        // 按sort排序

        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);

        }

    }

}