使用apache poi操作excel ...

用apache POI 操作 Excel有几个关键的地方：

 

[1]读文件流

这个问题是一个IO问题

    InputStream in = new FileInputStream("/tmp/aaa.xls");  

 

[2]如何取得Excel的操作对象

这个也就相当于，Excel的工作区，在这个里面你可以取得当前excel文件的相关信息

    POIFSFileSystem poifs = new POIFSFileSystem(fis);   

    HSSFWorkbook wb = new HSSFWorkbook(poifs);  

HSSFWorkbook 对象，是我们最想得到的对象。

以后的所有操作都是从这里开始的。

 

[3]如何取得sheet的数目

    wb.getNumberOfSheets()  

 

[4]如何根据index取得sheet对象

    HSSFSheet sheet = wb.getSheetAt(0);  

有了Sheet就相当于取得了一张表一样。

 

[5]如何取得有效的行数

    int rowcount = sheet.getLastRowNum();  

 

[6]如何根据index取得行对象

    HSSFRow row = sheet.getRow(i);  

有了行对象，就可以取得每一个单元对象

 

[7]如何知道一个行有多少个单元

    colcount = row.getLastCellNum();  

 

[8]如何取得一个单元对象

    HSSFCell cell = row.getCell(j);  

 

 

[9]如何取得单元的值此处仅以字符串为例

    if(cell!=null){   

       System.out.println("cell is: "+cell.getStringCellValue());   

    }  

 

下面是我的测试的完整的程序。我也是从网上找的资料，然后自己又做了测试。在此又做了整理。

感谢网上提供此参考资料的朋友。

 

package demo.excel;   

 

import java.io.File;   

import java.io.FileInputStream;   

import java.io.FileNotFoundException;   

import java.io.IOException;   

import java.io.InputStream;   

import java.util.ArrayList;   

import java.util.List;   

  

import org.apache.poi.hssf.eventusermodel.HSSFRequest;   

import org.apache.poi.hssf.model.Sheet;   

import org.apache.poi.hssf.model.Workbook;   

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.poifs.filesystem.POIFSFileSystem;   

  

public class ExcelDemo {   

    public static void main(String[] args) {    

        File f = new File("/home/zhangyi/dell500.xls");     

        if (f.exists()) {    

            // read   

            try {   

                InputStream fis = new FileInputStream(f);    

                POIFSFileSystem poifs = new POIFSFileSystem(fis);   

                HSSFWorkbook wb = new HSSFWorkbook(poifs);   

                List retList = new ArrayList();   

                System.out.println("sheet number : " + wb.getNumberOfSheets());   

                                    

                HSSFSheet s = wb.getSheetAt(0);   

                System.out.println("sheet obj is : "+s);                        

                   

                for (int h = 0; h < wb.getNumberOfSheets(); ++h) {   

                    List list = new ArrayList();    

                    HSSFSheet sheet = wb.getSheetAt(h);   

                    int rowcount = sheet.getLastRowNum();   

                    rowcount++;   

                    System.out.print("-----sheet[" + h + "]: row count = "  

                            + rowcount);                        

                    int colcount = 0;   

                    for (int i = 0; i < rowcount; ++i) {   

                        //i=0 indicate the first

                        HSSFRow row = sheet.getRow(i);                          

                        // row   

                        if (row == null)   

                            continue; // without the row, break and continue;   

                        if (colcount == 0) { // colunm count set to column of   

                            // the first effective row   

                            colcount = row.getLastCellNum();   

                            System.out.println(", column count = " + colcount);   

                        String[] fieldValue = new String[colcount];   

                        for (short j = 0; j < colcount; ++j) { // column data in   

                                                                // the current  

                            HSSFCell cell = row.getCell(j);   

                            // fieldValue[j] = getCellStringValue(cell);   

                            if(cell!=null){   

                                System.out.println("cell: "+cell.getStringCellValue());   

                            }   

                            // System.out.println("cell is : " +cell.getCellComment()); 

                        } 

                        list.add(fieldValue);   

                    }  

                    retList.add(list);   

                }   

            } catch (FileNotFoundException e) {   

                // TODO Auto-generated catch block   

                e.printStackTrace();   

            } catch (IOException e) {   

                // TODO Auto-generated catch block   

                e.printStackTrace();   

            } 

        }

    }   

  }