【导入Excel】java导入Excel 文件转成对象

一、spring boot 配置文件

spring:
  mvc:
    hiddenmethod:
      filter:
        enabled: true

二、接口

    @RequestMapping("/importByExcel.do")
    @ResponseBody
    public BaseResult importByExcel(@RequestParam(value = "fileName") MultipartFile attach ) throws Exception {

        BaseResult rslt = new BaseResult();
        houseLogicService.importByExcel(attach);
        rslt.setErrorCode(BaseResult.SUCCESS);

        return rslt;
    }

 @Override
    public void importByExcel(MultipartFile attach) {
        /*
         * 生成字段映射map
         */
        LinkedHashMap<String, String> deviceHeaderMap = null;
        try {
            deviceHeaderMap = ExcelUtils.generateHeaderMap(HouseDto.class, false);

        } catch (Exception e) {
            LogUtils.error(e);
            throw new ErrorInfoException(PlatformResultCst.SYSTEM_ERROR, new String[]{deviceHeaderMap.getClass().getName()});
        }

        /*
         * 根据表格获取对象信息
         */
        List<HouseDto> houseDtoList = new ArrayList<>();
        try {
            houseDtoList = ExcelUtils.importExcel(HouseDto.class, attach.getInputStream(), deviceHeaderMap);
        } catch (Exception e) {
            LogUtils.error(e);
            throw new ErrorInfoException(PlatformResultCst.SYSTEM_ERROR, new String[]{attach.getName()});
        }

        /*
         * @Date: 2021/7/21 14:18
         * Step 3: 保存数据,只要有一个错，就导入失败
         */
        for (HouseDto houseDto : houseDtoList) {

            // 其他字段业务填充

            // 保存到数据库
            addAndUpdateHouse(houseDto);
        }
    }

导入的工具类

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;

import java.awt.*;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.*;

public class ExcelUtils {

   

    /**
     * 生成一个标题style
     *
     * @return style
     */
    public static XSSFCellStyle getHeaderStyle(Workbook workbook) {
        return getHeaderStyle(workbook, (CTColor) Color.BLUE, IndexedColors.WHITE.getIndex());
    }

    /**
     * 生成一个指定颜色的标题style
     *
     * @param workbook
     * @param foregroundColor
     * @param fontColor
     * @return
     */
    public static XSSFCellStyle getHeaderStyle(Workbook workbook, CTColor foregroundColor, short fontColor) {

        /*
         *  生成一个样式（用于标题）
         */
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();

        /*
         * 设置这些样式
         */
        style.setFillForegroundColor(new XSSFColor(foregroundColor));
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        /*
         *  生成一个字体
         */
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setColor(fontColor);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

        /*
         *  把字体应用到当前的样式
         */
        style.setFont(font);

        return style;
    }

