Excel注解ColumnType类型新增文本

RuoYi

2024-03-22 6e5b0e711c9e7924428f2e43825bd4599d73ccd8

Excel注解ColumnType类型新增文本

 src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java

@@ -39,6 +39,7 @@
import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.ClientAnchor;

import org.apache.poi.ss.usermodel.DataFormat;

import org.apache.poi.ss.usermodel.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.usermodel.DataValidationHelper;

@@ -783,6 +784,8 @@
        titleFont.setFontHeightInPoints((short) 16);

        titleFont.setBold(true);

        style.setFont(titleFont);

        DataFormat dataFormat = wb.createDataFormat();

        style.setDataFormat(dataFormat.getFormat("@"));

        styles.put("title", style);



        style = wb.createCellStyle();

@@ -845,6 +848,9 @@
                headerFont.setBold(true);

                headerFont.setColor(excel.headerColor().index);

                style.setFont(headerFont);

                // 设置表格头单元格文本形式

                DataFormat dataFormat = wb.createDataFormat();

                style.setDataFormat(dataFormat.getFormat("@"));

                headerStyles.put(key, style);

            }

        }

@@ -862,32 +868,64 @@
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        for (Object[] os : fields)

        {

            Field field = (Field) os[0];

            Excel excel = (Excel) os[1];

            String key = StringUtils.format("data_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor());

            if (!styles.containsKey(key))

            if (Collection.class.isAssignableFrom(field.getType()))

            {

                CellStyle style = wb.createCellStyle();

                style.setAlignment(excel.align());

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

                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                style.setFillForegroundColor(excel.backgroundColor().getIndex());

                Font dataFont = wb.createFont();

                dataFont.setFontName("Arial");

                dataFont.setFontHeightInPoints((short) 10);

                dataFont.setColor(excel.color().index);

                style.setFont(dataFont);

                styles.put(key, style);

                ParameterizedType pt = (ParameterizedType) field.getGenericType();

                Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];

                List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);

                for (Field subField : subFields)

                {

                    Excel subExcel = subField.getAnnotation(Excel.class);

                    annotationDataStyles(styles, subField, subExcel);

                }

            }

            else

            {

                annotationDataStyles(styles, field, excel);

            }

        }

        return styles;

    }



    /**

     * 根据Excel注解创建表格列样式

     * 
     * @param styles 自定义样式列表

     * @param field  属性列信息

     * @param excel  注解信息

     */

    public void annotationDataStyles(Map<String, CellStyle> styles, Field field, Excel excel)

    {

        String key = StringUtils.format("data_{}_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor(), excel.cellType());

        if (!styles.containsKey(key))

        {

            CellStyle style = wb.createCellStyle();

            style.setAlignment(excel.align());

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

            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            style.setFillForegroundColor(excel.backgroundColor().getIndex());

            Font dataFont = wb.createFont();

            dataFont.setFontName("Arial");

            dataFont.setFontHeightInPoints((short) 10);

            dataFont.setColor(excel.color().index);

            style.setFont(dataFont);

            if (ColumnType.TEXT == excel.cellType())

            {

                DataFormat dataFormat = wb.createDataFormat();

                style.setDataFormat(dataFormat.getFormat("@"));

            }

            styles.put(key, style);

        }

    }



    /**

@@ -904,7 +942,7 @@
        if (isSubList())

        {

            // 填充默认样式，防止合并单元格样式失效

            sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));

            sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));

            if (attr.needMerge())

            {

                sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));

@@ -922,7 +960,7 @@
     */

    public void setCellVo(Object value, Excel attr, Cell cell)

    {

        if (ColumnType.STRING == attr.cellType())

        if (ColumnType.STRING == attr.cellType() || ColumnType.TEXT == attr.cellType())

        {

            String cellValue = Convert.toStr(value);

            // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。

@@ -1034,7 +1072,7 @@
                    CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);

                    sheet.addMergedRegion(cellAddress);

                }

                cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));

                cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));



                // 用于读取对象中的属性

                Object value = getTargetValue(vo, field, attr);


 src/main/java/com/ruoyi/framework/aspectj/lang/annotation/Excel.java

@@ -171,7 +171,7 @@


    public enum ColumnType

    {

        NUMERIC(0), STRING(1), IMAGE(2);

        NUMERIC(0), STRING(1), IMAGE(2), TEXT(3);

        private final int value;



        ColumnType(int value)


 src/main/java/com/ruoyi/project/system/domain/SysUser.java

@@ -42,7 +42,7 @@
    private String email;



    /** 手机号码 */

    @Excel(name = "手机号码")

    @Excel(name = "手机号码", cellType = ColumnType.TEXT)

    private String phonenumber;



    /** 用户性别 */