java操作excel（通过POI）

官网

https://poi.apache.org/

我们使用的是usermodel

读取excel所有数据，并打印出来

方式一：

表单名：testcase

定义实体类（说明：这里单纯打印读取的excel内容，未用到实体类，反射的时候才会用到实体类）

package com.qzcsbj;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class TestCase {
    private String caseId;
    private String describe;
    private String url;
    private String method;
    private String parameters;
    private String expect;
    private String actual;

    public String getCaseId() {
        return caseId;
    }

    public void setCaseId(String caseId) {
        this.caseId = caseId;
    }

    public String getDescribe() {
        return describe;
    }

    public void setDescribe(String describe) {
        this.describe = describe;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getMethod() {
        return method;
    }

    public void setMethod(String method) {
        this.method = method;
    }

    public String getParameters() {
        return parameters;
    }

    public void setParameters(String parameters) {
        this.parameters = parameters;
    }

    public String getExpect() {
        return expect;
    }

    public void setExpect(String expect) {
        this.expect = expect;
    }

    public String getActual() {
        return actual;
    }

    public void setActual(String actual) {
        this.actual = actual;
    }

    @Override
    public String toString() {
        return "TestCase{" +
                "caseId='" + caseId + '\'' +
                ", describe='" + describe + '\'' +
                ", url='" + url + '\'' +
                ", method='" + method + '\'' +
                ", parameters='" + parameters + '\'' +
                ", expect='" + expect + '\'' +
                ", actual='" + actual + '\'' +
                '}';
    }
}

读取excel

package com.qzcsbj;

import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class Test {
    public static void readExcel(String excelPath, String sheetName){
        InputStream in = null;
        try {
            File file = new File(excelPath);
            in = new FileInputStream(file);
            Workbook workbook = WorkbookFactory.create(in);
            Sheet sheet = workbook.getSheet(sheetName);
            Row firstRow = sheet.getRow(0);
            int lastCellNum = firstRow.getLastCellNum();
            String[] titles = new String[lastCellNum];
            for (int i = 0; i < lastCellNum; i++) {
                Cell cell = firstRow.getCell(i);
                String title = cell.getStringCellValue();
                titles[i] = title;
            }
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum  ; i++) {
                Row rowData = sheet.getRow(i);
                System.out.print("第"+i+"行数据：");
                for (int j = 0; j < lastCellNum ; j++) {
                    Cell cell = rowData.getCell(j);
                    String cellValue = cell.getStringCellValue();
                    // 打印获取到的值
                    System.out.print("【"+ titles[j] + "="+ cellValue+"】");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in!=null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    public static void main(String[] args) {
        readExcel("E:\\case.xlsx","testcase");
    }
}

结果：

方式二：

表单名：testcase2

package com.qzcsbj;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class ReadExcel {
    public static void readExcel(String excelPath, String sheetName){
        InputStream in = null;
        DataFormatter dataFormatter = new DataFormatter();
        File file = new File(excelPath);
        try {
            in = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = workbook.getSheet(sheetName);
            // 首行
            Row header = sheet.getRow(0);

            // 首行所有列
            Iterator<Cell> headerCells = header.iterator();
            while (headerCells.hasNext()){
                Cell cell = headerCells.next();
                System.out.println("行索引：" + cell.getRowIndex()+"，列索引：" + cell.getColumnIndex() + "，当前单元格的值：" + dataFormatter.formatCellValue(cell));
            }

            // 所有行
            Iterator<Row> rowIterator = sheet.rowIterator();
            boolean isFirstRow = true;
            while (rowIterator.hasNext()){
                Row row = rowIterator.next();
                // 忽略首行
                if (isFirstRow){
                    isFirstRow = false;
                    continue;
                }

                HashMap<String, String> rowMap = new HashMap<>();
                // 获取当前行所有列
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()){
                    // 当前列
                    Cell cell = cells.next();
                    System.out.println("行索引：" + cell.getRowIndex()+"，列索引：" + cell.getColumnIndex() + "，当前单元格的值：" + dataFormatter.formatCellValue(cell));
                    rowMap.put(dataFormatter.formatCellValue(header.getCell(cell.getColumnIndex())),dataFormatter.formatCellValue(cell));
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭流
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {
        String excelPath = "E:\\case.xlsx";
        String sheetName = "testcase2";
        readExcel(excelPath, sheetName);
    }
}