Java解析Excel檔案

以下是項目當中用到的很簡單的Java解析Excel的例子,和大家分享,需要引入jxl.jar包  

public ArrayList parseVideo() {  

        try {  

            Workbook book = Workbook.getWorkbook(new File("ua.xls"));  

            Sheet se = book.getSheet(1);得到第1個sheet(Excel通常會有3個Sheet)  

            int rownum = se.getRows();   //得到總行數  

            // Cell ce = null;  

            // StringBuffer su = new StringBuffer();  

            ArrayList list = new ArrayList();  

            DBManager db = new DBManager();  

            conn = db.getConnection();  

            stmt = conn.createStatement();  

            for (int i = 1; i < rownum; i++) {  

                Mobilemeter rea = new Mobilemeter();  

                rea.setMobileID(se.getCell(0, i).getContents());  //第i行的第1列  

                rea.setModel(se.getCell(3, i).getContents());//第i行的第4列  

                rea.setShortUA(se.getCell(11, i).getContents());  

                String arr = se.getCell(8, i).getContents();  

                String ayy = arr.replaceAll("\\u002A", "x");  

                rea.setPicFormat(se.getCell(8, i).getContents());  

                rea.setResourceRing(se.getCell(9, i).getContents());  

                rea.setRingFormat(se.getCell(10, i).getContents());  

                list.add(rea);  

            }  

            book.close();  

            return list;  

        } catch (Exception e) {  

            e.printStackTrace();  

            return null;  

        }  

    }  

1. 環境

   jdk1.6

   poi3.6

2. 解析工具類

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import org.apache.log4j.Logger;

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;

public class ExcelParser implements Iterator<List<String>> {

    private static final Logger logger = Logger.getLogger(ExcelParser.class);

    private InputStream is = null;

    private Iterator<?> rowIterator = null;

    public ExcelParser(String excelFile){

        HSSFWorkbook book = null;

        HSSFSheet sheet = null;

        try {

            is = new FileInputStream(excelFile);

            book = new HSSFWorkbook(is);

            sheet = book.getSheetAt(0);

            rowIterator = sheet.rowIterator();

        } catch (Exception e) {

            logger.error(e.getMessage(), e);

        }

    }

    public boolean hasNext(){

        return rowIterator.hasNext();

    public List<String> next(){

        HSSFRow row = (HSSFRow)rowIterator.next();

        List<String> erow = new ArrayList<String>();

        Iterator<?> cellIterator = row.cellIterator();

        while(cellIterator.hasNext()){

            HSSFCell cell = (HSSFCell)cellIterator.next();

            erow.add(this.getCellValue(cell));

        return erow;

    public void remove(){

        throw new UnsupportedOperationException("本EXCEL解析器是隻讀的."); 

    private String getCellValue(HSSFCell cell){

        String value = null;

        //簡單的查檢列類型

        switch(cell.getCellType())

        {

            case HSSFCell.CELL_TYPE_STRING://字元串

                value = cell.getRichStringCellValue().getString();

                break;

            case HSSFCell.CELL_TYPE_NUMERIC://數字

                long dd = (long)cell.getNumericCellValue();

                value = dd+"";

            case HSSFCell.CELL_TYPE_BLANK:

                value = "";

                break;    

            case HSSFCell.CELL_TYPE_FORMULA:

                value = String.valueOf(cell.getCellFormula());

            case HSSFCell.CELL_TYPE_BOOLEAN://boolean型值

                value = String.valueOf(cell.getBooleanCellValue());

            case HSSFCell.CELL_TYPE_ERROR:

                value = String.valueOf(cell.getErrorCellValue());

            default:

        return value;

    public void close(){

        if(is != null){

            try {

                is.close();

            } catch (IOException e) {

                logger.error(e.getMessage(), e);

            }

    }//close

}

3. 用法

ExcelParser ep = new ExcelParser("d:/test.xls");

        while(ep.hasNext()){

            List<String> row = ep.next();

            System.out.println(row.get(0) + ", " + row.get(1));

        ep.close();

本文轉自yunlielai51CTO部落格，原文連結：http://blog.51cto.com/4925054/1107659，如需轉載請自行聯系原作者