EasyUI+JFinal+ExcelPOI实现数据的导出
EasyUI+JFinal+ExcelPOI实现数据的导出
需求简介
完成EasyUI的datagrid的数据的导出，可以根据选中的行进行导出，也可以根据当前页面数据进行导出，导出到Excel，导出的数据超出定量（比如超过了100条）分文件打印，最后返回的是所有Excel文件的zip包。
对于没有接触过EasyPOI的来说，首先需要构建entities，类似于一种映射关系，关于得到的数据怎么存到Excel。遇到性别存储的为数组，可以通过entity.setReplace(new String[]{"男_1","女_0"});来转换，如果需要映射的关系比较复杂，比如部门ID和部门名称的关系，可以通过设置字典来处理，设置一个Handler继承IExcelDictHandler，处理相应的映射关系即可。
注：内容类目是可选显示的。
整体流程图
主要代码和解释
现有功能解释
数据的展示
点击不同的用户组可以筛选人员
可以选择想要显示的列目：
流程解释
点击按钮进入JS函数
function selectColu() {
var rows = $("#dg").datagrid('getSelections');
if(rows.length<=0){
$.messager.confirm('Confirm','是否确定导出全部信息?',function(r){
if(r){
doExcel('user',rows);
}
});
}else{
$.messager.confirm('Confirm','确定导出?',function(r){
if(r){
console.log(rows);
doExcelSimple('user',rows);
}
});
}
}
全部导出执行doExcel()：
function doExcel(url,rows) {
var form=$("<form>");
form.attr("style","display:none");
form.attr("target","");
form.attr("method","post");
form.attr("action",getRootPath()+"/export/exportData");
var input1=$("<input>");
input1.attr("type","hidden");
input1.attr("name","rows");
input1.attr("value",JSON.stringify(rows));
var input2=$("<input>");
input2.attr("type","hidden");
input2.attr("name","url");
input2.attr("value",url);
var input3=$("<input>");
input3.attr("type","hidden");
input3.attr("name","name");
input3.attr("value",$("#SearchUserName").val());
var input4=$("<input>");
input4.attr("type","hidden");
input4.attr("name","pwd");
input4.attr("value",$("#SearchPwd").val());
var input5=$("<input>");
input5.attr("type","hidden");
input5.attr("name","sort");
input5.attr("value",sort);
var input6=$("<input>");
input6.attr("type","hidden");
input6.attr("name","order");
input6.attr("value",order);
var input7=$("<input>");
input7.attr("type","hidden");
input7.attr("name","orgId");
input7.attr("value",orgId);
var page = $("#dg").datagrid('getPager').data('pagination').options;
var input8=$("<input>");
input8.attr("type","hidden");
input8.attr("name","pageNum");
input8.attr("value",page.pageNumber);
var input9=$("<input>");
input9.attr("type","hidden");
input9.attr("name","pageSize");
input9.attr("value",page.pageSize);
$("body").append(form);
form.append(input1);
form.append(input2);
form.append(input3);
form.append(input4);
form.append(input5);
form.append(input6);
form.append(input7);
form.append(input8);
form.append(input9);
form.submit();
}
导出部分数据：
function doExcelSimple(url,rows) {
var form=$("<form>");
form.attr("style","display:none");
form.attr("target","");
form.attr("method","post");
form.attr("action",getRootPath()+"/export/exportData");
var input1=$("<input>");
input1.attr("type","hidden");
input1.attr("name","rows");
input1.attr("value",JSON.stringify(rows));
var input2=$("<input>");
input2.attr("type","hidden");
input2.attr("name","url");
input2.attr("value",url);
$("body").append(form);
form.append(input1);
form.append(input2);
form.submit();
}
获取项目根目录代码：
function getRootPath() {
var curWwwPath = window.document.location.href;
var pathName = window.document.location.pathname;
var pos = curWwwPath.indexOf(pathName);
var localhostPaht = curWwwPath.substring(0, pos);
var projectName = pathName.substring(0, pathName.substr(1).indexOf('/') + 1);
return (localhostPaht + projectName);
}
注：这里没有使用Ajax是因为Ajax异步请求没有办法返回Zip数据（renderFile返回的zip包）
Controller层代码
public class ExportController extends Controller {
HideService hideService=new HideService();
ExportService exportService=new ExportService();
UserService userService=new UserService();
private static Logger logger= LogManager.getLogger(UserController.class);
public void index(){
render("exportController");
}
public void exportData(){
String rows = getPara("rows");
String url = getPara("url");
List<ExcelExportEntity> entities = new ArrayList<>();
String[] showList = hideService.getShowList(url);
exportService.setEntities(rows, entities,showList);
ExportParams params = new ExportParams("用户表","sheetName", ExcelType.XSSF);
params.setDictHandler(new OrgHandler());
//创建文件夹
File dir = new File("E:\\excel\\");
if(!dir.exists()){
dir.mkdirs();
}
Workbook workbook=null;
// 如果选中的有数据就直接打印
if(rows.length()>2){
//数据转换
List<UserVo> list = JSONObject.parseArray(rows, UserVo.class);
logger.debug(JSON.toJSONString(list));
//集合分割
List<List<UserVo>> lists = SeparateList.partition(list, 5);
// 导出
workbook=exportService.writeToExcel(entities, params, lists,url);
}else {
List<UserVo> realList=new ArrayList<>();
int page = Integer.parseInt(getPara("pageNum", "1"));
int pageSize = Integer.parseInt(getPara("pageSize", "8"));
String name = getPara("name");
String pwd = getPara("pwd");
String sort = getPara("sort");
String order = getPara("order");
int orgId = Integer.parseInt(getPara("orgId", "1"));
userService.getList(realList, page, pageSize, name, pwd, sort, order, orgId);
List<List<UserVo>> lists = SeparateList.partition(realList, 5);
workbook=exportService.writeToExcel(entities, params, lists,url);
}
try {
assert workbook != null;
workbook.close();
String newZipPath="E:\\"+url+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".zip";
Compress.compress("E:\\excel\\",newZipPath);
Compress.deleteFile(new File("E:\\excel\\"));
logger.debug(newZipPath);
renderFile(new File(newZipPath));
} catch (IOException e) {
e.printStackTrace();
}
}
}
获取显示列目代码：
public String[] getShowList(String url){
List<Record> list = Db.find("select field from hide where checked_col = 1 and view_name = ? order by order_num",url);
String[] columns=new String[list.size()];
for(int i=0;i<list.size();i++){
columns[i]= list.get(i).get("field");
}
logger.debug("转化后的columns"+ Arrays.toString(columns));
return columns;
}
查询的表展示：
构造entities：
public void setEntities(String rows, List<ExcelExportEntity> entities, String[] showList) {
for(String column:showList){
if("name".equals(column)){
entities.add(new ExcelExportEntity("姓名","name"));
}else if("id".equals(column)){
entities.add(new ExcelExportEntity("ID","id"));
}else if("password".equals(column)){
ExcelExportEntity entity=new ExcelExportEntity("密码", "password");
entities.add(entity);
}else if("email".equals(column)){
entities.add(new ExcelExportEntity("邮箱","email"));
}else if("orgId".equals(column)){
ExcelExportEntity entity=new ExcelExportEntity("部门", "orgId");
entity.setDict("orgId");
entities.add(entity);
}else if("age".equals(column)){
entities.add(new ExcelExportEntity("年龄","age"));
}else if("sex".equals(column)){
ExcelExportEntity entity = new ExcelExportEntity("性别", "sex");
entity.setReplace(new String[]{"男_1","女_0"});
entities.add(entity);
}else if("birthday".equals(column)){
ExcelExportEntity entity = new ExcelExportEntity("生日", "birthday");
entity.setFormat("yyyy-MM-dd");
entities.add(entity);
}else if ("addTime".equals(column)){
ExcelExportEntity entity = new ExcelExportEntity("添加时间", "addTime");
entity.setFormat("yyyy-MM-dd");
entities.add(entity);
}
}
}
字典处理（把相应的部门ID转为部门名称）：
public class OrgHandler implements IExcelDictHandler {
OrgService orgService=new OrgService();
@Override
public String toName(String dict, Object obj, String name, Object value) {
if("orgId".equals(dict)){
List<Org> orgList = orgService.getOrgList();
HashMap<Integer, String> orgs = new HashMap<>(orgList.size());
for(Org org:orgList){
orgs.put(org.getInt("id"),org.getStr("name"));
}
return orgs.get((Integer) value);
}
return null;
}
@Override
public String toValue(String s, Object o, String s1, Object o1) {
return null;
}
}
集合分割函数：
public static <T> List<List<T>> partition(final List<T> list, final int size) {
Integer limit = (list.size() + size - 1) / size;
List<List<T>> mglist = new ArrayList<List<T>>();
Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
mglist.add(list.stream().skip(i * size).limit(size).collect(Collectors.toList()));
});
return mglist;
}
循环输出到Excel：
public Workbook writeToExcel(List<ExcelExportEntity> entities, ExportParams params, List lists,String url) {
Workbook workbook=null;
int i=0;
for(Object list1:lists){
i+=1;
workbook = ExcelExportUtil.exportExcel(params, entities, (List)list1);
try {
String path="E:\\excel\\"+url+new SimpleDateFormat("yyyyMMdd").format(new Date())+"_"+i+".xlsx";
logger.debug(path);
FileOutputStream os = new FileOutputStream(new File(path));
workbook.write(os);
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return workbook;
}
导出所有数据中查询数据的代码（查询父级部门需要显示子级部门就需要查询父级部门下子级部门的ID，最后搜索在ID数组中的数据）：
设置UserVo的目的就在于JFinal的集成Model的实体类直接操作数据库，相当于一个数据库对象，在工作中不能把数据库的字段直接暴露出来，需要一个提供给前端展现的实体类，UserVo应运而生。所以在搜索到数据之后也需要对数据进行转储操作。
public int getList(List<UserVo> realList, int page, int rows, String name, String pwd, String sort, String order, int orgId) {
Page<User> userPage = null;
if(orgId==1){
userPage = paginate(page,rows,name,pwd,sort,order,new ArrayList<Integer>(),"*");
}else{
ArrayList<Integer> orgIds = new ArrayList<>();
orgIds.add(orgId);
orgIds.addAll(getChild(orgId));
userPage = paginate(page,rows,name,pwd,sort,order,orgIds,"*");
}
List<User> userList = userPage.getList();
logger.debug("获得的UserList"+userPage.getList());
logger.debug("一共有多少条数据"+userPage.getTotalRow());
for(User user:userList){
realList.add(new UserVo(
user.getInt("id"),
user.getStr("name"),
user.getStr("pwd"),
user.getStr("email"),
user.getInt("age"),
user.getInt("sex")==null?1:user.getInt("sex"),
user.getDate("birthday"),
user.getStr("remark"),
user.getInt("org_id"),
user.getDate("addtime")==null?null:user.getDate("addtime")
));
}
return userPage.getTotalRow();
}
获得所有子节点的ID：
public List<Integer> getChild(int orgId){
List<Org> orgs = Org.DAO.find("select * from org where pid = ?", orgId);
ArrayList<Integer> children = new ArrayList<>();
if(orgs.size()>0){
for(Org org:orgs){
children.add(org.getInt("id"));
children.addAll(getChild(org.getInt("id")));
}
}
return children;
}
UserVo属性：
private int id;
private String name;
private String password;
private String email;
private int age;
private int sex;
private Date birthday;
private String remark;
private int orgId;
private Date addTime;
项目运行需要的依赖：
maven：easypoi-annotation,easypoi-base
在网站根据名字搜索可以搜索到meaven仓库里的相关所有jar包，导入即可Jar包搜索，我选择的是4.2.0版本的。
相关阅读：
