Excel表格的读取工具类

最新推荐文章于 2023-07-12 11:35:38 发布

原创最新推荐文章于 2023-07-12 11:35:38 发布 · 561 阅读

2 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

23 篇文章

订阅专栏

这篇博客介绍了如何创建一个Java工具类来处理Excel表格的读取操作，包括具体的实现方法和测试示例。

Excel表格读取类容

1.处理excel读取的工具类

package CommonUtil.ExcelUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;

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;

/**
 * 处理excel读取的工具类  
 * jar包 3.14版本 <artifactId>poi-ooxml</artifactId> <version>3.14</version>
 *           	
 */
@SuppressWarnings("resource")
public class ReadExcelUtil {
	
	/**
	 * 要求excel版本在2007以上
	 *
	 * @param file
	 *            文件信息
	 * @return 
	 */
	public static List<List<Object>> readExcel(File file){
		if (!file.exists()) {
			try {
				throw new Exception("找不到文件");
			} catch (Exception e) { 
				e.printStackTrace();
			}
		}
		List<List<Object>> list = new LinkedList<List<Object>>();
		XSSFWorkbook xwb = null;
		try {
			xwb = new XSSFWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e) { 
			e.printStackTrace();
		} catch (IOException e) { 
			e.printStackTrace();
		}
		// 读取第一张表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				Object value = null;
				cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					// String类型返回String数据
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					// 数值类型返回double类型的数字
					value = cell.getNumericCellValue();
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					// 布尔类型
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					// 空单元格
					break;
				default:
					value = cell.toString();
				}
				if (value != null && !value.equals("")) {
					// 单元格不为空，则加入列表
					linked.add(value);
				}
			}
			if (linked.size() != 0) {
				list.add(linked);
			}
		}
		return list;
	}

	/**
	 * 要求excel版本在2007以上
	 *
	 * @param fileInputStream
	 *            文件信息
	 * @return
	 * @throws Exception
	 */
	public static List<List<Object>> readExcel(FileInputStream fileInputStream) throws Exception {
		List<List<Object>> list = new LinkedList<List<Object>>();
		XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
		// 读取第一张表格内容
		XSSFSheet sheet = xwb.getSheetAt(1);
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				Object value = null;
				cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					value = cell.getNumericCellValue();
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					break;
				default:
					value = cell.toString();
				}
				if (value != null && !value.equals("")) {
					// 单元格不为空，则加入列表
					linked.add(value);
				}
			}
			if (linked.size() != 0) {
				list.add(linked);
			}
		}
		return list;
	}
}

2.测试如下（@SuppressWarnings("static-access")表示忽略警告不必理会）

package com.test;

import java.io.File;
import java.util.List;

import CommonUtil.ExcelUtil.ReadExcelUtil;

public class ReadExcelTest {

	@SuppressWarnings("static-access")
	public static void main(String[] args) throws Exception {  
		File file = new File("D:\\test.xls");
		List<List<Object>> blist = new ReadExcelUtil().readExcel(file); 
		System.out.println(blist.toString());
	} 
}