如何设计一个以注解形式对EXCEL进行导入和导出的工具类

一个自己写的简单导入导出工具类,客户端请根据情况调整stream输出
1、注解类（导入）

import java.lang.annotation.*;

/**
 * 特殊性Excel文件头读取/请使用ccys/../Util同包下的工具
 * @author mars_q
 * @date 2020/7/6 11:29
 * create by 2012692013@qq.com
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
public @interface ExcelImporter {
    /**
     * 表名
     * @return
     */
    String value() default "";

    /**
     * 工作表名 没有的时候默认取sheet(0)
     * @return
     */
    String sheetName() default "";

    /**
     * 标题行
     * @return
     */
    int titleRow() default 0;

    /**
     * 起始行
     * @return
     */
    int startRow() default 1;

    /**
     * 描述
     * @return
     */
    String desc() default "";
}

import java.lang.annotation.*;

/**
 * 特殊性Excel文件列读取/请使用ccys/../Util同包下的工具
 * @author mars_q
 * @date 2020/7/6 11:29
 * create by 2012692013@qq.com
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelCellImporter {

    /**
     * 列名称
     * @return
     */
    String value() default "";

    /**
     * 描述
     * @return
     */
    String desc() default "";

    /**
     * 是否必须
     * @return
     */
    boolean required() default true;

    /**
     * 需要转换解释的值
     * @return
     */
    ExcelTrans[] trans() default {};
}

2、注解类（导出）

import java.lang.annotation.*;

/**
 * excel文件导出/请使用ccys/../Util同包下的工具
 * @author mars_q
 * @date 2020/7/10 11:29
 * create by 2012692013@qq.com
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
public @interface ExcelExporter {
    /**
     * 表名
     * @return
     */
    String value() default "";

    /**
     * 工作表名 没有的时候默认工作表名
     * @return
     */
    String sheet() default "";

    /**
     * 描述
     * @return
     */
    String desc() default "";
}

import java.lang.annotation.*;

/**
 * excel文件导出/请使用ccys/../Util同包下的工具
 * @author mars_q
 * @date 2020/7/10 11:29
 * create by 2012692013@qq.com
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelCellExporter {

    /**
     * 列名称
     * @return
     */
    String value() default "";

    /**
     * 排序
     * @return
     */
    int order() default 0;

    /**
     * 描述
     * @return
     */
    String desc() default "";

    /**
     * 需要转换解释的值
     * @return
     */
    ExcelTrans[] trans() default {};
}

3、解释器注解类

import java.lang.annotation.*;

/**
 * Excel文件->bean/bean->文件 值相互转换/请使用ccys/../Util同包下的工具
 * @author mars_q
 * @date 2020/7/6 11:29
 * create by 2012692013@qq.com
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelTrans {

    /**
     * 当前类的值
     * @return
     */
    String bean() default "";

    /**
     * 描述
     * @return
     */
    String excel() default "";
}

4、导入|导出工具类

//这里可自行定义一个excelException进行错误抛出
import com.demo.util.base.exception.ExcelException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;

