最新POI操作Excel 合并单元格（根据对象中的code,就是标识，来合并列）

最新推荐文章于 2024-07-16 15:48:46 发布

myworldsxw

最新推荐文章于 2024-07-16 15:48:46 发布

阅读量1.1k

点赞数

分类专栏： java poi

本文链接：https://blog.csdn.net/u012903135/article/details/84943503

版权

java 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

poi

1 篇文章 0 订阅

订阅专栏

直接贴代码了代码里面都有注解，超级简单。比网上搜索的简单明了。

poi 的相关 jar包 csdn 上有很多，可以自行下载！！！！

导出的最后接口如上图格式一样，根据订单号，来合并后面指定的列（mergeIndex）

package com.cn.export.xls;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.*;

    public class SwDemo {



        public static void main(String[] args) throws IllegalAccessException {

            List<Order> orders = new ArrayList<>();
            orders.add(new Order("N001", "sw1", "2018-01-01", "2018-01-02", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N001", "sw1", "2018-01-02", "2018-01-03", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N001", "sw1", "2018-01-03", "2018-01-04", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N002", "sw2", "2018-10-01", "2018-10-02", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N002", "sw2", "2018-10-02", "2018-10-03", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N003", "sw3", "2018-01-05", "2018-01-06", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N003", "sw3", "2018-01-06", "2018-01-07", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N003", "sw3", "2018-01-07", "2018-01-08", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N003", "sw3", "2018-01-09", "2018-01-10", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N004", "sw4", "2018-06-01", "2018-06-02", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N004", "sw4", "2018-06-02", "2018-06-03", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N004", "sw4", "2018-06-03", "2018-06-04", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N005", "sw5", "2018-07-11", "2018-07-12", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N005", "sw5", "2018-07-12", "2018-07-13", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N005", "sw5", "2018-07-13", "2018-07-14", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N006", "sw6", "2018-10-11", "2018-10-12", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N006", "sw6", "2018-10-12", "2018-10-13", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N007", "sw7", "2018-11-10", "2018-11-11", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N008", "sw8", "2018-12-11", "2018-12-12", "1", "3", "10", "30", "admin", "公司1", "游玩1"));
            orders.add(new Order("N008", "sw8", "2018-12-12", "2018-12-13", "1", "3", "10", "30", "admin", "公司1", "游玩1"));

            String headName = "交易明细";
            String fullName = "交易汇总";
            String[] headTitle = {"编码", "预定人姓名", "产品名称", "开始时间", "结束时间", "成本单价",
                    "成本总价", "代理单价", "代理总价", "操作人", "公司名称"};
            String[] fullTitle = {"成本总价", "代理总价", "退款金额", "总条数"};
            // 设置需要合并的列 （索引）
            int[] megerIndexs = new int[]{0, 1, 2, 6, 8, 9, 10};
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("new sheet");

            // settings font style =>  设置 字体样式
            Font titleFont = wb.createFont();
            titleFont.setBold(true);
            // align center => 居中样式
            CellStyle alignCentterCellStyle = wb.createCellStyle();
            // HorizontalAlignment.CENTER 已经过期， 请用 CellStyle.ALIGN_CENTER 代替 水平
            // 居中
            alignCentterCellStyle.setAlignment(HorizontalAlignment.CENTER);
            // VerticalAlignment.CENTER 已过期 请用 CellStyle.VERTICAL_CENTER 代替 垂直居中
            alignCentterCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            alignCentterCellStyle.setFont(titleFont);

            CellStyle contentCellStyle = wb.createCellStyle();
            contentCellStyle.setAlignment(HorizontalAlignment.CENTER);
            contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // create headName firstRow => 交易明细
            Row headRow = sheet.createRow(0);
            // create headName firstCell => 交易汇总
            Cell headCell = headRow.createCell(0);
            headCell.setCellValue(headName);
            // create first Cell merge => full line
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 20));

            // create headTitle second Row => 头部标题
            Row headTitleRow = sheet.createRow(1);
            for (int i = 0; i < headTitle.length; i++) {
                Cell headTitleCell = headTitleRow.createCell(i);
                setCellValueAndStyles(headTitleCell, headTitle[i], alignCentterCellStyle);
            }

            // set content start Row index => 设置加入内容的开始行
            int index = 2;
            Map<String, PoiRecordModel> poiRecordModelMap = new HashMap<>();
            for (int i = 0; i < orders.size(); i++) {
                Row contentRow = sheet.createRow(index);
                Map<String, String> map = objectToMap(orders.get(i));
                for (int j = 0; j < headTitle.length; j++) {
                    if (index == 2) {
                        // 记录第一行 合并开始的行 index
                        PoiRecordModel poiRecordModel = new PoiRecordModel();
                        poiRecordModel.setRowIndex(index);
                        poiRecordModelMap.put(orders.get(i).getCode(), poiRecordModel);
                    }
                    for (int k = 0; k < megerIndexs.length; k++) {
                        if (i > 0 && j == megerIndexs[k]) {
                            if (!orders.get(i).getCode().equals(orders.get(i - 1).getCode())) {
                                PoiRecordModel poiRecordModel = poiRecordModelMap.get(orders.get(i - 1).getCode());
                                if(poiRecordModel.getRowIndex()!=(index-1)) {
                                    sheet.addMergedRegion(new CellRangeAddress(poiRecordModel.getRowIndex(), index - 1, j, j));
                                }
                            }
                        }
                        // 设置最后一行 合并 判断如果 最后一行等于上一行的code 就设置 合并，否则不合并
                        if((i+1) == orders.size() && j == megerIndexs[k] && orders.get(i).getCode().equals(orders.get(i - 1).getCode())){
                            PoiRecordModel poiRecordModel = poiRecordModelMap.get(orders.get(i - 1).getCode());
                            if(poiRecordModel.getRowIndex()!=(index)) {
                                sheet.addMergedRegion(new CellRangeAddress(poiRecordModel.getRowIndex(), index, j, j));
                            }
                        }
                    }
                    Cell contentCell = contentRow.createCell(j);
                    contentCell.setCellValue(map.get(headTitle[j]));
                    contentCell.setCellStyle(contentCellStyle);
                }
                // 这里的意思是，循环一行中的所有列之后，
                // 如果当前行的code 不等于 上一行的code 那么就记录下 当前行的index
                // 用来下次循环判断，合并的开始行
                if(i>0 && !orders.get(i).getCode().equals(orders.get(i - 1).getCode())){
                    PoiRecordModel poiRecordModel1 = new PoiRecordModel();
                    poiRecordModel1.setRowIndex(index);
                    poiRecordModelMap.put(orders.get(i).getCode(), poiRecordModel1);
                }
                index++;
            }


            // set fullHead => 设置交易汇总
            int fullIndex = index+1;
            Row fullRow = sheet.createRow(fullIndex);
            Cell fullCell = fullRow.createCell(0);
            fullCell.setCellValue(fullName);
            // create first Cell merge => full line 合并
            sheet.addMergedRegion(new CellRangeAddress(fullIndex, fullIndex, 0, 20));
            // set fullHeadTitle => 设置交易汇总标题

            Row fullHeadTitle = sheet.createRow(fullIndex+1);
            for (int i = 0;i<fullTitle.length;i++){
                Cell fullHeadCell = fullHeadTitle.createCell(i);
                setCellValueAndStyles(fullHeadCell, fullTitle[i], alignCentterCellStyle);
            }

            //TODO 这里设置汇总数据，跟上面以上，就不重复了

            // Write the output to a file
            try (OutputStream fileOut = new FileOutputStream("D:\\sw\\workbook.xlsx")) {
                wb.write(fileOut);
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        private static void setCellValueAndStyles(Cell cell ,String title, CellStyle style){
            cell.setCellValue(title);
            cell.setCellStyle(style);
        }

        private static Map<String, String> objectToMap(Order order) {
            Map<String, String> map = new HashMap<>();
            map.put("编码", order.getCode());
            map.put("预定人姓名", order.getName());
            map.put("产品名称", order.getProductName());
            map.put("开始时间", order.getStartDate());
            map.put("结束时间", order.getEndDate());
            map.put("成本单价", order.getCostPrice());
            map.put("成本总价", order.getCostPriceAll());
            map.put("代理单价", order.getAgenPrice());
            map.put("代理总价", order.getAgenPriceAll());
            map.put("操作人", order.getOperUserName());
            map.put("公司名称", order.getCompanyName());
            return map;
        }
    }

