上传excel文件到服务器

落日映苍穹つ 2022-07-14 13:57 300阅读 0赞

最近遇到了需要上传excel文件，并将excel表中的数据都出来，存到数据库中的需求，今天将步骤整理一下，如下：

一、新建一个html(或jsp页面)，如：uploadExcel.html，代码如下：

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <title>导入Excel表</title>
    <meta http-equiv="Content-Type" content="text/html; charset=gbk">
    
    
    </head>
    <body class="dialogBody">
    	<div>
    		<form id="form2" method="post" action="../../uploadExcel"
    			enctype="multipart/form-data" style="align: center">
    				<table width="100%" border="0" cellspacing="0" cellpadding="6" class="blockTable">
    					<tr>
    						<td>
    						</td>
    					</tr>
    					<tr>
    						<td align="center"><h2>选择Excel表：</h2></td>
    						<td align="center">
    							<div>
    								<input type="file" name="file_upload"  />
    							</div>
    						</td>
    					</tr>
    					<tr>
    						<td align="center"><input type="submit" name="submit" value="上传" /></td>
    					</tr>
    					<tr></tr>
    					<tr></tr>
    					<tr></tr>
    				</table>
    		</form>
    	</div>
    </body>
    </html>

2、在web.xml中配置servlet和servlet-mapping，代码如下：

<servlet>
    		<servlet-name>uploadExcel</servlet-name>
    		<servlet-class>com.shop.upload.UploadServlet</servlet-class>
    		<init-param>
    		<param-name>filePath</param-name>
    		<param-value>store</param-value>
    		</init-param>
    		<init-param>
    		<param-name>tempFilePath</param-name>
    		<param-value>temp</param-value>
    		</init-param>
    	</servlet>
    	<servlet-mapping>
    		<servlet-name>uploadExcel</servlet-name>
    		<url-pattern>/uploadExcel</url-pattern>
    	</servlet-mapping>

3、servlet类代码如下：

package com.upload;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.PrintWriter;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.ServletConfig;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.fileupload.FileItem;
    import org.apache.commons.fileupload.disk.DiskFileItemFactory;
    import org.apache.commons.fileupload.servlet.ServletFileUpload;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    /**
     * 上传文件的servlet类
     */
    public class UploadServlet extends HttpServlet{
    	/**
    	 * @function：Excel 表格上传
    	 */
    	private String filePath;  //存放上传文件的目录
    	private String tempFilePath;//存放临时文件的目录
    	@Override
    	public void init(ServletConfig config) throws ServletException {
    		super.init(config);
    		//读取初始化参数filePath
    		filePath=config.getInitParameter("filePath");
    		//读取初始化参数tempFilePath
    		tempFilePath=config.getInitParameter("tempFilePath");
    		filePath=getServletContext().getRealPath(filePath);
    		tempFilePath=getServletContext().getRealPath(tempFilePath);
    	}
    	@Override
    	protected void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		response.setContentType("text/html; charset=GBK");
    		response.setHeader("Cache-Control", "no-cache");
    		response.setCharacterEncoding("GBK");
    		PrintWriter out = response.getWriter();
    		try {
    			//创建一个基于硬盘的FileItem工厂
    			DiskFileItemFactory factory=new DiskFileItemFactory();
    			//设置向硬盘写数据时所用的缓冲区的大小，暂定10M，一会再改
    			factory.setSizeThreshold(1000*1024);
    			//设置临时目录
    			factory.setRepository(new File(tempFilePath));
    			
    			//创建一个文件上传处理器
    			ServletFileUpload upload=new ServletFileUpload(factory);
    			//设置允许上传的文件的最大尺寸，暂定10M，一会再改
    			upload.setSizeMax(1000*1024);
    		
    			Map<String, String> params = new HashMap<String, String>();// 存放请求参数
    			List<FileItem> items=upload.parseRequest(request);
    			Iterator iter=items.iterator();
    			while(iter.hasNext()){
    				FileItem item=(FileItem) iter.next();
    				if(item.isFormField()){
    					processFormField(item,params);  //处理普通的表单域
    				}else{
    					processUploadFile(item,params);  //处理上传文件
    				}
    			}
    			String path=params.get("path");
    			int total=getExcelTotal(path);
    			out.println("<h1 align=\"center\">点击'确认'按钮继续</h1>");
    			out.flush();
    			out.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    			out.println("<h1 align=\"center\">上传出错--点击'取消'按钮</h1>");
    			out.flush();
    			out.close();
    		}
    	}
    	
    	/**
    	 * 获得excel中数据总数
    	 * @param path
    	 * @return
    	 */
    	public int getExcelTotal(String path) {
    		final Sheet sheet;
    		final Workbook book;
    		int total=0;
    		try {
    			// t.xls为要读取的excel文件名
    			book = new VillageHouses().getWorkBookObject(path) ;
    			// 获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
    			sheet = book.getSheetAt(0) ;
    			total = sheet.getLastRowNum() ;
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return total;
    	}
    	
    	/**
    	 * 处理上传文件
    	 * @param item
    	 * @param params
    	 * @throws IOException
    	 */
    	private void processUploadFile(FileItem item, Map<String, String> params) throws IOException {
    		createFileDirectory(filePath);
    		String fileName = item.getName() ;
    		String fileType = fileName.substring(fileName.lastIndexOf("."), fileName.length());
    		
    		long time = System.currentTimeMillis();// 时间毫秒数
    		String savePath = filePath +"/"+ time + fileType;
    		System.out.println("服务器文件路径："+savePath);
    		InputStream inputStream = item.getInputStream();// 获取文件流
    		FileOutputStream outputStream = new FileOutputStream(savePath);// 创建输出流
    		byte[] tyte = new byte[1024];
    		int len = 0;
    		while ((len = inputStream.read(tyte)) > 0) {
    			outputStream.write(tyte, 0, len);
    		}
    		inputStream.close();
    		outputStream.close();
    		item.delete();// 删除临时文件
    		params.put("path", savePath);
    	}
    	
    	/**
    	 * 处理表单数据
    	 * @param item
    	 * @param params
    	 */
    	private void processFormField(FileItem item, Map<String, String> params) {
    		String name=item.getFieldName();//获得表单域的名字
    		String value=item.getString(); //获得表单域的值
    		if("xqid".equals(name))
    		params.put("xqid", value);
    		if("id".equals(name))
    			params.put("id", value);
    	}
    	
    	/**
    	 * 判断项目所在服务器上的文件夹是否创建
    	 * @param path
    	 */
    	private void createFileDirectory(String path) {
    		File file = new File(path);
    		if (!file.exists()) {
    			// 创建文件夹
    			file.mkdirs();
    		}
    	}
    	
    }

这个就是jsp负责页面，servlet负责实现具体的上传功能，代码中有标注，很简单，应该都可以看懂