java excel 相同数据列cell（单元格合并）

72 阅读 0 评论 48 点赞

我是靠谱客的博主生动水壶，最近开发中收集的这篇文章主要介绍java excel 相同数据列cell（单元格合并），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

package com.jeeplus.fileConfig;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

public class ExcelUtils {

    public static void main(String[] args) throws IOException {
        //构建测试数据
        List<Map<String, String>> searchDataList = new ArrayList<>();
        Map<String, String> myMap = new HashMap<>();
        myMap.put("displayCode", "1001001");
        myMap.put("practName", "砂石");
        myMap.put("areaName", "湖南");
        myMap.put("price", "19");
        searchDataList.add(myMap);
        myMap = new HashMap<>();
        myMap.put("displayCode", "1001001");
        myMap.put("practName", "砂石");
        myMap.put("areaName", "河北");
        myMap.put("price", "20");
        searchDataList.add(myMap);
        myMap = new HashMap<>();
        myMap.put("displayCode", "1001002");
        myMap.put("practName", "水泥");
        myMap.put("areaName", "江苏");
        myMap.put("price", "21");
        searchDataList.add(myMap);
        myMap = new HashMap<>();
        myMap.put("displayCode", "1001003");
        myMap.put("practName", "混凝土");
        myMap.put("areaName", "北京");
        myMap.put("price", "22");
        searchDataList.add(myMap);
        myMap = new HashMap<>();
        myMap.put("displayCode", "1001004");
        myMap.put("practName", "砂砾");
        myMap.put("areaName", "四川");
        myMap.put("price", "23");
        searchDataList.add(myMap);
        myMap = new HashMap<>();
        myMap.put("displayCode", "1001004");
        myMap.put("practName", "砂砾");
        myMap.put("areaName", "内蒙古");
        myMap.put("price", "24");
        searchDataList.add(myMap);
        SXSSFWorkbook wb=new SXSSFWorkbook(500);

        //标题格式
        XSSFCellStyle ztStyle0 = (XSSFCellStyle) wb.createCellStyle();

        // 创建单元格样式对象
        XSSFCellStyle ztStyle = (XSSFCellStyle) wb.createCellStyle();
        Font ztFont = wb.createFont();
        ztFont.setFontHeightInPoints((short)9);    // 将字体大小设置为18px
        ztFont.setFontName("宋体");
        ztStyle.setFont(ztFont);                    // 将字体应用到样式上面

        // 设置单元格内容水平对其方式
        ztStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容垂直对其方式
        ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        ztStyle.setWrapText(true);                     // 设置单元格内容是否自动换行

        // 创建单元格样式对象
        XSSFCellStyle ztStyle2 = (XSSFCellStyle) wb.createCellStyle();
        ztStyle2.setFont(ztFont);                    // 将字体应用到样式上面

        // 设置单元格内容水平对其方式
        ztStyle2.setAlignment(HorizontalAlignment.LEFT);
        // 设置单元格内容垂直对其方式
        ztStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        ztStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        ztStyle2.setWrapText(true); // 设置单元格内容是否自动换行

        Sheet sheet=wb.createSheet("合并测试");
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(0, CellType.STRING);
        cell.setCellValue("代号");
        sheet.setColumnWidth(0, 3000);
        cell.setCellStyle(ztStyle);
        cell = row.createCell(1, CellType.STRING);
        cell.setCellValue("名称");
        sheet.setColumnWidth(1, 5000);
        cell.setCellStyle(ztStyle);
        cell = row.createCell(2, CellType.STRING);
        cell.setCellValue("所在地");
        sheet.setColumnWidth(2, 10000);
        cell.setCellStyle(ztStyle);
        cell = row.createCell(3, CellType.STRING);
        cell.setCellValue("单价");
        sheet.setColumnWidth(3, 3000);
        cell.setCellStyle(ztStyle);

        if(searchDataList != null && searchDataList.size() > 0 )  {
            int n = 1 ;
            Map<Map<String, String>,Row> rowMap = new LinkedHashMap<>();
            //{0=代号, 1=材料名称, 2=所在地, 4=单价}
            Map<String, String> map = null;
            //key为displayCode,value为displayCode相同的个数有多少个
            Map<String,Integer> sameCountMap = new HashMap<>();
            //key为displayCode,value为第一个代号为该displayCode的初始的行的索引号
            Map<String,Integer> startDisplayCodeMap = new HashMap<>();
            Integer count = null;
            String prevDisplayCode = null;
            String displayCode = null;
            Integer startRowIndex = null;
            for(int i = 0 ; i < searchDataList.size() ; i ++ ) {

                map = searchDataList.get(i);
                displayCode = map.get("displayCode");
                map = searchDataList.get(i);
                //代号
                row=sheet.createRow(n);
                cell = row.createCell(0, CellType.STRING);
                cell.setCellValue(displayCode);
                cell.setCellStyle(ztStyle);
                //材料名称
                cell = row.createCell(1, CellType.STRING);
                cell.setCellValue(map.get("practName"));
                cell.setCellStyle(ztStyle2);
                //所在地
                cell = row.createCell(2, CellType.STRING);
                cell.setCellValue(map.get("areaName"));
                cell.setCellStyle(ztStyle2);
                //单价
                cell = row.createCell(3, CellType.STRING);
                cell.setCellValue(map.get("price"));
                cell.setCellStyle(ztStyle);
                rowMap.put(map, row);

                //合并行
                count = sameCountMap.get(displayCode);
                if(count == null) {
                    count = 0;
                    startDisplayCodeMap.put(displayCode, n);
                }
                count++;
                sameCountMap.put(displayCode, count);
                if(prevDisplayCode != null && !prevDisplayCode.equals(displayCode)) {
                    startRowIndex = startDisplayCodeMap.get(prevDisplayCode);//获取该代号的初始行位置
                    count = sameCountMap.get(prevDisplayCode);//获取相同代号总共有多少行
                    if(count > 1) {
                        sheet.addMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex+count-1, 0, 0));//合并代号行
                        sheet.addMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex+count-1, 1, 1));//合并工料机名称行
                    }
                }else if(i == searchDataList.size() - 1){
                    //最后一行
                    startRowIndex = startDisplayCodeMap.get(displayCode);//获取该代号的初始行位置
                    count = sameCountMap.get(displayCode);//获取相同代号总共有多少行
                    if(count > 1) {
                        sheet.addMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex+count-1, 0, 0));//合并代号行
                        sheet.addMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex+count-1, 1, 1));//合并名称行
                    }
                }
                prevDisplayCode = displayCode;
                n++;
            }
        }
        File file = new File("E:\demo.xlsx");
        FileOutputStream fout = new FileOutputStream(file);
        wb.write(fout);
        fout.close();
    }

}