我的POI代码库

添加的maven依赖是

<poi.version>3.15</poi.version>

...

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

 

读取excel中数据的简单写法

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

public class Test81 {

    public static void main(String[] args) {

        try (InputStream is = new FileInputStream("C:\\Users\\jiashubing\\Desktop\\aaaa.xls");
             Workbook book = new HSSFWorkbook(is);) {
            Sheet sheet = book.getSheetAt(0);

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                for (int j = 0; j < 5; j++) {
                    Cell cell = sheet.getRow(i).getCell(j);
                    if (cell != null) {
                        System.out.println(cell.getStringCellValue());
                    }
                }
            }
        } catch (IOException e) {
            //return;
        }
    }

}

 这样读取的是xls 格式的文件

把 Workbook 改成用WorkbookFactory 实现，那么就必须是读xlsx文件： Workbook book = WorkbookFactory.create(is);

 

　　POI的操作中大量使用了坐标，于是新建了一个类，包含四个参数，代表起始坐标 (firstRow,lastRow) 和结束坐标 (firstCol,lastRow) ，在下面的例子中凡是计算合并单元格的时候都会用到

public class ExcelPositionRange {
    private int firstRow;
    private int firstCol;
    private int lastRow;
    private int lastCol;

    public ExcelPositionRange(int firstRow, int firstCol, int lastRow, int lastCol) {
        this.firstRow = firstRow;
        this.firstCol = firstCol;
        this.lastRow = lastRow;
        this.lastCol = lastCol;
    }

    public ExcelPositionRange() {
    }

    //... get/set方法
}

 

获取合并单元格

/**
 * 获取合并的单元格
 */
private static ExcelPositionRange getMergedRegionPositionRange(Cell cell) {
    Sheet sheet = cell.getSheet();
    int row = cell.getRowIndex();
    int column = cell.getColumnIndex();
    int sheetMergeCount = sheet.getNumMergedRegions();

    for (int i = 0; i < sheetMergeCount; ++i) {
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();
        if (row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn) {
            sheet.getRow(firstRow);
            return new ExcelPositionRange(firstRow, firstColumn, lastRow, lastColumn);
        }
    }

    return new ExcelPositionRange(row, column, row, column);
}

 

读取单元格数据内容

/**
 * 读取数据内容（参数可以改为Cell）
 *
 * @param sheet  Sheet页面
 * @param row    行数
 * @param column 列数
 * @return 读取的内容
 */
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();

    //判断该单元格是否是合并区域的内容
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();

        if (row >= firstRow && row <= lastRow) {
            if (column >= firstColumn && column <= lastColumn) {
                Row fRow = sheet.getRow(firstRow);
                return getStringValue(fRow.getCell(firstColumn));
            }
        }
    }

    return getStringValue(sheet.getRow(row).getCell(column));
}

/**
 * 获取单元格中的值，转为String类型
 */
private static String getStringValue(Cell cell) {
    if (cell == null)
        return "";

    //其他数据类型当成字符串读取
    cell.setCellType(CellType.STRING);
    return cell.getStringCellValue();
}

 

获取单元格的总高、宽度

使用到了“获取合并单元格”那个方法

/**
 * 获取单元格的总高度（单位：坐标点）
 */
private static double getTotalHeight(Cell cell) {
    ExcelPositionRange tmpRange = getMergedRegionPositionRange(cell);
    double totalHeight = 0;
    for (int i = tmpRange.getFirstRow(); i <= tmpRange.getLastRow(); i++) {
        totalHeight += cell.getSheet().getRow(i).getHeightInPoints();
    }
    return totalHeight;
}

/**
 * 获取单元格的总宽度（单位：像素）
 */
private static double getTotalWidth(Cell cell) {
    ExcelPositionRange tmpRange = getMergedRegionPositionRange(cell);
    double totalWidthInPixels = 0;
    for (int i = tmpRange.getFirstCol(); i <= tmpRange.getLastCol(); i++) {
        totalWidthInPixels += cell.getSheet().getColumnWidthInPixels(i);
    }
    return totalWidthInPixels;
}

 

 

 

原创文章，欢迎转载，转载请注明出处！