多种情况合并单元格的方法讲解
程序员文章站
2022-03-07 21:56:01
...
直接上代码,原理之前的随笔已经讲过了。
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!
以上就是多种情况合并单元格的方法讲解的详细内容,更多请关注其它相关文章!