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，删除一般都可以解决。