package com.cn.excel;
import java.io.File;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class Test {
public static void main(String[] args) {
List<TestPo> testList = load("src/main/resources/test.xlsx", "工作表1", TestPo.class);
for (int i = 0; i < testList.size(); i++) {
System.out.print("【" + testList.get(i).getNo() + "】");
System.out.print("【" + testList.get(i).getName() + "】");
System.out.print("【" + testList.get(i).getValue() + "】");
System.out.println();
}
}
/**
* 解析Excel表
*
* @param excelPath
* 文件路径
* @param sheetName
* 工作表名
* @param clazz
* 实体类
* @return
*/
public static <T> List<T> load(String excelPath, String sheetName, Class<T> clazz) {
List<T> list = new ArrayList<T>();
try {
// 创建workbook对象
Workbook workbook = WorkbookFactory.create(new File(excelPath));
// 获取表单sheetName
Sheet sheet = workbook.getSheet(sheetName);
// 获取第一行
Row titleRow = sheet.getRow(0);
// 获取最后一列列号(列数)
int lastCellNum = titleRow.getLastCellNum();
String[] fields = new String[lastCellNum];
// 循环处理每一列,取出每一列的字段名保存到数组(处理表头)
for (int column = 0; column < lastCellNum; column++) {
Cell cell = titleRow.getCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String title = cell.getStringCellValue();
title = title.substring(0, title.indexOf("("));
fields[column] = title;
}
// 获取最后一列行号
int lastRowIndex = sheet.getLastRowNum();
// 循环处理每一个数据行
for (int row = 0; row <= lastRowIndex; row++) {
T obj = clazz.newInstance();
// 拿到一个数据行
Row dataRow = sheet.getRow(row);
if (dataRow == null || isEmptyRow(dataRow)) {
continue;
}
// 拿到此数据行上的每一列
for (int column = 0; column < lastCellNum; column++) {
Cell cell = dataRow.getCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String value = null;
// 获取单元格的值
value = getCellValue(cell);
if (isMergedRegion(sheet, row, column)) {
value = getMergedRegionValue(sheet, row, column);
}
// 获取要反射的方法
String methodName = "set" + fields[column];
// 获取要反射的方法对象
Method method = clazz.getMethod(methodName, String.class);
// 完成反射调用
method.invoke(obj, value);
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet 表单
* @param row 行
* @param column 列
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
//获取sheet中合并单元格的总数
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
*
* @param 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);
Cell fCell = fRow.getCell(firstColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK);
fCell.setCellType(CellType.STRING);
return getCellValue(fCell);
}
}
}
return null;
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
return cell.getStringCellValue();
}
/**
*
* @param dataRow
* @return
*/
private static boolean isEmptyRow(Row dataRow) {
int lastCellNum = dataRow.getLastCellNum();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = dataRow.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
if (value != null && value.trim().length() > 0) {
return false;
}
}
return true;
}
}