导入Excel

最新推荐文章于 2022-12-05 14:29:21 发布

Zerlinda_Li

最新推荐文章于 2022-12-05 14:29:21 发布

阅读量1k

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/licheng989/article/details/38557973

版权

java 专栏收录该内容

48 篇文章 0 订阅

订阅专栏

前面利用poi实现了导出excel，现在再来实现导入功能。

基本思路是前台页面上传Excel文件，将文件路径作为参数传到后台，后台获取完整路径，并通过InputStream来得到文件，再解析出文件内容，作为实体对象存到数据库，最后删除上传的excel文件。

这是页面代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="gx.util.base.SecurityUtil"%>
<%
	String contextPath = request.getContextPath();
	SecurityUtil securityUtil = new SecurityUtil(session);
%>
<!DOCTYPE html>
<html>
<head>
<title>会议组织</title>
<jsp:include page="../../inc.jsp"></jsp:include>


<script type="text/javascript">
	var uploader;//上传对象
	// 导出excel
	var export1 = function(){
		window.open(gx.contextPath + '/base/cmorganization!export.gx');
	};
	
	$(function() {
		//文件上传
		uploader = new plupload.Uploader({//上传插件定义
			browse_button : 'pickfiles',//选择文件的按钮
			container : 'container',//文件上传容器
			runtimes : 'html5,flash',//设置运行环境，会按设置的顺序，可以选择的值有html5,gears,flash,silverlight,browserplus,html4
			flash_swf_url : gx.contextPath + '/jslib/plupload-1.5.8/js/plupload.flash.swf',// Flash环境路径设置
			url :  gx.contextPath + '/plUploadFile?fileFolder=/userPhoto',//上传文件路径
			max_file_size : '10mb',//100b, 10kb, 10mb, 1gb
			chunk_size : '10mb',//分块大小，小于这个大小的不分块
			unique_names : true,//生成唯一文件名
			// 指定要浏览的文件类型
			filters : [ {
				title : 'excel文件',
				extensions : 'xls,xlsx'
			} ]
		});
		uploader.bind('Init', function(up, params) {//初始化时
			//$('#filelist').html("<div>当前运行环境: " + params.runtime + "</div>");
			$('#filelist').html("");
		});
		
		uploader.bind('FilesAdded', function(up, files) {//选择文件后
			debugger;
			$.each(files, function(i, file) {
				$('#filelist').append('<div id="' + file.id + '">' + file.name + '(' + plupload.formatSize(file.size) + ')<strong></strong>' + '<span οnclick="uploader.removeFile(uploader.getFile($(this).parent().attr(\'id\')));$(this).parent().remove();" style="cursor:pointer;" class="ext-icon-cross" title="删除">    </span></div>');
			});
			up.refresh();
		});
		
		uploader.bind('BeforeUpload', function(uploader, file) {//上传之前
			if (uploader.files.length > 1) {
				parent.$.messager.alert('提示', '只允许最多选择1个文件！', 'error');
				uploader.stop();
				return;
			}
			$('.ext-icon-cross').hide();
		});
		
		uploader.bind('UploadProgress', function(up, file) {//上传进度改变
			var msg;
			if (file.percent == 100) {
				msg = '99';//因为某些大文件上传到服务器需要合并的过程，所以强制客户看到99%，等后台合并完成...
			} else {
				msg = file.percent;
			}
			$('#' + file.id + '>strong').html(msg + '%');
			parent.gx.progressBar({//显示文件上传滚动条
				title : '文件上传中...',
				value : msg
			});
		});
		uploader.bind('Error', function(up, err) {//出现错误
			$('#filelist').append("<div>错误代码: " + err.code + ", 描述信息: " + err.message + (err.file ? ", 文件名称: " + err.file.name : "") + "</div>");
			up.refresh();
		});
		
		uploader.bind('FileUploaded', function(up, file, info) {//上传完毕
			var response = $.parseJSON(info.response);
			if (response.status) {
				$('#' + file.id + '>strong').html("100%");
				$.post(gx.contextPath + '/base/cmorganization!upload.gx?cmorganizationUploadFile='+response.fileUrl,function(result) {
					if (result.success) {
						$.messager.alert("系统提示","上传成功");
						$("#dlg2").dialog("close");
						$("#dg").datagrid("reload");
						location.reload();
					}
				}, 'json');
			}
		});
		uploader.init();
		
	});
	
	// 导出excel
	var exportFunction = function(){
		var checklength = $('#grid').datagrid('getSelections');
		if (checklength.length > 0) {
			var saveid="";
			for (var a=0; a<checklength.length; a++) {
				var cmid = checklength[a].cmId;
				saveid += "'"+cmid+"',";
			}
			if (saveid.length > 0) {
				saveid = saveid.substring(0, saveid.length-1);
				window.location.href=gx.contextPath+ '/base/cmorganization!export.gx?cmId='+saveid;
			} else {
				parent.$.messager.alert('提示',"请勾选可操作信息进行导出!");
			}
		} else {
			parent.$.messager.alert('提示',"请勾选可操作信息进行导出!");
		}
	};
	
	// 打开导入数据面板
	var openUploadFileDialog = function(){
		$("#dlg2").dialog('open').dialog('setTitle','批量导入数据');
	};

	// 下载导入模板
	var downloadTemplate = function(){
		window.open(gx.contextPath +'/securityJsp/video/template/cmorganizationExporTemplate.xlsx');
	};	


	
	var uploadFile = function() {
		//点添加
		var dialog = parent.gx.modalDialog({
			title : '导入',
			url : gx.contextPath + '/securityJsp/video/MeetingManagementFileForm.jsp',
			buttons : [ {
				text : '保存',
				iconCls: 'icon-save',
				handler : function() {
					dialog.find('iframe').get(0).contentWindow.submitForm(dialog, grid, parent.$);
				}
			},{
				text : '关闭',
				iconCls: 'icon-cancel',
				handler: function () { dialog.dialog("close"); }
			} ]
		});
	};
	
		
		var uploadFile = function() {
			if ($('form').form('validate')) {
				if (uploader.files.length > 0) {
					uploader.start();
					uploader.bind('StateChanged', function(uploaders) { // 在所有的文件上传完毕时，提交表单
						if (uploaders.files.length === (uploaders.total.uploaded + uploaders.total.failed)) {
							//这里操作文件insert
							parent.gx.progressBar('close');//关闭上传进度条
						}
					});
					}
			}
		};
		
