通过POI将Excel数据导入数据库

http://chengyue2007.iteye.com/blog/455911

上一篇写了从数据库导出excel。今天继上一篇写出从excel导入数据库。数据库表有这些字段：ID,BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISH,BOOK_DATE,BOOK_ISBN,BOOK_PAGE,BOOK_PRICE。

连接数据库类：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

private String classString="oracle.jdbc.driver.OracleDriver";
private String username="benz";
private String password="benz";
private String url="java:oracle:thin:@192.168.1.17:1521:bhdba";
private Connection con=null;

public Connection getConnection(){
   try {
    Class.forName(classString);
    con=DriverManager.getConnection(url,username,password);
   } catch (ClassNotFoundException e) {
    e.printStackTrace();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   return con;
}

}

具体操纵类：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

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 ExcelToDB {

private Connection con;
private DBConnection db;
private PreparedStatement pst;
private String filePath="f:\\test.xls";

public boolean insertDB(){
  
   boolean flag=true;
   db=new DBConnection();
   con=db.getConnection();
   try {
    //文件流指向excel文件
    FileInputStream fin=new FileInputStream(filePath);
    HSSFWorkbook workbook=new HSSFWorkbook(fin);//创建工作薄
    HSSFSheet sheet=workbook.getSheetAt(0);//得到工作表
    HSSFRow row=null;//对应excel的行
    HSSFCell cell=null;//对应excel的列
   
    int totalRow=sheet.getLastRowNum();//得到excel的总记录条数
    //以下的字段一一对应数据库表的字段
    String bookName="";
    String bookAuthor="";
    String bookPublish="";
    Date bookDate=null;
    String bookIsbn="";
    int bookPage=0;
    float bookPrice=0.0f;
   
    String sql="insert into book(ID,BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISH," +
      "BOOK_DATE,BOOK_ISBN,BOOK_PAGE,BOOK_PRICE) " +
      "values(SEQ_BOOK.NEXTVAL,?,?,?,?,?,?,?)"; //SEQ_BOOK.NEXTVAL为数据库表序列
   
    for(int i=1;i<=totalRow;i++){
     row=sheet.getRow(i);
     cell=row.getCell(1);
     bookName=cell.getRichStringCellValue().toString();
     cell=row.getCell(2);
     bookAuthor=cell.getRichStringCellValue().toString();
     cell=row.getCell(3);
     bookPublish=cell.getRichStringCellValue().toString();
    
     cell=row.getCell(4);
     //格式化字符串时间
     SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
     bookDate=new Date((format.parse(cell.getRichStringCellValue().toString())).getTime());
    
     cell=row.getCell(5);
     bookIsbn=cell.getRichStringCellValue().toString();
     cell=row.getCell(6);
     bookPage=Integer.parseInt(cell.getRichStringCellValue().toString());
     cell=row.getCell(7);
     bookPrice=Float.parseFloat(cell.getRichStringCellValue().toString());
    
     pst=con.prepareStatement(sql);
     pst.setString(1,bookName);
     pst.setString(2,bookAuthor);
     pst.setString(3,bookPublish);
     pst.setDate(4,bookDate);
     pst.setString(5,bookIsbn);
     pst.setInt(6,bookPage);
     pst.setFloat(7,bookPrice);
    
     pst.execute();
    }
   
   
   } catch (FileNotFoundException e) {
    flag=false;
    e.printStackTrace();
   } catch(IOException ex){
    flag=false;
    ex.printStackTrace();
   } catch(SQLException exx){
    flag=false;
    exx.printStackTrace();
   } catch(ParseException exxx){
    exxx.printStackTrace();
   }finally{
    try {
     pst.close();
     con.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
   return flag;
  
}

public static void main(String args[]){
   ExcelToDB toDB=new ExcelToDB();
   toDB.insertDB();
}

}

 取得Excel文件的总行数和总列数
HSSFSheet hssheet = wb.getSheetAt(0);
  HSSFRow row = (HSSFRow) hssheet.getRow(0);   
  int rowNum = hssheet.getPhysicalNumberOfRows();
  short colNum = (short)hssheet.getRow((short)0).getPhysicalNumberOfCells();

评论

2 楼 zi_wu_xian 2017-06-15  

18335864773 写道

用过一个叫pageoffice的插件去生成excel。感觉挺方便的。比poi生成excel简单。兼容性，功能，还有性能方面都比poi的好

PageOffice是在客户端生成的，主要还是处理文件的在线编辑。POI是后台生成文件。

1 楼 18335864773 2017-06-14  

用过一个叫pageoffice的插件去生成excel。感觉挺方便的。比poi生成excel简单。兼容性，功能，还有性能方面都比poi的好