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

悠悠 2022-08-30 15:55 367阅读 0赞

# 一、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;
        }
    
    
    }

### 三、测试 ###

postman

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW4xMDc4NzY_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW4xMDc4NzY_size_16_color_FFFFFF_t_70]: /images/20220829/c9b9b82ce97541948c8d78bc144bd1a2.png