最近遇到了需要上传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负责实现具体的上传功能，代码中有标注，很简单，应该都可以看懂