上传读取Excel文件内容并入库

淡淡的烟草味﹌ 2021-09-23 11:00 407阅读 0赞

需求：读取前端上传的Excel，将数据入库，本工具类仅读取文件内容，不涉及到入库。当然，既然已经拿到了数据，那入库轻而易举。

package com.xx.utils;
    
    import lombok.extern.slf4j.Slf4j;
    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.text.DecimalFormat;
    import java.util.LinkedHashMap;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    
    /**
     * Description:excel相关工具类
     * 作者：Marco.Gu
     */
    @Slf4j
    public class ExcelUtils {
    
        /**
         * Description:读取excel文件
         * 作者：Marco.Gu
         * date:2019/11/11 9:24
         * @param file //文件
         * @param startrow //开始行号
         * @param startcol //开始列号
         * @param sheetnum //sheet
         * @return list
         */
    
    
        public static List<Map<String, Object>> readExcel(MultipartFile file, int startrow, int startcol, int sheetnum) throws IOException {
            List<Map<String, Object>> resultList = new LinkedList<>();
            try (InputStream fi = file.getInputStream();) {
                String fileName = file.getOriginalFilename();
                //进行版本选择解析方式
                Workbook wb = null;
                if (fileName.endsWith(".xlsx")) {
                    wb = new XSSFWorkbook(fi);
                } else if (fileName.endsWith(".xls")) {
                    wb = new HSSFWorkbook(fi);
                } else {
                    log.error("文件非EXCEL");
                    return null;
                }
                Sheet sheet = wb.getSheetAt(sheetnum);
                int rowNum = sheet.getLastRowNum() + 1;
                for (int i = startrow; i < rowNum; i++) {
                    Map<String, Object> resultMap = new LinkedHashMap<>();
                    Row row = sheet.getRow(i);
                    //每行的最后一个单元格位置
                    int cellNum = row.getLastCellNum();
                    //列循环开始
                    for (int j = startcol; j < cellNum; j++) {
                        Cell cell = row.getCell(j);
                        String cellValue = null;
                        if (null != cell) {
                            CellType cellTypeEnum = cell.getCellTypeEnum();
                            // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                            switch (cellTypeEnum) {
                                case NUMERIC:
                                    DecimalFormat format = new DecimalFormat("#");
                                    cellValue = format.format(cell.getNumericCellValue());
                                    break;
                                case STRING:
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case FORMULA:
                                    cellValue = cell.getCellFormula() + "";
                                    break;
                                case _NONE:
                                    cellValue = "";
                                    break;
                                case BOOLEAN:
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case ERROR:
                                    cellValue = String.valueOf(cell.getErrorCellValue());
                                    break;
                            }
                        } else {
                            cellValue = "";
                        }
                        String key = sheet.getRow(0).getCell(j).getStringCellValue();
                        resultMap.put(key, cellValue);
                    }
                    resultList.add(resultMap);
                }
            } catch (IOException e) {
                log.error("EXCEL读取异常", e);
                throw e;
            }
            return resultList;
        }
    }