多種情況合併儲存格的方法講解

直接上程式碼，原理之前的隨筆已經講過了。

1.先看看效果

 

# 

2.html程式碼，含js程式碼

cpm system

<div>
    <a>导出</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;
    }
}</t></t></t></t>

 

# 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>> 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> 1 || data.getRowSpan() > 1) {
                    CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex + data.getRowSpan() - 1, col, col + data.getColSpan() - 1);
                    sheet.addMergedRegion(cra);
                }
            }
        }return workbook;
    }
}</titles.length></list>

 

 

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> 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> countPropertyCount(List<member> rows){

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




}</exceldata></list></member></string></member></string></member></member></string></string></member></member></member></member></member></member></member></member>

#匯出excel功能使用poi類別庫實作。至此，頁面展示與匯出均OK！

 

以上是多種情況合併儲存格的方法講解的詳細內容。更多資訊請關注PHP中文網其他相關文章！