From 0e2b97a886f403d6e42f3e4195b592902529c49f Mon Sep 17 00:00:00 2001 From: RuoYi <yzz_ivy@163.com> Date: 星期四, 03 十二月 2020 13:28:04 +0800 Subject: [PATCH] 升级poi到最新版本4.1.2 --- ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 137 +++++++++++++++++++++++++++++++++++++++------ 1 files changed, 117 insertions(+), 20 deletions(-) diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java index d950b77..bf77d93 100644 --- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java +++ b/ruoyi-common/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,9 +15,9 @@ 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; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; @@ -101,6 +101,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 +242,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); } @@ -262,6 +272,10 @@ { val = DateUtil.getJavaDate((Double) val); } + } + else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) + { + val = Convert.toBool(val, false); } if (StringUtils.isNotNull(fieldType)) { @@ -341,6 +355,7 @@ if (Type.EXPORT.equals(type)) { fillExcelData(index, row); + addStatisticsRow(); } } String filename = encodingFilename(sheetName); @@ -447,6 +462,30 @@ 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); + + style = wb.createCellStyle(); + style.cloneStyleFrom(styles.get("data")); + style.setAlignment(HorizontalAlignment.LEFT); + styles.put("data1", style); + + style = wb.createCellStyle(); + style.cloneStyleFrom(styles.get("data")); + style.setAlignment(HorizontalAlignment.CENTER); + styles.put("data2", style); + + style = wb.createCellStyle(); + style.cloneStyleFrom(styles.get("data")); + style.setAlignment(HorizontalAlignment.RIGHT); + styles.put("data3", style); return styles; } @@ -476,13 +515,11 @@ { if (ColumnType.STRING == attr.cellType()) { - cell.setCellType(CellType.STRING); cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix()); } 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)); } } @@ -530,7 +567,8 @@ { // 创建cell cell = row.createCell(column); - cell.setCellStyle(styles.get("data")); + int align = attr.align().value(); + cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); // 用于读取对象中的属性 Object value = getTargetValue(vo, field, attr); @@ -546,15 +584,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 +765,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 +880,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; } @@ -887,27 +977,34 @@ Cell cell = row.getCell(column); if (StringUtils.isNotNull(cell)) { - if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) + if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) { val = cell.getNumericCellValue(); - if (HSSFDateUtil.isCellDateFormatted(cell)) + if (DateUtil.isCellDateFormatted(cell)) { val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换 } 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) + else if (cell.getCellType() == CellType.STRING) { val = cell.getStringCellValue(); } - else if (cell.getCellTypeEnum() == CellType.BOOLEAN) + else if (cell.getCellType() == CellType.BOOLEAN) { val = cell.getBooleanCellValue(); } - else if (cell.getCellTypeEnum() == CellType.ERROR) + else if (cell.getCellType() == CellType.ERROR) { val = cell.getErrorCellValue(); } -- Gitblit v1.9.2