POI 合并单元格

原始结果：

 

 

期望结果：

 

关键代码通用类如下：

 

SchoolController 部分代码：

    @RequestMapping(value="/exportExcel")
    public String exportExcel(HttpServletResponse response) {
        response.setContentType("application/binary;charset=ISO8859_1");
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            String fileName = new String(("导出excel例子").getBytes(), "ISO8859_1");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式
            String[] titles = { "省份", "市", "人名" , "学校名"};
            schoolService.exportExcel(titles, outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }	

 

 

 

SchoolServiceImpl部分代码：

    @Override
    public void exportExcel(String[] titles, ServletOutputStream outputStream) {
        
        School school = new School();
//      该类就不贴出代码了，建议自己按照图示，拼装学校集合
        List<School> schools = schoolDao.getSchoolList(school);
        
        // 创建一个workbook 对应一个excel应用文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workBook.createSheet("导出excel例子");
        // 自适应列宽度
        sheet.autoSizeColumn(1, true);
        ExportUtil exportUtil = new ExportUtil(workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        
        // 构建表头
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;
        for (int i = 0; i < titles.length; i++) {
            cell = headRow.createCell(i);
            cell.setCellStyle(headStyle);
            cell.setCellValue(titles[i]);
        }
        // 构建表体数据
        if (schools != null && schools.size() > 0) {
            for (int j = 0; j < schools.size(); j++) {
                XSSFRow bodyRow = sheet.createRow(j + 1);
                School schoolInner = schools.get(j);

                cell = bodyRow.createCell(0);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(schoolInner.getProvinces());

                cell = bodyRow.createCell(1);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(schoolInner.getCity());
                
                cell = bodyRow.createCell(2);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(schoolInner.getLinker());
                
                cell = bodyRow.createCell(3);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(schoolInner.getSchoolName());
                
            }
//                /* 
//                 * 设定合并单元格区域范围 
//                 *  firstRow  0-based 
//                 *  lastRow   0-based 
//                 *  firstCol  0-based 
//                 *  lastCol   0-based 
//                 */ 
//            sheet.addMergedRegion(new CellRangeAddress(2, 7, 0, 0));
//            sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
//            sheet.addMergedRegion(new CellRangeAddress(4, 7, 1, 1));

            
            int lastRowNum = sheet.getLastRowNum();
            exportUtil.mergerData(sheet, 0, 1 , lastRowNum);
            Map<String,String> map = exportUtil.mergerData(sheet, 1, 1 , lastRowNum);
//            System.out.println("map: "+JSON.toJSONString(map));
            for(String value : map.values()){
                String [] strArr = value.split(",");
                int firstRow = Integer.parseInt(strArr[0])-1;
                int lastRow = Integer.parseInt(strArr[1])-1;
                exportUtil.mergerData(sheet, 2, firstRow, lastRow);
            }
            
            
            
        }
        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

 

 

ExportUtil：代码

package com.bugyun.util;

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
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;

public class ExportUtil {
    private XSSFWorkbook wb = null;

    private XSSFSheet sheet = null;

    /**
     * @param wb
     * @param sheet
     */
    public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet) {
        this.wb = wb;
        this.sheet = sheet;
    }

    /**
     * 合并单元格后给合并后的单元格加边框
     * 
     * @param region
     * @param cs
     */
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

        int toprowNum = region.getFirstRow();
        for (int i = toprowNum; i <= region.getLastRow(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
                                               // (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 设置表头的单元格样式
     * 
     * @return
     */
    public XSSFCellStyle getHeadStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格的背景颜色为淡蓝色
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

    /**
     * 设置表体的单元格样式
     * 
     * @return
     */
    public XSSFCellStyle getBodyStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
//        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
    
    /**
     * @param sheet
     * @param colIndex       合并的第几列  （下标从0开始）
     * @param startRowIndex  从第几行开始合并（算上标题，从0开始算）
     * @param endRowIndex    从第几行结束合并
     */
    public Map<String,String> mergerData(XSSFSheet sheet,int colIndex, int startRowIndex , int endRowIndex) {
        Map<String,String> map = new HashMap<>();
        
        breakFor:
        for (int i = startRowIndex; i <= endRowIndex; i++) {
            Cell cell = sheet.getRow(i).getCell(colIndex);

            for (int j = i + 1; j <= endRowIndex; j++) {
                Cell celltemp = sheet.getRow(j).getCell(colIndex);

                // 如果下一行与被比较行相等，则继续该循环，直到不等才跳出
                if (!celltemp.getStringCellValue().equals(cell.getStringCellValue())) {
                    int temp = j-1;
                    if (temp > i) {
                        // 合并单元格
                        map.put(cell.getStringCellValue(), i+1+","+j);
                        sheet.addMergedRegion(new CellRangeAddress(i, temp, colIndex, colIndex));
                        
                    }
                    i = temp;
                    break;
                }
                if (j == endRowIndex) {
                    map.put(cell.getStringCellValue(), i+1+","+j);
                    sheet.addMergedRegion(new CellRangeAddress(i, endRowIndex, colIndex, colIndex));
                    break breakFor;
                }
            }
        }
        return map ;
    }
}

 

pom.xml 引入包：

 

        <!-- poi 导出Excel包 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>

 

School类字段 ，参考代码就不一一贴出来了；

 

参考文章：

http://bbs.csdn.net/topics/390420021

http://blog.csdn.net/hehexiaoyou/article/details/37873131