基于Java的织羽智慧社区（物业）后台管理系统设计源码

package com.zy.community.common.utils.poi; import com.zy.community.common.annotation.Excel; import com.zy.community.common.annotation.Excel.ColumnType; import com.zy.community.common.annotation.Excel.Type; import com.zy.community.common.annotation.Excels; import com.zy.community.common.config.ZyConfig; import com.zy.community.common.core.domain.r.ZyResult; import com.zy.community.common.core.text.Convert; import com.zy.community.common.exception.CustomException; import com.zy.community.common.utils.DateUtils; import com.zy.community.common.utils.DictUtils; import com.zy.community.common.utils.StringUtils; import com.zy.community.common.utils.reflect.ReflectUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.*; import java.lang.reflect.Field; import java.math.BigDecimal; import java.text.DecimalFormat; import java.util.*; import java.util.stream.Collectors; /** * Excel相关处理 * * @author yangdi */ public class ExcelUtil<T> { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); /** * Excel sheet最大行数，默认65536 */ public static final int sheetSize = 65536; /** * 工作表名称 */ private String sheetName; /** * 导出类型（EXPORT:导出数据；IMPORT：导入模板） */ private Type type; /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 导入导出数据列表 */ private List<T> list; /** * 注解列表 */ private List<Object[]> fields; /** * 统计列表 */ private Map<Integer, Double> statistics = new HashMap<Integer, Double>(); /** * 数字格式 */ private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); /** * 实体对象 */ public Class<T> clazz; public ExcelUtil(Class<T> clazz) { this.clazz = clazz; } public void init(List<T> list, String sheetName, Type type) { if (list == null) { list = new ArrayList<T>(); } this.list = list; this.sheetName = sheetName; this.type = type; createExcelField(); createWorkbook(); } /** * 对excel表单默认第一个索引名转换成list * * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(InputStream is) throws Exception { return importExcel(StringUtils.EMPTY, is); } /** * 对excel表单指定表格索引名转换成list * * @param sheetName 表格索引名 * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(String sheetName, InputStream is) throws Exception { this.type = Type.IMPORT; this.wb = WorkbookFactory.create(is); List<T> list = new ArrayList<T>(); Sheet sheet = null; if (StringUtils.isNotEmpty(sheetName)) { // 如果指定sheet名,则取指定sheet中的内容. sheet = wb.getSheet(sheetName); } else { // 如果传入的sheet名不存在则默认指向第1个sheet. sheet = wb.getSheetAt(0); } if (sheet == null) { throw new IOException("文件sheet不存在"); } int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { // 定义一个map用于存放excel列的序号和field. Map<String, Integer> cellMap = new HashMap<String, Integer>(); // 获取表头 Row heard = sheet.getRow(0); for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) { Cell cell = heard.getCell(i); if (StringUtils.isNotNull(cell)) { String value = this.getCellValue(heard, i).toString(); cellMap.put(value, i); } else { cellMap.put(null, i); } } // 有数据时才处理 得到类的所有field. Field[] allFields = clazz.getDeclaredFields(); // 定义一个map用于存放列的序号和field. Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>(); for (int col = 0; col < allFields.length; col++) { Field field = allFields[col]; Excel attr = field.getAnnotation(Excel.class); if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) { // 设置类的私有字段属性可访问. field.setAccessible(true); Integer column = cellMap.get(attr.name()); if (column != null) { fieldsMap.put(column, field); } } } for (int i = 1; i < rows; i++) { // 从第2行开始取数据,默认第一行是表头. Row row = sheet.getRow(i); T entity = null; for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) { Object val = this.getCellValue(row, entry.getKey()); // 如果不存在实例则新建. entity = (entity == null ? clazz.newInstance() : entity); // 从map中得到对应列的field. Field field = fieldsMap.get(entry.getKey()); // 取得类型,并根据对象类型设置值. Class<?> fieldType = field.getType(); if (String.class == fieldType) { String s = Convert.toStr(val); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = Convert.toStr(val); } } 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) { val = Convert.toLong(val); } else if (Double.TYPE == fieldType || Double.class == fieldType) { val = Convert.toDouble(val); } else if (Float.TYPE == fieldType || Float.class == fieldType) { val = Convert.toFloat(val); } else if (BigDecimal.class == fieldType) { val = Convert.toBigDecimal(val); } else if (Date.class == fieldType) { if (val instanceof String) { val = DateUtils.parseDate(val); } else if (val instanceof Double) { val = DateUtil.getJavaDate((Double) val); } } else if (Boolean.TYPE == fieldType || Boolean.class ==