Excel注解支持自动统计数据总和

RuoYi

2020-08-26 ba3549e8243b8abce60588f107684a246323db58

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

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

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

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

@@ -96,6 +97,16 @@
     * 注解列表

     */

    private List<Object[]> fields;



    /**

     * 统计列表

     */

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



    /**

     * 数字格式

     */

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



    /**

     * 实体对象

@@ -194,7 +205,10 @@
                    // 设置类的私有字段属性可访问.

                    field.setAccessible(true);

                    Integer column = cellMap.get(attr.name());

                    fieldsMap.put(column, field);

                    if (column != null)

                    {

                        fieldsMap.put(column, field);

                    }

                }

            }

            for (int i = 1; i < rows; i++)

@@ -334,6 +348,7 @@
                if (Type.EXPORT.equals(type))

                {

                    fillExcelData(index, row);

                    addStatisticsRow();

                }

            }

            wb.write(outputStream);

@@ -436,6 +451,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;

    }

@@ -465,7 +489,7 @@
    {

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

        {

            cell.setCellType(CellType.NUMERIC);

            cell.setCellType(CellType.STRING);

            cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());

        }

        else if (ColumnType.NUMERIC == attr.cellType())

@@ -534,11 +558,16 @@
                {

                    cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, 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)

@@ -677,6 +706,53 @@
            }

        }

        return StringUtils.stripEnd(propertyString.toString(), separator);

    }



    /**

     * 合计统计信息

     */

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

        }

    }



    /**

@@ -831,7 +907,7 @@
                    {

                        if ((Double) val % 1 > 0)

                        {

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

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

                        }

                        else

                        {