Java实现读取excel文件内容

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 readExcel(String path) throws Exception {
	 File file = new File(path);
	 FileInputStream fis = null;
	 Workbook workBook = null;
	 List 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;
	 }