Excelインポートデータ--解析(2003と2007の互換性)

7137 ワード

Excelはjavaで解析し,最初は難しくなかったが,2003バージョンと2007以上のバージョンでの互換解析は困難であった.長いことやってやっとできた.次は私が修正したコードです.
package com.test;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.pojo.VHrUser;
 

/**

 *    Excel    

 */

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 org.apache.poi.ss.usermodel.Row row=null;

      


       public List readExcel(String excelFilePath) throws    IOException {

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

            version = version2003;

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

            version = version2007;

 

        List list = new ArrayList();

         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=((org.apache.poi.ss.usermodel.Row) row).getCell(j);

                           int type = cell.getCellType(); //          
                           String k="";
                           switch (type) { //       
                                       case Cell.CELL_TYPE_BLANK:
                                               k = "";
                                               break;
                                       case Cell.CELL_TYPE_BOOLEAN:
                                               k = cell.getBooleanCellValue() + "";
                                               break;
                                      case Cell.CELL_TYPE_ERROR:
                                               k = cell.getErrorCellValue() + "";
                                               break;
                                     case Cell.CELL_TYPE_FORMULA:
                                               k = cell.getCellFormula();
                                               break;
                                     case Cell.CELL_TYPE_NUMERIC:
                                                if(DateUtil.isCellDateFormatted(cell)){
                                                         k = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-mm-dd");//      
                                                 }else{
                                                          k=cell.getNumericCellValue()+"";   
                                                  }
                                                    break;
                                     case Cell.CELL_TYPE_STRING:
                                              k = cell.getStringCellValue();
                                              break;
                                    default:
                                                   break;
                                          }

                           

                            str[i][j]=k;
                         

                     }

              }

              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.setUsername(temp_str[s]);

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

                            vUser.setDate(temp_str[s+2]);
                            list.add(vUser);

                            break;

                     }
            }
              return list;

       }

 

       public static void main(String[] args){

              UserImport i=new UserImport();

              try {

                     List<VHrUser> vuser_list = i.readExcel("d:\\excel.xlsx");

                     for (VHrUser user : vuser_list) {

                            System.out.println("    =="+user.getUsername());

                     }
              } catch (IOException e) {

                     e.printStackTrace();

              }

              System.exit(0);

       }

}
package com.pojo;

public class VHrUser {
    private String username;
    private String password;
    private String date;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
    
}