import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class excel {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
String fpath = "D:\\Project\\Excel.xls";
File file = new File(fpath);
List lst = null;
try {
lst = read(file, 0, true);
for ( int i = 0 ; i < lst.size(); i++ ) {
System.out.println(lst.get(i));
}
} catch ( Exception e ) {
System.out.println(e);
}
}
/*===============================================================================================
*
* Excel 파일을 읽어 List로 리턴한다.
*
* Parameter
* File : Excel 파일
* nSheetIndex : 읽어올 Excel 파일의 Sheet Index
* bDefineTitle : true->첫 번째 Row를 Key로 지정
* false->Cell_x 로 임의의 Key 지정
*
*===============================================================================================
* */
public static List read(File file, int nSheetIndex, boolean bDefineTitle) throws Exception {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(nSheetIndex);
HSSFRow row = null;
HSSFCell cell = null;
List lst = null;
HashMap map = null;
HashMap t_map = null;
int nRow, nCell;
// Row Count Read
nRow = sheet.getPhysicalNumberOfRows();
lst = new ArrayList();
t_map = new HashMap();
for ( int i = 0 ; i < nRow ; i++ ) {
row = sheet.getRow(i);
if ( row != null ) {
// Read Row -> Cell Count Read
nCell = row.getPhysicalNumberOfCells();
if ( i == 0 ) {
for ( int j = 0 ; j < nCell ; j++ ) {
cell = row.getCell(j);
t_map.put(String.valueOf(j), (bDefineTitle)?getValue(cell):"Cell_"+j);
}
} else {
map = new HashMap();
for ( int j = 0 ; j < nCell ; j++ ) {
cell = row.getCell(j);
map.put(t_map.get(String.valueOf(j)), getValue(cell));
}
lst.add(map);
}
}
}
return lst;
}
/*===============================================================================================
*
* Cell Type 에 알맞은 형식으로 데이터를 읽어와 String 리턴한다.
*
*===============================================================================================
* */
public static String getValue(HSSFCell cell) {
String value = "";
if ( cell != null ) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA :
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = String.valueOf(cell.getNumericCellValue()); //double
break;
case HSSFCell.CELL_TYPE_STRING :
value = cell.getStringCellValue(); //String
break;
case HSSFCell.CELL_TYPE_BLANK :
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN :
value = String.valueOf(cell.getBooleanCellValue()); //boolean
break;
case HSSFCell.CELL_TYPE_ERROR :
value = String.valueOf(cell.getErrorCellValue()); // byte
break;
default :
}
}
return value.trim();
}
}