excel上传/下载使用注解方式,非常实用的一个demo工程

最新推荐文章于 2024-08-01 18:45:00 发布

原创最新推荐文章于 2024-08-01 18:45:00 发布 · 319 阅读

0 ·

CC 4.0 BY-SA版权

转载请注明原创

文章标签：

#excel导入导出注解

Java 专栏收录该内容

73 篇文章

订阅专栏

该博客介绍了一个通过注解方式处理Excel导入导出的实用示例工程。首先，在项目中引入了Apache POI依赖。接着，定义了ExcelField、ExcelCodeToString和ExcelFormat等注解，分别用于标记对象属性、枚举值转换和日期格式化。此外，还提供了一个Excel工具类以及通用工具类和对象复制工具类。最后，展示了如何在Controller中实现查询数据和Excel导入的测试方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在pom添加poi依赖:

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>

完整的依赖如下:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.hf</groupId>
    <artifactId>hantopy</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>hantopy</name>
    <description>汉字和拼音,简繁互转工具实现</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.github.stuxuhai/jpinyin -->
        <dependency>
            <groupId>com.github.stuxuhai</groupId>
            <artifactId>jpinyin</artifactId>
            <version>1.1.8</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.belerweb/pinyin4j -->
        <dependency>
            <groupId>com.belerweb</groupId>
            <artifactId>pinyin4j</artifactId>
            <version>2.5.1</version>
        </dependency>

        <!-- mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.17</version>
        </dependency>
        <!--mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!--poi start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>
        <!--poi start-->

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

定义一些excel注解

ExcelField注解用来表明需要导出的对象的属性数据

package com.hf.hantopy.utils.excel;

import java.lang.annotation.*;

/**
 * @Description:
 * @Date: 2019/3/3
 * @Auther:
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelField {
    /**
     * 列名称
     *
     * @return String
     */
    String name() default "";
}

ExcelCodeToString:用来将一些一些数据对应的枚举值之间的互转

package com.hf.hantopy.utils.excel;

/**
 *  *code转换为对应的中文注释
 *  * @ExcelCodeToString(values={"0:失效","1:正常"})
 * @Date: 2019/3/21
 * @Auther: 
 */

import java.lang.annotation.*;

/**
 *
 * @ExcelCodeToString(values={"0:失效","1:正常"})
 * @author
 *
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelCodeToString {
    /**
     * code 对应的中文注释
     * @ExcelCodeToString(values={"0:失效","1:正常"})
     * @return String[]
     */
    String[] values() ;
}

ExcelFormat注解用来格式化日期的

package com.hf.hantopy.utils.excel;

/**
 * @Description:
 * @Date: 2019/3/21
 * @Auther: wm yu
 */

import java.lang.annotation.*;

/**
 * 列属性信息
 *
 *      支持Java对象数据类型：Date
 *      支持Excel的Cell类型为：String
 *
 * @author 
 * @date  2018年11月26日09:19:13
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelFormat {
    /**
     * 时间格式化，日期类型时生效
     *
     * @return String
     */
    String pattern() default "yyyy-MM-dd HH:mm:ss";
}

excel的工具类:

package com.hf.hantopy.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;

/**
 * @Description: 用于导出数据
 * @Author 
 * @Date 2018年11月26日09:18:44
 */
public class ExcelUtil {

