java使用poi读取excel

注：对于一些数据量小的excel使用什么插件都可以，但是大的数据量都建议使用poi，因为内存消耗会非常高。一般推荐使用EasyExcel 。

导入pom

<properties>
    <java.version>1.8</java.version>
        <poi.version>4.1.2</poi.version>
    </properties>
<dependencies>
    <!--excel操作框架-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-scratchpad</artifactId>
        <version>${poi.version}</version>
    </dependency>

    <!-- 读取大量excel数据时使用 -->
    <dependency>
        <groupId>com.monitorjbl</groupId>
        <artifactId>xlsx-streamer</artifactId>
        <version>2.1.0</version>
    </dependency>
</dependencies>

方法一

package com.fmz.demofmztest.testUtil;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Objects;

/**
 * @Description: 常规POI读取
 * 只能读取 xlsx 格式文件
 * @Author yizhixiansheng
 * @Date 2022/6/20
 */
public class ReadExcel {
    public static void main(String[] args) throws Exception {
        long t1 = System.currentTimeMillis();
        excel("C:\\Users\\88999\\Desktop\\临时文件夹\\活动数据提取表.xlsx",1,0);
        long t2 = System.currentTimeMillis();

        System.out.println((t2-t1) + "毫秒");
    }

    /**
     * 读取excel，含列头名称
     * @param filePath xlsx 格式文件 路径
     * @param sheetIndex sheet标签页下标  从0开始
     * @param titleRowIndex 默认第一行为列头名称
     * @return void
     * @author yizhixiansheng
     * @create 2022/6/20
     **/
    public static void excel(String filePath,int sheetIndex,int titleRowIndex) throws Exception {
        File file = new File(filePath);
        if (!file.exists()){
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        if(Objects.isNull(sheets)){
            throw new Exception("读取Excel异常!");
        }
        // 获取指定表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(sheetIndex);
        // 获取指定默认标题行
        XSSFRow titleRow = sheetAt.getRow(titleRowIndex);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            // 读取每一格内容
            StringBuilder sb = new StringBuilder();
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);
                cell.setCellType(CellType.STRING);
                if (null == cell.getStringCellValue() || "".equals(cell.getStringCellValue())) {
                    continue;
                }
                sb.append(cell);
            }
            System.out.println(i + "\t" + sb);
        }
    }

    /**
     * 读取excel
     * @param filePath xlsx 格式文件 路径
     * @param sheetIndex sheet标签页下标  从0开始
     * @return void
     * @author yizhixiansheng
     * @create 2022/6/20
     **/
    public static void excel(String filePath,int sheetIndex) throws Exception {
        File file = new File(filePath);
        if (!file.exists()){
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        if(Objects.isNull(sheets)){
            throw new Exception("读取Excel异常!");
        }
        // 获取指定表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(sheetIndex);
        // 循环获取每一行数据
        for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            // 读取每一格内容
            StringBuilder sb = new StringBuilder();
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell cell = row.getCell(index);
                cell.setCellType(CellType.STRING);
                if (null == cell.getStringCellValue() || "".equals(cell.getStringCellValue())) {
                    continue;
                }
                sb.append(cell);
            }
            System.out.println(i + "\t" + sb);
        }
    }
}

方法二

package com.fmz.demofmztest.testUtil;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: 大文件excel读取
 * 只能读取 xlsx 格式文件
 * @Author yizhixiansheng
 * @Date 2022/6/20
 */
public class ReadBigExcel {
    public static Map<String, List<String>> test(File file) throws Exception{
        FileInputStream in = new FileInputStream(file);
        Map<String,List<String>> mapData = new HashMap<String, List<String>>();
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                .bufferSize(4096)  //读取资源时，缓存到内存的字节大小，默认是1024
                .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        int sheetNums = wk.getNumberOfSheets();
        for(int i = 0 ; i < sheetNums;i ++){
            List<String> sheetData = new ArrayList<String>();
            Sheet sheet = wk.getSheetAt(i);
            String sheetName = wk.getSheetName(i);
            //遍历所有的行
            int k = 0;
            for (Row row : sheet) {
                StringBuilder sb = new StringBuilder();
                //遍历所有的列
                for (Cell cell : row) {
                    sb.append(cell.getStringCellValue());
                }
                System.out.println(k++ + "\t" + sb.toString());
            }


        }
        return mapData;
    }

    public static void main(String[] args) throws Exception {
        File file = new File("C:\\Users\\88999\\Desktop\\临时文件夹\\活动数据提取表.xlsx");
        long t1 = System.currentTimeMillis();
        test(file);
        long t2 = System.currentTimeMillis();

        System.out.println((t2-t1)/1000 + "秒");

    }
}

结果