用org.apache.poi将Excel里的数据导入数据库

来源：互联网发布：惠州市政务网络问政编辑：程序博客网时间：2024/06/05 02:40

将Excel里的数据导入到数据库，使用的是apache开发的POI jar包。思路很简单：读出Excel中所需要的数据--拼接成sql语句--导入即可。直接贴代码

package com.hwxx.utils;import java.io.FileInputStream;import java.io.IOException;import java.sql.*;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class Excel2SqlServer {private static Connection conn;private static Statement stmt;private static String url ="jdbc:sqlserver://localhost:1433;DatabaseName=xxxxxx";private static String classforname="com.microsoft.sqlserver.jdbc.SQLServerDriver";private static String uid = "sa";private static String pwd = "xxxxxx";//执行插入sql语句private static void insert(String sql){try {ResultSet rs=null;stmt.execute(sql); rs = stmt.getResultSet();} catch (Exception e) {e.printStackTrace();}}//解析Excel文件，拼接成sql语句@SuppressWarnings("unused")private static void insertSql(){try{//获取Excel的文件流POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:/dfly.xls"));HSSFWorkbook wb = new HSSFWorkbook(fs);//选择Excel文件中的第3个sheet子表HSSFSheet sheet = wb.getSheetAt(3);HSSFRow row = null;String sql;//sheet.getLastRowNum()为最后一行for(int i=1;i<=sheet.getLastRowNum();i++){//从第i行开始row = sheet.getRow(i);//获取i行中的某个单元格的值HSSFCell cell = row.getCell(2); cell.setCellType(cell.CELL_TYPE_STRING);HSSFRichTextString richStr = cell.getRichStringCellValue();String deptname = richStr.toString();cell = row.getCell(3);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String username = richStr.toString();cell = row.getCell(4);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String cardnum = richStr.toString();cell = row.getCell(5);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String oanum = richStr.toString();cell = row.getCell(6);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String telnum = richStr.toString();cell = row.getCell(7);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String mailnum = richStr.toString();cell = row.getCell(8);cell.setCellType(cell.CELL_TYPE_STRING);richStr = cell.getRichStringCellValue();String remark = richStr.toString();//拼接sql语句sql =  "insert into hltp_employee(delete_flag,username,password,homephone,email,remark) values('N','"+username +"','123456','"+ telnum +"','"+ mailnum+"','"+ remark+"')" ;insert(sql);}}catch(IOException e){}}public static void main(String []args){System.out.println("test");try{ Class.forName(classforname);}catch(ClassNotFoundException  ex){}try{conn = DriverManager.getConnection( url, uid, pwd);stmt = conn.createStatement();}catch(SQLException ex){ System.out.println("connecttion error!"); ex.printStackTrace();return;}insertSql();}}

0 0