    public static int DEFAULT_COLOUMN_WIDTH = 17;


    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title            标题行
     * @param headerList       属性-列头
     * @param sheetDataListArr 数据集
     * @param colWidth         列宽 默认 至少17个字节
     * @param out              输出流
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public static void exportExcel(String title, List<String> headerList, List<?> sheetDataListArr,
                                   int colWidth, OutputStream out)
            throws IllegalArgumentException, IllegalAccessException {
        // 声明一个工作薄缓存
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        // 表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        Sheet sheet = workbook.createSheet();
        // 设置列宽 至少字节数
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
        if (sheetDataListArr != null && !sheetDataListArr.isEmpty()) {
            Class<?> sheetClass = sheetDataListArr.get(0).getClass();
            Map<String, String> headMap = new LinkedHashMap<>();
            Field[] allFields = CommonUtil.getAllFields(sheetClass);
            if (allFields != null && allFields.length > 0) {
                for (Field field : allFields) {
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        if (headerList != null && headerList.size() != 0) {
                            for (String header : headerList) {
                                if (field.getName().equals(header)) {
                                    headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                                }
                            }
                        } else {
                            headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                        }
                    }
                }
            }

            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] headers = new String[headMap.size()];
            List<Field> fields = new ArrayList<Field>();

            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                String fieldName = iter.next();
                if (allFields != null && allFields.length > 0) {
                    for (Field field : allFields) {
                        if (Modifier.isStatic(field.getModifiers()) || Modifier.isAbstract(field.getModifiers()) || Modifier.isNative(field.getModifiers())
                                || Modifier.isFinal(field.getModifiers())) {
                            continue;
                        }
                        if (field.getName().equals(fieldName)) {
                            fields.add(field);
                        }
                    }
                }
                headers[ii] = headMap.get(fieldName);

                int bytes = fieldName.getBytes().length;
                arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                ii++;
            }


            // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Object obj : sheetDataListArr) {
                if (rowIndex == 65535 || rowIndex == 0) {
                    if (rowIndex != 0) {
                        // 如果数据超过了，则在第二页显示
                        sheet = workbook.createSheet();
                    }
                    // 表头 rowIndex=0
                    Row titleRow = sheet.createRow(0);
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                    // 列头 rowIndex =1
                    Row headerRow = sheet.createRow(1);
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);

                    }
                    // 数据内容从 rowIndex=2开始
                    rowIndex = 2;
                }
                Row rowData = sheet.createRow(rowIndex);
                for (int i = 0; i < fields.size(); i++) {
                    Cell newCell = rowData.createCell(i);
                    Field field = fields.get(i);
                    field.setAccessible(true);
                    Object fieldValue = field.get(obj);
                    String fieldValueString = CommonUtil.formatValue(field, fieldValue);
                    newCell.setCellValue(fieldValueString);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
        }
        // 自动调整宽度
        /*
         * for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }
         */
        try {
            workbook.write(out);
            out.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * cros
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response, HttpServletRequest request) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setHeader("Access-Control-Allow-Methods", "POST,GET,OPTIONS,DELETE,PUT");
            response.setHeader("Access-Control-Max-Age", "3600");
            String originHeader = request.getHeader("Origin");
            response.setHeader("Access-Control-Allow-Origin", originHeader);
            response.setHeader("Access-Control-Allow-Credentials", "true");
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List getData(MultipartFile file) throws IOException {
        if(file==null){
            return new ArrayList();
        }
        String filename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        return doGetData(inputStream, filename);
    }


    /**
     * 解析excel内容,得到list
     * @param inputStream
     * @param fileName
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> doGetData(InputStream inputStream,String fileName) throws IOException {

        Workbook workbook = null;
        //判断excel的两种格式xls,xlsx
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        }
        //得到sheet的总数
        int numberOfSheets = workbook.getNumberOfSheets();
        //第一行的标题 得到标题行map,按照顺序存储
        Map<Integer, String> headersNameMap = new HashMap<>();
        List<Map<String,Object>> objects = new ArrayList<>();

        //循环每一个sheet
        for (int i = 0; i < numberOfSheets; i++) {

            //得到第i个sheet
            Sheet sheet = workbook.getSheetAt(i);
            System.out.println(sheet.getSheetName() + "  sheet");

            //得到行的迭代器
            Iterator<Row> rowIterator = sheet.iterator();

            getHeader(rowIterator, headersNameMap);

            //循环每一行
            while (rowIterator.hasNext()) {
                //存储数据,以单元格名称+值(key-value的形式存储)
                Map<String,Object> dataMap = new HashMap<>();

                //得到一行对象
                Row row = rowIterator.next();

                //得到列对象迭代器(对应行的)
                Iterator<Cell> cellIterator = row.cellIterator();

                int columnCount = 0;

                //循环每一列
                while (cellIterator.hasNext()) {
                    //得到单元格对象
                    Cell cell = cellIterator.next();
                    if (cell.getCellStyle().getDataFormatString().equals("m/d/yy") && cell.getDateCellValue()!=null) {
                        dataMap.put(headersNameMap.get(columnCount), cell.getDateCellValue());
                    } else {
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                dataMap.put(headersNameMap.get(columnCount), cell.getDateCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:
                                dataMap.put(headersNameMap.get(columnCount), cell.getStringCellValue());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                dataMap.put(headersNameMap.get(columnCount), cell.getNumericCellValue());
                                break;
                        }
                    }
                    columnCount++;
                } //end of cell iterator
                if(!CollectionUtils.isEmpty(dataMap)){
                    objects.add(dataMap);
                }
            } //end of rows iterator


        } //end of sheets for loop

        System.out.println("\nread excel successfully...");

        return objects;

    }

    /**
     * 获取
     * @param rowIterator
     * @param headersNameMap
     */
    private static void getHeader(Iterator<Row> rowIterator, Map<Integer, String> headersNameMap) {
        int rowCount = 0;
        for (int i = 0; i < 3; i++) {
            if (rowCount >= 1 && rowCount <= 2) {
                //跳过头名称  提示语
                rowIterator.next();
                continue;
            }
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            int columnCount = 0;

            //循环每一列
            while (cellIterator.hasNext()) {
                //得到单元格对象
                Cell cell = cellIterator.next();
                if (rowCount == 0) {
                    //记录列属性
                    headersNameMap.put(columnCount, cell.getStringCellValue());
                }
                columnCount++;

            } //end of cell iterator
            rowCount++;
        }
    }

}

