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+"秒");
}
}