    /**
     * 生成一个用于内容的style
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle getContentStyle(Workbook workbook) {
        /*
         *  生成并设置另一个样式（用于内容）
         */
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        // style.setFillForegroundColor(new XSSFColor(Color.YELLOW));
        // style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        /*
         *  生成另一个字体
         */
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);

        /*
         *  把字体应用到当前的样式
         */
        style.setFont(font);

        return style;
    }

    /**
     * excel导入数据
     * 导入的表格第一行必须为标题行
     *
     * @param clazz             导入的类型
     * @param xls               excel源
     * @param propertyHeaderMap key为标题头  value为类字段
     * @return
     * @throws Exception
     */
    @SuppressWarnings("rawtypes")
    public static <T> List importExcel(Class<T> clazz, InputStream xls, LinkedHashMap<String, String> propertyHeaderMap) throws Exception {
        try {
            /*
             *  取得Excel
             */
            HSSFWorkbook wb = new HSSFWorkbook(xls);
            HSSFSheet sheet = wb.getSheetAt(0);
            Field[] fields = clazz.getDeclaredFields();

            /*
             * 获取标题行
             */
            HSSFRow rowTitle = sheet.getRow(0);

            /*
             *  行循环
             */
            List<T> modelList = new ArrayList<T>(sheet.getPhysicalNumberOfRows() * 2);
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                /*
                 * 数据模型
                 */
                T model = clazz.newInstance();
                int nullCount = 0;

                // for (Field field : fields) {

                while (true) {

                    /*
                     * 获取字段
                     */
                    HSSFCell cell = sheet.getRow(i).getCell(nullCount);
                    if (cell == null) {
                        break;
                    }
                    String strField = propertyHeaderMap.get(rowTitle.getCell(nullCount).getStringCellValue());
                    Field field = clazz.getDeclaredField(strField);

                    /*
                     * 赋值给model
                     */
                    try {
                        if (cell == null || cell.toString().length() == 0) {
                            nullCount++;
                        } else if (field.getType().equals(Date.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(), new Date(cell.getDateCellValue().getTime()));

                            }
                        } else if (field.getType().equals(Timestamp.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new Timestamp(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(),
                                        new Timestamp(cell.getDateCellValue().getTime()));
                            }

                        } else if (field.getType().equals(java.sql.Date.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(),
                                        new java.sql.Date(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(),
                                        new java.sql.Date(cell.getDateCellValue().getTime()));
                            }
                        } else if (field.getType().equals(Integer.class)) {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));
                            }
                        } else if (field.getType().equals(BigDecimal.class)) {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new BigDecimal(cell.getNumericCellValue()));
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
                            }
                        } else {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new BigDecimal(cell.getNumericCellValue()));
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), parseString(cell));
                            }
                        }
                    } catch (Exception e) {
                        LogUtils.error(e);

                    }
                    // }
                    if (nullCount >= rowTitle.getLastCellNum()) {
                        break;
                    }
                    nullCount++;
                }
                modelList.add(model);

            }
            return modelList;

        } finally {
            xls.close();
        }
    }

    /**
     * 解析字符串为string
     *
     * @param cell
     * @return
     */
    private static String parseString(HSSFCell cell) {
        return String.valueOf(cell).trim();
    }

    /**
     * 日期字符串转换为日期
     *
     * @param dateString
     * @return
     * @throws ParseException
     */
    private static long parseDate(String dateString) throws ParseException {
        if (dateString.indexOf("/") == 4) {
            return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();
        } else if (dateString.indexOf("-") == 4) {
            return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();
        } else if (dateString.indexOf("年") == 4) {
            return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();
        } else if (dateString.length() == 8) {
            return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();
        } else {
            return new Date().getTime();
        }
    }

    /**
     * 生成字段表头的map映射
     *
     * @param clazz
     * @param flag  true表示字段为key(生成表格用) false表示表头名称为key(表格导入使用)
     * @return
     * @throws Exception
     */
    public static <T> LinkedHashMap<String, String> generateHeaderMap(Class<T> clazz, Boolean flag) throws Exception {
        LinkedHashMap<String, String> propertyHeaderMap = new LinkedHashMap<String, String>();

        /*
         * 取出类字段
         */
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            /*
             * 取出注解
             */
            FieldMata mataAnnotation = field.getAnnotation(FieldMata.class);
            if (mataAnnotation == null) {
                continue;
            }
            if (mataAnnotation.name() != null && !"".equals(mataAnnotation.name())) {
                if (mataAnnotation.isList() == false) {
                    if (flag == true) {
                        propertyHeaderMap.put(field.getName(), mataAnnotation.name());
                    } else {
                        propertyHeaderMap.put(mataAnnotation.name(), field.getName());
                    }
                }
            }
        }
        return propertyHeaderMap;
    }


}

补充注解 FieldMata 

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD})
public @interface FieldMata {

    /**
     * 字段名称
     *
     * @return
     */
    String name() default "";

    /**
     * 是否是列表
     */
    boolean isList() default false;

    /**
     * 字段是否是dto
     */
    boolean isDto() default false;

}

三、测试

postman