当前位置: 首页>后端>正文

POI实现Excel导入

Excel2003版(后缀为.xls)最大行数是65536行,最大列数是256列。
Excel2007以上的版本(后缀为.xlsx)最大行数是1048576行,最大列数是16384列。

Excel读取:
根据指定的开始和结束行数读取返回结果,结果格式为List<Map<String, Object>>

1.在pom.xml加上依赖

<!--读取 excel 的所需jar包-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>

2.Control

@RequestMapping(value = "import")
    public  List<Map<String, Object>>  uploadExcel(@RequestParam("file") MultipartFile file){
        List<Map<String, Object>> maps = excelReaderService.readExcel(file, "1");
        return maps;

    }

3.Service

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class ExcelReaderService {
    /**
     Excel2003版(后缀为.xls)最大行数是65536行,最大列数是256列。
     Excel2007以上的版本(后缀为.xlsx)最大行数是1048576行,最大列数是16384列。
     */

private  static  Logger logger = LoggerFactory.getLogger(ExcelReaderService.class);
    //定义excel类型
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";


    /**
     * 读取excel文件内容
     * @param file
     * @param type
     * @return
     */
    public  List<Map<String, Object>> readExcel(MultipartFile file,String type){
        //声明返回结果集result
        List<Map<String, Object>> result = new ArrayList<>();
        //声明一个工作薄
        Workbook workbook = null;
        InputStream inputStream = null;
        String fileName= file.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length());
        try {
            inputStream = file.getInputStream();
            workbook = getWorkbook(inputStream,fileType);
            //处理Excel内容
            result = HandleData(workbook,1,-1,false);
        } catch (Exception e) {
            logger.error("解析Excel失败,文件名:" + fileName +"错误信息:"+ e.getMessage(),e);
           throw  new RuntimeException(e);
        }finally {

            try {
                if(null!= workbook) {
                    workbook.close();
                }
                if(null!= inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
               logger.error("关闭数据流出错!错误信息:",e.getMessage());
            }

        }
        return result;
    }

    /**
     * 处理Excel内容转为List<Map<String,Object>>输出
     * workbook:已连接的工作薄
     * StatrRow:读取的开始行数(默认填0,0开始,传过来是EXcel的行数值默认从1开始,这里已处理减1)
     * EndRow:读取的结束行数(填-1为全部)
     * ExistTop:是否存在头部(如存在则读取数据时会把头部拼接到对应数据,若无则为当前列数)
     */
    private static List<Map<String, Object>> HandleData(Workbook workbook, int StatrRow, int EndRow, boolean ExistTop) {
        //声明返回结果集result
        List<Map<String, Object>> result = new ArrayList<>();
        //声明一个Excel头部函数
        ArrayList<String> top = new ArrayList<>();
        //解析sheet(sheet是Excel脚页)
        /**
         *此处会读取所有脚页的行数据,若只想读取指定页,不要for循环,直接给sheetNum赋值,脚页从0开始(通常情况Excel都只有一页,所以此处未进行进一步处理)
         */
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            //如存在头部,处理头部数据
            if (ExistTop) {
                int firstRowNum = sheet.getFirstRowNum();
                Row firstRow = sheet.getRow(firstRowNum);
                if (null == firstRow) {
                    logger.warn("解析Excel失败,在第一行没有读取到任何数据!");
                }
                for (int i = 0; i < firstRow.getLastCellNum(); i++) {
                    top.add(convertCellValueToString(firstRow.getCell(i)));
                }
            }
            //处理Excel数据内容
            int endRowNum;
            //获取结束行数
            if (EndRow == -1) {
                endRowNum = sheet.getPhysicalNumberOfRows();
            } else {
                endRowNum = EndRow <= sheet.getPhysicalNumberOfRows() EndRow : sheet.getPhysicalNumberOfRows();
            }
            int temp = 0;
            //遍历行数
            for (int i = StatrRow - 1; i < endRowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                Map<String, Object> map = new HashMap<>();
                //获取所有列数据
                for (int y = 0; y < row.getLastCellNum(); y++) {
                    if (top.size() > 0) {
                        if (top.size() >= y) {
                            map.put(top.get(y), convertCellValueToString(row.getCell(y)));
                        } else {
                            map.put(String.valueOf(y + 1), convertCellValueToString(row.getCell(y)));
                        }
                    } else {
                        map.put(String.valueOf(y + 1), convertCellValueToString(row.getCell(y)));
                    }
                }

                if(null ==result){
                    int num = row.getRowNum();
                    logger.info("第"+ num + "行数据不符合规范,已忽略!,忽略累计"+  temp );
                }
                result.add(map);
            }
        }
        return result;
    }

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型(xls或xlsx)
     * @return 包含文件数据的工作簿对象
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws  IOException{
        //用自带的方法新建工作薄
        Workbook workbook = WorkbookFactory.create(inputStream);
        //后缀判断有版本转换问题
        //Workbook workbook = null;
        //if (fileType.equalsIgnoreCase(XLS)) {
        // workbook = new HSSFWorkbook(inputStream);
        //} else if (fileType.equalsIgnoreCase(XLSX)) {
        // workbook = new XSSFWorkbook(inputStream);
        //}
        return workbook;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    public static  String convertCellValueToString(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case BLANK: // 空值
                cellValue = "null";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        cellValue = switchNumeric(cellValue);
        return cellValue;
    }

    private static String switchNumeric(String str) {
        if (str.indexOf(".") > 0) {
            //正则表达

          /*  s = s.replaceAll("0+?$", "");//去掉后面无用的零
            s = s.replaceAll("[.]$", "");//如小数点后面全是零则去掉小数点*/
            str = str.replaceAll("0+?$", "").replaceAll("[.]$", "");
        }
        return str;
    }

    public static void main(String[] args) {
          String numeric = switchNumeric("90.070");
          System.out.printf("switchNumeric=======" + numeric);

    }
}

4.结果

[
    {
        "1": "双缝干涉",
        "2": "叙述",
        "3": "发多少",
        "4": "的额",
        "5": "大风吹"
    },
    {
        "1": "1",
        "2": "1.2",
        "3": "1.3",
        "4": "1.4"
    },
    {
        "1": "2",
        "2": "2.2",
        "3": "2.3",
        "4": "",
        "5": "2.5"
    },
    {
        "1": "",
        "2": "3.2",
        "3": "3.3",
        "4": "",
        "5": "3.5"
    }
]

参考:https://www.jb51.net/article/202762.htm
SpringBoot实现内存数据导出成Excel在另一篇文章 文章地址:https://www.jb51.net/article/202767.htm


https://www.xamrdz.com/backend/3tr1935295.html

相关文章: