POI操作Excel,兼容2003/2007

转载http://blog.163.com/fengzhisha0914@126/blog/static/150314785201102625119277/
需要jar包：

1.poi-ooxml-3.6-20091214.jar
2.poi-3.6-20091214.jar
3.poi-contrib-3.6-20091214.jar
4.poi-examples-3.6-20091214.jar
5.poi-ooxml-schemas-3.6-20091214.jar
6.poi-scratchpad-3.6-20091214.jar
7.xmlbeans-2.3.0.jar
8.openxml4j-bin-beta.jar
9.geronimo-stax-api_1.0_spec-1.0.jar







package com.test;



import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.DateFormat;

import java.text.ParseException;

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.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;





import com.njdt_oa.common.domain.VHrUser;

import com.njdt_oa.common.util.IExcelReader;



/**

* 用户从Excel批量导入

*

* @author PF

*/

public class UserImport {



private static final int version2003 = 2003;

private static final int version2007 = 2007;

private static int version = version2003;

private static Workbook wb = null;

private static Sheet sheet=null;

private static Cell cell = null;

private static Row row=null;



@SuppressWarnings("static-access")

public List<VHrUser> readExcel(String excelFilePath) throws IOException {

if (excelFilePath.endsWith(".xls"))

version = version2003;

else if (excelFilePath.endsWith(".xlsx"))

version = version2007;



List<VHrUser> list = new ArrayList<VHrUser>();

InputStream stream=null;

if (version == version2003) {

stream = new FileInputStream(excelFilePath);

wb = (Workbook) new HSSFWorkbook(stream);

} else if (version == version2007) {

wb = (Workbook) new XSSFWorkbook(excelFilePath);

}





sheet = wb.getSheetAt(0);



// 行数(从0开始,相当于最后一行的索引),列数

int count_row=sheet.getLastRowNum(),count_cell=sheet.getRow(0).getPhysicalNumberOfCells();



String[][] str=new String[count_row][count_cell];



for (int i = 0; i < count_row; i++) {

for (int j = 0; j < count_cell; j++) {

row=sheet.getRow(i+1);

cell=row.getCell(j);



String obj_content="";

if(cell.getCellType()==cell.CELL_TYPE_BLANK)

obj_content="";

else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC)

obj_content=(int)cell.getNumericCellValue()+"";

else if(cell.getCellType()==cell.CELL_TYPE_STRING)

obj_content=cell.getStringCellValue();



str[i][j]=obj_content;

}

}

cell=null;

row=null;

sheet=null;

wb=null;





for (int k = 0; k < str.length; k++) {

String[] temp_str=str[k];

VHrUser vUser=new VHrUser();

for (int s = 0; s < temp_str.length; s++) {

vUser.setPaixu(new Long(temp_str[s]));

vUser.setOamail(temp_str[s+1]);

vUser.setUsername(temp_str[s+2]);

vUser.setPingying(temp_str[s+3]);

vUser.setUsersex(temp_str[s+4]);

list.add(vUser);

break;

}

}

return list;

}



public static void main(String[] args){

UserImport i=new UserImport();

try {

List<VHrUser>vuser_list = i.readExcel("D:\\Users\\Administrator\\Desktop\\Import_Test.xlsx");

for (VHrUser user : vuser_list) {

System.out.println("用户姓名=="+user.Username);

}

} catch (IOException e) {

e.printStackTrace();

}

System.exit(0);

}

}







注意jar包冲突，如果出现org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException。。。检查是否有xbean.xml，删除一般都可以解决。