JavaScript/Jsp 多图上传和上传Excelx表到数据库

迷南。 2022-07-15 04:53 229阅读 0赞

**实现目标：利用MyEclipse/jsp/jquery多种知识完成Excel表的上传和多图的上传。**

**1.多图上传**

**准备工作需要添加4个jar包和css样式，imgs图片，js样式到工程里面。**

**index代码：**

<%@ page language="java" import="java.util.*" 
    pageEncoding="utf-8"%>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
      <head>
        <base href="<%=basePath%>">    
        <title>多张图片上传</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="css/dhtmlXVault.css" />
    
        <script language="JavaScript" type="text/javascript" src="js/dhtmlXVault.js"></script>
    
        <script language="JavaScript" type="text/javascript">
            var vault = null;
            function doOnLoad() {
                preLoadImages();
                vault = new dhtmlXVaultObject();
                vault.setServerHandlers("UploadHandler.jsp", "GetInfoHandler.jsp", "GetIdHandler.jsp");
                vault.create("vault1");
    	}
    
    	function preLoadImages(){
    		var imSrcAr = new Array("btn_add.gif","btn_clean.gif","btn_upload.gif","ico_file.png","ico_image.png","ico_sound.png","ico_video.png","ico_zip.png","pb_back.gif","pb_demoUload.gif","pb_empty.gif");
    		var imAr = new Array(0);
    		for(var i=0;i<imSrcAr.length;i++){
    			imAr[imAr.length] = new Image();
    			imAr[imAr.length-1].src = "imgs/"+imSrcAr[i];
    		}
    	}
        </script>
    
        <style>
    	body {font-size:12px}
    	.{font-family:arial;font-size:12px}
    	h1 {cursor:hand;font-size:16px;margin-left:10px;line-height:10px}
    	xmp {color:green;font-size:12px;margin:0px;font-family:courier;background-color:#e6e6fa;padding:2px}
    	.hdr{
    		background-color:lightgrey;
    		margin-bottom:10px;
    		padding-left:10px;
    	}
        </style>
    
    </head> 
      <body οnlοad="doOnLoad()">
        <div style="color:#ff0000;font-size:12px;">* 目前支持的上传格式 jpg,gif,bmp,png</div>
        <div id="vault1"></div>  
      </body>
    </html>

GetIdHeader代码：

<%@ page import="org.apache.commons.fileupload.*" %>
    <%@ page import="org.apache.commons.fileupload.servlet.*" %>
    <%@ page import="com.scand.fileupload.*" %>
    <%@ page import="java.io.*" %>
    <%@ page import="java.util.*" %>
    <%
        String id =  request.getSession().getId().toString();
        out.println(id);
        session.setAttribute("FileUpload.Progress."+id,"0");
    %>

**GetInfoHeader代码：**

<%
    	out.println(session.getAttribute("FileUpload.Progress."
    			+ request.getParameter("sessionId").toString().trim()));
    %>

**UploadHandler代码：**

<%@ page import="org.apache.commons.fileupload.*" pageEncoding="UTF-8"%>
    <%@ page import="org.apache.commons.fileupload.servlet.*"%>
    <%@ page import="com.scand.fileupload.*"%>
    <%@ page import="java.io.*"%>
    <%@ page import="java.util.*"%>
    <%
    	String uploadFolder = request.getRealPath("/" + "\\upload");
    
    	// Check that we have a file upload request
    	boolean isMultipart = FileUpload.isMultipartContent(request);
    
    	if (!isMultipart) {
    
    		out.println("Use multipart form to upload a file!");
    
    	} else {
    
    		String fileId = request.getParameter("sessionId").toString()
    				.trim();
    
    		//创建数据工厂
    		FileItemFactory factory = new ProgressMonitorFileItemFactory(
    				request, fileId);
    		ServletFileUpload upload = new ServletFileUpload(factory);
    
    		//从请求对象中获得要上传对象
    		List /* FileItem */items = upload.parseRequest(request);
    
    		//迭代所有上传的FileItem
    		Iterator iter = items.iterator();
    		while (iter.hasNext()) {
    			FileItem item = (FileItem) iter.next();
    
    			if (item.isFormField()) {
    				//如果是表单对象再此处处理
    			} else {
    				//处理上传文件
    				String fieldName = item.getFieldName();
    				String fileName = item.getName();
    				int i2 = fileName.lastIndexOf("\\");
    				if (i2 > -1)
    					fileName = fileName.substring(i2 + 1);
    				File dirs = new File(uploadFolder);
    				if (!dirs.exists()) {
    					dirs.mkdir();
    				}
    				//dirs.mkdirs();
    				long l = System.currentTimeMillis();
    				String houzhui = fileName.substring(fileName
    						.lastIndexOf("."));
    
    				fileName = l + houzhui;
    				if (houzhui.equals(".jpg") || houzhui.equals(".gif")
    						|| houzhui.equals(".png")
    						|| houzhui.equals(".bmp")
    						|| houzhui.equals(".JPG")
    						|| houzhui.equals(".GIF")
    						|| houzhui.equals(".PNG")
    						|| houzhui.equals(".BMP")) {
    					File uploadedFile = new File(dirs, fileName);
    					item.write(uploadedFile);
    					session.setAttribute("FileUpload.Progress."
    							+ fileId, "-1");
    				} else {
    					session.setAttribute("FileUpload.Progress."
    							+ fileId, "-2");
    				}
    			}
    		}
    	}
    %>

**downLoad代码：**

<%@page import="com.jspsmart.upload.SmartUpload"%>
    <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://"
    			+ request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    	String fileName = request.getParameter("fileName");
    	if (fileName != null) {
    		fileName = fileName.replaceAll(basePath, "");
    		SmartUpload su = new SmartUpload();
    		su.initialize(pageContext);
    		su.downloadFile(fileName);
    	}
    %>
    
    <head>
    <base href="<%=basePath%>">
    <title>download</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">
    <script type="text/javascript">
    	function d1(obj) {
    		window.location.href = "download.jsp?fileName=" + obj.src;
    	}
    </script>
    </head>
    
    <body>
    	<form action="" name="baseForm">
    		<img alt="" src="upload/1478057251961.jpg" name="zyf1"
    			οnclick="d1(this)"> <br> <img alt=""
    			src="upload/1478057251961.jpg" name="zyf2"> <br> <img
    			alt="" src="upload/1478057251961.jpg" name="zyf3"> <br>
    	</form>
    </body>
    </html>

**效果：上传成功后会传到工程里面的upload文件夹中**

**![Center][]**

**![Center 1][]![Center 2][]**

**2.将Excel表单上传到数据库中**

**DBConnection代码：**

package com.jredu.excel;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class DBConnection {
    	   private static Connection con = null;
    	    
    	   private static String driverName = "com.mysql.jdbc.Driver";
    		
    	   private static String userName   = "root";
    		
    	   private static String userPasswd = "ffffff";
    		
    	   private static String dbName     = "you";
    		
    	   private static String url = "jdbc:mysql://localhost/" + dbName 
    		                        + "?user=" + userName 
    		                        + "&password=" + userPasswd
    		                        + "&useUnicode=true&characterEncoding=gbk";
        public static Connection getConnection(){
        	
        	try{
        		 Class.forName(driverName);
          	     con = DriverManager.getConnection(url);
        	}catch (Exception e) {
    			e.printStackTrace();
    		}
        	return con;
        }
        public static void closeConnection(){
        	if(con != null){
        		try {
    				con.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
        	}
        	
        }
    }

**demoSel代码：**

package com.jredu.excel;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    public class demoSel extends HttpServlet {
    	public demoSel() {
    		super();
    	}
    
    	public void destroy() {
    		super.destroy();
    	}
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    
    		response.setContentType("text/html");
    		response.setCharacterEncoding("utf-8");
    		PrintWriter out = response.getWriter();
    		System.out.println("导入成功");
    		// TestExcel.start();
    	}
    
    	public void init() throws ServletException {
    	}
    
    }

**Excel实体类代码:**

package com.jredu.excel;
    
    public class Excel {
    
    	private String id;
    	private String name;
    	private String className;
    	private String sex;
    	
    	public Excel(){};
    	public String getId() {
    		return id;
    	}
    	public void setId(String id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getClassName() {
    		return className;
    	}
    	public void setClassName(String className) {
    		this.className = className;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    }

**TestExcel代码：**

package com.jredu.excel;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class TestExcel {
    
    	 //记录类的输出信息-
    	//获取Excel文档的路径-
    	 //.xlsx文件用XSSFWorkbook   .xlx 用的HSSFWorkbook
    	public static String filepath="C://Users/Administrator/Desktop/excel.xlsx";
    	public static void start(String filepath){
    		//创建对excel工作簿的引用
    		XSSFWorkbook wookbook;
    		try {
    			wookbook = new XSSFWorkbook(new FileInputStream(filepath));
    			//在Execl文档中，第一张工作表的缺省索引是0
    			//XSSFSheet sheet=wookbook.getSheetAt(0);
    			XSSFSheet sheet=wookbook.getSheet("Sheet1");
    			//获取到Execl文件中的所有行数-
    			int rows=sheet.getPhysicalNumberOfRows();
    			//遍历行
    			for(int i=1;i<rows;i++){
    				//读取左上端单元格
    				XSSFRow row=sheet.getRow(i);
    				//行不能空
    				if(row!=null){
    					//获取到所有的列-
    					int cells=row.getPhysicalNumberOfCells();
    					String value="";
    					//遍历列-
    					for(int j=0;j<cells;j++){
    						//获取列的值
    						XSSFCell cell=row.getCell(j);
    						if(cell!=null){
    							switch(cell.getCellType()){
    							//这个类型是：公式
    							//Excel里面的“公式”，可以用cell.getNumericCellValue(); 来获得“结果”，也就是“公式”计算之后的结果 
    							case HSSFCell.CELL_TYPE_FORMULA:
    								break;
    							//这个类型是：数字
    							case HSSFCell.CELL_TYPE_NUMERIC:
    								value+=cell.getNumericCellValue()+",";
    								break;
    							case HSSFCell.CELL_TYPE_STRING:
    								value+=cell.getStringCellValue()+",";
    								break;
    								default:
    									value +=0;
    							}
    						}
    					}
    					//将数据插入到mysql数据库中
    					String[] val=value.split(",");
    					Excel entity=new Excel();
    					entity.setId(val[0]);
    					entity.setName(val[1]);
    					entity.setClassName(val[2]);
    					entity.setSex(val[3]);
    					TestMethod method=new TestMethod();
    					int a=method.add(entity);
    					if(a>0){
    					System.out.println("插入成功");
    					}else{
    						System.out.println("插入失败");
    					}			
    				}
    			}
    		} catch (FileNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    			
    	}
    }

**TestMethod代码:**

package com.jredu.excel;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class TestMethod {
    
    	public int add(Excel te) {
    
    		Connection con = DBConnection.getConnection();
    		PreparedStatement pstmt = null;
    		int count = 0;
    		String sql = " insert into excel(id,name,className,sex) values(?,?,?,?)";
    		try {
    			pstmt = con.prepareStatement(sql);
    			pstmt.setString(1, te.getId());
    			pstmt.setString(2, te.getName());
    			pstmt.setString(3, te.getClassName());
    			pstmt.setString(4, te.getSex());
    			count = pstmt.executeUpdate();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				pstmt.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			DBConnection.closeConnection();
    		}
    		return count;
    	}
    }

**index中代码：**

**效果：**

**数据库中数据 Excel表中数据**

**![Center 3][]![Center 4][]**

[Center]: /images/20220715/6aa3b487053d45e3b5c7dac56bd1015b.png
[Center 1]: https://img-blog.csdn.net/20161102204049290?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: /images/20220715/e3e0a1ddb8044c12a4169b86ce301f4c.png
[Center 3]: https://img-blog.csdn.net/20161102212418949?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: /images/20220715/9dbcab32cc0e45e087fd604535d8c6a2.png