[Java知识库]JAVA读取Excel文件

使用POI读取Excel

jar包下载地址：https://download.csdn.net/download/Spring_East/33142735

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

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;

public class XlsFile {
	public static void main(String[] args) throws Exception {
		// 获取文件路径
		ReadXlsFile("D:/FileTest.xls");
	}

	/**
	 * 
	 * @param path
	 * @throws Exception excel文件的读取
	 */
	public static void ReadXlsFile(String path) throws Exception {
		FileInputStream is = new FileInputStream(path);
		HSSFWorkbook sheets = new HSSFWorkbook(is);// 获取工作表
		System.out.println("共有" + sheets.getNumberOfSheets() + "个sheet工作表");
		// 对工作表进行遍历
		for (int k = 0; k < sheets.getNumberOfSheets(); k++) {
			HSSFSheet sheet = sheets.getSheetAt(k);//获取当前工作表sheet
			int rows = sheet.getPhysicalNumberOfRows();// 获取行数
			
			// sheets.getSheetName(k)获取工作表sheet的名称
			System.out.println(sheets.getSheetName(k) + "    有" + rows + "行");
			List<List> sheetList = new ArrayList<List>();//定义list用来存储excel中的数据
			for (int r = 0; r < rows; r++) {
				HSSFRow row = sheet.getRow(r);//获取行数据

				List rowList = new ArrayList<Object>();
				//若行数为空则不计入getPhysicalNumberOfRows()方法总行数，所以若当前行为空，则我们需要遍历到获取到行数的下一行，故需要对总行数加1
				if (row != null) {
					int cells = row.getPhysicalNumberOfCells();// 获取列数
					for (int c = 0; c < cells; c++) {
						HSSFCell cell = row.getCell(c);
						if (cell == null) {
							cells++;
						}
						String value = getStringValue(cell);
						rowList.add(value);
					}
				} else {
					rows++;
				}
				sheetList.add(rowList);
			}
			for (List list : sheetList) {
				System.out.println(list);
			}
		}
	}

	/**
	 * 
	 * @param cell
	 * @return 获取表格中的数据，并转为String类型
	 */
	public static String getStringValue(HSSFCell cell) {
		if (cell == null) {
			return "";
		}
		String value = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			value = "" + cell.getNumericCellValue();
			break;
		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		default:
		}
		return value;
	}
}

excel文件内容为：

运行结果为：