本文详细介绍了一种基于注解的Excel导入导出框架，包括注解定义、枚举类型、示例类以及控制器实现。该框架支持多页签数据处理，提供了数据校验、格式设定等功能。
注解Excel导入导出
目录
-
excel目录结构：
-
excel
-
annotation
-
excelExport
package com.hahashujia.basic.excel.annotation; import com.hahashujia.basic.excel.enums.DecimalType; import com.hahashujia.basic.excel.enums.TimeType; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel 导入导出注解 * @author hahashujia */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelExport { /** 定义字段排序 */ int order(); /** * 表头中文 * * @return */ String titleName() default ""; /** * 列宽 * * @return */ int titleSize() default 20; /** * 是否允许空值 ，默认不允许 * * @return */ boolean empty() default false; /** * 是否允许空值 ，默认不允许 * * @return */ boolean template() default true; /** * 内部类 * * @return */ ExcelExport.CellType cellType() default @ExcelExport.CellType; /** * 内部类 * * @return */ ExcelExport.SheetType sheetType() default @ExcelExport.SheetType; /** * 设置格式 * * @return */ @interface CellType { TimeType timeType() default TimeType.TIMEF_FORMAT; DecimalType decimalType() default DecimalType.TWO; /** * true : 结合decimalType使用 * @return */ boolean isMoney() default false; } /** * 设置sheet * * @return */ @interface SheetType { /** * true : 启用以下两个配置 * @return */ boolean isSheet() default false; int order() default 0; String name() default ""; } } -
excelImport
package com.hahashujia.basic.excel.annotation; import com.hahashujia.basic.excel.enums.DecimalType; import com.hahashujia.basic.excel.enums.TimeType; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel 导入注解 * @author hahashujia */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelImport { /** * 数据开始列 */ int column() default 0; /** * 表头中文 * * @return */ String titleName() default ""; /** * 是否允许空值 ，默认不允许 * * @return */ boolean empty() default false; /** * 内部类 * * @return */ ExcelImport.CellType cellType() default @ExcelImport.CellType; /** * 设置格式 * * @return */ @interface CellType { TimeType timeType() default TimeType.TIMEF_FORMAT; DecimalType decimalType() default DecimalType.TWO; /** * true : 结合decimalType使用 * @return */ boolean isMoney() default false; } } -
ExcelSheet
package com.hahashujia.basic.excel.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel 导入导出注解 * @author hahashujia */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelSheet { /** * true : 启用以下配置 * @return */ boolean isSheet() default true; /** * sheet 位置 * @return */ int sheetNum(); /** * sheet名称 * @return */ String sheetName(); /** * Title行 * @return */ int titleRow() default 0; /** * 数据开始行 * @return */ int row() default 1; /** * 数据开始列 */ int column() default 0; /** * 是否允许空值 ，默认不允许 * * @return */ boolean empty() default false; }
-
-
enums
-
DecimalType
package com.hahashujia.basic.excel.enums; /** * <p> * 小数点格式 * </p> * * @author hahashujia * @version V0.0.1 * @date 2019年09月20日 */ public enum DecimalType { /** * 零位 */ ZERO(0, "#"), /** * 一位 */ ONE(1, "#.0"), /** * 两位 */ TWO(2, "#.00"), /** * 三位 */ THREE(3, "#.000"), /** * 四位 */ FOUR(4, "#.0000"), /** * 五位 */ FIVE(5, "#.00000"), /** * 六位 */ SIX(6, "#.000000"), /** * 七位 */ SEVEN(7, "#.0000000"), /** * 八位 */ EIGHT(8, "#.00000000"), /** * 九位 */ NAN(9, "#.000000000"), /** * 十位 */ TEN(10, "#.0000000000"); /** * 日期格式 */ private String decimal; private int scale; /** * 日期格式 * * @param scale * @param decimal */ DecimalType(int scale, String decimal) { this.scale = scale; this.decimal = decimal; } /** * 获取日期格式 * * @return */ public String getDecimal() { return decimal; } /** * 获取日期格式 * * @return */ public int getScale() { return scale; } } -
ExcelType
package com.hahashujia.basic.excel.enums; /** * <p> * Excel 类型 * </p> * * @author hahashujia * @version V0.0.1 * @date 2019年09月12日 */ public enum ExcelType { /** * XLS */ XLS, /** * XLS_X */ XLS_X } -
TimeType
package com.hahashujia.basic.excel.enums; /** * <p> * 日期格式 * </p> * * @author hahashujia * @version V0.0.1 * @date 2019年09月20日 */ public enum TimeType { /** * yyyy-MM-dd */ DATE_FORMAT("yyyy-MM-dd"), /** * yyyy-MM */ YEAR_S_MONTH("yyyy-MM"), /** * yyyyMM */ YEAR_MONTH("yyyyMM"), /** * yyyy-MM-dd HH:mm:ss */ TIMEF_FORMAT("yyyy-MM-dd HH:mm:ss"), /** * yyyy-MM-dd HH:mm:ss.SSS */ MSEL_FORMAT("yyyy-MM-dd HH:mm:ss.SSS"), /** * yyyy年MM月dd日 */ ZHCN_DATE_FORMAT("yyyy年MM月dd日"), /** * yyyy年MM月dd日HH时mm分ss秒 */ ZHCN_TIME_FORMAT("yyyy年MM月dd日HH时mm分ss秒"), /** * yyyy年MM月dd日HH时mm分ss秒SSS毫秒 */ ZHCN_MSEL_FORMAT("yyyy年MM月dd日HH时mm分ss秒SSS毫秒"), /** * yyyyMMdd */ DATE_STR_FORMAT("yyyyMMdd"), /** * yyyyMMddHHmmss */ TIME_STR_FORMAT("yyyyMMddHHmmss"), /** * yyyyMMddHHmmssSSS */ MSEL_STR_FORMAT("yyyyMMddHHmmssSSS"), /** * yyyy-MM-dd HH:mm */ MSEL_MIU_FORMAT("yyyy-MM-dd HH:mm"), /** * yyyyMMddHH */ MS_MIU_FORMAT("yyyyMMddHH"); /** * 日期格式 */ private String timeType; /** * 日期格式 * * @param timeType */ TimeType(String timeType) { this.timeType = timeType; } /** * 获取日期格式 * * @return */ public String getTimeType() { return timeType; } }
-
-
exmple
-
ImpExpTemplateMultiple
package com.hahashujia.basic.excel.exmple; import com.hahashujia.basic.excel.annotation.ExcelSheet; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; /** * 导入导出测试 * @author hahashujia */ @Builder @Data @NoArgsConstructor @AllArgsConstructor public class ImpExpTemplateMultiple { @ExcelSheet(sheetNum = 1, sheetName = "列表1", titleRow = 1, row = 6, column = 1) public List<ImpExpTemplateSingle> singleList; @ExcelSheet(sheetNum = 2, sheetName = "列表2", titleRow = 1, row = 6, column = 1) public List<ImpExpTemplateSingle> templateSingleList; } -
ImpExpTemplateSingle
package com.hahashujia.basic.excel.exmple; import com.hahashujia.basic.excel.annotation.ExcelExport; import com.hahashujia.basic.excel.annotation.ExcelImport; import com.hahashujia.basic.excel.enums.DecimalType; import com.hahashujia.basic.excel.enums.TimeType; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.math.BigDecimal; import java.util.Date; /** * 导入导出测试 * @author hahashujia */ @Builder @Data @NoArgsConstructor @AllArgsConstructor public class ImpExpTemplateSingle { @ExcelImport(titleName = "主键ID", column = 1) @ExcelExport(titleName = "主键ID", order = 0) private Long id; @ExcelImport(titleName = "类型", column = 2) @ExcelExport(titleName = "类型", order = 1) private String type; @ExcelImport(titleName = "数字", column = 3, cellType = @ExcelImport.CellType(isMoney = true, decimalType = DecimalType.TWO)) @ExcelExport(titleName = "数字", order = 2, cellType = @ExcelExport.CellType(isMoney = true, decimalType = DecimalType.TWO)) private Double number; @ExcelImport(titleName = "金额", column = 4) @ExcelExport(titleName = "金额", order = 3) private BigDecimal amount; @ExcelImport(titleName = "日期", column = 5, cellType = @ExcelImport.CellType(timeType = TimeType.DATE_FORMAT)) @ExcelExport(titleName = "日期", order = 4, cellType = @ExcelExport.CellType(timeType = TimeType.DATE_FORMAT)) private Date date; @ExcelExport(titleName = "错误原因", order = 5, template = false) private String error; } -
multiple-imp-data.xlsx
-
single-imp-data.xls
-
-
utils
-
ExcelUtil
package com.hahashujia.basic.excel.utils; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.hahashujia.basic.excel.annotation.ExcelExport; import com.hahashujia.basic.excel.annotation.ExcelImport; import com.hahashujia.basic.excel.annotation.ExcelSheet; import com.hahashujia.basic.excel.enums.ExcelType; import com.hahashujia.common.enums.BasicCode; import com.hahashujia.common.exceptions.BasicException; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.beans.PropertyDescriptor; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.math.BigDecimal; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; /** * <p> * excel 工具类 * </p> * * @author hahashujia * @version V3 * @date 2019年11月13日 */ @Slf4j public class ExcelUtil { /** * xls 后缀 */ public static final String XLS = "xls"; /** * xlsx 后缀 */ public static final String XLS_X = "xlsx"; /** * 列不对等 */ public static final String ROW_NUM_ERROR = "导入模板异常！"; /** * 文件不存在 */ public static final String FILE_NOT_ERROR = "文件不存在！"; /** * 表头错误 */ public static final String NAME_ERROR = "表头错误！"; /** * 实体空异常 */ public static final String BEAN_ERROR = "实体空异常！"; /** * 科学计数 */ public static final String E = "e"; /** * 传入文本对象输出list集合（导入） * * @param file 流文件 * @param clazz 要转义成的类对象 * @return */ public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) { // 检查文件 Workbook workbook = getWorkBook(file); List<T> list = new ArrayList<T>(); checkFile(file); // 获得HSSFWorkbook工作薄对象 //获取对象总数量并按注解排序 Field[] fields = getSortFieldsImport(clazz); if (workbook != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { // 获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if (sheet == null || sheet.getLastRowNum() == 0) { continue; } // 获取当前sheet工作表的列总数 int firstLine = sheet.getRow(0).getPhysicalNumberOfCells(); if (fields.length != firstLine) { throw new BasicException(BasicCode.FAIL.code, ROW_NUM_ERROR); } // 获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); // 循环所有行 for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { // 获得当前行 Row row = sheet.getRow(rowNum); if (row == null) { continue; } Object obj = getNewInstance(clazz); for (int cellNum = 0; cellNum < firstLine; cellNum++) { // 取出对应注解 ExcelImport excelImport = fields[cellNum].getAnnotation(ExcelImport.class); Cell cell = row.getCell(cellNum); if (rowNum == 0) { // 第一行 判断表头名称 if (cell == null || StringUtils.isEmpty(cell.getStringCellValue()) || !cell.getStringCellValue().equals(excelImport.titleName())) { throw new BasicException(BasicCode.FAIL.code, NAME_ERROR); } continue; } Object value = getCellValue(cell); // 判断注解是否允许空值 if (!excelImport.empty()) { if (value == null || "".equals(value)) { log.error("【excel导入】{} 列不能为空！", excelImport.titleName()); throw new BasicException(BasicCode.FAIL.code, excelImport.titleName() + "列不能为空"); } } // 根绝类型 实体类赋值 createBean(fields[cellNum], obj, value); } if (rowNum == 0) { // 表头不做记录 continue; } list.add((T) obj); } } } return list; } /** * 传入文本对象输出Class（导入） * * @param file 流文件 * @param clazz 要转义成的类对象 * @return */ public static <T> T importMultiple(MultipartFile file, Class<T> clazz) { // 检查文件 checkFile(file); // 获得HSSFWorkbook工作薄对象 Workbook workbook = getWorkBook(file); //获取对象总数量 Field[] fields = getSortFieldsByExcelSheet(clazz); Object obj = getNewInstance(clazz); if (workbook != null) { ExcelSheet excel = null; Field field = null; int order = 0; Sheet sheet = null; for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) { field = fields[fieldNum]; excel = field.getAnnotation(ExcelSheet.class); order = excel.sheetNum(); sheet = workbook.getSheetAt(order); Type genericType = field.getGenericType(); ParameterizedType pt = (ParameterizedType) genericType; //得到泛型里的class类型对象 Class<?> genericClazz = (Class<?>) pt.getActualTypeArguments()[0]; createBean(field, obj, importMultiple(sheet, genericClazz, excel)); } } return (T) obj; } private static <T> List<T> importMultiple(Sheet sheet, Class<T> clazz, ExcelSheet excelSheet) { int titleRow = excelSheet.titleRow(); Field[] fields = getSortFieldsImport(clazz); List<T> list = new ArrayList<T>(); // 获取当前sheet工作表的列总数 int firstLine = sheet.getRow(titleRow).getPhysicalNumberOfCells(); if (fields.length != firstLine) { throw new BasicException(BasicCode.FAIL.code, ROW_NUM_ERROR); } // 获得当前sheet的开始行 int firstRowNum = excelSheet.row(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); // 验证表头名称 Row row = null; // 循环所有行 for (int rowNum = titleRow; rowNum <= lastRowNum; rowNum++) { // 获得当前行 row = sheet.getRow(rowNum); if (row == null) { continue; } Object obj = getNewInstance(clazz); for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) { ExcelImport excel = fields[fieldNum].getAnnotation(ExcelImport.class); Cell cell = row.getCell(excel.column()); if (rowNum == titleRow) { // 第一行 判断表头名称 if (cell == null || StringUtils.isEmpty(cell.getStringCellValue()) || !cell.getStringCellValue().equals(excel.titleName())) { log.info("【excel导入】-{}-{}", excelSheet.sheetName(), excel.titleName() + NAME_ERROR); throw new BasicException(BasicCode.FAIL.code, "【" + excelSheet.sheetName() + "】【" + excel.titleName() + "】" + NAME_ERROR); } continue; } Object value = getCellValue(cell); // 判断注解是否允许空值 if (!excel.empty()) { if (value == null || "".equals(value)) { log.error("【excel导入】-{}-{}列不能为空！", excelSheet.sheetName(), excel.titleName()); throw new BasicException(BasicCode.FAIL.code, "【" + excelSheet.sheetName() + "】【" + excel.titleName() + "】" + "列不能为空！"); } } // 根绝类型 实体类赋值 createBean(fields[fieldNum], obj, value); } if (rowNum == titleRow) { // 表头不做记录 rowNum = firstRowNum - 1; continue; } list.add((T) obj); } if (list.size() == 0 && !excelSheet.empty()) { log.error("【excel导入】{} 页签不能为空！", excelSheet.sheetName()); throw new BasicException(BasicCode.FAIL.code, excelSheet.sheetName() + "页签不能为空！"); } return list; } /** * 导出模版 * * @param excelName excel 名称 * @param clazz 数据集 * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportTemplate(String excelName, Class<T> clazz, HttpServletResponse response) { return export(excelName, null, clazz, ExcelType.XLS, response, false); } /** * 导出模版 * * @param excelName excel 名称 * @param clazz 数据集 * @param type excel 类型 * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportTemplate(String excelName, Class<T> clazz, ExcelType type, HttpServletResponse response) { return export(excelName, null, clazz, type, response, false); } /** * excel 导出 （对象） * * @param excelName excel 名称 * @param list 数据集 * @param clazz 反射clazz * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportExcel(String excelName, List<T> list, Class<T> clazz, HttpServletResponse response) { return export(excelName, list, clazz, ExcelType.XLS, response, true); } /** * excel 导出 （对象） * * @param excelName excel 名称 * @param list 数据集 * @param clazz 反射clazz * @param type excel 类型 * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportExcel(String excelName, List<T> list, Class<T> clazz, ExcelType type, HttpServletResponse response) { return export(excelName, list, clazz, type, response, true); } /** * excel 导出 （Map） * * @param excelName excel 名称 * @param clazz 反射clazz * @param list 数据集 * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportExcel(String excelName, Class<T> clazz, List<Map<String, Object>> list, HttpServletResponse response) { return exportExcel(excelName, clazz, list, ExcelType.XLS, response, true); } /** * excel 导出 （Map） * * @param excelName excel 名称 * @param clazz * @param list 数据集 * @param type excel 类型 * @param response 使用response可以导出到浏览器 * @param <T> * @return */ public static <T> Boolean exportExcel(String excelName, Class<T> clazz, List<Map<String, Object>> list, ExcelType type, HttpServletResponse response) { return exportExcel(excelName, clazz, list, type, response, false); } /** * excel 导出 （Map） * * @param excelName excel 名称 * @param clazz * @param list 数据集 * @param type excel 类型 * @param response 使用response可以导出到浏览器 * @param flag true：数据导出 false：模版导出 * @param <T> * @return */ private static <T> Boolean exportExcel(String excelName, Class<T> clazz, List<Map<String, Object>> list, ExcelType type, HttpServletResponse response, boolean flag) { if (list == null || list.size() == 0) { log.error("【excel导出】{}", "excel导出数据空异常！"); return false; } List<T> ts = JSONArray.parseArray(JSON.toJSONString(list), clazz); return export(excelName, ts, clazz, type, response, flag); } /** * 模板导出多种数据 * * @param excelName * @param clazz * @param response * @param <T> * @return */ public static <T> Boolean exportMultipleTemplate(String excelName, Class<T> clazz, HttpServletResponse response) { return exportMultiple(excelName, null, clazz, ExcelType.XLS, response, false); } /** * 导出多种数据 * * @param excelName * @param model * @param clazz * @param response * @param <T> * @return */ public static <T> Boolean exportMultiple(String excelName, T model, Class<T> clazz, HttpServletResponse response) { return exportMultiple(excelName, model, clazz, ExcelType.XLS, response, true); } /** * 导出多种数据 * * @param excelName * @param model * @param clazz * @param type * @param response * @param <T> * @return */ public static <T> Boolean exportMultiple(String excelName, T model, Class<T> clazz, ExcelType type, HttpServletResponse response) { return exportMultiple(excelName, model, clazz, type, response, true); } /** * 导出多种数据 * * @param excelName * @param model * @param clazz * @param type * @param response * @param flag * @param <T> * @return */ public static <T> Boolean exportMultiple(String excelName, T model, Class<T> clazz, ExcelType type, HttpServletResponse response, boolean flag) { // 设置默认文件名为当前时间：年月日时分秒 if (StringUtils.isEmpty(excelName)) { log.info("【excel导出】{}", "excel导出未设置文件名，默认使用时间戳代替！"); excelName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); } createResponse(excelName, response, type); //获取对象总数量 Field[] fields = getSortFieldsByExcelSheet(clazz); Workbook workbook = getWorkbook(type); CellStyle titleCellStyle = getTitleCellStyle(workbook); for (int fieldNum = 0; fieldNum < fields.length; fieldNum++) { Field field = fields[fieldNum]; Type genericType = field.getGenericType(); List<?> list = null; if (flag) { try { list = (List<?>) field.get(model); } catch (IllegalAccessException e) { e.printStackTrace(); throw new BasicException(BasicCode.FAIL.code, "excel导出异常"); } } ParameterizedType pt = (ParameterizedType) genericType; //得到泛型里的class类型对象 Class<?> sheetClazz = (Class<?>) pt.getActualTypeArguments()[0]; String sheetName = field.getAnnotation(ExcelSheet.class).sheetName(); createSheet(workbook, titleCellStyle, sheetName, list, sheetClazz, flag); } //将文件输出 OutputStream outputStream = null; try { outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return true; } catch (IOException e) { log.error("excel导出异常!{}", e); throw new BasicException(BasicCode.FAIL.code, "excel导出异常"); } } /** * excel 导出 （对象） * * @param excelName excel 名称 * @param list 数据集 * @param clazz 反射clazz * @param type excel 类型 * @param response 使用response可以导出到浏览器 * @param flag true：数据导出 false：模版导出 * @param <T> * @return */ private static <T> Boolean export(String excelName, List<T> list, Class<T> clazz, ExcelType type, HttpServletResponse response, boolean flag) { if (flag) { // 非模版导出，判断数据是否为空！ if (list == null || list.size() == 0) { log.error("【excel导出】{}", "excel导出数据空异常！"); return false; } } // 设置默认文件名为当前时间：年月日时分秒 if (StringUtils.isEmpty(excelName)) { log.info("【excel导出】{}", "excel导出未设置文件名，默认使用时间戳代替！"); excelName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); } createResponse(excelName, response, type); Workbook workbook = getWorkbook(type); CellStyle titleCellStyle = getTitleCellStyle(workbook); createSheet(workbook, titleCellStyle, excelName, list, clazz, flag); try { //将文件输出 OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return true; } catch (IOException e) { e.printStackTrace(); throw new BasicException(BasicCode.FAIL.code, "excel导出异常"); } } /** * 创建excel工作簿 * * @param workbook 工作簿 * @param sheetName sheet 名称 * @param list 数据集 * @param sheetClazz 反射clazz * @param flag true：数据导出 false：模版导出 */ private static void createSheet(Workbook workbook, CellStyle titleCellStyle, String sheetName, List<?> list, Class<?> sheetClazz, boolean flag) { Field[] fields = getSortFieldsExcelExport(sheetClazz); // 创建一个工作表sheet 默认是表名是sheet0 Sheet sheet = workbook.createSheet(sheetName); setWorkBook(workbook, titleCellStyle, sheet, fields, flag); // CellStyle 缓存 Map<String, CellStyle> hashMap = new HashMap<>(8); try { if (flag) { // 开始生成excel for (int rowIndex = 1; rowIndex <= list.size(); rowIndex++) { Object obj = list.get(rowIndex - 1); Field[] sortFields = getSortFieldsExcelExport(obj.getClass()); //创建第 rowIndex 行） Row row = sheet.createRow(rowIndex); for (int i = 0; i < sortFields.length; i++) { Field field = sortFields[i]; if (!field.isAccessible()) { field.setAccessible(true); } Object object = new PropertyDescriptor(field.getName(), sheetClazz).getReadMethod().invoke(obj); if (!field.getAnnotation(ExcelExport.class).empty() && object == null) { log.error("【excel导出】class映射地址：{},空指针参数：{},{}", sheetClazz.getCanonicalName(), field.getName(), "数据集空指针"); throw new BasicException(BasicCode.FAIL.code, "【excel导出】class映射地址：" + sheetClazz.getCanonicalName() + ",空指针参数：" + field.getName() + ",数据集空指针"); } setValue(getCell(workbook, hashMap, row, i, object, field), object, field); } } } } catch (Exception e) { log.error("【excel导出】sheetName:{}, sheetClazz:{}, e:{}", sheetName, sheetClazz, e); e.printStackTrace(); throw new BasicException(BasicCode.FAIL.code, "excel导出异常"); } } // ---------------------------------------------- excel 工具 --------------------------------------------- /** * 设置表格内容的值 * * @param cell 单元格对象 * @param value 单元格的值 */ private static void setValue(Cell cell, Object value, Field field) { if (value == null || "".equals(value)) { return; } else if (value instanceof String) { cell.setCellValue(value.toString()); } else if (value instanceof Integer || value instanceof Double || value instanceof Float || value instanceof Long || value instanceof Short || value instanceof BigDecimal) { if (field.getAnnotation(ExcelExport.class).cellType().isMoney()) { // 判断类型 BigDecimal bi1 = new BigDecimal(value.toString()); int scale = field.getAnnotation(ExcelExport.class).cellType().decimalType().getScale(); cell.setCellValue(bi1.setScale(scale, BigDecimal.ROUND_HALF_UP).toString()); } else { cell.setCellValue(value.toString()); } } else if (value instanceof Date) { SimpleDateFormat sdf = new SimpleDateFormat( field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType()); cell.setCellValue(sdf.format((Date) value)); } } /** * 设置excel单元格样式 * * @param workbook * @param hashMap * @param row * @param num * @param value * @param field * @return */ private static Cell getCell(Workbook workbook, Map<String, CellStyle> hashMap, Row row, int num, Object value, Field field) { CellStyle cellStyle; // 获取指定单元格 Cell cell = row.createCell(num); // 设置类型 DataFormat format = workbook.createDataFormat(); if (value instanceof Integer || value instanceof Double || value instanceof Float || value instanceof Long || value instanceof Short || value instanceof BigDecimal) { if (field.getAnnotation(ExcelExport.class).cellType().isMoney()) { cellStyle = hashMap.get( field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal()); if (cellStyle == null) { cellStyle = getCellStyle(workbook); cellStyle.setDataFormat(format.getFormat( field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal())); hashMap.put(field.getAnnotation(ExcelExport.class).cellType().decimalType().getDecimal(), cellStyle); } } else { cellStyle = hashMap.get("@"); if (cellStyle == null) { cellStyle = getCellStyle(workbook); cellStyle.setDataFormat(format.getFormat("@")); hashMap.put("@", cellStyle); } } } else if (value instanceof Date) { cellStyle = hashMap.get(field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType()); if (cellStyle == null) { cellStyle = getCellStyle(workbook); cellStyle.setDataFormat(format.getFormat( field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType())); hashMap.put(field.getAnnotation(ExcelExport.class).cellType().timeType().getTimeType(), cellStyle); } } else { cellStyle = hashMap.get("@"); if (cellStyle == null) { cellStyle = getCellStyle(workbook); cellStyle.setDataFormat(format.getFormat("@")); hashMap.put("@", cellStyle); } } cell.setCellStyle(cellStyle); return cell; } /** * 设置excel 样式 （第一行格式） * * @param workbook * @param cellStyle * @param sheet * @param fields * @param flag true：数据导出 false：模版导出 */ private static void setWorkBook(Workbook workbook, CellStyle cellStyle, Sheet sheet, Field[] fields, boolean flag) { //写入excel的表头（创建第一行） Row row = sheet.createRow(0); // 设置类型 DataFormat format = workbook.createDataFormat(); // 设置列宽、表头、数据类型 for (int i = 0; i < fields.length; i++) { if (!fields[i].getAnnotation(ExcelExport.class).template() && !flag) { continue; } //设置宽度 sheet.setColumnWidth(i, fields[i].getAnnotation(ExcelExport.class).titleSize() * 256); //创建第一行 Cell cell = row.createCell(i); //设置表头名称 cell.setCellValue(fields[i].getAnnotation(ExcelExport.class).titleName()); cell.setCellStyle(cellStyle); cellStyle.setDataFormat(format.getFormat("@")); //sheet.setDefaultColumnStyle(i, cellStyle); } } /** * 初始化样式属性 * * @param workbook * @return */ private static CellStyle getTitleCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); // 设置对齐方式为居中对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置自动换行 cellStyle.setWrapText(true); // 设置单元格内容垂直对其方式为居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置字体 Font font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 14); font.setBold(true); cellStyle.setFont(font); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); return cellStyle; } /** * 初始化样式属性 * * @param workbook * @return */ private static CellStyle getCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); // 设置对齐方式为居中对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置自动换行 cellStyle.setWrapText(true); // 设置单元格内容垂直对其方式为居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置字体 Font font = workbook.createFont(); font.setFontName("宋体"); cellStyle.setFont(font); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); return cellStyle; } /** * 创建excel 导出 response信息 * * @param excelName * @param response */ private static void createResponse(String excelName, HttpServletResponse response, ExcelType type) { // 设置response头信息 // response.reset(); // 改成输出excel文件 response.setContentType("application/vnd.ms-excel"); response.setHeader("Access-Control-Expose-Headers", "Content-disposition"); try { switch (type) { case XLS: response.setHeader("Content-disposition", "attachment; filename=" + new String(URLEncoder.encode(excelName, "UTF-8").getBytes("UTF-8"), "ISO8859-1") + ".xls"); break; case XLS_X: response.setHeader("Content-disposition", "attachment; filename=" + new String(URLEncoder.encode(excelName, "UTF-8").getBytes("UTF-8"), "ISO8859-1") + ".xlsx"); break; default: log.error("【excel导出】{}", "excel类型错误，只支持xls与xlsx！"); throw new BasicException(BasicCode.FAIL.code, "【excel导出】excel类型错误，只支持xls与xlsx！"); } } catch (UnsupportedEncodingException e) { log.error("【excel导出】{}", "设置response信息异常！"); throw new BasicException(BasicCode.FAIL.code, "【excel导出】设置response信息异常！"); } } /** * 根据实体类型 赋值数据 * * @param field * @param newInstance * @param value * @param <T> */ private static <T> void createBean(Field field, T newInstance, Object value) { if (!field.isAccessible()) { field.setAccessible(true); } try { if (value == null) { field.set(newInstance, null); } else if (Long.class.equals(field.getType())) { field.set(newInstance, Long.valueOf(String.valueOf(value))); } else if (String.class.equals(field.getType())) { field.set(newInstance, String.valueOf(value)); } else if (Integer.class.equals(field.getType())) { field.set(newInstance, Integer.valueOf(String.valueOf(value))); } else if (Date.class.equals(field.getType())) { SimpleDateFormat sdf = new SimpleDateFormat( field.getAnnotation(ExcelImport.class).cellType().timeType().getTimeType()); if (value instanceof Date) { field.set(newInstance, sdf.parse(sdf.format(value))); } else { field.set(newInstance, sdf.parse(value.toString())); } } else if (Boolean.class.equals(field.getType())) { field.set(newInstance, (Boolean) value); } else if (Double.class.equals(field.getType())) { field.set(newInstance, Double.valueOf(String.valueOf(value))); } else if (Float.class.equals(field.getType())) { field.set(newInstance, Float.valueOf(String.valueOf(value))); } else if (BigDecimal.class.equals(field.getType())) { field.set(newInstance, new BigDecimal(String.valueOf(value))); } else { field.set(newInstance, value); } } catch (Exception e) { log.error("【excel导入】excel实体转换异常！字段【{}】,值（{}）, {}, {}", field.getAnnotation(ExcelImport.class).titleName(), value, newInstance, e); throw new BasicException(BasicCode.FAIL.code, "【excel导入】excel实体转换异常！ 字段【" + field.getAnnotation(ExcelImport.class).titleName() + "】，值（" + value + "）"); } } /** * 实体判空，注解判空 * * @param clazz * @return */ private static Field[] getSortFieldsImport(Class clazz) { //获取对象总数量 Field[] fields = clazz.getDeclaredFields(); if (fields == null || fields.length == 0) { log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！"); throw new BasicException(BasicCode.FAIL.code, BEAN_ERROR); } List<Field> list = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(ExcelImport.class)) { list.add(field); } } fields = new Field[list.size()]; int num = 0; for (Field field : list) { fields[num++] = field; } Arrays.sort(fields, (field, fieldAfter) -> { return field.getAnnotation(ExcelImport.class).column() - fieldAfter.getAnnotation(ExcelImport.class).column(); }); return fields; } /** * 实体判空，注解判空 * * @param clazz * @return */ private static Field[] getSortFieldsExcelExport(Class clazz) { //获取对象总数量 Field[] fields = clazz.getDeclaredFields(); if (fields == null || fields.length == 0) { log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！"); throw new BasicException(BasicCode.FAIL.code, BEAN_ERROR); } List<Field> list = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(ExcelExport.class)) { list.add(field); } } fields = new Field[list.size()]; int num = 0; for (Field field : list) { fields[num++] = field; } Arrays.sort(fields, (field, fieldAfter) -> { return field.getAnnotation(ExcelExport.class).order() - fieldAfter.getAnnotation(ExcelExport.class).order(); }); return fields; } /** * 实体判空，注解判空 * * @param clazz * @return */ private static Field[] getSortFieldsByExcelSheet(Class clazz) { //获取对象总数量 Field[] fields = clazz.getDeclaredFields(); if (fields == null || fields.length == 0) { log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "实体空异常！"); throw new BasicException(BasicCode.FAIL.code, BEAN_ERROR); } List<Field> list = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(ExcelSheet.class)) { list.add(field); } } fields = new Field[list.size()]; int num = 0; for (Field field : list) { fields[num++] = field; } Arrays.sort(fields, (field, fieldAfter) -> { return field.getAnnotation(ExcelSheet.class).sheetNum() - fieldAfter.getAnnotation(ExcelSheet.class).sheetNum(); }); return fields; } /** * 列转化值 * * @param cell 列值 * @throws IOException */ private static Object getCellValue(Cell cell) { if (cell == null) { return null; } Object cellValue; // 把数字当成String来读，避免出现1读成1.0的情况 // 判断数据的类型 switch (cell.getCellTypeEnum()) { case NUMERIC: if (DateUtil.isValidExcelDate(cell.getNumericCellValue())) { CellStyle style = cell.getCellStyle(); if (style == null) { return false; } int formatIndex = style.getDataFormat(); String formatString = style.getDataFormatString(); boolean isDate = DateUtil.isADateFormat(formatIndex, formatString); if (isDate) { Date date = cell.getDateCellValue(); return date; } } if ((long) cell.getNumericCellValue() != cell.getNumericCellValue()) { // double 类型 cellValue = new BigDecimal(String.valueOf(cell.getNumericCellValue())); } else { cellValue = (long)cell.getNumericCellValue(); } break; // 字符串 case STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; // Boolean case BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; // 公式 case FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; // 空值 case BLANK: cellValue = null; break; // 故障 case ERROR: cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } /** * 创建工作簿 * * @param type * @return */ private static Workbook getWorkbook(ExcelType type) { Workbook workbook = null; switch (type) { case XLS: workbook = new HSSFWorkbook(); break; case XLS_X: workbook = new XSSFWorkbook(); break; default: log.error("【excel导出】{}", "excel类型错误，只支持xls与xlsx！"); throw new BasicException(BasicCode.FAIL.code, "【excel导出】excel类型错误，只支持xls与xlsx！"); } return workbook; } /** * 由文件生成 poi Workbook * * @param file * @return */ private static Workbook getWorkBook(MultipartFile file) { // 获得文件名 String fileName = file.getOriginalFilename(); // 创建Workbook工作薄对象，表示整个excel Workbook workbook = null; // 获取excel文件的io流 InputStream is = null; try { is = file.getInputStream(); // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith(XLS)) { // 2003 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(XLS_X)) { // 2007 workbook = new XSSFWorkbook(is); } } catch (IOException e) { throw new BasicException(BasicCode.FAIL.code, "excel 转换 HSSFWorkbook 异常！"); } return workbook; } /** * 检查文件 * * @param file * @throws IOException */ private static void checkFile(MultipartFile file) { // 判断文件是否存在 if (null == file) { throw new BasicException(BasicCode.FAIL.code, FILE_NOT_ERROR); } // 获得文件名 String fileName = file.getOriginalFilename(); // 判断文件是否是excel文件 if (!fileName.endsWith(XLS) && !fileName.endsWith(XLS_X)) { log.error(fileName + "不是excel文件"); throw new BasicException(BasicCode.FAIL.code, fileName + "不是excel文件"); } } /** * 初始化实体 * * @param clazz * @param <T> * @return */ private static <T> Object getNewInstance(Class<T> clazz) { try { return clazz.newInstance(); } catch (IllegalAccessException e) { log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！"); throw new BasicException(BasicCode.FAIL.code, "excel导入异常"); } catch (InstantiationException e) { log.error("【excel导入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel导入异常！"); throw new BasicException(BasicCode.FAIL.code, "excel导入异常"); } } }
-
-
下面就是调用了：
-
ImpExpTemplateController
package com.hahashujia.controller; import com.hahashujia.basic.excel.exmple.ImpExpTemplateMultiple; import com.hahashujia.basic.excel.utils.ExcelUtil; import com.hahashujia.basic.excel.enums.ExcelType; import com.hahashujia.basic.excel.exmple.ImpExpTemplateSingle; import com.hahashujia.common.response.ResultModel; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.math.BigDecimal; import java.time.LocalDateTime; import java.time.ZoneOffset; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author hahashujia * @describe * @createTime 2019-11-01 */ @RestController @RequestMapping("/imp-exp-template/") @Api(value = "导入导出模板", description = "导入导出模板") public class ImpExpTemplateController { @ApiOperation(value = "导入模板下载(单一数据)", notes = "导入模板下载(单一数据)") @GetMapping(value = "single/exp/template") public void singleExpTemplate(HttpServletResponse response) { String fileName = "导入模板下载(单一数据)"; ExcelUtil.exportTemplate(fileName, ImpExpTemplateSingle.class, ExcelType.XLS, response); } @ApiOperation(value = "数据导出(单一数据)", notes = "数据导出(单一数据)") @GetMapping(value = "single/exp/data") public void singleExpData(HttpServletResponse response) { List<ImpExpTemplateSingle> list = new ArrayList<>(); list.add(getImpExpTemplateModel(1L)); list.add(getImpExpTemplateModel(2L)); list.add(getImpExpTemplateModel(3L)); list.add(getImpExpTemplateModel(4L)); list.add(getImpExpTemplateModel(5L)); list.add(getImpExpTemplateModel(6L)); list.add(getImpExpTemplateModel(7L)); list.add(getImpExpTemplateModel(8L)); list.add(getImpExpTemplateModel(9L)); list.add(getImpExpTemplateModel(10L)); String fileName = "数据导出-" + LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8")); ExcelUtil.exportExcel(fileName, list, ImpExpTemplateSingle.class, ExcelType.XLS, response); } @ApiOperation(value = "数据导入(单一数据)", notes = "数据导入(单一数据)") @PostMapping(value = "single/imp/data") public ResponseEntity<ResultModel> singleImpData(MultipartFile file) { List<ImpExpTemplateSingle> list = ExcelUtil.importExcel(file, ImpExpTemplateSingle.class); System.err.println(list.toString()); return ResponseEntity.ok(ResultModel.ok(null)); } @ApiOperation(value = "导入模板下载(多种数据)", notes = "导入模板下载(多种数据)") @GetMapping(value = "multiple/exp/template") public void multipleExpTemplate(HttpServletResponse response) { String fileName = "导入模板下载(单一数据)"; ExcelUtil.exportMultipleTemplate(fileName, ImpExpTemplateMultiple.class, response); } @ApiOperation(value = "数据导入(多种数据)", notes = "数据导入(多种数据)") @PostMapping(value = "multiple/imp/data") public ResponseEntity<ResultModel> multipleImpData(MultipartFile file) { ImpExpTemplateMultiple multiple = ExcelUtil.importMultiple(file, ImpExpTemplateMultiple.class); System.err.println(multiple.toString()); return ResponseEntity.ok(ResultModel.ok(null)); } @ApiOperation(value = "数据导出(多种数据)", notes = "数据导出(多种数据)") @GetMapping(value = "multiple/exp/data") public void multipleExpData(HttpServletResponse response) { List<ImpExpTemplateSingle> list = new ArrayList<>(); list.add(getImpExpTemplateModel(1L)); list.add(getImpExpTemplateModel(2L)); list.add(getImpExpTemplateModel(3L)); list.add(getImpExpTemplateModel(4L)); list.add(getImpExpTemplateModel(5L)); list.add(getImpExpTemplateModel(6L)); list.add(getImpExpTemplateModel(7L)); list.add(getImpExpTemplateModel(8L)); list.add(getImpExpTemplateModel(9L)); list.add(getImpExpTemplateModel(10L)); ImpExpTemplateMultiple multiple = ImpExpTemplateMultiple.builder() .singleList(list) .templateSingleList(list) .build(); String fileName = "数据导出-" + LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8")); ExcelUtil.exportMultiple(fileName, multiple, ImpExpTemplateMultiple.class, response); } private ImpExpTemplateSingle getImpExpTemplateModel(Long id) { ImpExpTemplateSingle model = ImpExpTemplateSingle.builder() .id(id) .type("字符串") .number(Math.random() * 10) .amount(new BigDecimal(200.5)) .date(new Date()) .error("错误信息") .build(); System.err.println(model.toString()); return model; } }
-
扫一扫
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
