show.isp文件 - <%@ page contentType="text/html;charset=gb2312" %>
- <%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
-
- <%@ taglib uri="http://struts./tags-bean" prefix="bean" %>
- <%@ taglib uri="http://struts./tags-html" prefix="html" %>
- <%@ taglib uri="http://struts./tags-logic" prefix="logic" %>
- <%@ taglib uri="http://struts./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="java.io.*"%>
- <jsp:useBean id="smartUpload" scope="page"
- class="com.jspsmart.upload.SmartUpload" />
- <%
- request.getSession().removeAttribute("FileInputStream");
- String contract_id = "", add_flag = "";
- try {
-
- 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();
-
-
- }
- </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;
-
-
-
-
-
-
-
-
-
- public class AddressAction extends DispatchAction {
-
-
-
-
-
- public ActionForward addExcel(ActionMapping mapping, ActionForm form,
- HttpServletRequest request, HttpServletResponse response) {
-
- try {
- InputStream is = (InputStream) request.getSession().getAttribute("InputStream");
-
- Connection conn = Conn.Conn().getconn();
- Service.service().importexcel(is,conn, "ADDRESS",5);
-
- }catch (Exception e) {
-
- 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) {
-
- e.printStackTrace();
- }
-
- 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;
-
-
-
-
-
-
-
-
-
- public class Service {
- private static Service service = new Service();
- public static Service service(){
- if(service == null){
- return new Service();
- }else{
- return service;
- }
- }
-
-
-
-
-
- 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) {
-
- e.printStackTrace();
- }
- return list;
- }
-
-
-
-
- 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) {
-
- e.printStackTrace();
- }
- return true;
- }
-
-
-
-
-
-
-
-
- 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];
-
- 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++){
-
- 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);
- }
-
- }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();
- }
- }
- }
-
|