package com.cn.export.xls;

public class PoiRecordModel {

    private int rowIndex;

    public int getRowIndex() {
        return rowIndex;
    }

    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }

    @Override
    public String toString() {
        return "PoiRecordModel{" +
                ", rowIndex=" + rowIndex +
                '}';
    }
}

package com.cn.export.xls;

public class Order {

    private String code;
    private String name;
    private String startDate;
    private String endDate;
    private String costPrice;
    private String costPriceAll;
    private String agenPrice;
    private String agenPriceAll;
    private String operUserName;
    private String companyName;
    private String productName;


    public Order() {

    }

    public Order(String code, String name, String startDate, String endDate, String costPrice,
                 String costPriceAll, String agenPrice, String agenPriceAll, String operUserName,
                 String companyName, String productName) {
        this.code = code;
        this.name = name;
        this.startDate = startDate;
        this.endDate = endDate;
        this.costPrice = costPrice;
        this.costPriceAll = costPriceAll;
        this.agenPrice = agenPrice;
        this.agenPriceAll = agenPriceAll;
        this.operUserName = operUserName;
        this.companyName = companyName;
        this.productName = productName;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getStartDate() {
        return startDate;
    }

    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    public String getEndDate() {
        return endDate;
    }

    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    public String getCostPrice() {
        return costPrice;
    }

    public void setCostPrice(String costPrice) {
        this.costPrice = costPrice;
    }

    public String getCostPriceAll() {
        return costPriceAll;
    }

    public void setCostPriceAll(String costPriceAll) {
        this.costPriceAll = costPriceAll;
    }

    public String getAgenPrice() {
        return agenPrice;
    }

    public void setAgenPrice(String agenPrice) {
        this.agenPrice = agenPrice;
    }

    public String getAgenPriceAll() {
        return agenPriceAll;
    }

    public void setAgenPriceAll(String agenPriceAll) {
        this.agenPriceAll = agenPriceAll;
    }

    public String getOperUserName() {
        return operUserName;
    }

    public void setOperUserName(String operUserName) {
        this.operUserName = operUserName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    @Override
    public String toString() {
        return "Order{" +
                "code='" + code + '\'' +
                ", name='" + name + '\'' +
                ", startDate='" + startDate + '\'' +
                ", endDate='" + endDate + '\'' +
                ", costPrice='" + costPrice + '\'' +
                ", costPriceAll='" + costPriceAll + '\'' +
                ", agenPrice='" + agenPrice + '\'' +
                ", agenPriceAll='" + agenPriceAll + '\'' +
                ", operUserName='" + operUserName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", productName='" + productName + '\'' +
                '}';
    }
}