通用工具类:

package com.hf.hantopy.utils.excel;

import com.hf.hantopy.utils.BeanUtils;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Date: 2019/3/21
 * @Auther: 
 */
public class CommonUtil {


    /**
     * 获取含有ExcelField注解的所有字段名
     * @param sheetClass
     */
    public static void getAnnoFields(Class<?> sheetClass, List<String> headList){
        if(null != sheetClass){
            Field[] fields = sheetClass.getDeclaredFields();
            Arrays.stream(fields).filter((Field field) -> field.isAnnotationPresent(ExcelField.class)).forEach(var -> {
                headList.add(var.getName());
            });
        }
    }

    /**
     * 获取指定类的所有字段
     * @param sheetClass
     * @return
     */
    public static Field[] getAllFields(Class<?> sheetClass) {
        if(null != sheetClass){
            Field[] fields = sheetClass.getDeclaredFields();
            //排除掉static和final
            List<Field> list = Arrays.stream(fields).filter((Field field) -> (!Modifier.isStatic(field.getModifiers())) && !Modifier.isFinal(field.getModifiers())).collect(Collectors.toList());
            fields = new  Field[list.size()];
            list.toArray(fields);
            return fields;
        }
        return null;
    }


    public static Byte parseByte(String value) {
        try {
            value = value.replaceAll("　", "");
            return Byte.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseByte but input illegal input=" + value, e);
        }
    }

    public static Boolean parseBoolean(String value) {
        value = value.replaceAll("　", "");
        if (Boolean.TRUE.toString().equalsIgnoreCase(value)) {
            return Boolean.TRUE;
        } else if (Boolean.FALSE.toString().equalsIgnoreCase(value)) {
            return Boolean.FALSE;
        } else {
            throw new RuntimeException("parseBoolean but input illegal input=" + value);
        }
    }

