效果:
弹出层使用的是jquery插件layer 插件下载地址http://download.csdn.net/detail/rendeyishi/8048139
因为我是使用的是jquery里面的ajaxSubmit进行文件上传 所以这个jquery.form.js必须存在
还需要的js文件是:layer.min.js jquery-1.7.2.js
例子script代码:
<a href="javascript:void(0);" id="import" class="easyui-linkbutton" >Excel导入</a>
$('#import').on('click', function(){
var pageii = $.layer({
type: 1,
title: false,
area: ['auto', 'auto'],
border: [0], //去掉默认边框
closeBtn: [0, false], //去掉默认关闭按钮
shift: 'left', //从左动画弹出
page: {
html: '<div style="width:420px; height:260px; padding:20px; border:1px solid #ccc; background-color:#eee;">'+
'<button type="button" id="download">下载Excel模板</button><button id="pagebtn" class="btns" οnclick="">关闭</button>'+
'<form id="upload" enctype="multipart/form-data" action="${basePath}/payment/importBuildInfoExcel.html" method="post">'+
' <input name="fileBuildInfo" id="fileBuildInfo" class="big" type="file" />'+
'<input type="button" id="importBuildInfo"class="btns" value="导入"/>'+
' </form>'+
'</div>'
}
});
//自设关闭
$('#pagebtn').on('click', function(){
layer.close(pageii);
});
//点击导入的时候触发的事件
$('#importBuildInfo').on('click',function(){
if($('#fileBuildInfo').val()==""){
alert("请先选择要上传的房屋信息文件!");
}else{
$('#upload').ajaxSubmit({
url:"${basePath}/payment/importBuildInfoExcel.html",
cache:false,
dataType:'json',
success: function(data) {
if(data.result!=null){
$.each(data.result,function(i,value){
alert(value);
});
}
} ,
error:function(){
alert("error");
}
});
}
});
//下载excel模板
$('#download').on('click',function(){
location.href="${basePath}/payment/downloadInfo.html";
});
});
下载模板的方法
/**
* 下载房屋信息模板
* @author ljr
* @param request
* @param response
* @throws Exception
*/
@RequestMapping("/downloadInfo")
@ResponseBody
public void download(HttpServletRequest request,HttpServletResponse response) throws Exception {
response.setContentType("text/html;charset=UTF-8");
BufferedInputStream in = null;
BufferedOutputStream out = null;
request.setCharacterEncoding("UTF-8");
String separator = File.separator;
String rootpath = request.getSession().getServletContext().getRealPath(separator+"upload");
String fileName = "ImportTemplate.xlsx";
try {
File f = new File(rootpath + separator + fileName);
response.setContentType("application/x-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename="+fileName);
response.setHeader("Content-Length",String.valueOf(f.length()));
in = new BufferedInputStream(new FileInputStream(f));
out = new BufferedOutputStream(response.getOutputStream());
byte[] data = new byte[1024];
int len = 0;
while (-1 != (len=in.read(data, 0, data.length))) {
out.write(data, 0, len);
}
} catch (Exception e) {
logger.error("下载房屋信息模版异常:",e);
} finally {
if (in != null) {
in.close();
}
if (out != null) {
out.close();
}
}
}
因为我文件上传我使用的springmvc:
所以配置:
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="20000000"/>
</bean>
文件上传代码:
@RequestMapping("/importBuildInfoExcel")
@ResponseBody
public DefaultResult importBuildInfoExcel(@RequestParam(value="fileBuildInfo",required=false) MultipartFile buildInfo,HttpServletRequest request,HttpServletResponse response){
try {
if (buildInfo != null) {
String path = request.getSession().getServletContext().getRealPath("/upload")+"/"+buildInfo.getOriginalFilename();
File file=ToolKit.getFileFromBytes(buildInfo.getBytes(), path);//需要先保存在本地
List<BuildInfo> list=BuildInfoExcel.importBuildInfo(file, request);//这里是解析excel文件
DefaultResult dr=buildInfoService.insertBuildInfoCommunity(list, logger);//这里做的是一个插入数据库功能
if(dr.getResult()!=null){
List<String> returnList=(List<String>) dr.getResult();
for (String s : returnList) {
logger.info(s);
}
}
ToolKit.deleteFile(path);
return dr;
}
} catch (IOException e) {
logger.error("导入房屋信息报错:",e);
}
return null;
}
解析excel的代码
public static List<BuildInfo> importBuildInfo(File file ,HttpServletRequest request){
try {
// 创建需要批量插入数据集合
List<BuildInfo> list = new ArrayList<BuildInfo>();
// 创建一个FileInputStream 文件输入流
FileInputStream inputStream = new FileInputStream(file);
// 创建对Excel工作簿文件的引用
Workbook wookbook = null;
String name = file.getName();
String fileType = name.substring(name.lastIndexOf(".") + 1,
name.length());
if (fileType.equals("xlsx")) {
wookbook = new XSSFWorkbook(inputStream);
} else if (fileType.equals("xls")) {
wookbook = new HSSFWorkbook(inputStream);
}
// 在Excel文档中,第一张工作表的缺省索引是0
Sheet sheet = wookbook.getSheetAt(0);
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 遍历行 从第二行开始遍历
for (int i = 1; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 创建对象
BuildInfo object = new BuildInfo();
object.setRow(i+1);
object.setCommunityName((row.getCell(0)
.getStringCellValue()).trim());// 获取小区名称
object.setHomeAddress((row.getCell(1).getStringCellValue())
.trim());// 获取房屋信息
// 设置姓名
Cell nameCell = row.getCell(2);
if (nameCell != null && !nameCell.equals("")) {
nameCell.setCellType(Cell.CELL_TYPE_STRING);
String bname = nameCell.getStringCellValue().trim();
if (bname != null && StringUtils.isEmpty(bname)) {
object.setName(bname);
}
}
// 设置性别
Cell sexCell = row.getCell(3);
if (sexCell != null && !sexCell.equals("")) {
sexCell.setCellType(Cell.CELL_TYPE_STRING);
String sex = sexCell.getStringCellValue().trim();
if(sex.equals("男")|| sex.equals("女")){
object.setSex(sex);
}
}
Cell homeAreaCell = row.getCell(4);
if (homeAreaCell != null && !homeAreaCell.equals("")) {
homeAreaCell.setCellType(Cell.CELL_TYPE_STRING);
String homeAreaString = homeAreaCell
.getStringCellValue().trim();
if (!homeAreaString.equals("")) {
try {
Float homeArea = Float
.parseFloat(homeAreaString);
object.setHomeArea(homeArea);
} catch (NumberFormatException e) {
object.setHomeArea(null);
}
}
}
Cell mobileCell = row.getCell(5);
if (mobileCell != null && !(mobileCell.equals(""))) {
mobileCell.setCellType(Cell.CELL_TYPE_STRING);
String mobile = mobileCell.getStringCellValue().trim();
if (!mobile.equals("")) {
object.setPhone(mobile);
}
}
// 将对象增加到集合中
list.add(object);
}
}
// 返回集合
return list;
} catch (IOException e) {
logger.error("创建导入excel对象报错!", e);
}
return null;
}
//根据byte[]获取file
/**
* 根据字节数组获取File
* @param b 字节数组
* @param outputFile 输出的路径(保存路径)
* @return
*/
public static File getFileFromBytes(byte[] b, String outputFile) {
BufferedOutputStream stream = null;
File file = null;
try {
file = new File(outputFile);
FileOutputStream fstream = new FileOutputStream(file);
stream = new BufferedOutputStream(fstream);
stream.write(b);
} catch (Exception e) {
logger.error("文件保存出错",e);
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e1) {
logger.error("文件流关闭出错",e1);
}
}
}
return file;
}