package Common;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;

/**
*
* @author LFF
* @version 0.5 Excel文件操作幫助類
*
*/
public class ExcelPOIHelper {

// D盤建一個空的workbook.xls文件
public static void Create(String path, String name) {
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream("D:/workbook.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* 取出Excel所有工作簿名
*
* @param fullPath
* Excel文件完整地址("D:/workbook.xls")
* @return 工作簿名列表
*/
public static List<String> GetSheets(String fullPath) {
List<String> result = new ArrayList<String>();
try {
FileInputStream file = new FileInputStream(fullPath);
POIFSFileSystem ts = new POIFSFileSystem(file);
Workbook workbook = new HSSFWorkbook(ts);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
String sheetName = workbook.getSheetName(i);
result.add(i, sheetName);
}
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return result;
}

/**
* 取工作簿中所有的行
*
* @param fullPath
* Excel文件完整地址("D:/workbook.xls")
* @param sheetName
* 工作簿名
* @return 鍵值對：<RowKey,<ColumnName, Value>>
*/
public static Map<String, List<Map<String, String>>> GetRows(
String fullPath, String sheetName) {
Map<String, List<Map<String, String>>> resultRow = new HashMap<String, List<Map<String, String>>>();
List<Map<String, String>> resultCells;
Map<String, String> resultCell;
try {
FileInputStream file = new FileInputStream(fullPath);
POIFSFileSystem ts = new POIFSFileSystem(file);
Workbook workbook = new HSSFWorkbook(ts);
Sheet sheet = workbook.getSheet(sheetName);

int rowCounts = sheet.getPhysicalNumberOfRows();// 行數(shù)
int columnCounts = sheet.getRow(0).getPhysicalNumberOfCells(); // 列數(shù)

for (int i = 1; i < rowCounts; i++) {
Row row = sheet.getRow(i);// 循環(huán)取第一行之后的每一行
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);

resultCells = new ArrayList<Map<String, String>>();
resultCell = new HashMap<String, String>();

String rowKey = row.getCell(0).toString();

for (int j = 1; j < columnCounts; j++) {
Cell cell = row.getCell(j);// 循環(huán)取第一列之后的每一列
if (null != cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);

String columnName = sheet.getRow(0).getCell(j)
.toString();
String cellValue = cell.toString();

resultCell.put(columnName, cellValue);
}
}
resultCells.add(resultCell);
resultRow.put(rowKey, resultCells);
}
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

return resultRow;
}
}