Java代码
show.isp文件
Java代码
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="http://struts.apache.org/tags-logic" prefix="logic" %>
<%@ taglib uri="http://struts.apache.org/tags-tiles" prefix="tiles" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<script>
function selectAll(obj)
{
for(var i = 0;i<obj.elements.length;i++)
if(obj.elements[i].type == "checkbox")
obj.elements[i].checked = true;
}
function selectOther(obj)
{
for(var i = 0;i<obj.elements.length;i++)
if(obj.elements[i].type == "checkbox" )
{
if(!obj.elements[i].checked)
obj.elements[i].checked = true;
else
obj.elements[i].checked = false;
}
}
</script>
<%
int pageno = ((Integer)request.getAttribute("pageno")).intValue();
int totalpage = ((Integer)request.getAttribute("totalpage")).intValue();
%>
<html>
<body>
<form action="loadfile.do" method="post" enctype="multipart/form-data">
<table><tr><td><input type="file" name="file" size="20"></td><td>
<input type="submit" value="上传"></td></tr>
</form>
<form action="<%=request.getContextPath()%>/address.do?method=exportExcel"
method="post" >
<table><tr><td><input type="submit" name="import" value="IMPORT"></td><td>
</td></tr>
</form>
</table>
</body>
</html>
调用 loadfile.jsp文件
Java代码
<%@ page contentType="text/html; charset=GBK"%>
<%@ page import="java.util.*"%>
<%@ page import="com.jspsmart.upload.*"%>
<%
//@ page import="com.suntek.devmanager.web.SysParam"
%>
<%@ page import="java.io.*"%>
<jsp:useBean id="smartUpload" scope="page"
class="com.jspsmart.upload.SmartUpload" />
<%
request.getSession().removeAttribute("FileInputStream");
String contract_id = "", add_flag = "";
try {
// SmartUpload smartUpload = new SmartUpload();
smartUpload.initialize(pageContext);
//最大上传文件小于四兆
smartUpload.setTotalMaxFileSize(4096000);
//上载文件
smartUpload.upload();
//取得上载的文件
com.jspsmart.upload.File lo_File = smartUpload.getFiles().getFile(0);
byte[] fileContent = new byte[(int) lo_File.getSize()];
for (int k = 0; k < (int) lo_File.getSize(); k++) {
fileContent[k] = lo_File.getBinaryData(k);
}
InputStream fs = null;
if (!lo_File.isMissing()) {
//取得上载的文件的文件名
fs = new ByteArrayInputStream(fileContent);
//将文件写到数据库中
}
request.getSession().setAttribute("InputStream", fs);
//得到文件名
} catch (Exception e) {
e.printStackTrace();
return;
}
%>
<html>
<head>
<script language="javascript">
function Window_Onload()
{
document.all.manager.submit();
// window.returnValue = "true";
// top.close();
}
</script>
<title>有问题请和我联系</title>
</head>
<body leftmargin="0" topmargin="0" class="DialogOuter"
onload="return Window_Onload();">
<form action="<%=request.getContextPath()%>/address.do?method=addExcel"
method="post" name="manager">
</form>
<br>
<br>
<br>
<br>
<div align="center">
系统正在上传数据,请稍候!!
</div>
</body>
</html>
action文件
Java代码
package com.yourcompany.struts.action;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.jaxen.function.ext.LowerFunction;
import com.dao.Service;
import com.db.Conn;
import com.tb.Factory.DaoFactory;
import com.tb.hibernate.Address;
import com.yourcompany.struts.form.AddressForm;
/**
* MyEclipse Struts Creation date: 05-16-2008
*
* XDoclet definition:
*
* @struts.action path="/address" name="addressForm" input="/form/address.jsp"
* scope="request" validate="true"
*/
public class AddressAction extends DispatchAction {
/*
* Generated Methods
*/
public ActionForward addExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
//AddressForm addressForm = (AddressForm) form;
try {
InputStream is = (InputStream) request.getSession().getAttribute("InputStream");
//InputStream is = new FileInputStream("c://Address.xls");
Connection conn = Conn.Conn().getconn();
Service.service().importexcel(is,conn, "ADDRESS",5);//
}catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
return mapping.findForward("index");
}
public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
Connection conn = Conn.Conn().getconn();
response.reset();
response.setCharacterEncoding("GBK");
response.setContentType("Application/msexcel;charset=gbk");
response.setHeader("Content-disposition","attachment; 导出数据.xls");
WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(response.getOutputStream());
WritableSheet sheet = book.createSheet("数据", 0);
Label label = new Label(1, 1, "1234");
sheet.addCell(label);
book.write();
book.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Service.service().exportexcel(conn, "ADDRESS");//
return null;
}
}
调用的service文件
Java代码
package com.dao;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.db.Conn;
import com.tb.hibernate.Address;
/**
* @author Administrator
*
*/
/**
* @author Administrator
*
*/
public class Service {
private static Service service = new Service();
public static Service service(){
if(service == null){
return new Service();
}else{
return service;
}
}
/**
* @author Administrator
*获得table 表的字段类型或者字段名
*column_name,data_type
*/
public List getTypeNameOrColumnName(Connection conn, String tableName,String arg){
Statement ps = null;
ResultSet rs = null;
List<String> list = new ArrayList<String>();
String sql="select "+arg+" from user_tab_columns where table_name like '"+tableName+"'";
try {
ps = conn.createStatement();
rs = ps.executeQuery(sql);
while(rs.next()){
list.add(rs.getString(arg));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* @author Administrator
* 将导入的excle插入到table中
*/
public boolean insertTable(Connection conn, String tableName, List<String> list,int colNum){
PreparedStatement ps = null;
try {
String columnName="";
List<String> columnList = getTypeNameOrColumnName(conn,"ADDRESS","column_name");
for(int i=0;i<colNum+1;i++){
columnName += columnList.get(i);
columnName +=",";
}
columnName = columnName.substring(0,columnName.length()-1);
conn.setAutoCommit(false);
for(int j=0; j< list.size();j++){
String sql="insert into "+tableName+"("+columnName+") values(seq_a.nextval,"+list.get(j)+")";
ps = conn.prepareStatement(sql);
ps.addBatch();
if (j % 100 == 0 ) {
ps.executeBatch();
conn.commit();
}else{
ps.executeBatch();
conn.commit();
}
}
conn.setAutoCommit(true);
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return true;
}
/**
*
* @param is excel文件流
* @param conn 数据连接
* @param tableName 表名
* @param colNum excel中数据列数
*/
public void importexcel(InputStream is,Connection conn, String tableName, int colNum){
try{
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet[] sheets = rwb.getSheets(); //
List<String> list = Service.service().getTypeNameOrColumnName(conn,tableName,"data_type");
int nullCol = list.size()-colNum; //后面不用复制的项数
List<String> listarray = new ArrayList<String>();
for(int i=0; i<sheets.length; i++){
Sheet sheet = sheets[i];
//k代表数据所在的行
for(int k=0; k<sheet.getRows(); k++){
Address address = new Address();
StringBuffer str = new StringBuffer();
for(int m=0;m<list.size()-nullCol;m++){
// for(int m=0;m<colNum;m++){
Cell cel1= sheet.getCell(m,k);//列，行，
//将数据根据不同数据类型拼接为串
if(list.get(m+1).toUpperCase().indexOf("NUMBER")>-1 ||list.get(m+1).toUpperCase().indexOf("INT")>-1){//
if(!"".equals(cel1.getContents())){
str.append(cel1.getContents());
}else{
str.append(0);
}
//DATE、DATETIME、TIME、TIMESTAMP to_date('2002-02-28','yyyy-mm-dd')
}else if(list.get(m+1).toUpperCase().indexOf("DATE")>-1||list.get(m+1).toUpperCase().indexOf("TIMESTAMP")>-1
||list.get(m+1).toUpperCase().indexOf("DATETIME")>-1||list.get(m+1).toUpperCase().indexOf("TIME")>-1){
String strdate ="";
if(!"".equals(cel1.getContents())){
strdate = "to_date('"+cel1.getContents()+"','yy-mm-dd')";
}
str.append(!"".equals(cel1.getContents())?strdate:"NULL");
}else{
str.append("'");
str.append(!"".equals(cel1.getContents())?cel1.getContents().trim():cel1.getContents());
str.append("'");
}
str.append(",");
}
listarray.add(str.toString().substring(0, str.toString().length()-1));
}
}
Service.service().insertTable(conn,tableName,listarray,colNum);
}catch(Exception e){
e.printStackTrace();
}
}
}
