poi解析excel

在我们的项目当中经常会遇到把数据导入到Excel中，或者读取Excel中的数据到数据库中，用Apache POI可以很方便的实现，Apache POI是Apache基金会的开放源码函式库，可以在其官网上下载其Jar包，官网是http://poi.apache.org，下载后把里面所有的jar包拷贝到项目中(其中不仅仅是根目录下的Jar包)，好，我们先来看看如何写入数据到Excel中，注意，这里讲的是针对Excel 2007的，如果是以前的版本的，读取的方式是不一样的，请注意：

Java代码复制代码
import java.io.FileNotFoundException;  
import java.io.FileOutputStream;  
import java.io.IOException;  
 
import org.apache.poi.ss.usermodel.CreationHelper;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
/** 
* 写入数据到Excel 
* @author http://www.lookhan.com 
* 
*/ 
public class WriteExcel{  
       
      public static void main(String[] args){  
          try{  
              Workbook wb = new XSSFWorkbook();  
              CreationHelper createHelper = wb.getCreationHelper();  
              //创建页  
              Sheet sheet = wb.createSheet("sheet1");  
              // 创建行  
              Row row = sheet.createRow((short) 0);  
              // 创建单元格  
              row.createCell(0).setCellValue(258258258);  
              row.createCell(1).setCellValue(0.67);  
              row.createCell(2).setCellValue(createHelper.createRichTextString("http://www.lookhan.com"));  
              row.createCell(3).setCellValue(createHelper.createRichTextString("Java知识笔记"));  
              // 写入文件  
              FileOutputStream fileOut = null;  
              fileOut = new FileOutputStream("D:\\lookhan.xlsx");  
              wb.write(fileOut);  
              fileOut.close();  
              System.out.println("写入成功！");  
          } catch (FileNotFoundException e){  
              e.printStackTrace();  
          } catch (IOException e){  
              e.printStackTrace();  
          }  
      }  
        
}  
上面的测试类运行后，你就能在D盘下看到Excel文件了。

再看看如何读取Excel，首先定义一个自定义异常类，便于提示在读取Excel文件时出错的地方，因为一般对Excel的读取情况常见的是导入Excel数据到数据库中，一般如果用户在导入出错的话，可能是某个单元格的格式不对，用一个自定义异常可以很好的提示：

Java代码复制代码
/** 
* 读取Excel时格式错误的自定义Exception 
* @author http://www.lookhan.com 
* 
*/ 
public class ExcelFormatException extends Exception {  
 
    private static final long serialVersionUID = 3435456589196458401L;  
    private int row;  
    private int column;  
      
    public ExcelFormatException(String message, int row, int column){  
        super(message);  
        this.row = row;  
        this.column = column;  
    }  
    //出错的行  
    public int getRow() {  
        return row;  
    }  
    //出错的列  
    public int getColumn() {  
        return column;  
    }  
      
} 
再来看看测试类：

Java代码复制代码
import java.io.FileInputStream;  
import java.io.InputStream;  
 
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.DateUtil;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.ss.usermodel.WorkbookFactory;  
/** 
* 测试对Excel的读取 
* @author http://www.lookhan.com 
* 
*/ 
public class ReaderExcel {  
 
    public static void main(String[] args){  
        String src = "D:\\lookhan.xlsx";  
        try {  
            ReaderExcel test = new ReaderExcel();  
            test.getExcel(src);  
        } catch (Exception e) {  
            ExcelFormatException excelException = (ExcelFormatException)e;  
            System.out.println(excelException.getMessage()+"行："+excelException.getColumn()+"列："+excelException.getRow());  
        }  
    }  
 
    public void getExcel(String src) throws Exception{  
          
        InputStream inp;  
        inp = new FileInputStream(src);  
        Workbook wb = WorkbookFactory.create(inp);  
        //读取第一页  
        Sheet sheet = wb.getSheetAt(0);  
        //从第二行开始到最后一行(第一行是标题)  
        for(int i=1; i1;i++){  
            Row row = sheet.getRow(i);  
            //循环四列(Excel是四列)  
            for (int j=0; j<4; j++){  
                Cell cell = row.getCell(j);  
                if(j == 0){  
                    switch (cell.getCellType()){  
                        case Cell.CELL_TYPE_NUMERIC:  
                            if (DateUtil.isCellDateFormatted(cell)){  
                                System.out.println(cell.getDateCellValue());  
                            } else {  
                                throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                            }  
                            break;  
                        default:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                    }  
                }  
                if(j == 1){  
                    switch (cell.getCellType()){  
                        case Cell.CELL_TYPE_STRING:  
                            System.out.println(cell.getRichStringCellValue());  
                            break;  
                        case Cell.CELL_TYPE_NUMERIC:  
                            if (DateUtil.isCellDateFormatted(cell)){  
                                System.out.println(cell.getDateCellValue().toString());  
                            } else {  
                                System.out.println(cell.getNumericCellValue());  
                            }  
                            break;  
                        case Cell.CELL_TYPE_BOOLEAN:  
                            System.out.println(cell.getBooleanCellValue());  
                            break;  
                        case Cell.CELL_TYPE_FORMULA:  
                            System.out.println(cell.getCellFormula());  
                            break;  
                        default:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                    }  
                }  
                if(j == 2){  
                    switch (cell.getCellType()){  
                        case Cell.CELL_TYPE_STRING:  
                            System.out.println(cell.getRichStringCellValue());  
                            break;  
                        case Cell.CELL_TYPE_NUMERIC:  
                            if (DateUtil.isCellDateFormatted(cell)){  
                                System.out.println(cell.getDateCellValue());  
                            } else {  
                                System.out.println(cell.getNumericCellValue());  
                            }  
                            break;  
                        case Cell.CELL_TYPE_BOOLEAN:  
                            System.out.println(cell.getBooleanCellValue());  
                            break;  
                        case Cell.CELL_TYPE_FORMULA:  
                            System.out.println(cell.getCellFormula());  
                            break;  
                        default:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                    }  
                }  
                if(j == 3){  
                    switch (cell.getCellType()){  
                        case Cell.CELL_TYPE_STRING:  
                            System.out.println(cell.getRichStringCellValue());  
                            break;  
                        case Cell.CELL_TYPE_NUMERIC:  
                            if (DateUtil.isCellDateFormatted(cell)){  
                                throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                            } else {  
                                System.out.println(cell.getNumericCellValue());  
                            }  
                            break;  
                        case Cell.CELL_TYPE_BOOLEAN:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                        case Cell.CELL_TYPE_FORMULA:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                        default:  
                            throw new ExcelFormatException("格式错误",(cell.getRowIndex()+1),(cell.getColumnIndex()+1));  
                    }  
                }  
            }  
        }  
        inp.close();  
          
    }  
      
}