用Apache POI来实现对Excel的读写
转载http://www.lookhan.com/experience/experience/20110113211355.html在我们的项目当中经常会遇到把数据导入到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();
[*]
[*] }
[*]
[*]}
上面的读取Excel的代码写的可能不是很好，里面有好多的判断，是这样的，POI对Excel的读取是要看里面数据的类型的，而有些数据有可能是字符串，又有可能是数字类型，而对不同的类型的读取所用的方法是不一样的，所以我在这里是先得到数据的类型，然后根据其类型来调用不同的方法。感觉这里用的不是很好，可是我也找不到比较好的方法，有知道的告诉我一声。
页:
[1]