1、根据索引取值,支持xls和xlsx
package cn.lfg.read;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
public class Read_Excel {
private static void parseInfoFromInputFile(String inputFilePath,
int rowBegin) throws Exception {
FileInputStream fileInput = new FileInputStream(inputFilePath);// 创建文件输入流
String suffix = inputFilePath
.substring(inputFilePath.lastIndexOf(".") + 1);
if (suffix.equalsIgnoreCase("xls")) {
System.out.println(suffix);
HSSFWorkbook wb = new HSSFWorkbook(fileInput);// 由输入流文件得到工作簿对象
HSSFSheet sheet = wb.getSheetAt(0);// 获取第一个sheet
int lastRowNum = sheet.getLastRowNum(); // 获取表格内容的最后一行的行数
// rowBegin代表要开始读取的行号,下面这个循环的作用是读取每一行内容
for (int i = rowBegin; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);// 获取每一行
int columnNum = row.getLastCellNum();// 获取每一行的最后一列的列号,即总列数
for (int j = 0; j < columnNum; ++j) {
String str = null;
HSSFCell cell = row.getCell(j);// 获取每个单元格
if (cell == null) {
str = null;
} else {
str = cell.toString();
}
switch (j) {
case 0:
System.out.print("name:" + str);
break;
case 2:
System.out.print(" SFZH:" + str);
break;
case 3:
System.out.print(" ID:" + str);
break;
case 5:
System.out.print(" BMMC:" + str);
break;
case 9:
System.out.print(" XMPY:" + str);
break;
default:
break;
}
}
System.out.println();
}
wb.close();
fileInput.close();
} else if (suffix.equalsIgnoreCase("xlsx")) {
System.out.println(suffix);
XSSFWorkbook wb = new XSSFWorkbook(fileInput);// 由输入流文件得到工作簿对象
XSSFSheet sheet = wb.getSheetAt(0);// 获取第一个sheet
int lastRowNum = sheet.getLastRowNum(); // 获取表格内容的最后一行的行数
// rowBegin代表要开始读取的行号,下面这个循环的作用是读取每一行内容
for (int i = rowBegin; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);// 获取每一行
int columnNum = row.getLastCellNum();// 获取每一行的最后一列的列号,即总列数
for (int j = 0; j < columnNum; ++j) {
XSSFCell cell = row.getCell(j);// 获取每个单元格
System.out.print(cell + " ");
}
System.out.println();
}
wb.close();
fileInput.close();
}
}
public static void main(String[] args) throws Exception {
// String path = "F:\\2021101201.xlsx";
String path = "F:\\20211012.xls";
parseInfoFromInputFile(path, 1);
}
}
2、更具列名获取值,仅支持xls
public static List<DataInfo> readExcel(String path) throws Exception {
File file = new File(path);
FileInputStream fis = null;
Workbook workBook = null;
List<DataInfo> datas = new ArrayList<>();
if (file.exists()) {
try {
fis = new FileInputStream(file);
workBook = WorkbookFactory.create(fis);
int numberOfSheets = workBook.getNumberOfSheets(); // 获取sheet数量
for (int s = 0; s < numberOfSheets; s++) { // numberOfSheets sheet 数量外循环
Sheet sheetAt = workBook.getSheetAt(s);
// 获取工作表名称
String sheetName = sheetAt.getSheetName();
// 获取当前Sheet的总行数
int rowsOfSheet = sheetAt.getPhysicalNumberOfRows();
if(rowsOfSheet>0) {
//表头 第一行
Row row0 = sheetAt.getRow(0);
int physicalNumberOfCells = sheetAt.getRow(0).getPhysicalNumberOfCells();
String[] title = new String[physicalNumberOfCells];
for (int i = 0; i < physicalNumberOfCells; i++) {
title[i] = row0.getCell(i).getStringCellValue();
}
for (int r = 1; r < rowsOfSheet; r++) {//获取每一列数据,以总行数循环条件
Row row = sheetAt.getRow(r);// 获取的第几行数据
int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
// 遍历每一列
DataInfo excel = new DataInfo();
for (int c = 0; c < cellCount; c++) {//总列数循环
Cell cell = row.getCell(c);
cell.setCellType(CellType.STRING);
CellType cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
// 下面是被获取到的数据存放在用户实体类 read_excel
switch (c) {
case 0:
excel.setChineseName(cellValue);
break;
case 1:
excel.setCardHolderPaperNumber(cellValue);
break;
case 2:
excel.setCardNumber(cellValue);
break;
case 3:
excel.setEmployeeNumber(cellValue);
break;
case 4:
excel.setCompanyName(cellValue);
break;
case 5:
excel.setDepartmentName(cellValue);
break;
case 6:
excel.setCardHolderName(cellValue);
break;
default:
break;
}
}
// 获取的数据存放在list 最后进行添加到数据库操作
datas.add(excel);
}
}
}//循环完成
fis.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else {
System.out.println("文件不存在!");
}
return datas;
}