应工作需要,特做此实验。
1、应用jdbc:odbc桥接和简单的access数据库;
2、excel表格和access表格中的相关字段都已经设置好,且字段数目已知;
3、此程序主要目的是由手头的excel表格采用自动的方式,将其中的数据输入到数据库中。
4、主要代码:
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import javax.swing.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;//需要从apache网站上下载
.................
Connection conn;
Statement stament;
String dburl="jdbc:odbc:test",dbusername="",dbpassword="",sql;
................
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//加载数据库驱动驱动
}catch(ClassNotFoundException e){
e.printStacTrace();
}
................
public void action(){
HSSFCell cell;
JFileChooser opener = new JFileChooser();//文件选择器,
opener.showOpenDialog(this);//打开文件选择对话框
try{
conn = DriverManager.getConnection(dburl,dbusername,dbpassword);
stament = conn.createStatement();
String sourcefile = opener.getSelectedFile().getPath().toString();
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(sourcefile));
for(int sheetnum =0;sheetnum < wb.getNumberOfSheets();sheetnum++){//first for
if(null != wb.getSheetAt(sheetnum)){//first if
HSSFSheet sheet = wb.getSheetAt(sheetnum);
for(int rownumofsheet =1;rownumofsheet<=sheet.getLastRowNum();rownumofsheet++){//second for
if(null != sheet.getRow(rownumofsheet)){//second if
HSSFRow row = sheet.getRow(rownumofsheet);
int firstcellnum=row.getFirstCellNum();
int lastcellnum = row.getLastCellNum();
int n = lastcellnum - firstcellnum;
String [] content = new String[n];
int i=0;
for (short cellnum =0;cellnum <= row.getLastCellNum();cellnum++){//third for
if(null != row.getCell(cellnum)){//third if
content[i] = row.getCell(cellnum).toString();
i++;
}//end third if
}//end third for
sql = "insert into jx values('"+content[0]+"','"+content[1]+"','"+content[2]+"','"+content[3]+"','"+content[4]+"','"+content[5]+"','"+content[6]+"','"+content[7]+"','"+content[8]+"','"+content[9]+"','"+content[10]+"','"+content[11]+"')";
stament.executeUpdate(sql);
}//end second if
}//end second for
}//end first if
}//end first for
}// end try
catch(Exception ex){
System.out.println("error");
ex.printStackTrace();
}
}