请详细给出,在JSP页面中如何调用一下代码的相关步骤,实现EXCEL导入数据库的功能!
JAVA代码如下:import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class POITest {
private static Connection conn = null;
private static Statement stmt = null;
private static boolean connectDB2() { String url = "";
String username = "username";
String password = "password"; //加载驱动程序以连接数据库
try {
//添加类库驱动包db2jcc.jar和db2jcc_license_cu.jar
Class.forName("com.ibm.db2.jcc.DB2Driver");
url = "jdbc:db2://192.168.0.1:50000/dbname";
//添加类库驱动包db2java.jar
//Class.forName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
//url = "jdbc:db2:njtcdata";
conn = DriverManager.getConnection(url, username, password);
stmt = conn.createStatement();
}
//捕获加载驱动程序异常
catch (ClassNotFoundException cnfex) {
System.err.println("装载JDBC驱动程序失败。");
cnfex.printStackTrace();
return false;
}
//捕获连接数据库异常
catch (SQLException sqlex) {
System.err.println("无法连接数据库");
sqlex.printStackTrace();
//System.exit(1); // terminate program
return false;
}
return true;
} private static boolean readExcelToDB2() {
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(new FileInputStream("c:\\test.xls"));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
return false;
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
String name = "";
int id = 0;
int rowNum, cellNum;
int i;
rowNum = sheet.getLastRowNum();
for (i = 0; i <= rowNum; i++) {
row = sheet.getRow(i);
//cellNum = row.getLastCellNum();
cell = row.getCell((short) 0);
name = cell.getStringCellValue();
cell = row.getCell((short) 1);
id = (int) cell.getNumericCellValue();
String sql = "insert into TEST(ID, NAME) values(" + id + ",'" + name + "')";
try {
stmt.executeUpdate(sql);
} catch (SQLException e1) {
e1.printStackTrace();
return false;
}
}
return true;
} public static void main(String[] args) {
if (connectDB2()==true){
if (readExcelToDB2()==true)
System.out.println("数据导入成功");
else
System.out.println("数据导入失败");
}
else{
System.out.println("数据库连接失败");
}
}
}
JAVA代码如下:import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class POITest {
private static Connection conn = null;
private static Statement stmt = null;
private static boolean connectDB2() { String url = "";
String username = "username";
String password = "password"; //加载驱动程序以连接数据库
try {
//添加类库驱动包db2jcc.jar和db2jcc_license_cu.jar
Class.forName("com.ibm.db2.jcc.DB2Driver");
url = "jdbc:db2://192.168.0.1:50000/dbname";
//添加类库驱动包db2java.jar
//Class.forName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
//url = "jdbc:db2:njtcdata";
conn = DriverManager.getConnection(url, username, password);
stmt = conn.createStatement();
}
//捕获加载驱动程序异常
catch (ClassNotFoundException cnfex) {
System.err.println("装载JDBC驱动程序失败。");
cnfex.printStackTrace();
return false;
}
//捕获连接数据库异常
catch (SQLException sqlex) {
System.err.println("无法连接数据库");
sqlex.printStackTrace();
//System.exit(1); // terminate program
return false;
}
return true;
} private static boolean readExcelToDB2() {
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(new FileInputStream("c:\\test.xls"));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
return false;
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
String name = "";
int id = 0;
int rowNum, cellNum;
int i;
rowNum = sheet.getLastRowNum();
for (i = 0; i <= rowNum; i++) {
row = sheet.getRow(i);
//cellNum = row.getLastCellNum();
cell = row.getCell((short) 0);
name = cell.getStringCellValue();
cell = row.getCell((short) 1);
id = (int) cell.getNumericCellValue();
String sql = "insert into TEST(ID, NAME) values(" + id + ",'" + name + "')";
try {
stmt.executeUpdate(sql);
} catch (SQLException e1) {
e1.printStackTrace();
return false;
}
}
return true;
} public static void main(String[] args) {
if (connectDB2()==true){
if (readExcelToDB2()==true)
System.out.println("数据导入成功");
else
System.out.println("数据导入失败");
}
else{
System.out.println("数据库连接失败");
}
}
}
if (POITest.connectDB2()==true){
if (POITest.readExcelToDB2()==true)
System.out.println("数据导入成功");
else
System.out.println("数据导入失败");
}
else{
System.out.println("数据库连接失败");
}
}
<input name="cb_reconn" type="submit" id="cb_reconn"
value=" 导入数据库 ">
</form>
if (POITest.readExcelToDB2()==true)
System.out.println("数据导入成功");
else
System.out.println("数据导入失败");
}
else{
System.out.println("数据库连接失败");
}
}
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD> <BODY>
<%
if (connectDB2()==true){
if (readExcelToDB2()==true){
System.out.println("数据导入成功");
request.getRequestDispatcher("成功页面!").forward(request,response) ;
}
else{
System.out.println("数据导入失败");
request.getRequestDispatcher("失败页面").forward(request,response) ;
}
}
else{
System.out.println("数据库连接失败");
}
%>
</BODY>
</HTML>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
点击按钮跳到本页,完成操作转到成功或失败页面!