转自:https://blog.csdn.net/dearfinance/article/details/57402412
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/dearfinance/article/details/57402412
在自己使用easyUi做excel导入导出功能时,结合使用jexcelapi的API做的excel导入导出功能
在pom.xml文件加入
<!-- 上传组件包 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
<!-- Excel操作jar -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
前端页面使用
<!-- 配置导入框 -->
<div id="importExcel" class="easyui-dialog" title="导入excel文件"
style="width: 400px; height: 300px;" data-options="modal:true">
<form id="uploadExcel" method="post" enctype="multipart/form-data">
选择文件: <input id = "excel" name = "excel" class="easyui-filebox" style="width:200px" data-options="prompt:'请选择文件...'">
</form>
<div style="text-align: center; padding: 5px 0;">
<a id = "booten" href="javascript:void(0)" class="easyui-linkbutton"
οnclick="uploadExcel()" style="width: 80px" id="tt">导入</a>
</div>
</div>
function uploadExcel() {
$("#uploadExcel").form('submit');
}
/* 配置导入框 */
$("#uploadExcel").form({
type : 'post',
url : '${pageContext.request.contextPath}/LeadToExcel/LeadInUser',
dataType : "json",
onSubmit: function() {
var fileName= $('#excel').filebox('getValue');
//对文件格式进行校验
var d1=/\.[^\.]+$/.exec(fileName);
if (fileName == "") {
$.messager.alert('Excel批量用户导入', '请选择将要上传的文件!');
return false;
}else if(d1!=".xls"){
$.messager.alert('提示','请选择xls格式文件!','info');
return false;
}
$("#booten").linkbutton('disable');
return true;
},
success : function(result) {
var result = eval('(' + result + ')');
if (result.success) {
$.messager.alert('提示!', '导入成功','info',
function() {
$("#booten").linkbutton('enable');
$('#importExcel').dialog('close');
$('#user').datagrid('reload');
});
} else {
$.messager.confirm('提示',"导入失败!");
$("#booten").linkbutton('enable');
}
}
});
@RequestMapping("/LeadInUser")
public String leadInExcel(HttpServletResponse response,@RequestParam(value="excel")CommonsMultipartFile excel) throws Exception{
JSONObject jso=new JSONObject();
InputStream in;
try {
// 获取前台exce的输入流
in = excel.getInputStream();
//excel的表头与文字对应,获取excel表头
LinkedHashMap<String, String> map = leadToExcelController.getLeadToFiledPublicQuestionBank();
//获取组合excle表头数组,防止重复用的
String[] uniqueFields =new String[] {"名称","密码","年龄" };
//获取需要导入的具体的表
User user = new User();
//excel转化成的list集合
ArrayList list = null;
try {
//调用excle共用类,转化成list
list=ExcelUtil.excelToList(in, user.getClass(), map, uniqueFields);
} catch (ExcelException e) {
e.printStackTrace();
}
//保存实体集合
int num = this.userService.batchInsert(list);
if(num > 0){
jso.put("success", true);
ResponseUtil.write(response, jso);
}else{
jso.put("success", false);
ResponseUtil.write(response, jso);
}
} catch (IOException e1) {
e1.printStackTrace();
jso.put("success", false);
ResponseUtil.write(response, jso);
}
return null;
}
导出功能
{
iconCls : 'icon-print',
text : '导出',
handler : function() {
var grid = $('#user');
var options = grid.datagrid('getPager').data("pagination").options;
var page = options.pageNumber;//当前页数
var rows = options.pageSize;//每页的记录数(行数)
var url = '${pageContext.request.contextPath}/LeadToExcel/LeadToUser?rows='+rows+'&page='+page
window.location.href=url;
}
}
@RequestMapping("/LeadToUser")
public String leadToExcelQuestionBank(HttpServletResponse response,@RequestParam(value="page") String page,@RequestParam(value="rows") String rows) throws Exception {
JSONObject jso=new JSONObject();
try {
// excel表格的表头,map
LinkedHashMap<String, String> fieldMap = leadToExcelController.getLeadToFiledPublicQuestionBank();
// excel的sheetName
String sheetName = "用户";
// excel要导出的数据
ArrayList<User> list = this.userService.getAllUser((Integer.parseInt(page)-1)*Integer.parseInt(rows),Integer.parseInt(rows));
// 导出
if (list == null || list.size() == 0) {
jso.put("success", false);
ResponseUtil.write(response, jso);
}else {
//将list集合转化为excle
ExcelUtil.listToExcel(list, fieldMap, sheetName, response);
}
return null;
} catch (ExcelException e) {
e.printStackTrace();
jso.put("success", false);
ResponseUtil.write(response, jso);
}
return null;
}
ExcelUtil工具类是在网上找的
---------------------
作者:等_天蓝再看海
来源:CSDN
原文:https://blog.csdn.net/dearfinance/article/details/57402412
版权声明:本文为博主原创文章,转载请附上博文链接!