我有一个jsp页面如下,当我选中要导入的excel文件时,点击导入按钮要实现将这个excel文件中的数据信息导入到后台对应的一个数据表中去,怎么去实现呢?好像jxl可以实现,不知道怎么去搞,各位帮忙了,上面催着完成哦 <html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'TestExcel.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
--> </head>
<body>
<input type="file" name="inportFile" >
<input type="button" value="导入">
</body>
</html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'TestExcel.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
--> </head>
<body>
<input type="file" name="inportFile" >
<input type="button" value="导入">
</body>
</html>
1 上传
2 用jxl解析,然后把数据保存到数据库
这上面有完整的例子
package com.excel;import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Hashtable;
import java.util.Iterator;import org.apache.poi.hssf.model.Workbook;
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.hssf.util.HSSFColor.WHITE;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;import com.sun.org.apache.xerces.internal.impl.xs.identity.ValueStore;public class ExcelToDB {
private static Connection conn = null;
private static Statement stmt = null; private static boolean getConnectionToMysql() {
try {
String url = "jdbc:mysql://localhost:3306/teacher?user=root&password=wafdqq00";
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
// TODO: handle exception
System.out.println("驱动加载失败!");
e.printStackTrace();
return false;
} catch (SQLException e) {
// TODO: handle exception
System.out.println("数据库连接失败!");
e.printStackTrace();
System.exit(1);
return false;
}
return true;
} private static String getCellValue(HSSFCell cell) {
String value = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().toString() + "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateFormat.getDateInstance().format(
cell.getDateCellValue())
+ "";
} else {
DecimalFormat df = new DecimalFormat("#");
value = df.format(cell.getNumericCellValue()) + "";
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula() + "";
break;
}
return value;
} private static boolean readExceltoMysql() {
POIFSFileSystem fSystem = null;
HSSFWorkbook wb = null;
try {
FileInputStream fileInputStream = new FileInputStream(
"d://2009.xls");
fSystem = new POIFSFileSystem(fileInputStream);
wb = new HSSFWorkbook(fSystem);
} catch (IOException e) {
// TODO: handle exception
e.printStackTrace();
return false;
}
HSSFRow row = null;
HSSFCell cell = null;
Hashtable ht = new Hashtable();
String sql = "";
/*
* HSSFSheet hs = wb.getSheetAt(1);
* System.out.println(hs.getSheetName());
*/
for (int m = 0; m < 2; m++) {
HSSFSheet hSheet = wb.getSheetAt(m);
String sheetName = hSheet.getSheetName();
System.out.println(sheetName); int rowNum = hSheet.getLastRowNum();
System.out.println(rowNum);
for (int i = 3; i <= rowNum; i++) {
row = hSheet.getRow(i);
// if(row != null)
for (int j = 1; j < row.getLastCellNum(); j++) {
// System.out.println(row.getLastCellNum());
cell = row.getCell(j);
String name = ExcelToDB.getCellValue(cell);
ht.put(j, name);
sql = "insert into test( name,projectname,address,date,size,content,"
+ "totalmoney,buildingmoney,facilitymoney,othermoney,otherdollar,projectID,"
+ "projecttime,gov,projectbegintime,contact,tel,phone,"
+ "economytype) values ('"
+ ht.get(1)
+ "','"
+ ht.get(2)
+ "','"
+ ht.get(3)
+ "',"
+ "'"
+ ht.get(4)
+ "','"
+ ht.get(5)
+ "','"
+ ht.get(6)
+ "',"
+ "'"
+ ht.get(7)
+ "','"
+ ht.get(8)
+ "','"
+ ht.get(9)
+ "',"
+ "'"
+ ht.get(10)
+ "','"
+ ht.get(11)
+ "','"
+ ht.get(12)
+ "',"
+ "'"
+ ht.get(13)
+ "','"
+ ht.get(14)
+ "','"
+ ht.get(15)
+ "',"
+ "'"
+ ht.get(16)
+ "','"
+ ht.get(17)
+ "','"
+ ht.get(18) + "'," + "'" + ht.get(19) + "')"; }
try {
stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO: handle SQLException
System.out.println("插入失败!");
e.printStackTrace();
return false;
}
}
}
return true;
} public static void main(String[] args) {
if (ExcelToDB.getConnectionToMysql() == true) {
if (ExcelToDB.readExceltoMysql() == true) {
System.out.println("数据导入成功!");
} else {
System.out.println("数据导入失败!");
}
} else
System.out.println("数据库连接失败!");
}
}
jxl.Workbook rwb = jxl.Workbook.getWorkbook(这里把从页面上得到文件的InputStream流传过来);
// int sheetNumber = rwb.getNumberOfSheets();
// System.out.println("sheets:"+sheetNumber);
//得到所有的sheets
Sheet[] sheets = rwb.getSheets();
//遍历所有sheets
for(Sheet sheet: sheets){
// System.out.println(sheet.getName());
// System.out.println(sheet.getColumns());
// System.out.println(sheet.getRows());
//直接取得当前sheet的一个列,注意是从0开始的
Cell[] cells = sheet.getColumn(2);
//下边是取得某列的内容
for(Cell cell:cells){
}
is = new FileInputStream(new File(filePath));
wb = Workbook.getWorkbook(is);这句里面的filePath要怎么写?就是:filePath="这里面要怎么写???";