public class ExcelImport {
    public ExcelImport() {
    }
    /**
     * 读EXCEL文件，获取信息集合
     *
     * @param mFile
     * @return
     */
    public static <T> List<T> getExcelInfo(MultipartFile mFile,Class<T> clazz) {
        String fileName = mFile.getOriginalFilename();// 获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName) || isExcelCsv(fileName)) {
                isExcel2003 = false;
            }
            return createExcel(mFile.getInputStream(), isExcel2003,clazz);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is          输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    private static  <T> List<T> createExcel(InputStream is, boolean isExcel2003,Class<T> clazz) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb,clazz);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel
     *
     * @param wb
     * @return
     */
    private static  <T> List<T> readExcelValue(Workbook wb,Class<T> clazz) {
        try {
            ExcelImporter excelReader = clazz.getAnnotation(ExcelImporter.class);
//            // 预留于多表文件上传
//            String value = excelReader.value();
            String sheetName = excelReader.sheetName();
            int titleNum = excelReader.titleRow();
            int startNum = excelReader.startRow();
            Sheet sheet = null;
            if (StringUtils.isBlank(sheetName))
                sheet = wb.getSheetAt(0);
            else
                sheet = wb.getSheet(sheetName);
            // 得到Excel的行数
            int totalRows = sheet.getPhysicalNumberOfRows();

            int totalCells = 0;
            // 得到Excel的列数(前提是有行数)
            if (totalRows > 1 && sheet.getRow(titleNum) != null) {
                totalCells = sheet.getRow(titleNum).getLastCellNum();
            }
            Row titleRow = sheet.getRow(titleNum);
            // 解析表头
            Map<String, Integer> titleMap = new HashMap<>();
            for (int i = 0; i < titleRow.getLastCellNum(); i++) {
                Cell cell = titleRow.getCell(i);
                if (cell == null || StringUtils.isBlank(cell.getStringCellValue()))
                    continue;
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.trim();
                titleMap.put(cellValue, i);
            }
            // 解析实体类
            Map<String, String> beanMap = new HashMap<>();
            // 需要解析的列
            List<Integer> parseCell = new ArrayList<>();
            // 解析的节点
            Map<Integer, String> parseBean = new HashMap<>();
            // 翻译节点
            Map<String, Map<String,String>> transMap = new HashMap<>();
            Field[] declaredFields = clazz.getDeclaredFields();
            for (int i = 0; i < declaredFields.length; i++) {
                Field field = declaredFields[i];
                boolean present = field.isAnnotationPresent(ExcelCellImporter.class);
                if (present) {
                    ExcelCellImporter reader = field.getAnnotation(ExcelCellImporter.class);
                    String title = reader.value();
                    if (titleMap.get(title) == null && reader.required()) {
                        System.err.println("Excel内找不到约定对应字段:" + title + "\r\n\tfrom:" + clazz.getName());
                        //这里可自行定义一个excelException进行错误抛出
                        throw new ExcelException("Excel内找不到约定对应字段:" + title);
                    }
                    beanMap.put(title, field.getName());
                    parseCell.add(titleMap.get(title));
                    parseBean.put(titleMap.get(title), field.getName());
                    // 加入解析器
                    ExcelTrans[] trans = reader.trans();
                    if (trans != null && trans.length > 0) {
                        Map<String, String> propertyTransMap = new HashMap<>();
                        for (int j = 0; j < trans.length; j++) {
                            ExcelTrans tran = trans[j];
                            String bean = tran.bean();
                            String excel = tran.excel();
                            propertyTransMap.put(excel, bean);
                        }
                        transMap.put(field.getName(),propertyTransMap);
                    }
                } else continue;
            }


            List<T> exportList = new ArrayList<>();

            if (parseCell.size() < 1)
                return null;
            // 循环Excel行数
            for (int r = startNum; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                T instance = clazz.newInstance();
                //有效数据行
                int validData = 0;
                for (int c = 0; c < parseCell.size(); c++) {
                    Integer cellNum = parseCell.get(c);
                    if (cellNum == null)
                        continue;
                    Cell cell = row.getCell(cellNum);
                    if (cell == null || cell.toString().trim().equals(""))
                        continue;
                    validData++;
                    cell.setCellType(CellType.STRING);
                    Field field = clazz.getDeclaredField(parseBean.get(cellNum));
                    Class<?> type = field.getType();
                    Object value = cell.getStringCellValue().trim();
                    Map<String, String> valueTransMap = transMap.get(field.getName());
                    if (valueTransMap != null && valueTransMap.get(value) != null)
                        value = valueTransMap.get(value);

                    if (type.equals(String.class))
                        value = String.valueOf(value);
                    if (type.equals(Integer.class))
                        value = Integer.valueOf(new Double(value.toString()).intValue());
                    if (type.equals(BigDecimal.class))
                        value = new BigDecimal(value.toString());
                    if (type.equals(Date.class))
                        value = new BigDecimal(String.valueOf(value));
                    if (type.equals(Double.class))
                        value = new Double(String.valueOf(value));
                    if (type.equals(Float.class))
                        value = new Float(String.valueOf(value));

                    field.setAccessible(true);
                    field.set(instance,value);
                    field.setAccessible(false);
                }
                if (validData > 0) {
                    if (instance instanceof ExcelImportReader) {
                        Method reconstruct = clazz.getDeclaredMethod("reconstruct", Object.class);
                        Object invoke = reconstruct.invoke(instance,instance);
                        instance = (T) invoke;
                    }
                    if (instance != null)
                    	exportList.add(instance);
                }
            }
            return exportList;
        }catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return new ArrayList<T>();
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    private static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath) || isExcelCsv(filePath))) {
            //文件名不是excel格式
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    private static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    private static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    // @描述：是否是csv文件
    private static boolean isExcelCsv(String filePath) {
        return filePath.matches("^.+\\.(?i)(csv)$");
    }
}

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author mars_q
 * @date 2019/5/6 14:25
 * create by 2012692013@qq.com
 */
