excel导入导出通用

caihorse

浏览: 144679 次
性别:
来自: 广州

最近访客更多访客>>

xuecaobao123

心杀心

newersky

zhaotao_king

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

java

Excel Struts SQL Apache Hibernate

show.isp文件

 <%@ 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文件

<%@ 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文件

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

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();
	  }
   }
}

导出excle

  //long eid = Long.parseLong(request.getParameter("eid").toString());
  String path = request.getSession().getServletContext().getRealPath(
    "/"
    +new Date().getTime()+"test.xls");
  OutputStream os = new FileOutputStream(path);
  WritableWorkbook wwb = null;

        try {
            wwb = Workbook.createWorkbook(os);

        } catch (IOException e) {
            e.printStackTrace();

        }
        if(wwb!=null){
        //创建一个可写入的工作表
            //Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
            WritableSheet ws = wwb.createSheet("工作表名称", 0);

            String[] arrname = {"订单编号","客户姓名", "移动电话","办公电话", "EMAIL", "订单金额" };
            writesheet(ws,arrname,0);
            List list = new ArrayList();
            long eid = 0;
            List<ListTail> listtail = new ListTailDao().getListTailinfo(eid);
            for(int i=0;i<listtail.size();i++){
            String [] arr = {listtail.get(i).getListid()+"",listtail.get(i).getCustomer()+"",
               listtail.get(i).getCphone()+"",listtail.get(i).getCtel()+"",
               listtail.get(i).getCemail()+"",listtail.get(i).getListprice()+""};

            writesheet(ws,arr,1+i);
            }
            //下面开始添加单元格


            try {
                //从内存中写入文件中
                wwb.write();
            } catch (IOException e) {
                e.printStackTrace();
            }finally{
            wwb.close();
            os.close();

            }
        }
        downfile(path,response);
        java.io.File myDelFile = new java.io.File(path);
        myDelFile.delete();

    return null;
  /*response.setContentType("text/html;charset=utf-8");
  response.setHeader("Cache-Control", "no-cache");
  System.out.println("**************");
  response.getWriter().write("9874");
  return null;*/
}
    public void writesheet( WritableSheet ws,String [] arr,int rowsize){
        for(int colsize=0;colsize<arr.length;colsize++){
          Label labelC = new Label(colsize,rowsize,arr[colsize]);
          try {
     ws.addCell(labelC);
    } catch (RowsExceededException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    } catch (WriteException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
          }
    }
    public void downfile(String path,HttpServletResponse response) throws Exception{
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    OutputStream fos = null;
    InputStream fis = null;
    String filepath = servlet.getServletContext().getRealPath("/" + path);
    File uploadFile = new File(path);
    fis = new FileInputStream(uploadFile);
    bis = new BufferedInputStream(fis);
    fos = response.getOutputStream();
    bos = new BufferedOutputStream(fos);
    try{
          response.setContentType("application/ms-excel;charset=utf-8");
          response.setHeader("Content-Disposition", "attachment;filename=" +
                             URLEncoder.encode(path, "utf-8"));
          int bytesRead = 0;
          byte[] buffer = new byte[8192];
          while ((bytesRead = bis.read(buffer, 0, 8192)) != -1) {
              bos.write(buffer, 0, bytesRead);
          }
         } catch (Exception e){
        e.printStackTrace();
         }finally{
          bos.flush();
          fis.close();
          bis.close();
          fos.close();
          bos.close();
         }
    }