    public static Integer parseInt(String value) {
        try {
            value = value.replaceAll("　", "");
            return Integer.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseInt but input illegal input=" + value, e);
        }
    }

    public static Short parseShort(String value) {
        try {
            value = value.replaceAll("　", "");
            return Short.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseShort but input illegal input=" + value, e);
        }
    }

    public static Long parseLong(String value) {
        try {
            value = value.replaceAll("　", "");
            return Long.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseLong but input illegal input=" + value, e);
        }
    }

    public static Float parseFloat(String value) {
        try {
            value = value.replaceAll("　", "");
            return Float.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseFloat but input illegal input=" + value, e);
        }
    }

    public static Double parseDouble(String value) {
        try {
            value = value.replaceAll("　", "");
            return Double.valueOf(value);
        } catch(NumberFormatException e) {
            throw new RuntimeException("parseDouble but input illegal input=" + value, e);
        }
    }

    public static Date parseDate(String value, ExcelFormat excelFormat) {
        try {
            String datePattern = "yyyy-MM-dd HH:mm:ss";
            if (excelFormat != null) {
                datePattern = excelFormat.pattern();
            }
            SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
            return dateFormat.parse(value);
        } catch(ParseException e) {
            throw new RuntimeException("parseDate but input illegal input=" + value, e);
        }
    }

    /**
     * 参数解析 （支持：Byte、Boolean、String、Short、Integer、Long、Float、Double、Date）
     *
     * @param field
     * @param value
     * @return Object
     */
    public static Object parseValue(Field field, String value) {
        Class<?> fieldType = field.getType();

        ExcelFormat excelFormat = field.getAnnotation(ExcelFormat.class);
        if(value==null || value.trim().length()==0){
            return null;
        }
        value = value.trim();

        if (Boolean.class.equals(fieldType) || Boolean.TYPE.equals(fieldType)) {
            return parseBoolean(value);
        }else if (String.class.equals(fieldType)) {
            return value;
        } else if (Short.class.equals(fieldType) || Short.TYPE.equals(fieldType)) {
            return parseShort(value);
        } else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
            return parseInt(value);
        } else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
            return parseLong(value);
        } else if (Float.class.equals(fieldType) || Float.TYPE.equals(fieldType)) {
            return parseFloat(value);
        } else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
            return parseDouble(value);
        } else if (Date.class.equals(fieldType)) {
            return parseDate(value, excelFormat);

        } else {
            throw new RuntimeException("request illeagal type, type must be Integer not int Long not long etc, type=" + fieldType);
        }
    }

    /**
     * 参数格式化为String
     *
     * @param field
     * @param value
     * @return String
     */
    public static String formatValue(Field field, Object value) {
        Class<?> fieldType = field.getType();
        if(value==null) {
            return "";
        }
        if(field.isAnnotationPresent(ExcelCodeToString.class)){
            String [] codeStrings=field.getAnnotation(ExcelCodeToString.class).values();
            for(String str : codeStrings) {
                String[] split = str.split(":");
                String code =split[0];
                String string =split[1];

                if(code.equals(String.valueOf(value))) {
                    return string;
                }else {
                }
            }
            return "undefined";
        }else if (Boolean.class.equals(fieldType) || Boolean.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (String.class.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Short.class.equals(fieldType) || Short.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Float.class.equals(fieldType) || Float.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
            return String.valueOf(value);
        } else if (Date.class.equals(fieldType)) {
            String datePattern = "yyyy-MM-dd HH:mm:ss";
            if(field.isAnnotationPresent(ExcelFormat.class)){
                datePattern=field.getAnnotation(ExcelFormat.class).pattern();
            }
            SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
            return dateFormat.format(value);
        } else {
            throw new RuntimeException("request illeagal type, type must be Integer not int Long not long etc, type=" + fieldType);
        }
    }


    /**
     * 将Excel中的数据转换为对象,根据@ExcelField注解上的值来确定
     */
    public static List<Object> ExcelData2Object(List<Map<String,Object>> dataList,Class<?> clzz){
        List<Map<String, Object>> resultList = new ArrayList<>();
        if(!CollectionUtils.isEmpty(dataList)){
            dataList.stream().forEach(map -> {
                Field[] fields = clzz.getDeclaredFields();
                Map<String, Object> tempMap = new HashMap<>();
                if(!CollectionUtils.isEmpty(Arrays.asList(fields))){
                    Arrays.stream(fields).filter((Field f) -> f.isAnnotationPresent(ExcelField.class)).forEach(field -> {
                        String name = field.getAnnotation(ExcelField.class).name();
                        //获取map的key,也就是对应@ExcelField注解上面的值
                        map.entrySet().stream().forEach(mmp -> {
                            if(mmp.getKey().equals(name)){
                                //判断该属性上是否有@ExcelCodeToString注解,如果有需要做转换
                               if(field.isAnnotationPresent(ExcelCodeToString.class)){
                                   String[] values = field.getAnnotation(ExcelCodeToString.class).values();
                                   Arrays.stream(values).forEach(var -> {
                                       if(map.get(mmp.getKey()).equals(var.split(":")[1])){
                                           int status = Integer.parseInt(var.split(":")[0]);
                                           tempMap.put(field.getName(),status);
                                           return;
                                       }
                                   });
                               }else{
                                 tempMap.put(field.getName(),map.get(mmp.getKey()));
                               }
                            }
                        });
                    });
                }
                resultList.add(tempMap);
            });
        }
        //处理状态值转换




        List<Object> objectList = new ArrayList<>();
        //将map转换为对象
        resultList.stream().forEach(var -> {
            Object o = BeanUtils.map2Object(var, clzz);
            objectList.add(o);
        });
        return objectList;
    }
}