public class ExcelExport {

    private static String FILE_TYPE = ".xlsx";

    public static <T> void export(HttpServletResponse response, List<T> data) {
        if (data == null || data.isEmpty())
            return;
        //获取模板
        Class<?> clazz = data.get(0).getClass();
        //只支持模板型创建
        if (!clazz.isAnnotationPresent(ExcelExporter.class))
            return;
        ExcelExporter exporter = clazz.getAnnotation(ExcelExporter.class);
        String fileSimpleName = StringUtils.isNotBlank(exporter.value())?exporter.value():clazz.getSimpleName();
        String fileName = fileSimpleName + FILE_TYPE;
        String sheetName = fileSimpleName;

        Field[] fields = clazz.getDeclaredFields();
        // 排序列
        List<ExportCellOrder> exportCellOrders = new ArrayList<>();
        // 翻译节点
        Map<String, Map<String,String>> transMap = new HashMap<>();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            if (!field.isAnnotationPresent(ExcelCellExporter.class))
                continue;
            ExcelCellExporter cellExporter = field.getAnnotation(ExcelCellExporter.class);
            int order = cellExporter.order();
            String cellName = cellExporter.value();
            String property = field.getName();
            exportCellOrders.add(new ExportCellOrder() {{
                setOrder(order);
                setCellName(cellName);
                setProperty(property);
            }});
            // 加入解析器
            ExcelTrans[] trans = cellExporter.trans();
            if (trans != null && trans.length > 0) {
                Map<String, String> propertyTransMap = new HashMap<>();
                for (int j = 0; j < trans.length; j++) {
                    ExcelTrans tran = trans[j];
                    String bean = tran.bean();
                    String excel = tran.excel();
                    propertyTransMap.put(bean, excel);
                }
                transMap.put(field.getName(),propertyTransMap);
            }
        }
        Collections.sort(exportCellOrders);

