java使用poi解析Excel文件

本文取自http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.html

java中读取Excel文件并解析

• Excel2007及以前的文件使用[HSSFWorkbook]6
• Excel2007后的文件使用[XSSFWorkbook]6
  

  ---

使用poi的jar包。

Common.java

public class Common {
    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_FILE = "Not File!";
}

Util.java

public class Util {

    public static String getPostfix(String path){
        if( path == null || Common.EMPTY.equals(path.trim())){
            return Common.EMPTY;
        }

        if(path.contains(Common.POINT)){
            return path.substring(path.lastIndexOf(Common.POINT) + 1,path.length());
        }
        return Common.EMPTY;
    }
}

ReadExcel.java

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.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.manniu.ninecloud.entities.Devices;

/**
 * @Description 解析Excel文件
 * @data 2015-06-01
 * @author guo
 */
public class ReadExcel {
    public List<Devices> readExcel(String path) throws IOException{
        if (path == null || Common.EMPTY.equals(path)) {
            return null;
        }else {
            String postfix = Util.getPostfix(path);
            if(!Common.EMPTY.equals(postfix)){
                if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
                    return readXlsx(path);
                }else if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                }
            } else {
                System.out.println(path+Common.NOT_FILE);
            }
        }
        return null;
    }


    /**
    * @读取2007及之前的Excel文档
    */
    public List<Devices> readXlsx(String path) throws IOException {
        InputStream io = new FileInputStream(path);
        XSSFWorkbook xsBook = new XSSFWorkbook(io);

        Devices devices = null;
        List<Devices> list = new ArrayList<Devices>();

        //循环行
        for (int rowNum = 0; rowNum < xsBook.getNumberOfSheets(); rowNum++) {
            XSSFSheet xSheet = xsBook.getSheetAt(rowNum);
            if (xSheet == null) {
                continue;
            }

            for (int num = 1; num < xSheet.getLastRowNum(); num++) {
                XSSFRow xRow = xSheet.getRow(num);
                if (xRow != null) {
                    devices = new Devices();
                    devices.setState(Integer.parseInt(getValue(xRow.getCell(0))));
                    devices.setType(Integer.parseInt(getValue(xRow.getCell(1))));
                    devices.setModel(getValue(xRow.getCell(2)));
                    devices.setVer(getValue(xRow.getCell(3)));

                    devices.setPn(getValue(xRow.getCell(4)));
                    devices.setVn(getValue(xRow.getCell(5)));
                    devices.setSn(getValue(xRow.getCell(6)));
                    list.add(devices);
                }
            }
        }
        return list;
    }




    //读取Excel2007前的.xls文件
    public List<Devices> readXls(String path) throws IOException {
        InputStream io = new FileInputStream(path);
        HSSFWorkbook hBook = new HSSFWorkbook(io);

        Devices devices = null;
        List<Devices> list = new ArrayList<Devices>();

        for (int rowNum = 0; rowNum < hBook.getNumberOfSheets(); rowNum++) {
            HSSFSheet hSheet = hBook.getSheetAt(rowNum);
            if (hSheet == null) {
                continue;
            }

            for (int num = 1; num < hSheet.getLastRowNum(); num++) {
                HSSFRow sRow = hSheet.getRow(num);
                if (sRow != null) {
                    devices = new Devices();
                    devices.setState(Integer.parseInt(getValue(sRow.getCell(0))));
                    devices.setType(Integer.parseInt(getValue(sRow.getCell(1))));

                    devices.setModel(getValue(sRow.getCell(2)));
                    devices.setVer(getValue(sRow.getCell(3)));

                    devices.setPn(getValue(sRow.getCell(4)));
                    devices.setVn(getValue(sRow.getCell(5)));
                    devices.setSn(getValue(sRow.getCell(6)));
                    list.add(devices);
                }
            }
        }
        return list;
    }





    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xCell){
        if(xCell.getCellType() == xCell.CELL_TYPE_BOOLEAN){
            return String.valueOf(xCell.getBooleanCellValue());
        }else if (xCell.getCellType() == xCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xCell.getNumericCellValue());
        }else {
            return String.valueOf(xCell.getStringCellValue());
        }   
    }




    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hCell){
        if (hCell.getCellType() == hCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hCell.getBooleanCellValue());
        }else if (hCell.getCellType() == hCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hCell.getNumericCellValue());
        }else {
            return String.valueOf(hCell.getStringCellValue());
        }
    }






    //Test main
    public static void main(String[] args) throws IOException {

        String excel2003 = "D://test/test.xls";
        String excel2010 = "D://test/test1.xlsx";

        ReadExcel re = new ReadExcel();
        List<Devices> list = re.readExcel(excel2003);

        if(list != null){
            for (Devices devices : list) {
                System.out.println(devices.getState()+"--"+devices.getType());
            }
        }
    }
}

poi 包下载地址