首先页面有个导入按钮
<td style="vertical-align:top;padding:12px 10px 0 0">
<a class="btn btn-small btn-success" οnclick="goImport();">导入</a>
</td>
调用这个onclick事件
<script type="text/javascript">
//导入
function goImport(){
top.jzts();
var diag = new top.Dialog();
diag.Drag=true;
diag.Title ="请选择";
diag.URL = url+'admin/net/goImport.do';
diag.Width = 600;
diag.Height = 500;
diag.CancelEvent = function(){ //关闭事件
location.reload();
diag.close();
};
diag.show();
}
</script>
根据请求路径找到这个goImport.do方法
@RequestMapping(value = "/goImport")
public ModelAndView goImport() throws Exception {
ModelAndView mv = this.getModelAndView();
mv.setViewName("/admin/net/goImport");
return mv;
}
跳转到导入页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%
String path = request.getContextPath();
String basePath = path+"/";
%>
<!DOCTYPE html>
<html lang="en">
<head>
<base href="<%=basePath%>">
<meta charset="utf-8" />
<title></title>
<meta name="description" content="overview & stats" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link href="static/css/bootstrap.min.css" rel="stylesheet" />
<link href="static/css/bootstrap-responsive.min.css" rel="stylesheet" />
<link rel="stylesheet" href="static/css/font-awesome.min.css" />
<!-- 下拉框 -->
<link rel="stylesheet" href="static/css/chosen.css" />
<link rel="stylesheet" href="static/css/ace.min.css" />
<link rel="stylesheet" href="static/css/ace-responsive.min.css" />
<link rel="stylesheet" href="static/css/ace-skins.min.css" />
<link rel="stylesheet" type="text/css" href="themes/default/css/jquery-ui.css" />
<link type="text/css" href="scripts/uploadify/uploadify.css" rel="stylesheet" >
<script src="scripts/jquery-1.11.3.js" type="text/javascript" ></script>
<script type="text/javascript" src="static/js/jquery-1.7.2.js"></script>
<script src="scripts/jquery.form.js"></script>
<!--引入自动填充组件start-->
<script type="text/javascript" src="static/js/jquery.autocomplete.js"></script>
<!--引入弹窗组件start-->
<script type="text/javascript" src="plugins/attention/zDialog/zDialog.js"></script>
<script type="text/javascript" src="plugins/attention/zDialog/zDrag.js"></script>
<!--引入弹窗组件end-->
<script type="text/javascript" src="static/js/jquery.tips.js"></script>
<!-- 区域选择 css/js -->
<link href="themes/default/css/loading/msgbox.css" rel="stylesheet" type="text/css">
<link href="themes/default/css/commoncss.css" rel="stylesheet" type="text/css">
<link href="themes/default/css/base.css?v=1.0.2" rel="stylesheet" type="text/css" />
<script src="scripts/jquery.cookie.js" type="text/javascript"></script>
<script src="scripts/common.js?v=1.0.6" type="text/javascript"></script>
<script src="scripts/myui/hsAreaTabs.js?v=1.0.3" type="text/javascript" ></script>
<script src="scripts/myui/hsFloatPanel.js?v=1.0.3" type="text/javascript" ></script>
<script src="scripts/hsAreaUser.js?v=1.0.3" type="text/javascript"></script>
<!-- 时间插件 -->
<link rel="stylesheet" type="text/css"
href="themes/default/css/jquery-ui.css" />
<script src="scripts/date/laydate.js"></script>
<script type="text/javascript" src="scripts/jquery-ui.js"></script>
<script type="text/javascript"
src="scripts/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript"
src="scripts/jqPagination/js/jquery.jqpagination.js"></script>
<script type="text/javascript" src="scripts/uploadify/jquery.uploadify.min.js"></script>
<script type="text/javascript">
window.jQuery || document.write("<script src='static/assets/js/jquery.js'>"+"<"+"/script>");
</script>
<!-- <![endif]-->
<!--[if IE]>
<script type="text/javascript">
window.jQuery || document.write("<script src='static/assets/js/jquery1x.js'>"+"<"+"/script>");
</script>
<![endif]-->
<script type="text/javascript" src="static/js/chosen.jquery.min.js"></script><!-- 下拉框 -->
<!-- <script type="text/javascript" src="static/js/bootstrap-datepicker.min.js"></script>日期框 -->
<script type="text/javascript" src="static/js/bootbox.min.js"></script><!-- 确认窗口 -->
<script type="text/javascript" src="static/js/bootstrap.min.js"></script>
<!-- ace scripts -->
<script type="text/javascript" src="static/assets/js/ace/elements.fileinput.js"></script>
<script type="text/javascript" src="static/assets/js/ace/ace.js"></script>
<script type="text/javascript">
$(function(){
initForm();
});
function initForm() {
var fileupload = $('#file_upload');
fileupload
.uploadify({
'swf' : url + 'scripts/uploadify/uploadify.swf',
'uploader' : url
+ "admin/net/uploadFile.do",
//按钮显示的文字
'buttonText' : '导入',
//显示的高度和宽度,默认 height 30;width 120
'height' : 40,
'width' : 40,
//上传文件的类型 默认为所有文件 'All Files' ; '*.*'
//在浏览窗口底部的文件类型下拉菜单中显示的文本
'fileTypeDesc' : 'Store Files',
//允许上传的文件后缀
'fileTypeExts' : '*.xls; *.xlsx',
'fileSizeLimit' : '0',
'multi' : false,
'method' : 'post',
'onUploadSuccess' : function(file, result, response) {
var obj = eval('(' + result + ')');
//alert(obj.msg);
if (obj.res == "success") {
$("#path").val(obj.data);
}
},
'onUploadError' : function(file, errorCode, errorMsg,
errorString) {
alert(errorCode + '---' + errorMsg + '---'
+ errorString)
alert('The file ' + file.name
+ ' could not be uploaded: ' + errorString);
}
});
}
//保存
function save(){
initForm();
var path = $("#path").val();
if(path == ""){
alert("请选择文件!");
return;
}
this.close();
$.ajax({
url: url + "admin/net/importEcl.do",
type:"post",
data:{
path : $('#path').val()
},
dataType:"json",
success:function(data,status){
//$this.text("导入");
// var hsArtDialog = dialog({
// title: '提示',
// id:"hs-dialog",
// fixed:true,
// width:300,
// height:100
// });
if(data.state=="success"){
//hsArtDialog.content("导入成功!").showModal();
alert("导入成功!");
}else{
//hsArtDialog.content(data.msg).showModal();
alert(data.msg);
}
},
error:function(xhr,errinfor,ex){
//hsArtDialog.content("异常,请稍后重试或者联系管理员!").showModal();
alert("异常,请稍后重试或者联系管理员!");
}
});
}
</script>
<style type="text/css" >
.red{
color: #ff3c3c;
font-size: 20px;
line-height: 30px;
width: 50px;
display: inline;
}
</style>
</head>
<body>
<form action="admin/net/getStoreList.do" name="Form" id="Form"
method="post" enctype="multipart/form-data">
<div id="zhongxin">
<table id="table_report"
class="table table-striped table-bordered table-hover">
<tr>
<th style="text-align: left;">导入Excel:</th>
<td>
<input id="file_upload" type="file" name="file" class="w218">
<span class="ace-file-container" data-title="选择">
<span class="ace-file-name" data-title="请选择文件...">
<i class=" ace-icon fa fa-upload"></i>
</span>
</span>
<input id="path" name="path" type="hidden" value="">
<a class="remove" href="#"><i class=" ace-icon fa fa-times"></i></a>
</td>
</tr>
</table>
</div>
<div id="zhongxin2" class="center" style="display: none">
<br />
<br />
<br />
<br />
<br />
<img src="static/images/jiazai.gif" /><br />
<h4 class="lighter block green">提交中...</h4>
</div>
</form>
<div style="width: 100%; margin-top: 10px; text-align: center;">
<a class="btn btn-mini btn-primary" οnclick="save();">确定</a> <a
class="btn btn-mini btn-danger" οnclick="top.Dialog.close();">取消</a>
</div>
</body>
</html>
uploadify.do方法
@RequestMapping(value = "/uploadFile")
@ResponseBody
public Map<String, Object> uploadFile(HttpServletRequest request) throws Exception {
Map<String, Object> result = new HashMap<String, Object>();
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("Filedata");
if (file == null || file.getSize() == 0) {
result.put("res", "failed");
result.put("msg", "上传文件不存在!");
result.put("data", "");
return result;
}
CommonsMultipartFile cf = (CommonsMultipartFile) file;
DiskFileItem fi = (DiskFileItem) cf.getFileItem();
File sFile = fi.getStoreLocation();
String fileName = file.getOriginalFilename();
String savePath = request.getSession().getServletContext().getRealPath("/");// 保存路径
savePath = savePath + "upload" + File.separator + "temp" + File.separator;
UploadState state = UploadFileUtils.upload4Stream(fileName, savePath, sFile);
if (state == UploadState.UPLOAD_SUCCSSS) {
result.put("res", "success");
result.put("msg", "获取成功!");
result.put("data", savePath + fileName);
}
} catch (Exception e) {
e.printStackTrace();
result.put("res", "failed");
result.put("msg", "系统出错,请联系管理员!");
result.put("data", "");
}
return result;
}
获取到上传文件的路径之后把文件路径给保存在
goImport页面当中的一个隐藏文本框之中
<input id="path" name="path" type="hidden" value="">
点击保存按钮后调用save()方法,找到后台的importEcl.do方法
@RequestMapping(value = "/importEcl")
@ResponseBody
public Map<String, Object> importEcl(HttpServletRequest request) throws Exception {
Map<String, Object> rsmap = new HashMap<String, Object>();
String flag = "success", msg = "";
String path = str("path", request);
List<Map<String, String>> storeList = new ArrayList<Map<String, String>>();
if (path.endsWith("xls")) {
storeList = readXls(path);//本地环境使用
// storeList = readXls(URLDecoder.decode(path, "UTF-8"));// 测试环境使用
} else if (path.endsWith("xlsx")) {
storeList = readXlsx(path);
// storeList = readXlsx(URLDecoder.decode(path, "UTF-8"));
}
for (Map<String, String> store : storeList) {
Netcashflow netcashflow=new Netcashflow();
netcashflow.setCompany_code(store.get("arg0"));
netcashflow.setCompany_name(store.get("arg1"));
netcashflow.setDistrict_name(store.get("arg2"));
netcashflow.setCustomer_code(store.get("arg3"));
netcashflow.setCustomer_name(store.get("arg4"));
netcashflow.setInventory_name(store.get("arg5"));
netcashflow.setStatistics_time(store.get("arg6"));
netcashflow.setNumber(store.get("arg7"));
netcashflow.setMoney(store.get("arg8"));
netcashflow.setMonthly_price(store.get("arg9"));
netcashflow.setYear(store.get("arg10"));
netcashflow.setEnd_price(store.get("arg11"));
netcashflow.setCost_price(store.get("arg12"));
netcashflow.setAdd_ton(store.get("arg13"));
netcashflow.setAdd_price(store.get("arg14"));
netcashflow.setTotal_number(store.get("arg15"));
netcashflow.setTotal_money(store.get("arg16"));
netcashflow.setTotal_monthly_price(store.get("arg17"));
netcashflow.setTotal_year(store.get("arg18"));
netcashflow.setTotal_end_price(store.get("arg19"));
netcashflow.setTotal_cost_price(store.get("arg20"));
netcashflow.setTotal_add_ton(store.get("arg21"));
netcashflow.setTotal_add_price(store.get("arg22"));
netcashflow.setLead_name(store.get("arg23"));
netcashflow.setLead_time(store.get("arg24"));
netcashflowService.insert(netcashflow);
}
rsmap.put("state", flag);
rsmap.put("msg", msg);
return rsmap;
}
读取excel的两个工具类,一个是读取.xls,另一个是读取.xlsx结尾的excel
//读取xls public List<Map<String, String>> readXls(String path) throws IOException { InputStream is = new FileInputStream(path); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<Map<String, String>> list = new ArrayList<Map<String, String>>(); // Read the Sheet // for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); // numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); // if (hssfSheet == null) { // continue; // } // Read the Row for (int rowNum = 3; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { Map<String, String> store = new HashMap<String, String>(); HSSFCell arg0 = hssfRow.getCell(0); HSSFCell arg1 = hssfRow.getCell(1); HSSFCell arg2 = hssfRow.getCell(2); HSSFCell arg3 = hssfRow.getCell(3); HSSFCell arg4 = hssfRow.getCell(4); HSSFCell arg5 = hssfRow.getCell(5); HSSFCell arg6 = hssfRow.getCell(6); HSSFCell a7 = hssfRow.getCell(7); String arg7 = this.getCellValue(a7, true);// 当前单元格的值 HSSFCell a8 = hssfRow.getCell(8); String arg8 = this.getCellValue(a8, true);// 当前单元格的值 HSSFCell a9 = hssfRow.getCell(9); String arg9 = this.getCellValue(a9, true);// 当前单元格的值 HSSFCell a10 = hssfRow.getCell(10); String arg10 = this.getCellValue(a10, true);// 当前单元格的值 HSSFCell a11 = hssfRow.getCell(11); String arg11 = this.getCellValue(a11, true);// 当前单元格的值 HSSFCell a12 = hssfRow.getCell(12); String arg12 = this.getCellValue(a12, true);// 当前单元格的值 HSSFCell a13 = hssfRow.getCell(13); String arg13 = this.getCellValue(a13, true);// 当前单元格的值 HSSFCell a14 = hssfRow.getCell(14); String arg14 = this.getCellValue(a14, true);// 当前单元格的值 HSSFCell a15 = hssfRow.getCell(15); String arg15 = this.getCellValue(a15, true);// 当前单元格的值 HSSFCell a16 = hssfRow.getCell(16); String arg16 = this.getCellValue(a16, true);// 当前单元格的值 HSSFCell a17 = hssfRow.getCell(17); String arg17 = this.getCellValue(a17, true);// 当前单元格的值 HSSFCell a18 = hssfRow.getCell(18); String arg18 = this.getCellValue(a18, true);// 当前单元格的值 HSSFCell a19 = hssfRow.getCell(19); String arg19 = this.getCellValue(a19, true);// 当前单元格的值 HSSFCell a20 = hssfRow.getCell(20); String arg20 = this.getCellValue(a20, true);// 当前单元格的值 HSSFCell a21 = hssfRow.getCell(21); String arg21 = this.getCellValue(a21, true);// 当前单元格的值 HSSFCell a22 = hssfRow.getCell(22); String arg22 = this.getCellValue(a22, true);// 当前单元格的值 HSSFCell arg23 = hssfRow.getCell(23); HSSFCell arg24 = hssfRow.getCell(24); store.put("arg0", tostr(arg0)); store.put("arg1", tostr(arg1)); store.put("arg2", tostr(arg2)); store.put("arg3", tostr(arg3)); store.put("arg4", tostr(arg4)); store.put("arg5", tostr(arg5)); store.put("arg6", tostr(arg6)); store.put("arg7", tostr(arg7)); store.put("arg8", tostr(arg8)); store.put("arg9", tostr(arg9)); store.put("arg10", tostr(arg10)); store.put("arg11", tostr(arg11)); store.put("arg12", tostr(arg12)); store.put("arg13", tostr(arg13)); store.put("arg14", tostr(arg14)); store.put("arg15", tostr(arg15)); store.put("arg16", tostr(arg16)); store.put("arg17", tostr(arg17)); store.put("arg18", tostr(arg18)); store.put("arg19", tostr(arg19)); store.put("arg20", tostr(arg20)); store.put("arg21", tostr(arg21)); store.put("arg22", tostr(arg22)); store.put("arg23", tostr(arg23)); store.put("arg24", tostr(arg24)); list.add(store); } } // } return list; } //读取xlsx
public List<Map<String, String>> readXlsx(String path) throws IOException { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<Map<String, String>> list = new ArrayList<Map<String, String>>(); XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); for (int rowNum = 3; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow != null) { Map<String, String> store = new HashMap<String, String>(); XSSFCell arg0 = xssfRow.getCell(0); XSSFCell arg1 = xssfRow.getCell(1); XSSFCell arg2 = xssfRow.getCell(2); XSSFCell arg3 = xssfRow.getCell(3); XSSFCell arg4 = xssfRow.getCell(4); XSSFCell arg5 = xssfRow.getCell(5); XSSFCell arg6 = xssfRow.getCell(6); XSSFCell a7 = xssfRow.getCell(7); String arg7 = this.getCellValue(a7, true);// 当前单元格的值 XSSFCell a8 = xssfRow.getCell(8); String arg8 = this.getCellValue(a8, true);// 当前单元格的值 XSSFCell a9 = xssfRow.getCell(9); String arg9 = this.getCellValue(a9, true);// 当前单元格的值 XSSFCell a10 = xssfRow.getCell(10); String arg10 = this.getCellValue(a10, true);// 当前单元格的值 XSSFCell a11 = xssfRow.getCell(11); String arg11 = this.getCellValue(a11, true);// 当前单元格的值 XSSFCell a12 = xssfRow.getCell(12); String arg12 = this.getCellValue(a12, true);// 当前单元格的值 XSSFCell a13 = xssfRow.getCell(13); String arg13 = this.getCellValue(a13, true);// 当前单元格的值 XSSFCell a14 = xssfRow.getCell(14); String arg14 = this.getCellValue(a14, true);// 当前单元格的值 XSSFCell a15 = xssfRow.getCell(15); String arg15 = this.getCellValue(a15, true);// 当前单元格的值 XSSFCell a16 = xssfRow.getCell(16); String arg16 = this.getCellValue(a16, true);// 当前单元格的值 XSSFCell a17 = xssfRow.getCell(17); String arg17 = this.getCellValue(a17, true);// 当前单元格的值 XSSFCell a18 = xssfRow.getCell(18); String arg18 = this.getCellValue(a18, true);// 当前单元格的值 XSSFCell a19 = xssfRow.getCell(19); String arg19 = this.getCellValue(a19, true);// 当前单元格的值 XSSFCell a20 = xssfRow.getCell(20); String arg20 = this.getCellValue(a20, true);// 当前单元格的值 XSSFCell a21 = xssfRow.getCell(21); String arg21 = this.getCellValue(a21, true);// 当前单元格的值 XSSFCell a22 = xssfRow.getCell(22); String arg22 = this.getCellValue(a22, true);// 当前单元格的值 XSSFCell arg23 = xssfRow.getCell(23); XSSFCell arg24 = xssfRow.getCell(24); store.put("arg0", tostr(arg0)); store.put("arg1", tostr(arg1)); store.put("arg2", tostr(arg2)); store.put("arg3", tostr(arg3)); store.put("arg4", tostr(arg4)); store.put("arg5", tostr(arg5)); store.put("arg6", tostr(arg6)); store.put("arg7", tostr(arg7)); store.put("arg8", tostr(arg8)); store.put("arg9", tostr(arg9)); store.put("arg10", tostr(arg10)); store.put("arg11", tostr(arg11)); store.put("arg12", tostr(arg12)); store.put("arg13", tostr(arg13)); store.put("arg14", tostr(arg14)); store.put("arg15", tostr(arg15)); store.put("arg16", tostr(arg16)); store.put("arg17", tostr(arg17)); store.put("arg18", tostr(arg18)); store.put("arg19", tostr(arg19)); store.put("arg20", tostr(arg20)); store.put("arg21", tostr(arg21)); store.put("arg22", tostr(arg22)); store.put("arg23", tostr(arg23)); store.put("arg24", tostr(arg24)); list.add(store); } } // } return list; }
文件上传的一个工具类
// 上传文件的最大长度 // public static long maxFileSize = 1024 * 1024 * 1024 * 2L;// 2G public static long maxFileSize = 1024 * 1024 * 5L;// 5M /** * <b>function:</b>上传文件 * * @author zhaowei * @param uploadFileName * 文件名称 * @param savePath * 保存路径 * @param uploadFile * 上传的文件 * @return 是否上传成功 * @throws Exception */ public static UploadState upload4Stream(String fileName, String path, File file) throws Exception { init(); UploadState state = UploadState.UPLOAD_FAILURE; FileInputStream fis = null; try { long size = file.length(); if (size <= 0) { state = UploadState.UPLOAD_ZEROSIZE; } else { if (size <= maxFileSize) { fis = new FileInputStream(file); state = upload4Stream(fileName, path, fis); } else { state = UploadState.UPLOAD_OVERSIZE; } } } catch (FileNotFoundException e) { state = UploadState.UPLOAD_NOTFOUND; throw e; } catch (IOException e) { state = UploadState.UPLOAD_FAILURE; throw e; } finally { if (fis != null) { fis.close(); } } return state; } /** * <b>function:</b>通过输入流参数上传文件 * * @author zhaowei * @param uploadFileName * 文件名称 * @param savePath * 保存路径 * @param InputStream * 上传的文件的输入流 * @return 是否上传成功 * @throws Exception */ public static UploadState upload4Stream(String fileName, String path, InputStream is) throws Exception { init(); UploadState state = UploadState.UPLOAD_FAILURE; FileOutputStream fos = null; try { mkDir(path); fos = new FileOutputStream(path + fileName); byte[] buffer = new byte[bufferSize]; int len = 0; while ((len = is.read(buffer)) > 0) { fos.write(buffer, 0, len); } state = UploadState.UPLOAD_SUCCSSS; } catch (FileNotFoundException e) { state = UploadState.UPLOAD_NOTFOUND; throw e; } catch (IOException e) { state = UploadState.UPLOAD_FAILURE; throw e; } finally { if (is != null) { is.close(); } if (fos != null) { fos.flush(); fos.close(); } } return state; }
文件上传的一个枚举类/** * <b>function:</b> 文件上传状态 * * @package com.hoo.enums * @fileName UploadState.java * @createDate 2010-10-11 下午12:18:14 * @author hoojo */ public enum UploadState { UPLOAD_SUCCSSS(0, "上传文件成功!"), UPLOAD_FAILURE(1, "上传文件失败!"), UPLOAD_TYPE_ERROR( 2, "上传文件类型错误!"), UPLOAD_OVERSIZE(3, "上传文件过大!"), UPLOAD_ZEROSIZE(4, "上传文件为空!"), UPLOAD_NOTFOUND(5, "上传文件路径错误!"); private String state; private int flag; public String getState() { return this.state; } public int getFlag() { return this.flag; } UploadState(int flag, String state) { this.state = state; this.flag = flag; } }