poi作excel导入数据库
1.数据库设计，行为列表，有4个字段，分别为-自增长id：id，行为id：action_id，行为名称：action_name，备注：remark
2.需要的包：com.springsource.org.apache.poi-3.0.2.FINAL.jar和mysql-connector-java-5.1.18.jar
3.建项目，两段代码，第一个为连接mysql数据库的
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; 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 InsertDB {private Connection con; private MysqlConnection db; private PreparedStatement pst;//excel所在路径private String filePath="C:/Documents and Settings/Administrator/桌面/新建文件夹 (2)/test.xls"; public boolean insertDB(){boolean flag=true; db=new MysqlConnection(); 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的总记录条数 System.out.println("总行数为:"+totalRow); //以下的字段一一对应数据库表的字段 int action_id=0; String action_name=""; String remark=""; String sql="insert into config_user_analyze(action_id,action_name,remark) values(?,?,?)"; for(int i=1;i<=totalRow;i++){ row=sheet.getRow(i); cell=row.getCell((short) 0); action_id=(int) cell.getNumericCellValue();//第一字段为action_id，故转为int类型 System.out.println("action_id is "+action_id); cell=row.getCell((short)1); action_name=cell.toString();//第二字段为action_name，故转为String类型 System.out.println("action_name is"+action_name); cell=row.getCell((short)2); remark=cell.toString(); pst=con.prepareStatement(sql); pst.setInt(1,action_id); pst.setString(2,action_name); pst.setString(3,remark); pst.execute(); } } catch (FileNotFoundException e) { flag=false; e.printStackTrace(); } catch(IOException ex){ flag=false; ex.printStackTrace(); } catch(SQLException exx){ flag=false; exx.printStackTrace(); }return flag; }public static void main(String[] args) {InsertDB ii=new InsertDB();System.out.println(ii.insertDB());}}