对象复制工具类:

package com.hf.hantopy.utils;

import com.hf.hantopy.utils.excel.CommonUtil;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;

/**
 * @Description:
 * @Date: 2019/3/20
 * @Auther:
 */
public class BeanUtils extends org.springframework.beans.BeanUtils {
    public BeanUtils() {
    }


    public static <T> T propertiesCopy(Object source, Class<T> clazz) {
        if (null == source) {
            return null;
        } else {
            try {
                T obj = clazz.newInstance();
                org.springframework.beans.BeanUtils.copyProperties(source, obj);
                return obj;
            } catch (IllegalAccessException | InstantiationException var3) {
                throw new RuntimeException(var3);
            }
        }
    }

    /**
     * list中对象的copy
     * @param source
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> collectionCopy(Collection source, Class<T> clazz) {
        if (null == source) {
            return new ArrayList();
        } else {
            List<T> list = new ArrayList();
            Iterator var3 = source.iterator();

            while(var3.hasNext()) {
                Object o = var3.next();
                list.add(propertiesCopy(o, clazz));
            }

            return list;
        }
    }

    /**
     * 将对象转换为map
     * @param obj
     * @return
     */
    public static Map<String, Object> object2Map(Object obj) {
        Map<String, Object> map = new HashMap();
        if (obj == null) {
            return map;
        } else {
            Class clazz = obj.getClass();
            Field[] fields = clazz.getDeclaredFields();

            try {
                Field[] var4 = fields;
                int var5 = fields.length;

                for(int var6 = 0; var6 < var5; ++var6) {
                    Field field = var4[var6];
                    field.setAccessible(true);
                    map.put(field.getName(), field.get(obj));
                }

                return map;
            } catch (Exception var8) {
                throw new RuntimeException(var8);
            }
        }
    }


