package com.haiyisoft.iecp.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* excel读取数据
*
* @author 白亚龙
*
* <p>
* 2018-04-19 白亚龙 新建
* </p>
*/
public class ReadExceTool {
/**
* 导出Excel文件的信息 形式为：List<List<Object>> 支持格式为 ： xlsx，xls
* 第一层list为文件sheet信息 第二层list为一个sheet页面的信息 第三层为sheet内容某一行的信息
* 文件信息由上自下由左自右查找
* 分数数据暂时只能读取成小数保存
* @param File 输入文件
* @param String 文件后缀
* @return List<List<Object>> （最内层object为一个List<Object>数组）
*/
public List<List<Object>> readExcel(File file, String extension) {
List<List<Object>> fileObject = new java.util.ArrayList<List<Object>>();
Workbook wb = null;
// 读取文件格式判断，如果是excel文件则返回Workbook实例
wb = readFile(file, extension);
if(wb != null){
// 循环sheet页面，添加第一层list
for(int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++){
Sheet sheet = wb.getSheetAt(numSheet);
if(sheet == null){
continue;
}
/**
* column: sheet页面最大列数;
* 根据 sheet页最大行，最大列对页面进行循环处理
*/
List<Object> sheetObjects = new ArrayList<Object>();
// 获取数据最小列数
int startColumn = getFirstColNum(sheet, sheet.getLastRowNum());
// 获取数据最大列数
int endColumn = getLastColNum(sheet, sheet.getLastRowNum());
// 根据最大行数进行循环，将行数据数据放入第二层list
for(int i =0; i <= sheet.getLastRowNum(); i++){
Row row = sheet.getRow(i);
List<Object> rowValues = new ArrayList<Object>();
// 空行忽略
if(row == null){
continue;
}else {
// 每行内根据最大列数循环，将单元数据数据放入第三层list
for(int j = startColumn; j < endColumn; j++){
Cell cell = row.getCell(j);
// 空单元忽略
String cellValue = null;
if(cell == null) continue;
// 判断是否为合并单元格
if(isMergedRegion(sheet, i, j)){
// 合并单元格取值（所有单元格取第一个单元格的值，跨列合并单元格 只取第一个数据）
cellValue = getMergedValue(sheet, i, j);
}else{
// 单元格取值
cellValue = getValue(cell);
}
// 如果是跨列合并单元格 只取第一个数据
if(cellValue != null && cellValue.equals("ignoredData"))continue;
else rowValues.add(cellValue);
}
if (rowValues.size()>0) {
sheetObjects.add(rowValues);
}
}
}
if (sheetObjects.size()>0) {
fileObject.add(sheetObjects);
}
}
/*//遍历解析出来的list (数据验证)
for (List<Object> sheetObjects : fileObject) {
for (Object rowObjects : sheetObjects) {
List<Object> cellValues = (List<Object>) rowObjects;
for (int i = 0; i < cellValues.size(); i++) {
System.out.print(cellValues.get(i) + ",");
}
System.out.println();
}
System.out.println();
}*/
}
return fileObject;
}
/**
* 求Excel某一工作簿0~某行内的最小列数
* @param sheet 工作簿
* @param rowNum 行数
* @return
*/
private static int getFirstColNum(Sheet sheet, int lastRowNum) {
if(sheet == null){
return 0;
}
// 初始值设置为一个较大的值
int firstCol = 1000;
Row row = null;
// 根据行循环，取第一个非空值的最小下标
for(int i = 0; i <= lastRowNum; i++){
row = sheet.getRow(i);
if(row != null){
for(int j = 0; j < row.getLastCellNum(); j++){
if(row.getCell(j) != null){
if(firstCol > j){
firstCol = j;
break;
}
}
}
}
}
return firstCol;
}
/**
* 求Excel某一工作簿0~某行内的最大列数
* @param sheet 工作簿
* @param rowNum 行数
* @return
*/
private static int getLastColNum(Sheet sheet, int rowNum) {
if(sheet == null ){
return 0;
}
int lastCol = 0;
Row row = null;
for(int i = 0; i <= rowNum; i++){
row = sheet.getRow(i);
if(row != null){
int temp = row.getLastCellNum();
if(temp > lastCol){
lastCol = temp;
}
}
}
return lastCol;
}
/**
* 获取合并单元格的值
* @param sheet 工作簿
* @param rowNum 单元行
* @param columnIndex 单元列
* @return Object 单元值
*/
private static String getMergedValue(Sheet sheet, int rowNum,
int columnIndex) {
// 获取合并单元格个数
int mergeNum = sheet.getNumMergedRegions();
String value = null;
// 循环判断单元格所在合并单元格，合并单元格内所有单元格赋相同的值
for(int i = 0; i < mergeNum; i++){
CellRangeAddress range = sheet.getMergedRegion(i);
// 数据依次为合并单元格的第一列、最后一列、第一行、最后一行
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
// 判断是否在次单元格区间内
if(rowNum >= firstRow && rowNum <= lastRow){
// 所有单元格取第一个单元格的值，跨列合并单元格 只取第一个数据
if(columnIndex == firstColumn){
value = getValue(sheet.getRow(firstRow).getCell(firstColumn));
}else if(columnIndex > firstColumn && columnIndex <= lastColumn){
value = "ignoredData";
}
}
}
return value;
}
/**
* 判断是不是合并单元格
* @param sheet Excel工作簿
* @param rowNum 单元格行下标
* @param columnIndex 单元格列下标
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int rowNum,
int columnIndex) {
// 获取合并单元格个数
int sheetMergedNum = sheet.getNumMergedRegions();
for(int i = 0; i < sheetMergedNum; i++){
CellRangeAddress range = sheet.getMergedRegion(i);
// 数据依次为合并单元格的第一列、最后一列、第一行、最后一行
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
// 判断是否在次单元格区间内,如果是则返回true
if(rowNum >= firstRow && rowNum <= lastRow){
if(columnIndex >= firstColumn && columnIndex <= lastColumn){
return true;
}
}
}
// 如果不在所有的合并单元格内则返回false
return false;
}
/**
* 读取文件类型，如果是excel则返回对象实例， 如果不是则返回null
* @param filePath 文件路径
* @return Workbook实例
*/
private static Workbook readFile(File file, String extension) {
Workbook wb = null;
if(file != null && extension != null && !extension.equals("")){
try {
InputStream inputStream = null;
inputStream = new FileInputStream(file);
if(extension.equals("xls")){
wb = new HSSFWorkbook(inputStream);
}else if(extension.equals("xlsx")){
wb = new XSSFWorkbook(inputStream);
}else{
wb = null;
}
} catch (FileNotFoundException e) {
System.out.println("未找到文件位置！");
e.printStackTrace();
} catch (IOException e) {
System.out.println("文件类型不正确，请重新选择！");
e.printStackTrace();
}
}
return wb;
}
/**
* 获取单元格的值，并格式化 日期格式不变 其他格式转换为字符串类型
* @param cell 单元格
* @return
*/
private static String getValue(Cell cell){
String cellValue = null;
// 格式化数据对象
DecimalFormat df = new DecimalFormat("0.######");
if(cell != null){
// 获取单元格数据类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
DateFormat formater = null;
Date d = cell.getDateCellValue();
if(cell.getCellStyle().getDataFormat() == 31){
formater = new SimpleDateFormat("yyyy年MM月dd日");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 58){
formater = new SimpleDateFormat("MM月dd日");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 14){
formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 57){
formater = new SimpleDateFormat("yyyy年MM月");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 20){
formater = new SimpleDateFormat("HH:mm");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 32){
formater = new SimpleDateFormat("HH时mm分");
cellValue = formater.format(d);
}else if(cell.getCellStyle().getDataFormat() == 9){
cellValue = df.format(cell.getNumericCellValue()*100) + "%";
}else if(DateUtil.isCellDateFormatted(cell)){
formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = formater.format(d);
}else{
cellValue = df.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
try {
cellValue = df.format(cell.getNumericCellValue());
} catch (Exception e) {
cellValue = cell.getCellFormula();
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
cellValue = "";
break;
}
}
return cellValue;
}
}