package?com.askingdata.excel.util;

import?java.io.File;
import?java.util.ArrayList;
import?java.util.linkedHashMap;
import?java.util.List;
import?java.util.Map;

import?org.apache.poi.openxml4j.opc.OPCPackage;
import?org.apache.poi.openxml4j.opc.PackageAccess;
import?org.apache.poi.ss.usermodel.Cell;
import?org.apache.poi.ss.usermodel.Cellstyle;
import?org.apache.poi.ss.usermodel.DataFormatter;
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.xssf.usermodel.XSSFCell;
import?org.apache.poi.xssf.usermodel.XSSFWorkbook;

public?class?ExcelUtil?{

	/**
	?*?讀取xlsx文件，不適用于讀取大文件
	?*/
	public?static?List>?read（String?filePath）?throws?Exception?{
		List>?result?=?new?ArrayList<>（）;
		Workbook?wb?=?new?XSSFWorkbook（new?File（filePath））;
		for?（Sheet?sheet?:?wb）?{
			for?（Row?row?:?sheet）?{
				List?rowData?=?getRowData（row）;
				result.add（rowData）;
			}
		}
		wb.close（）;
		return?result;
	}

	/**
	?*?讀取xlsx文件，不適用于讀取大文件
	?*?
	?*?@return?map:?key->sheet?name?value->rowData
	?*/
	public?static?Map>>?readMultiSheet（String?filePath）
			throws?Exception?{
		Map>>?result?=?new?linkedHashMap<>（）;
		Workbook?wb?=?new?XSSFWorkbook（new?File（filePath））;
		for?（Sheet?sheet?:?wb）?{
			List>?sheetResult?=?new?ArrayList<>（）;
			for?（Row?row?:?sheet）?{
				List?rowData?=?getRowData（row）;
				sheetResult.add（rowData）;
			}
			result.put（sheet.getSheetName（）?sheetResult）;
		}
		wb.close（）;
		return?result;
	}

	/**
	?*?以SAX方式讀取xlsx文件，讀取后將結果全部加載到內存，適用于讀取較大文件
	?*?
	?*?@param?filePath
	?*????????????文件路徑
	?*?@param?minColumns
	?*????????????補齊到多少列，-1表示不補齊
	?*/
	public?static?Listject>>?readBigFile（String?filePath?int?minColumns）
			throws?Exception?{
		File?xlsxFile?=?new?File（filePath）;
		OPCPackage?p?=?OPCPackage.open（xlsxFile.getPath（）?PackageAccess.READ）;
		Xlsx2ListData?xlsx2ListData?=?new?Xlsx2ListData（p?minColumns?null）;
		Listject>>?result?=?xlsx2ListData.process（）;
		p.close（）;
		return?result;
	}

	/**
	?*?以SAX方式讀取xlsx文件，讀取每一行調用RowDataProcesser，適用于讀超大文件
	?*?
	?*?@param?filePath
	?*????????????文件路徑
	?*?@param?minColumns
	?*????????????補齊到多少列，-1表示不補齊
	?*?@param?rowDataProcesser
	?*????????????處理每一行的數據
	?*/
	public?static?void?readBigFile（String?filePath?int?minColumns
			RowDataProcesser?rowDataProcesser）?throws?Exception?{
		File?xlsxFile?=?new?File（filePath）;
		OPCPackage?p?=?OPCPackage.open（xlsxFile.getPath（）?PackageAccess.READ）;
		Xlsx2ListData?xlsx2ListData?=?new?Xlsx2ListData（p?minColumns
				rowDataProcesser）;
		xlsx2ListData.process（）;
		p.close（）;
	}

	private?static?List?getRowData（Row?row）?{
		List?rowData?=?new?ArrayList<>（）;
		int?cellNum?=?row.getLastCellNum（）;
		for?（int?i?=?0;?i?			Cell?cell?=?row.getCell（i）;
			if?（cell?==?null）?{
				rowData.add

?屬性????????????大小?????日期????時間???名稱
-----------?---------??----------?-----??----
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\
?????文件?????????737??2016-07-30?14:36??ExcelUtil\.classpath
?????文件?????????368??2016-07-30?13:51??ExcelUtil\.project
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\.settings\
?????文件??????????57??2016-07-30?14:16??ExcelUtil\.settings\org.eclipse.core.resources.prefs
?????文件?????????587??2016-07-12?11:04??ExcelUtil\.settings\org.eclipse.jdt.core.prefs
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\com\
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\com\askingdata\
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\com\askingdata\excel\
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\com\askingdata\excel\util\
?????文件????????5234??2016-08-16?14:37??ExcelUtil\bin\com\askingdata\excel\util\ExcelUtil.class
?????文件????????2355??2016-08-17?19:21??ExcelUtil\bin\com\askingdata\excel\util\Main$1.class
?????文件????????5833??2016-08-17?19:21??ExcelUtil\bin\com\askingdata\excel\util\Main.class
?????文件????????1417??2016-08-17?19:18??ExcelUtil\bin\com\askingdata\excel\util\Main2$1.class
?????文件????????6099??2016-08-17?19:18??ExcelUtil\bin\com\askingdata\excel\util\Main2.class
?????文件?????????248??2016-08-12?16:04??ExcelUtil\bin\com\askingdata\excel\util\RowDataProcesser.class
?????文件????????3583??2016-08-12?16:04??ExcelUtil\bin\com\askingdata\excel\util\Xlsx2ListData$SheetToList.class
?????文件????????4953??2016-08-12?16:04??ExcelUtil\bin\com\askingdata\excel\util\Xlsx2ListData.class
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\bin\test\
?????文件????????2654??2016-08-18?16:38??ExcelUtil\bin\test\Hufan.class
?????文件????????1488??2016-08-12?16:04??ExcelUtil\bin\test\Main$1.class
?????文件?????????675??2016-08-12?16:04??ExcelUtil\bin\test\Main.class
?????目錄???????????0??2016-08-18?17:53??ExcelUtil\doc\
?????目錄???????????0??2016-08-18?17:52??ExcelUtil\lib\
?????文件??????185140??2016-05-21?18:07??ExcelUtil\lib\commons-io-2.4.jar
?????文件??????398520??2016-04-16?20:08??ExcelUtil\lib\mongo-2.10.1.jar
?????文件?????2528204??2016-07-12?12:27??ExcelUtil\lib\poi-3.14-20160307.jar
?????文件?????1286511??2016-07-12?12:36??ExcelUtil\lib\poi-ooxml-3.14-20160307.jar
?????文件?????5919795??2016-07-12?12:48??ExcelUtil\lib\poi-ooxml-schemas-3.14-20160307.jar
?????文件?????1367760??2016-07-30?11:16??ExcelUtil\lib\xercesImpl.jar
............此處省略16個文件信息