        HSSFWorkbook workbook = create(response, sheetName, exportCellOrders, data, clazz,transMap);
        try {
            buildExcelFile(fileName, workbook);
            buildExcelDocument(fileName,workbook,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static<T> HSSFWorkbook create(HttpServletResponse response,
                                          String sheetName,
                                          List<ExportCellOrder> cells,
                                          List<T> data,
                                          Class<?> clazz,
                                          Map<String, Map<String,String>> transMap) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd HH:mm:ss"));
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        HSSFCell cell;

        for (int i = 0; i < cells.size(); i++) {
            ExportCellOrder cellOrder = cells.get(i);
//            余两个字符使居中列头不挤压
            sheet.setColumnWidth(i, (cellOrder.getCellName().length() + 2) * 512);
            cell = row.createCell(i);
            cell.setCellValue(cellOrder.getCellName());
            cell.setCellStyle(style);
        }

        for (int i = 0; i < data.size(); i++) {
            HSSFRow dataRow = sheet.createRow(i + 1);
            T t = data.get(i);
            for (int j = 0; j < cells.size(); j++) {
                ExportCellOrder c = cells.get(j);
                Field field = null;
                try {
                    String property = c.getProperty();
                    field = clazz.getDeclaredField(property);
                    Class<?> type = field.getType();
                    field.setAccessible(true);
                    Object o = field.get(t);
                    field.setAccessible(false);
                    HSSFCell rowCell = dataRow.createCell(j);
                    if (!type.equals(Date.class)) {
                        Map<String, String> valueTransMap = transMap.get(field.getName());
                        if (valueTransMap != null && valueTransMap.get(o) != null)
                            o = valueTransMap.get(o);
                        rowCell.setCellValue(o == null ? null : o.toString());
                    } else {
                        rowCell.setCellValue(o == null ? null : new SimpleDateFormat().format(((Date) o)));
                    }
                } catch (Exception e) {
                    if (field != null)
                        field.setAccessible(false);
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }


    //生成excel文件
    private static void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel
    private static void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    private static class ExportCellOrder implements Comparable<ExportCellOrder>{
        private int order;
        private String property;
        private String cellName;

        public ExportCellOrder() {
        }

        public int getOrder() {
            return order;
        }

        public void setOrder(int order) {
            this.order = order;
        }

        public String getProperty() {
            return property;
        }

        public void setProperty(String property) {
            this.property = property;
        }

        public String getCellName() {
            return cellName;
        }

        public void setCellName(String cellName) {
            this.cellName = cellName;
        }

        @Override
        public int compareTo(ExportCellOrder o) {
            int cop = order - o.getOrder();
            if (cop != 0)
                return cop;
            else
                return property.compareTo(o.property);
        }

        @Override
        public String toString() {
            return "ExportCellOrder{" +
                    "order=" + order +
                    ", property='" + property + '\'' +
                    ", cellName='" + cellName + '\'' +
                    '}';
        }
    }
}

5、简单使用示例（导入和注解内其他参数可自行查看）

	@ExcelExporter("金币导出表")
	public class Point{
		@ExcelCellExporter("金币数量")
	    private Integer num;
	    @ExcelCellExporter(value="金币种类",required=false,trans = {
	            @ExcelTrans(bean = "0",excel = "普通金币"),
	            @ExcelTrans(bean = "1",excel = "高级金币"),
	            @ExcelTrans(bean = "2",excel = "钻石金币"),
	    })
	    private Integer type;
	    
    @GetMapping("/export")
    @ApiOperation("导出")
    public void export(HttpServletResponse response,@ApiIgnore PointDto pointDto) {
        List<Point> listByLimit = pointService.findListByLimit(Point.class, new ArrayList<String>() {{
            if (pointDto.getState() != null)
                add("state = '" + pointDto.getState() + "'");
        }}, CodeEnum.DESC.msgOf());
        ExcelExport.export(response,listByLimit);
    }
}

@ExcelImporter("金币")
//@ExcelImporter(sheetName="金币")
//@ExcelImporter(sheetName="金币",titleRow=2,startRow=10)
public class Point{
	@ExcelCellImporter("金币数量")
    private Integer num;
    @ExcelCellImporter(value="金币种类",required=false,trans = {
            @ExcelTrans(bean = "0",excel = "普通金币"),
            @ExcelTrans(bean = "1",excel = "高级金币"),
            @ExcelTrans(bean = "2",excel = "钻石金币"),
    })
    private Integer type;
    
    @PostMapping("/import")
    @ApiOperation("导入")
    public void export(HttpServletRequest request,MultipartFile multipartFile) {
          List<Point> excelInfo = ExcelImport.getExcelInfo(multipartFile, Point.class);
    }
}

以上，如需要自行CV一下， 路过点个赞谢谢，写的不是细，各位大佬见谅

---

2020-08-13
新增接口：ExcelImportReader
解释：当实现该接口内reconstruct方法，在调用excel读取工具进行读取时，会自动调用此方法，以减少一次数据取出时的再处理操作。

/**
 * EXCEL读取数据处理接口
 * @author mars_q
 * @Description
 * @e-mail: 2012692013@qq.com
 * @date 2020/8/13 001314:23
 */
public interface ExcelImportReader {
    /**
     * 实现此方法以在读取过程中对instance进行数据处理
     * 如果实体类内进行了autowired,那么请从spring上下文中获取
     * @param o
     * @return
     */
    Object reconstruct(Object o);
}

另外，如果你在上面方法内调用了spring注入的bean，那么建议用以下工具获取bean，因为instance是被new声明的，无法注入对应bean

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @author mars_q
 * @Description
 * @e-mail: 2012692013@qq.com
 * @date 2020/8/13 001315:16
 */
@Component
public class SpringUtils implements ApplicationContextAware {

    private static ApplicationContext applicationContext = null;

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if (SpringUtils.applicationContext == null) {
            SpringUtils.applicationContext = applicationContext;
        }
    }

    /**
     * 获取对应Bean
     */
    public static Object getBean(String name) {
        return getApplicationContext().getBean(name);
    }
}