excel导入数据--解析（2003与2007兼容）

最新推荐文章于 2022-07-27 01:09:26 发布

hzywy123456

最新推荐文章于 2022-07-27 01:09:26 发布

阅读量188

点赞数

分类专栏： java 基础文章标签： poi解析2003与2007版本兼容

本文链接：https://blog.csdn.net/hzywy123456/article/details/84447281

版权

java 基础专栏收录该内容

9 篇文章 1 订阅

订阅专栏

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;
	}

}