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