</script>
</head>
<body class="easyui-layout" data-options="fit:true,border:false">	
	<!-- 导入数据面板 -->
	<div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
            closed="true" buttons="#dlg-buttons2">
        	<form method="post" class="form">
        	<table>
        		<tr>
        			<th>下载模版：</th>
        			<td><a href="javascript:void(0)" class="easyui-linkbutton"  οnclick="downloadTemplate()">导入模版</a></td>
        		 <tr>
				<tr>
				<th>附件信息:</th>
					<td  colspan="3">
						<div id="container"  style="float: left;width: 100%;">
						<a id="pickfiles" href="javascript:void(0);" style="float: left;"
							 class="easyui-linkbutton" data-options="iconCls:'ext-icon-zoom'">选择文件</a>
						</div>
						<div style="float: left;" id="filelist">您的浏览器没有安装Flash插件，或不支持HTML5！</div>
					</td>
				<tr>
        		</tr> 
        	</table>
        </form>
	</div>

	<div id="dlg-buttons2">
		<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" οnclick="uploadFile()">上传</a>
		<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" οnclick="javascript:$('#dlg2').dialog('close')">关闭</a>
	</div>
	
</body>
</html>

action中的代码：

	/**
	 * 导入Excel
	 */
	public String upload() throws Exception {
		String webParentPath = new File(getRequest().getSession().getServletContext().getRealPath("/")).getParent();// 当前WEB环境的上层目录
		String filePath =webParentPath+cmorganizationUploadFile;
		Workbook wb = ExcelUtil.readExcel(filePath); // 获取工作簿
		insertExcel(wb); // 插入数据库
		ExcelUtil.deleteFile(filePath); // 删除文件
		JSONObject result=new JSONObject();
		result.put("success", "true");
		ResponseUtil.write(ServletActionContext.getResponse(), result);
		return null;
	}
	
	/**
	 * 读取excel文件内容，并存到数据库
	 */
	public void insertExcel(Workbook wb) {
		Sheet sheet = wb.getSheetAt(0);
		if (sheet != null) {
			for(int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if(row == null) {
					continue;
				}
				TCrCmOrganization cmOrganization = new TCrCmOrganization();
				cmOrganization.setCmName(ExcelUtil.formatCell(row.getCell(0)));
				cmOrganization.setCmTimeks(DateUtil.stringToDate(ExcelUtil.formatCell(row.getCell(1))));
				cmOrganization.setCmTimejs(DateUtil.stringToDate(ExcelUtil.formatCell(row.getCell(2))));
				cmOrganization.setCmOrganizer(ExcelUtil.formatCell(row.getCell(3)));
				cmOrganization.setCmCapacity(ExcelUtil.formatCell(row.getCell(4)));
				cmOrganization.setCmContent(ExcelUtil.formatCell(row.getCell(5)));
				cmOrganization.setCmHost(ExcelUtil.formatCell(row.getCell(6)));
				cmOrganization.setCmRecordpeople(ExcelUtil.formatCell(row.getCell(7)));
				cmOrganization.setCmSummaryLevel(ExcelUtil.formatCell(row.getCell(8)));
				service.save(cmOrganization);
			}
		}
	}

action中调用的方法：

	/**
	 * 读取excel文件
	 */
	public static Workbook readExcel(String path) {
		Workbook wb = null;
		try {
		      InputStream inp = new FileInputStream(path);
		      wb = WorkbookFactory.create(inp);      
		    } catch (FileNotFoundException e) {
		      e.printStackTrace();
		    } catch (InvalidFormatException e) {
		      e.printStackTrace();
		    } catch (IOException e) {
		      e.printStackTrace();
		    }
	    return wb;
	}
	
    /**
     * 删除单个文件
     * @param   sPath    被删除文件的文件名
     * @return 单个文件删除成功返回true，否则返回false
     */
    public static boolean deleteFile(String sPath) {
        boolean flag = false;
        File file = new File(sPath);
        // 路径为文件且不为空则进行删除
        if (file.isFile() && file.exists()) {
            file.delete();
            flag = true;
        }
        return flag;
    }
    
	/**
	 * 将excel里面的数据类型全部转为String类型
	 */
	public static String formatCell(HSSFCell hssfCell){
		if(hssfCell==null){
			return "";
		}else{
			if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
				return String.valueOf(hssfCell.getBooleanCellValue());
			}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				return String.valueOf(hssfCell.getNumericCellValue());
			}else{
				return String.valueOf(hssfCell.getStringCellValue());
			}
		}
	}
	
	/**
	 * 将excel里面的数据类型全部转为String类型
	 */
	public static String formatCell(Cell cell){
		if(cell==null){
			return "";
		}else{
			if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
				return String.valueOf(cell.getBooleanCellValue());
			}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
				return String.valueOf(cell.getNumericCellValue());
			}else{
				return String.valueOf(cell.getStringCellValue());
			}
		}
	}

不喜勿喷，欢迎共同探讨。