/**
* 读取出filePath中的所有数据信息
* @param filePath excel文件的绝对路径
*
*/
public static void getDataFromExcel(String filePath){
List> list = new ArrayList>();
//判断是否为excel类型文件
if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")){
System.out.println("文件不是excel类型");
}
FileInputStream fis =null;
Workbook wookbook = null;
int flag = 0;
try{
//获取一个绝对地址的流
fis = new FileInputStream(filePath);
}catch(Exception e){
e.printStackTrace();
}
try{
//2003版本的excel,用.xls结尾
wookbook = new HSSFWorkbook(fis);//得到工作簿
}catch (Exception ex){
try{
fis = new FileInputStream(filePath);
//2007版本的excel,用.xlsx结尾
wookbook = new XSSFWorkbook(filePath);//得到工作簿
} catch (IOException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
//获得表头
Row rowHead = sheet.getRow(0);
//根据不同的data放置不同的表头
Map headMap = new HashMap();
//判断表头是否合格 ------------------------这里看你有多少列
if(rowHead.getPhysicalNumberOfCells() != 2){
System.out.println("表头列数与要导入的数据库不对应");
}
try{
//----------------这里根据你的表格有多少列
while (flag < 2){
Cell cell = rowHead.getCell(flag);
if (getRightTypeCell(cell).toString().equals("姓名")){
headMap.put("personName", flag);
}
if (getRightTypeCell(cell).toString().equals("性别")){
headMap.put("sex", flag);
}
flag++;
}
} catch (Exception e){
e.printStackTrace();
System.out.println("表头不合规范,请修改后重新导入");
}
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
//要获得属性
String name = "";
String sex = "";
List listPious = new ArrayList();
List listPiousInfo = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if(0 == totalRowNum){
System.out.println("Excel内没有数据!");
}
Cell cell_1 = null,cell_2 = null;
//获得所有数据
for(int i = 1 ; i <= totalRowNum ; i++){
//获得第i行对象
Row row = sheet.getRow(i);
try{
cell_1 = row.getCell(headMap.get("personName"));
cell_2 = row.getCell(headMap.get("sex"));
} catch (Exception e){
e.printStackTrace();
System.out.println("获取单元格错误");
}
try{
if(cell_1 != null && cell_2 != null){
PiousRegister piousReg = new PiousRegister();
name = (String) getRightTypeCell(cell_1);
piousReg.setPersonName(name);
sex = (String) getRightTypeCell(cell_2);
piousReg.setPersonSex(sex.equals("男") ? 0 : 1);
System.out.println("名字:"+name+",性别:"+sex);
}
} catch (ClassCastException e){
e.printStackTrace();
System.out.println("数据不全是数字或全部是文字!");
}
}
}
/**
*
* @param cell 一个单元格的对象
* @return 返回该单元格相应的类型的值
*/
public static Object getRightTypeCell(Cell cell){
DecimalFormat df = new DecimalFormat("#");
Object object = null;
switch(cell.getCellType()){
case Cell.CELL_TYPE_STRING :{
object=cell.getStringCellValue();
break;
}
case Cell.CELL_TYPE_NUMERIC :{
if(HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return df.format(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_FORMULA :{
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
object=cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_BLANK :{
cell.setCellType(Cell.CELL_TYPE_BLANK);
object=cell.getStringCellValue();
break;
}
}
return object;
}