Java解析Excel文件（.xlsx和.xls格式均适用）

最新推荐文章于 2024-09-23 11:00:00 发布

cxyhl

最新推荐文章于 2024-09-23 11:00:00 发布

阅读量1.4k

点赞数 2

文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/he_lei/article/details/122428498

版权

pom

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15</version>
    </dependency>

1，解析本地

public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");

        //excel文件路径
        String excelPath = "E:\\readExcelMaven\\test.xlsx";

        try {
            //String encoding = "GBK";
            File excel = new File(excelPath);
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在

                String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                if ( "xls".equals(split[1])){
                    FileInputStream fis = new FileInputStream(excel);   //文件流对象
                    wb = new HSSFWorkbook(fis);
                }else if ("xlsx".equals(split[1])){
                    wb = new XSSFWorkbook(excel);
                }else {
                    System.out.println("文件类型错误!");
                    return;
                }

                //开始解析
                Sheet sheet = wb.getSheetAt(0);     //读取sheet 0

                int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
                int lastRowIndex = sheet.getLastRowNum();
                System.out.println("firstRowIndex: "+firstRowIndex);
                System.out.println("lastRowIndex: "+lastRowIndex);

                for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
                    System.out.println("rIndex: " + rIndex);
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
                                System.out.println(cell.toString());
                            }
                        }
                    }
                }
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2.上传解析

    @Override
    public void upload(MultipartFile file) throws IOException {
        Assert.isTrue(file.getSize() != 0, "文件内容为空，请重新选择文件！");
        String fileName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);

        Workbook wb;
        InputStream inputStream = file.getInputStream();
        //根据文件后缀（xls/xlsx）进行判断
        if ("xls".equals(fileName)) {
            wb = new HSSFWorkbook(inputStream);
        } else if ("xlsx".equals(fileName)) {
            wb = new XSSFWorkbook(inputStream);
        } else {
            System.out.println("文件类型错误!");
            return;
        }
        //开始解析
        //读取sheet 0
        Sheet sheet = wb.getSheetAt(0);
        //第一行标题，第二行是列名，所以不读
        int firstRowIndex = sheet.getFirstRowNum() + 2;
        int lastRowIndex = sheet.getLastRowNum();
        logger.info("firstRowIndex: {}", firstRowIndex);
        logger.info("lastRowIndex: {} ", lastRowIndex);
        List<ShPrice> list = new ArrayList<>();
        //遍历行
        for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
            logger.info("rIndex: {} ", rIndex);
            Row row = sheet.getRow(rIndex);
            if (row != null) {
              
                  int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    //遍历列
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                        System.out.println(cIndex);
                        Cell cell = row.getCell(cIndex);

                        if (cell != null) {
                            System.out.println(cell.toString());
                        }
                    }
            }
        }
        this.saveBatch(list);
    }