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();

}

}