直接上代码，原理之前的随笔已经讲过了。
1.先看看效果
2.html代码，含js代码
cpm system
<div>
<a href="/home/export">导出</a>
</div>
地区公司部门员工姓名
3.后台代码
3.1 分页参数对象
package com.xincheng.cpm.common;/**
* created by hdwang on 2017/6/22.
* 分页参数 */public class pageparam {/** * 第几次绘画（前端标识） */private int draw;/** * 起始记录(从0开始)，mysql也是从0开始，吻合，good！ */private int start;/** * 页大小 */private int length;public int getdraw() {return draw;
}public void setdraw(int draw) {this.draw = draw;
}public int getstart() {return start;
}public void setstart(int start) {this.start = start;
}public int getlength() {return length;
}public void setlength(int length) {this.length = length;
}/** * 第几页(0-n) */public int getpage(){return this.start/this.length;
}
}
3.2 数据返回对象
package com.xincheng.cpm.common;import java.util.list;/**
* created by hdwang on 2017/6/22.
* 表格数据（datatables） */public class tabledata<t> {/** * 第几次绘画（前端标识） */private int draw;/** * 行过滤（不知道干嘛的） */private int recordsfiltered;/** * 总行数 */private int recordstotal;/** * 行数据 */private list<t> data;/** * 起始记录(用于前端初始化序列号用的) */private int start;/** * 错误信息 */private string error;public int getdraw() {return draw;
}public void setdraw(int draw) {this.draw = draw;
}public int getrecordsfiltered() {return recordsfiltered;
}public void setrecordsfiltered(int recordsfiltered) {this.recordsfiltered = recordsfiltered;
}public int getrecordstotal() {return recordstotal;
}public void setrecordstotal(int recordstotal) {this.recordstotal = recordstotal;
}public list<t> getdata() {return data;
}public void setdata(list<t> data) {this.data = data;
}public int getstart() {return start;
}public void setstart(int start) {this.start = start;
}public string geterror() {return error;
}public void seterror(string error) {this.error = error;
}
}
3.3 数据实体对象
package com.xincheng.cpm.common;import java.io.serializable;/**
* created by hdwang on 2017/7/14. */public class member{private string area;private string company;private string department;private string username;private integer arearowspan;private integer companyrowspan;private integer departmentrowspan;public member(string area,string company,string department,string username){this.area = area;this.company = company;this.department = department;this.username = username;
}public string getarea() {return area;
}public void setarea(string area) {this.area = area;
}public string getcompany() {return company;
}public void setcompany(string company) {this.company = company;
}public string getdepartment() {return department;
}public void setdepartment(string department) {this.department = department;
}public string getusername() {return username;
}public void setusername(string username) {this.username = username;
}public integer getarearowspan() {return arearowspan;
}public void setarearowspan(integer arearowspan) {this.arearowspan = arearowspan;
}public integer getcompanyrowspan() {return companyrowspan;
}public void setcompanyrowspan(integer companyrowspan) {this.companyrowspan = companyrowspan;
}public integer getdepartmentrowspan() {return departmentrowspan;
}public void setdepartmentrowspan(integer departmentrowspan) {this.departmentrowspan = departmentrowspan;
}
}
3.4 导出相关类
package com.xincheng.cpm.common;/**
* created by hdwang on 2017/7/14. */public class exceldata {private string value;//单元格的值private int colspan = 1;//单元格跨几列private int rowspan = 1;//单元格跨几行private boolean aligncenter;//单元格是否居中，默认不居中，如果选择是，则水平和上下都居中public boolean isaligncenter() {return aligncenter;
}public void setaligncenter(boolean aligncenter) {this.aligncenter = aligncenter;
}public string getvalue() {return value;
}public void setvalue(string value) {this.value = value;
}public int getcolspan() {return colspan;
}public void setcolspan(int colspan) {this.colspan = colspan;
}public int getrowspan() {return rowspan;
}public void setrowspan(int rowspan) {this.rowspan = rowspan;
}
}
package com.xincheng.cpm.common;import org.apache.poi.ss.usermodel.cell;import org.apache.poi.ss.usermodel.cellstyle;import org.apache.poi.ss.util.cellrangeaddress;import org.apache.poi.xssf.usermodel.xssffont;import org.apache.poi.xssf.usermodel.xssfrow;import org.apache.poi.xssf.usermodel.xssfsheet;import org.apache.poi.xssf.usermodel.xssfworkbook;import java.util.list;/**
* created by hdwang on 2017/7/14. */public class excelutil {/** * 生成excel工作簿
* @param sheettitle sheet名称
* @param titles 标题
* @param rows 行数据
* @return 工作簿 */public xssfworkbook execute(string sheettitle,string[] titles,list<list<exceldata>> rows) {//定义工作簿xssfworkbook workbook = new xssfworkbook();//th样式cellstyle titlestyle = workbook.createcellstyle();
titlestyle.setborderbottom((short) 1);
titlestyle.setborderright((short)1);
titlestyle.setborderleft((short)1);
titlestyle.setbordertop((short)1);
titlestyle.setverticalalignment((short)1);
titlestyle.setalignment((short)2);
xssffont font = workbook.createfont();
font.setbold(true);
titlestyle.setfont(font);//td样式cellstyle style = workbook.createcellstyle();
style.setborderbottom((short)1);
style.setborderright((short)1);
style.setborderleft((short)1);
style.setbordertop((short)1);
style.setverticalalignment((short)1);//创建工作表xssfsheet sheet = workbook.createsheet(sheettitle);
sheet.setdefaultrowheightinpoints(20.0f);//创建标题行xssfrow titlerow = sheet.createrow(0);for(int col=0;col<titles.length;col++) { //遍历列cell cell = titlerow.createcell(col);
cell.setcellstyle(titlestyle);
cell.setcellvalue(titles[col]);for(int row=0;row<rows.size();row++){ //遍历行int rowindex = row+1;
xssfrow contentrow = sheet.getrow(rowindex);if(contentrow == null){
contentrow = sheet.createrow(rowindex);
}
exceldata data = rows.get(row).get(col);
cell contentrowcell = contentrow.createcell(col);
contentrowcell.setcellstyle(style);
contentrowcell.setcellvalue(data.getvalue());//合并单元格if (data.getcolspan() > 1 || data.getrowspan() > 1) {
cellrangeaddress cra = new cellrangeaddress(rowindex, rowindex + data.getrowspan() - 1, col, col + data.getcolspan() - 1);
sheet.addmergedregion(cra);
}
}
}return workbook;
}
}
3.5 controller层
package com.xincheng.cpm.controller;import com.chenrd.common.excel.exportexcel;import com.xincheng.cpm.common.*;import com.xincheng.cpm.entity.cpm.user;import com.xincheng.cpm.service.userservice;import com.xincheng.cpm.vo.incomedailyvo;import org.apache.commons.lang3.stringutils;import org.apache.poi.xssf.usermodel.xssfworkbook;import org.springframework.beans.factory.annotation.autowired;import org.springframework.data.domain.page;import org.springframework.data.domain.pageimpl;import org.springframework.data.domain.pagerequest;import org.springframework.data.domain.pageable;import org.springframework.stereotype.controller;import org.springframework.ui.modelmap;import org.springframework.web.bind.annotation.requestmapping;import org.springframework.web.bind.annotation.requestmethod;import org.springframework.web.bind.annotation.responsebody;import javax.servlet.http.httpservletrequest;import javax.servlet.http.httpservletresponse;import javax.servlet.http.httpsession;import java.io.ioexception;import java.io.outputstream;import java.net.urlencoder;import java.util.*;/**
* created by hdwang on 2017/6/19. */@controller
@requestmapping(/home)public class homecontroller {
@autowired
userservice userservice;
@requestmapping()public string index(httpsession session, modelmap map, httpservletrequest request){
user user = (user) session.getattribute(user);
map.put(user,user);return home;
}
@requestmapping(value=/query,method= requestmethod.post)
@responsebodypublic tabledata<member> getuserbypage(pageparam pageparam, user user){
page<member> userpage = this.getmembers(pageparam);
tabledata<member> datas = new tabledata<>();
datas.setdraw(pageparam.getdraw());
datas.setstart(pageparam.getstart());
datas.setdata(userpage.getcontent());
datas.setrecordsfiltered((int)userpage.gettotalelements());
datas.setrecordstotal((int)userpage.gettotalelements());return datas;
}private page<member> getmembers(pageparam pageparam) {//1.模拟数据库查询pageable pageable = new pagerequest(pageparam.getpage(), pageparam.getlength());long count = 6;
list<member> members = getmembersfromdb();//2.计算rowspanthis.countrowspan(members);
page<member> memberpage = new pageimpl<member>(members,pageable,count);return memberpage;
}private void countrowspan(list<member> members) {
map<string,integer> propertycountmap = this.countpropertycount(members);
list<string> hadgetkeys = new arraylist<>(); //曾经取过的keyfor(member member:members){
string areakey = member.getarea();
string companykey = areakey+member.getcompany();
string departmentkey = companykey+ member.getdepartment();
integer areacount = propertycountmap.get(areakey);if(areacount == null){
member.setarearowspan(1);
}else{if(hadgetkeys.contains(areakey)){
member.setarearowspan(0); //曾经取过}else{
member.setarearowspan(areacount); //第一次取 hadgetkeys.add(areakey);
}
}
integer companycount = propertycountmap.get(companykey);if(companycount == null){
member.setcompanyrowspan(1);
}else {if(hadgetkeys.contains(companykey)){
member.setcompanyrowspan(0);
}else{
member.setcompanyrowspan(companycount);
hadgetkeys.add(companykey);
}
}
integer departmentcount = propertycountmap.get(departmentkey);if(companycount == null){
member.setdepartmentrowspan(1);
}else {if(hadgetkeys.contains(departmentkey)){
member.setdepartmentrowspan(0);
}else{
member.setdepartmentrowspan(departmentcount);
hadgetkeys.add(departmentkey);
}
}
}
}private list<member> getmembersfromdb() {
member member1 = new member(安徽,a,人力资源部, 小红);
member member2 = new member(安徽,b,人力资源部, 小明);
member member3 = new member(浙江,c,人力资源部, 小君);
member member4 = new member(浙江,c,技术部, 小王);
member member5 = new member(浙江,d,技术部, 小李);
member member6 = new member(浙江,d,人力资源部, 小刚);
list<member> members = new arraylist<>();
members.add(member1);
members.add(member2);
members.add(member3);
members.add(member4);
members.add(member5);
members.add(member6);return members;
}/** * 统计每个字段的每组成员个数
* @param rows 记录
* @return 每个字段的每组成员个数 */private map<string,integer> countpropertycount(list<member> rows){
map<string,integer> propertycountmap = new hashmap<>();for(member member:rows){// area: 无父级分组string area = member.getarea();if(propertycountmap.get(area) == null){
propertycountmap.put(area,1);
}else{int count = propertycountmap.get(area);
propertycountmap.put(area,count+1);
}// company:有area父组string company = member.getcompany();
string uniqueparent = member.getarea();
string key = uniqueparent + company;if(propertycountmap.get(key) == null){
propertycountmap.put(key,1);
}else{int count = propertycountmap.get(key);
propertycountmap.put(key,count+1);
}// department: 有area,company这两个父组string department = member.getdepartment();
uniqueparent = member.getarea()+member.getcompany();
key = uniqueparent + department;if(propertycountmap.get(key) == null){
propertycountmap.put(key,1);
}else{int count = propertycountmap.get(key);
propertycountmap.put(key,count+1);
}
}return propertycountmap;
}
@requestmapping(/export)public void export(httpservletresponse response) throws ioexception {
list<member> members = this.getmembersfromdb();this.countrowspan(members);
list<list<exceldata>> rows = new arraylist<>();for(member member:members){
list<exceldata> row = new arraylist<>();
exceldata col1 = new exceldata();
col1.setvalue(member.getarea());
col1.setrowspan(member.getarearowspan());
row.add(col1);
exceldata col2 = new exceldata();
col2.setvalue(member.getcompany());
col2.setrowspan(member.getcompanyrowspan());
row.add(col2);
exceldata col3 = new exceldata();
col3.setvalue(member.getdepartment());
col3.setrowspan(member.getdepartmentrowspan());
row.add(col3);
exceldata col4 = new exceldata();
col4.setvalue(member.getusername());
row.add(col4);
rows.add(row);
}
outputstream outputstream = response.getoutputstream();try {
string filename = urlencoder.encode(员工 + .xlsx, utf-8);
response.setcontenttype(application/vnd.ms-excel);
response.addheader(content-disposition, octet-stream;filename= + filename);
excelutil excelutil = new excelutil();
xssfworkbook workbook = excelutil.execute(sheet1,new string[]{地区,公司,部门,员工姓名},rows);
workbook.write(outputstream);
} finally {if (outputstream != null) outputstream.close();
}
}
}
导出excel功能使用poi类库实现。至此，页面展示和导出均ok！
以上就是多种情况合并单元格的方法讲解的详细内容。