An explanation of how to merge cells in various situations

Go directly to the code. The principle has been explained in the previous essay.

1. Take a look at the effect first

2.html code, including js code

cpm system

<div>
    <a href="/home/export">导出</a>
</div>

地区公司部门员工姓名

3.Backend code

3.1 Paging parameter object

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 Data return object

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 Data entity object

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 Export Related classes

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 layer

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();
        }
    }




}

The export excel function is implemented using the poi class library. At this point, page display and export are OK!

The above is the detailed content of An explanation of how to merge cells in various situations. For more information, please follow other related articles on the PHP Chinese website!