怎么在页面中实现excel文件导入到数据库中

2012-04-27

如何在页面中实现excel文件导入到数据库中我有一个jsp页面如下,当我选中要导入的excel文件时，点击导入按钮

如何在页面中实现excel文件导入到数据库中
我有一个jsp页面如下,当我选中要导入的excel文件时，点击导入按钮要实现将这个excel文件中的数据信息导入到后台对应的一个数据表中去，怎么去实现呢？好像jxl可以实现，不知道怎么去搞，各位帮忙了，上面催着完成哦

HTML code

<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>

[解决办法]
www.java2000.net 去搜索 jxl

你要做的分2部分
1 上传
2 用jxl解析，然后把数据保存到数据库
[解决办法]
http://download.csdn.net/source/1532774
这上面有完整的例子
[解决办法]
上传以后用 jxl 去解析，可以直接读取一列，非常方便的，去搜一下吧

[解决办法]
java poi 我用过 jxl 和poi 好像都行吧
[解决办法]
你也可以使用poi实现对excel的操作，给你一个，我之前做的代码：
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("数据库连接失败！");
}
}
[解决办法]

Java code

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){                                                 }

热点排行

Java Web开发

怎么在页面中实现excel文件导入到数据库中