急求用JAVA程序如何将Excel表单导入到我数据库中

[hyowner]

在线等待各位大哥的帮助,我机子上的数据库是SQLSERVER 问题如题 希望有知道的或做过之类的提供一个可参考的完整的代码或实例,再线等待 急!!!

[javagxc]

先把excel导入到数据库,然后
insert into 新库名称..表名(字段)
select (字段)
from 旧库名称..表名

[JavaAlpha]

这个是导入 MySQL的 可以参考该一下。

http://blog.csdn.net/sdrzths/archive/2009/06/03/4238696.aspx

[JavaAlpha]

看看这里的


http://heweiya.javaeye.com/blog/80932

[hyowner]

楼上的大哥做过类似的吗?

[whut_lcy]

正好最近作大量的excel报表处理，导入导出都有。

一般都用第三方类库解析excel然后处理，或者生成excel然后下载

基本方法也比较简单，可以用apache的poi或者jxl（据说是棒子程序员写的，没用过）。我用的是poi，很不错，excel,word,ppt都支持

[hyowner]

[Quote=引用 4 楼 whut_lcy 的回复:]
正好最近作大量的excel报表处理，导入导出都有。

一般都用第三方类库解析excel然后处理，或者生成excel然后下载

基本方法也比较简单，可以用apache的poi或者jxl（据说是棒子程序员写的，没用过）。我用的是poi，很不错，excel,word,ppt都支持


[/Quote]

楼上的兄弟能加我QQ吗 我QQ是57191841 希望能再具体点请教

[SambaGao]

jxl

[Dazzlingwinter]

jxl了

[sotom]

[code=Java]import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Vector;

import org.apache.log4j.Logger;

public class DBOperate {


/**
* 得到sequence的nextval
* @param sequenceName
* @return
*/
public static String getSequence(String sequenceName) {
String nextval = "";
if(sequenceName == null || "".equals(sequenceName)) {
System.out.println("sequenceName name is null!");
return null;
} else {
sequenceName = sequenceName.toUpperCase();
String sql = "select " + sequenceName + ".nextval nextid from user_sequences where sequence_name = '"
+ sequenceName + "'";

Statement stmt = null;
Connection conn = null;
ResultSet rs = null;

try {
conn = TreatDB.getConnDB("");
if (conn != null) {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

if(rs != null) {
while(rs.next()) {
nextval = rs.getString("nextid");
}
}
}
} catch (SQLException e) {
System.out.println("SQLException : " + e);
} finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
}
}
return nextval;
}
}

[sotom]

/**
* 一个插入、更新数据表的通用方法，传入一个sql脚本
* @param sql ：要进行操作的脚本
* @return ：发生变化的条数
*/
public static int updateSql(String sql) {
String dbName = "";
Connection conn = null;
Statement stmt = null;
int result = 0;
try {
conn = TreatDB.getConnDB(dbName);
if (conn != null) {
stmt = conn.createStatement();
//logger.info(sql);
result = stmt.executeUpdate(sql);
conn.commit();
}

} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("update sql error: "+e);

}
System.out.println("update sql error: "+e);
System.out.println("sql: " + sql);
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (Exception ex) {
//do nothing;
}

}
return result;
}

}

[sotom]

导入EXCEL的程序代码:

java 代码

