导入excel整形值校验优化

RuoYi

2020-09-17 ab8ccdc7f4b7b8574bf557ebb7f95fa7d80ed146

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

@@ -6,8 +6,8 @@
import java.io.InputStream;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.math.BigDecimal;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Comparator;

@@ -15,6 +15,7 @@
import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.UUID;

import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

@@ -101,6 +102,16 @@
     * 注解列表

     */

    private List<Object[]> fields;



    /**

     * 统计列表

     */

    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();



    /**

     * 数字格式

     */

    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");



    /**

     * 实体对象

@@ -232,19 +243,19 @@
                            val = Convert.toStr(val);

                        }

                    }

                    else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType))

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

                    else if (Long.TYPE == fieldType || Long.class == fieldType)

                    {

                        val = Convert.toLong(val);

                    }

                    else if ((Double.TYPE == fieldType) || (Double.class == fieldType))

                    else if (Double.TYPE == fieldType || Double.class == fieldType)

                    {

                        val = Convert.toDouble(val);

                    }

                    else if ((Float.TYPE == fieldType) || (Float.class == fieldType))

                    else if (Float.TYPE == fieldType || Float.class == fieldType)

                    {

                        val = Convert.toFloat(val);

                    }

@@ -341,6 +352,7 @@
                if (Type.EXPORT.equals(type))

                {

                    fillExcelData(index, row);

                    addStatisticsRow();

                }

            }

            String filename = encodingFilename(sheetName);

@@ -447,6 +459,15 @@
        headerFont.setColor(IndexedColors.WHITE.getIndex());

        style.setFont(headerFont);

        styles.put("header", style);

        
        style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font totalFont = wb.createFont();

        totalFont.setFontName("Arial");

        totalFont.setFontHeightInPoints((short) 10);

        style.setFont(totalFont);

        styles.put("total", style);



        return styles;

    }

@@ -482,7 +503,7 @@
        else if (ColumnType.NUMERIC == attr.cellType())

        {

            cell.setCellType(CellType.NUMERIC);

            cell.setCellValue(Integer.parseInt(value + ""));

            cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));

        }

    }



@@ -546,15 +567,20 @@
                {

                    cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));

                }

                else if (StringUtils.isNotEmpty(dictType))

                else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))

                {

                    cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));

                }

                else if (value instanceof BigDecimal && -1 != attr.scale())

                {

                    cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());

                }

                else

                {

                    // 设置列类型

                    setCellVo(value, attr, cell);

                }

                addStatisticsData(column, Convert.toStr(value), attr);

            }

        }

        catch (Exception e)

@@ -722,6 +748,53 @@
    }



    /**

     * 合计统计信息

     */

    private void addStatisticsData(Integer index, String text, Excel entity)

    {

        if (entity != null && entity.isStatistics())

        {

            Double temp = 0D;

            if (!statistics.containsKey(index))

            {

                statistics.put(index, temp);

            }

            try

            {

                temp = Double.valueOf(text);

            }

            catch (NumberFormatException e)

            {

            }

            statistics.put(index, statistics.get(index) + temp);

        }

    }



    /**

     * 创建统计行

     */

    public void addStatisticsRow()

    {

        if (statistics.size() > 0)

        {

            Cell cell = null;

            Row row = sheet.createRow(sheet.getLastRowNum() + 1);

            Set<Integer> keys = statistics.keySet();

            cell = row.createCell(0);

            cell.setCellStyle(styles.get("total"));

            cell.setCellValue("合计");



            for (Integer key : keys)

            {

                cell = row.createCell(key);

                cell.setCellStyle(styles.get("total"));

                cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));

            }

            statistics.clear();

        }

    }



    /**

     * 编码文件名

     */

    public String encodingFilename(String filename)

@@ -790,9 +863,9 @@
        if (StringUtils.isNotEmpty(name))

        {

            Class<?> clazz = o.getClass();

            String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);

            Method method = clazz.getMethod(methodName);

            o = method.invoke(o);

            Field field = clazz.getDeclaredField(name);

            field.setAccessible(true);

            o = field.get(o);

        }

        return o;

    }

@@ -896,7 +969,14 @@
                    }

                    else

                    {

                        val = new BigDecimal(val.toString()); // 浮点格式处理

                        if ((Double) val % 1 > 0)

                        {

                            val = new BigDecimal(val.toString());

                        }

                        else

                        {

                            val = new DecimalFormat("0").format(val);

                        }

                    }

                }

                else if (cell.getCellTypeEnum() == CellType.STRING)