欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用poi读取xls、xlsx文件

程序员文章站 2022-05-17 09:27:58
...

首先导入jar包:

commons-collections 4-4.1jar 

poi 3.9.jar   --核心jar包;

poi-ooxml.3.9.jar    --支持xlsx读取

poi-ooxml-schemas 3.9.jar  

xmlbeans-2.6.0.jar

maven配置:

使用poi读取xls、xlsx文件

读取excel工具类:

package com.feosa.common.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelPoiReaderUtils {

	/**
	 * 读取excel 第1张sheet (xls和xlsx)
	 * 
	 * @param filePath	excel路径
	 * @param columns	列名(表头)
	 * @author lizixiang ,2018-05-08
	 * @return
	 */
	public List<Map<String, String>> readExcel(String filePath,String columns[]) {
		Sheet sheet = null;
		Row row = null;
		Row rowHeader = null;
		List<Map<String, String>> list = null;
		String cellData = null;
		Workbook wb = null;
		if (filePath == null) {
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			if (".xls".equals(extString)) {
				wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(extString)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = null;
			}
			if (wb != null) {
				// 用来存放表中数据
				list = new ArrayList<Map<String, String>>();
				// 获取第一个sheet
				sheet = wb.getSheetAt(0);
				// 获取最大行数
				int rownum = sheet.getPhysicalNumberOfRows();
				// 获取第一行
				rowHeader = sheet.getRow(0);
				row = sheet.getRow(0);
				// 获取最大列数
				int colnum = row.getPhysicalNumberOfCells();
				for (int i = 1; i < rownum; i++) {
					Map<String, String> map = new LinkedHashMap<String, String>();
					row = sheet.getRow(i);
					if (row != null) {
						for (int j = 0; j < colnum; j++) {
							if(columns[j].equals(getCellFormatValue(rowHeader.getCell(j)))){
								cellData = (String) getCellFormatValue(row
										.getCell(j));
								map.put(columns[j], cellData);
							}
						}
					} else {
						break;
					}
					list.add(map);
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**	获取单个单元格数据
	 * @param cell
	 * @return
	 * @author lizixiang ,2018-05-08
	 */
	public Object getCellFormatValue(Cell cell) {
		Object cellValue = null;
		if (cell != null) {
			// 判断cell类型
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: {
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			}
			case Cell.CELL_TYPE_FORMULA: {
				// 判断cell是否为日期格式
				if (DateUtil.isCellDateFormatted(cell)) {
					// 转换为日期格式YYYY-mm-dd
					cellValue = cell.getDateCellValue();
				} else {
					// 数字
					cellValue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			case Cell.CELL_TYPE_STRING: {
				cellValue = cell.getRichStringCellValue().getString();
				break;
			}
			default:
				cellValue = "";
			}
		} else {
			cellValue = "";
		}
		return cellValue;
	}

}

测试类:

public static void main(String[] args) {
        String filePath = "D:/tools/托管文件.xls";
        String columns[] = {"账户名称","开户时间","证券帐号","开户机构"};
        List<Map<String, String>> list = new ExcelPoiReaderUtils().readExcel(filePath, columns);
        //遍历解析出来的list
        for (Map<String,String> map : list) {
            for (Entry<String,String> entry : map.entrySet()) {
                System.out.print(entry.getKey()+":"+entry.getValue()+",");
            }
            System.out.println();
        }
    }

excel文件:

使用poi读取xls、xlsx文件






相关标签: java xlsx poi