/**
* 将Excel文件中的数据添加到数据库中
* 新的监理信息　modify by heweiya
*
* @param xlsFile，Excel文件
* @return
*/
public String addJLXlsForm(InputStream is) {

String errStr = "";
String result = "";

try {
Workbook rwb = Workbook.getWorkbook(is);
// Workbook rwb = Workbook.getWorkbook(new File(filePath+fileName));
// 获取第一张Sheet表
Sheet sheet = rwb.getSheet(0);
// 获取总列数
int rsColumns = sheet.getColumns();
System.out.println("rsColumns = " + rsColumns);

// 获取总行数
int rsRows = sheet.getRows();
System.out.println("rsRows = " + rsRows);

if (rsColumns < 19) {
errStr = "错误原因：字段不全。";
vErr.addElement(errStr);
}

int i = 0;
int startRows = 1;
int startColumn = 0;
while (startRows < rsRows) {
Hashtable ht = new Hashtable();

String zj_id = DBOperate.getSequence("S_JIANLI");


Cell tmp = sheet.getCell(0,startRows);
String sfzh = tmp.getContents();

tmp = sheet.getCell(1,startRows);
String issueDate = tmp.getContents();
String issueDate_new = "";
if(issueDate != null && issueDate.length()>=6) issueDate_new = issueDate.substring(6) + issueDate.substring(3,5) + issueDate.substring(0,2);

tmp = sheet.getCell(2,startRows);
String approveDate = tmp.getContents();
String approve_date_new = "";
if(approveDate != null && approveDate.length()>=6) approve_date_new = approveDate.substring(6) + approveDate.substring(3,5) + approveDate.substring(0,2);

tmp = sheet.getCell(3,startRows);
String cerNo = tmp.getContents();

tmp = sheet.getCell(4,startRows);
String jianliMajor = tmp.getContents();

tmp = sheet.getCell(5,startRows);
String department = tmp.getContents();

tmp = sheet.getCell(6,startRows);
String jianliName = tmp.getContents();

tmp = sheet.getCell(7,startRows);
String jianliSex = tmp.getContents();

//毕业院校
tmp = sheet.getCell(8,startRows);
String degree = tmp.getContents();

//毕业时间
tmp = sheet.getCell(9,startRows);
String cooleage = tmp.getContents();

//所学专业
tmp = sheet.getCell(10,startRows);
String company = tmp.getContents();

//职务
tmp = sheet.getCell(11,startRows);
String tech_post = tmp.getContents();

// 工作单位
tmp = sheet.getCell(12,startRows);
String address = tmp.getContents();

// 职称
tmp = sheet.getCell(13,startRows);
String postcode = tmp.getContents();

tmp = sheet.getCell(14,startRows);
String jianliTel = tmp.getContents();

tmp = sheet.getCell(15,startRows);
String mobile = tmp.getContents();

tmp = sheet.getCell(16,startRows);
String email = tmp.getContents();

tmp = sheet.getCell(17,startRows);
String birthday = tmp.getContents();
String birthday_new = "";
if(birthday != null && birthday.length()>=6) birthday_new = birthday.substring(6) + birthday.substring(3,5) + birthday.substring(0,2);


tmp = sheet.getCell(18,startRows);
String jianli_type = tmp.getContents();
//加入密码　modify by heweiya 2007/05/17
String sql = "insert into jianli values('" + zj_id + "', '" + sfzh + "', to_date('" + issueDate_new + "','yyyymmdd'),to_date('"
+ approve_date_new + "','yyyymmdd'),'" + cerNo + "','" + jianliMajor + "','" + department + "','"
+ jianliName + "','" + jianliSex + "','" + degree + "','" + cooleage + "','"
+ company + "','" + tech_post + "','" + address + "','" + postcode + "','" + jianliTel
+ "','" + mobile + "','" + email + "',to_date('" + birthday_new + "','yyyymmdd'),'" + jianli_type + "',null,null,null,null,"+sfzh.substring(0, 10)+",null,null,null,null,null)";

int a = DBOperate.updateSql(sql);

System.out.println("成功导入第"+startRows+"条");
if (a == 0) {
errStr = errStr + "添加失败:库中已有此记录,或有值不符合库中设定的字段属性!";
} else if (a == 1) {
result = "添加成功";
} else {
errStr = errStr + "添加失败:连接数据库失败";
}



if (!"".equals(errStr)) {
vErr.addElement("导入：" + "【" + startRows + "】" + "<br>(错误原因：" + errStr);
errStr += "导入：" + "【" + startRows + "】" + "<br>(关键字段为：姓名："+jianliName+",身份证号为："+sfzh+",监理证书号为："+cerNo+")<br>";
} else {
vOk.add(ht);
}

startRows++;
}

rwb.close();
result = "成功导入：" + vOk.size()+"条记录。<br> 错误导入： " + vErr.size()+"条记录,错误记录及原因如下：<br>"+errStr;
System.out.println("vOK.size() = " + vOk.size());
System.out.println("VErr.size() = " + vErr.size());
return result;
} catch (Exception e) {
result = "在导入的过程当中发生了错误，其中错误的原因是：" + e.toString();
return result;
}
}
导出报表的代码:

java 代码

/**
*
* 导出报表
*
* @param
* @return
* @throws
*/
public ActionForward ExportXLS(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
HttpSession session = request.getSession();
String CerNo = (String) session.getAttribute("CerNo");// 登录名
String JianliName = (String) session.getAttribute("JianliName");// 角色ID
if (CerNo == null || JianliName == null) {
return mapping.findForward("Fail");
}
File business_rpt = new File(getServlet().getServletConfig()
.getServletContext().getRealPath("/report/water_jianli.jasper"));
JasperPrint jasperPrint = null;

Map parameters = new HashMap();
Jianli jianli = personalDAO.GetJianli(CerNo);

ReportModel report_user = new ReportModel();
report_user.setCol1(jianli.getJianliName());// 姓名
report_user.setCol2(jianli.getJianliSex());// 性别
report_user.setCol3(jianli.getDegree());// 学历
report_user.setCol4("'" + jianli.getSfzh());// 身份证
report_user.setCol5(jianli.getTechPost());// 职称
report_user.setCol6(jianli.getCerNo());// 证书编号
report_user.setCol7(jianli.getJianliMajor());// 监理专业
if (jianli.getOrgs() != null) {
report_user.setCol8(jianli.getOrgs().getOrgName());// 隶属单位
}
report_user.setCol10(jianli.getAddress());
report_user.setCol11(jianli.getJianliTel());
/**
* 加入简历的东东
*/
List Ljianli = assistDAO.GetEngineerAssist(CerNo);
if (Ljianli != null) {
for (int i = 0; i < Ljianli.size(); i++) {
Assistant assist = (Assistant) Ljianli.get(i);
String begindate = "";
String enddate = "";
SimpleDateFormat sfDate = new SimpleDateFormat("yyyy/MM/dd");
if (assist.getBegindate() != null) {
begindate = sfDate.format(assist.getBegindate());
}
if (assist.getEnddate() != null) {
enddate = sfDate.format(assist.getEnddate());
}
String deptname = (assist.getAtdeptname() == null)?"":assist.getAtdeptname();
String zewu = (assist.getAssistname() == null)?"":assist.getAssistname();
String huihua = "开始日期为：" + begindate + ",结束日期为：" + enddate
+ "，所在工作单位"+deptname+",担任职务为：" + zewu+"。";
if (assist.getOther() != null){ } [/code]

不知道好用不，没在公司代码没带在身边，呵呵。。。