JSP页面
<button class="button" style="font-size:13px;float: right;margin: 8px 10px 0 0;" οnclick="return ajaxFileUpload();">上传</button>
<input id="fileToUpload" type="file" size="5" name="fileToUpload" accept=".xls,.xlsx" style="float: right;margin: 8px 0 0 0;"/>
function ajaxFileUpload() {
if ($("#fileToUpload").val() == "") {
alert("必须选择要上传的宣传单!");
return false;
}
$("#loading").ajaxStart(function() {
$("#mrgfrm").attr("enctype", "multipart/form-data");
$("#mrgfrm").attr("encoding", "multipart/form-data");
$(this).show();
}).ajaxComplete(function() {
$("#mrgfrm").attr("enctype", "application/x-www-form-urlencoded");
$("#mrgfrm").attr("encoding", "application/x-www-form-urlencoded");
$(this).hide();
});
var con = confirm("不要存入特殊字符否则导入失败!");
if(con){
$.ajaxFileUpload({
url: 'usersupload',
secureuri: false,
fileElementId: 'fileToUpload',
dataType: 'json',
data: {subpath: 'users/<%=SystemHelper.getDateSimpTime()%>'},
success: function(data, status) {
if (data.error == '1') {
alert("上传失败:" + data.msg);
} else if(data.error == '2'){
alert("请上传Excel文档!");
}else if(data.error == '3'){
alert("导入失败!\n失败原因:导入的数据有误,或者手机号码重复\n请检查已有用户核对信息后再重新导入");
} else {
alert("上传成功!");
}
},
error: function(data, status, e) {
alert('上传出错!');
}
})
}
return false;
}
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.netjava.entity.Cucustomer;
import net.netjava.entity.Suer;
import net.netjava.excel.ExcelUtil;
import net.netjava.manager.MCucustomerDAO;
import net.netjava.page.AccessController;
import net.netjava.util.SystemHelper;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* 文件上传 具体步骤: 1)获得磁盘文件条目工厂 DiskFileItemFactory 要导包 2) 利用 request 获取 真实路径
* ,供临时文件存储,和 最终文件存储 ,这两个存储位置可不同,也可相同 3)对 DiskFileItemFactory 对象设置一些 属性
* 4)高水平的API文件上传处理 ServletFileUpload upload = new ServletFileUpload(factory);
* 目的是调用 parseRequest(request)方法 获得 FileItem 集合list ,
*
* 5)在 FileItem 对象中 获取信息, 遍历, 判断 表单提交过来的信息 是否是 普通文本信息 另做处理 6) 第一种. 用第三方 提供的
* item.write( new File(path,filename) ); 直接写到磁盘上 第二种. 手动处理
*
*/
public class UploadUserExcelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
// 保存文件的目录
private static String PATH_FOLDER = "/";
// 存放临时文件的目录
private static String TEMP_FOLDER = "/";
private static String[] FILE_EXT_LIST = {".xls",".xlsx"};
@Override
public void init(ServletConfig config) throws ServletException {
ServletContext servletCtx = config.getServletContext();
// 初始化路径
// 保存文件的目录
PATH_FOLDER = servletCtx.getRealPath("/upload");
// 存放临时文件的目录,存放xxx.tmp文件的目录
TEMP_FOLDER = servletCtx.getRealPath("/uploadTemp");
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8"); // 设置编码
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
// 获得磁盘文件条目工厂
DiskFileItemFactory factory = new DiskFileItemFactory();
// 如果没以下两行设置的话,上传大的 文件 会占用 很多内存,
// 设置暂时存放的 存储室 , 这个存储室,可以和 最终存储文件 的目录不同
/**
* 原理 它是先存到 暂时存储室,然后在真正写到 对应目录的硬盘上, 按理来说 当上传一个文件时,其实是上传了两份,第一个是以 .tem
* 格式的 然后再将其真正写到 对应目录的硬盘上
*/
factory.setRepository(new File(TEMP_FOLDER));
// 设置 缓存的大小,当上传文件的容量超过该缓存时,直接放到 暂时存储室
factory.setSizeThreshold(1024 * 1024);
// 高水平的API文件上传处理
ServletFileUpload upload = new ServletFileUpload(factory);
String msg="";
int error=0;
try {
// 提交上来的信息都在这个list里面
// 这意味着可以上传多个文件
// 请自行组织代码
List<FileItem> list = upload.parseRequest(request);
// 获取上传的文件
FileItem item = getUploadFileItem(list);
// 获取文件名
String filename = getUploadFileName(item);
String extName = filename.substring(filename.lastIndexOf(".")).toLowerCase();
if(Arrays.asList(FILE_EXT_LIST).contains(extName)){
filename=SystemHelper.createPK()+extName;
//获取路径
String subpath = null;
for (FileItem fileItem : list) {
if (fileItem.isFormField()) {
if (Objects.equals(fileItem.getFieldName(), "subpath")) {
InputStream stream = fileItem.getInputStream();
subpath = Streams.asString(stream, "UTF-8");
break;
}
}
}
String filePath=PATH_FOLDER;
if (subpath != null) {
filePath += "/" + subpath;
}
if(!new File(filePath).isDirectory()){
new File(filePath).mkdirs();
}
// 真正写到磁盘上
item.write(new File(filePath, filename)); // 第三方提供的
File saveFile = new File(new File(filePath), filename);
msg="/upload/" + (subpath == null ? "" : subpath) + "/" + filename ;
String usermsg = upload(request,saveFile);
if(usermsg.indexOf("失败") != -1){
error = 3;
}
}else{
error=2;
}
} catch (FileUploadException e) {
e.printStackTrace();
msg=e.getLocalizedMessage();
error=1;
} catch (Exception e) {
e.printStackTrace();
msg=e.getLocalizedMessage();
error=1;
}finally{
PrintWriter writer = response.getWriter();
writer.print("{");
writer.print("error:\"" + error + "\",");
writer.print("msg:\"" + msg + "\"");
writer.print("}");
writer.close();
}
}
private FileItem getUploadFileItem(List<FileItem> list) {
for (FileItem fileItem : list) {
if (!fileItem.isFormField()) {
return fileItem;
}
}
return null;
}
private String getUploadFileName(FileItem item) {
// 获取路径名
String value = item.getName();
// 索引到最后一个反斜杠
int start = value.lastIndexOf("/");
// 截取 上传文件的 字符串名字,加1是 去掉反斜杠,
String filename = value.substring(start + 1);
return filename;
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
private String upload(HttpServletRequest request,File file) throws Exception {
String msg = "导入成功!";
MCucustomerDAO cuMrg = new MCucustomerDAO();
boolean isrollback = false;
Suer user = AccessController.isValidUser(request.getSession());
try{
//判断excel是什么版本的
boolean isExcel2003 = true;
if (ExcelUtil.isExcel2007(file+"")){
isExcel2003 = false;
}
Workbook wookbook = null;
// 创建对Excel工作簿文件的引用
if (isExcel2003){
wookbook = new HSSFWorkbook(new FileInputStream(file));
}else{
wookbook = new XSSFWorkbook(new FileInputStream(file));
}
// 在Excel文档中,第一张工作表的缺省索引是0
Sheet sheet = wookbook.getSheetAt(0);
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int j = 1; j < rows; j++) {
// 读取左上端单元格
Row row = sheet.getRow(j);
// 行不为空
String value = "";
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
//遍历列
for (int t = 0; t < cells; t++) {
//获取到列的值
Cell cell = row.getCell(t);
if (cell != null) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
DecimalFormat df = new DecimalFormat("0");
value += df.format(cell.getNumericCellValue()) + ",";
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
value += cell.getStringCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
value += cell.getBooleanCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
value += cell.getCellFormula() + ",";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
value += "" + ",";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
value += "非法字符" + ",";
break;
default:
value += "未知类型" + ",";
break;
}
}
}
}
//将数据插入到mysql数据库中
String[] val = value.split(",");
Cucustomer cu = new Cucustomer();
cu.setCucustomerclass(0);
cu.setCucustomersource(0);//来自网页
cu.setCucustomertype(0);
cu.setCucrafttype(0);
cu.setCucreateid(user.getUserid());
cu.setCucreatetime(SystemHelper.createTime());
cu.setIsblack(0);
cu.setCuisvip(0);
if("周边游".indexOf(val[4])!=-1){
cu.setCutype(1);
}else if("国内游".indexOf(val[4])!=-1){
cu.setCutype(2);
}else{
cu.setCutype(3);
}
cu.setCucustomername(val[1]);
if("女".equals(val[2])){
cu.setSex(1);
}else{
cu.setSex(0);
}
cu.setCuid(Long.valueOf(val[0]));
cu.setCuphone(val[0]);
cu.setCuidcard(val[3]);
cuMrg.insert(cu);
}
}catch (Exception e) {
msg = "导入数据失败," + e.getLocalizedMessage() + "!";
e.printStackTrace();
isrollback = true;
}
return msg;
}
}