使用poi Java封装对Excel内容的读取,支持合并列形式

package com.cimstech.file.read;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
 * TODO Excel文件解析
 * 使用说明：
 * 	1、创建ExcelFileContent对象，需了解5个参数意义及使用
 * 	2、调用getExcelDatas()方法,返回Excel数据
 *
 * @author 赖奇
 * @version 1.0 
 * @since 2014-6-13
 */
public class ExcelFileContent
{
	protected static String excelFilePath;//Excel文件路径
	protected static FileInputStream fis;//Excel文件输入流
	protected static String[] sheetNames;//要解析的Sheet名称集
	protected static int headIndex;//Sheet表头下标
	protected static String[] attributes;//需要获取Excel列
	protected static boolean isMerge;//是否需要合并的列
	protected static String[] mergeAttributes;//需要合并的列(在需要获取Excel列中)
	protected static Map<String, Integer> headDatas;//Sheet表头列
	protected static List<List<Object>> excelDatas = new ArrayList<List<Object>>();
	
	/**
	 * 构造方法
	 * @param excelFilePath Excel文件路径,包括Excel文件名
	 * @param sheetNames 要解析的Sheet名称集,null表示全部获取
	 * @param headIndex Sheet表头下标,从0开始
	 * @param attributes 需要获取Excel列
	 * @param isMerge 是否需要合并的列,若为false,mergeAttributes可设置为null
	 * @param mergeAttributes 需要合并的列(在需要获取Excel列中)
	 */
	public ExcelFileContent(String excelFilePath, String[] sheetNames, int headIndex, String[] attributes, boolean isMerge, String[] mergeAttributes)
	{
		ExcelFileContent.excelFilePath = excelFilePath;
		ExcelFileContent.sheetNames = sheetNames;
		ExcelFileContent.headIndex = headIndex;
		ExcelFileContent.attributes = attributes;
		ExcelFileContent.isMerge = isMerge;
		ExcelFileContent.mergeAttributes = mergeAttributes;
	}
	/**
	 * 
	 * TODO 获取Excel工作对象 
	 * @return
	 */
	public Workbook getWorkbook()
	{
		try
		{
			fis = new FileInputStream(excelFilePath);
			if(excelFilePath.endsWith(".xlsx")){
				 return new XSSFWorkbook(fis);
			}
			else if(excelFilePath.endsWith(".xls")){
				return new HSSFWorkbook(fis);
			}
			else
			{
				throw new Exception("Excel文件格式不合法!");
			}
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return null;
	} 
	/**
	 * 
	 * TODO 获取Excel数据 
	 * @return
	 */
	public List<List<Object>> getExcelDatas()
	{
		Workbook workbook = getWorkbook();
		//按指定的Sheet
		if(sheetNames != null)
		{
			for (int i = 0; i < sheetNames.length; i++)
			{
				Sheet sheet = workbook.getSheet(sheetNames[i]);
				getSheetDatas(sheet);
			}
		}
		//没有指定Sheet,则全部
		else
		{
			int length = workbook.getNumberOfSheets();
			for (int i = 0; i < length; i++)
			{
				Sheet sheet = workbook.getSheet(workbook.getSheetName(i));
				getSheetDatas(sheet);
			}
		}
		try
		{
			fis.close();//关闭流
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		//需要合并
		if(isMerge)
		{
			//记录需要合并的列Index
			List<Integer> mergerIndex = new ArrayList<Integer>();
			for (String mergerAttribute : mergeAttributes)
			{
				Integer integerIndex = headDatas.get(mergerAttribute);
				if(integerIndex != null)
				{
					mergerIndex.add(integerIndex);
				}
			}
			for (int i = 0; i < excelDatas.size(); i++)
			{
				List<Object> rowDatas = excelDatas.get(i);
				for (int j = 0; j < mergerIndex.size(); j++)
				{
					if(rowDatas.get(mergerIndex.get(j)).equals(""))
					{
						//在第一行不为空的情况下
						if(i != 0)
						{
							Object object = excelDatas.get(i-1).get(mergerIndex.get(j));
							rowDatas.set(mergerIndex.get(j), object);
						}
					}
				}
			}
		}
		return excelDatas;
	}
	/**
	 * 
	 * TODO 获取一个Sheet的数据 
	 * @param sheet
	 */
	public void getSheetDatas(Sheet sheet)
	{
		if(sheet != null)
		{
			headDatas = getHeadDatas(sheet.getRow(headIndex));//获取表头内容
			int eqCount = 0;//实际存在的列个数
			List<String> noEqAttributes = new ArrayList<String>();
			for (String attribute : attributes)
			{
				if(headDatas.get(attribute) != null)
				{
					eqCount++;
				}
				//不存在的列数
				else
				{
					noEqAttributes.add(attribute);
				}
			}
			System.out.println("Sheet表名称为["+sheet.getSheetName()+"]中需导入的列数与存在的列数之比："+attributes.length+":"+eqCount);
			if(attributes.length != eqCount)
			{
				System.out.println("实际不存在的属性集(程序中需要获取的列在Sheet中不存在的列)：");
				for (int i = 0; i < noEqAttributes.size(); i++)
				{
					System.out.print(noEqAttributes.get(i)+"\t");
				}
				System.out.println();
			}
			//全部等于进行解析该Sheet页的所有内容
			else
			{
				int rowCount = sheet.getLastRowNum();
				for (int i = (headIndex+1); i <= rowCount; i++)
				{
					Row row = sheet.getRow(i);
					if(row == null)
					{
						System.out.println("在第"+i+"行出现空行。");
						continue;
					}
					//一行数据
					List<Object> rowDatas = new ArrayList<Object>();
					for (int j = 0; j < attributes.length; j++)
					{
						Integer cellIndex = headDatas.get(attributes[j]);
						Object cellValue = getCellValue(row.getCell(cellIndex));
						if(cellValue == null)
						{
							cellValue = "";
						}
						rowDatas.add(cellValue);
					}
					excelDatas.add(rowDatas);
				}
			}
		}
	}
	/**
	 * 
	 * TODO 获取一行的内容 
	 * @param headRow 头行对象
	 * @return
	 */
	public Map<String, Integer> getHeadDatas(Row headRow)
	{
		Map<String, Integer> headDatas = new HashMap<String, Integer>();
		short countCellNum = headRow.getLastCellNum();
		for (int j = 0; j < countCellNum; j++)
		{
			Cell cell = headRow.getCell(j);
			if(cell == null){
				continue;
			}
			else
			{
				headDatas.put(String.valueOf(getCellValue(cell)), j);
			}
		}
		return headDatas;
	}
	/**
	 * 
	 * TODO 获取Cell的内容 
	 * @param cell 
	 * @return 
	 */
	public Object getCellValue(Cell cell){
		Object obj = null;
		int cellType = cell.getCellType();
		switch (cellType) {
			case Cell.CELL_TYPE_BOOLEAN:
				obj = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_FORMULA:
				obj = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				obj = cell.getNumericCellValue();
				break;
			case Cell.CELL_TYPE_STRING:
				obj = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				obj = "";
				break;
			default:
				obj = cell.getRichStringCellValue();
				System.out.println("未知类型");
		}
		return obj;
	}
	/**
	 * 
	 * TODO Main方法例子 
	 * @param args
	 */
	public static void main(String[] args)
	{
		
		String[] attributes = new String[]{"序号","应用","模块/功能名称","逻辑模型","字段名"};
		ExcelFileContent excelFileContent = new ExcelFileContent("E:\\数据管控解析文件\\营配逻辑模型转换\\营配逻辑模型分布表.xlsx", new String[]{"Sheet6"}, 0, attributes, true, new String[]{"应用", "模块/功能名称"});
		long currentTimeMillis = System.currentTimeMillis();
		System.out.println("程序开始时间："+currentTimeMillis);
		List<List<Object>> excelDatas2 = excelFileContent.getExcelDatas();
		for (int i = 0; i < excelDatas2.size(); i++)
		{
			List<Object> list = excelDatas2.get(i);
			for (int j = 0; j < list.size(); j++)
			{
				System.out.print(list.get(j)+"\t");
			}
			System.out.println();
		}
		long currentTimeMillis2 = System.currentTimeMillis();
		System.out.println("执行时间："+(currentTimeMillis2-currentTimeMillis)/1000+"秒");
	}
}

转载于:https://my.oschina.net/qiqitrue/blog/1579112