    /**
     * 将map转换为对象,必须保证属性名称相同
     * @return
     */
    public static Object map2Object(Map<String,Object> map,Class<?> clzz){
        try {
            Object target = clzz.newInstance();
            if(CollectionUtils.isEmpty(map)){
                return target;
            }
            Field[] fields = clzz.getDeclaredFields();
            if(!CollectionUtils.isEmpty(Arrays.asList(fields))){
                Arrays.stream(fields).filter((Field field) -> map.containsKey(field.getName())).forEach(var -> {
                    //获取属性的修饰符
                    int modifiers = var.getModifiers();
                    if(Modifier.isStatic(modifiers) || Modifier.isFinal(modifiers)){
                        //在lambada中结束本次循环是用return,它不支持continue和break
                        return;
                    }
                    //设置权限
                    var.setAccessible(true);
                    try {
                        //检查类型转换
                        Object o = CommonUtil.parseValue(var, map.get(var.getName()).toString());
                        var.set(target,o);
                    } catch (IllegalAccessException e) {
                       //属性类型不对,非法操作,跳过本次循环,直接进入下一次循环
                        throw new RuntimeException(e);
                    }
                });
            }
            return target;
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }


}

实体类:

package com.hf.hantopy.model;

import com.hf.hantopy.utils.excel.ExcelCodeToString;
import com.hf.hantopy.utils.excel.ExcelField;
import lombok.Data;

import java.io.Serializable;

/**
 * @Description:
 * @Date: 2019/3/3
 * @Auther:
 */
@Data
public class CarAdmin implements Serializable {
    private static final long serialVersionUID = -7011993813000654045L;
    @ExcelField(name = "id")
    private String id;
    @ExcelField(name = "姓名")
    private String name;
    @ExcelField(name = "运营商id")
    private String agentId;
    @ExcelField(name = "手机号")
    private String mobile;
    @ExcelField(name = "年龄")
    private Integer age;
    @ExcelField(name = "职位")
    private String position;
    @ExcelField(name = "状态")
    @ExcelCodeToString(values={"1:在职","2:离职","3:冻结"})
    private Integer status;
    @ExcelField(name = "加入时间")
    private String joinTime;
    @ExcelField(name = "逻辑删除")
    @ExcelCodeToString(values={"0:否","1:是"})
    private Integer isDelete;
    @ExcelField(name = "条目创建时间")
    private String createTime;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAgentId() {
        return agentId;
    }

    public void setAgentId(String agentId) {
        this.agentId = agentId;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public String getJoinTime() {
        return joinTime;
    }

    public void setJoinTime(String joinTime) {
        this.joinTime = joinTime;
    }

    public Integer getIsDelete() {
        return isDelete;
    }

    public void setIsDelete(Integer isDelete) {
        this.isDelete = isDelete;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }
}

controller测试方法:

package com.hf.hantopy.controller;

import com.hf.hantopy.mapper.CarAdminMapper;
import com.hf.hantopy.model.CarAdmin;
import com.hf.hantopy.utils.excel.CommonUtil;
import com.hf.hantopy.utils.excel.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
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.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @Date: 2019/2/26
 * @Auther: 
 */
@RestController
@RequestMapping("/excel")
public class MyController {
  @Autowired
  private CarAdminMapper carAdminMapper;

    @PostMapping("/export")
    public void export(HttpServletResponse response){
        String title = "车管信息列表";
        List<String> headList = new ArrayList<>();
        List<CarAdmin> list = carAdminMapper.findAll();
        //重组数据

        if(!CollectionUtils.isEmpty(list)){
            CommonUtil.getAnnoFields(list.get(0).getClass(),headList);
            ExcelUtil.downloadExcelFile(title,headList,list,response);
        }
    }

    @PostMapping("/importExcel")
    public List<Object> importExcel(MultipartFile file, HttpServletRequest request){
        try {
            List<Map<String,Object>> list = ExcelUtil.getData(file);
            List<Object> objects = CommonUtil.ExcelData2Object(list, CarAdmin.class);
            return objects;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

可以进行查询数据测试,excel导入测试