POI 合并單元格

1. 合并單元格概述

合并單元格CellRangeAddress就是将幾個相鄰的單元格合并為一個單元格，即使沒有建立過行和單元格，也 可以建立合并單元格，因為單元格資訊是單獨存儲的

/**
     * Creates new cell range. base 0
     * 
     * @param firstRow Index of first row
     * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
     * @param firstCol Index of first column
     * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
     */
    public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)           

2. 建立合并單元格

建立合并單元格很簡單：

1. 建立一個合并單元格region
 2. 将region添加到工作表Sheet中           

CellRangeAddress region  = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
CellRangeAddress region  = new CellRangeAddress("A1:E10");
sheet.addMergedRegion(region)           

3. 合并單元格内容

合并單元格的内容需要通過設定合并區域左上角單元格的内容确定，設定合并區域内其他單元格是不起效的

上圖中需要設定A1單元格内容才是設定合并的那遠哥的内容

Cell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("合并單元格");           

4. 執行個體

package hssf.sheet.cell;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.ss.util.WorkbookUtil;

/**
 * 合并單元格
 * 1.建立一個合并單元格
 * 2.設定合并單元格内容
 * 3.設定合并單元格邊框樣式
 * */
public class ExportRegionCell {
    public static void main(String[] args) throws Exception {
        File file = new File("C:\\Users\\Administrator\\Desktop\\test.xls");
        if (file.exists()) {
            file.delete();
        }
        BufferedOutputStream out = null;
        try {
            out = new BufferedOutputStream(new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test.xls"));
            exportExcel(out);
        } finally {
            out.close();
        }
    }

    private static void exportExcel(BufferedOutputStream out) throws Exception {
        Workbook workbook = new HSSFWorkbook();
//      Workbook workbook = new XSSFWorkbook();
        String safeSheetName = WorkbookUtil.createSafeSheetName("合并單元格");
        Sheet sheet = workbook.createSheet(safeSheetName);

        // 1.建立一個合并單元格
//      CellRangeAddress region = new CellRangeAddress(0, 9, 0, 4);
        CellRangeAddress region = CellRangeAddress.valueOf("A1:E10");
        sheet.addMergedRegion(region);

        // 2.設定合并單元格内容
        Cell cell = sheet.createRow(0).createCell(0);
        cell.setCellValue("合并單元格");

        // 設定單元格内容水準垂直居中
        CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
        CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);

        // 3.設定合并單元格邊框
        setBorderStyle(sheet, region);

        workbook.write(out);
    }

    /**
     * 設定合并單元格邊框 - 線條
     * */
    private static void setBorderStyle(Sheet sheet, CellRangeAddress region) {
        // 合并單元格左邊框樣式
        RegionUtil.setBorderLeft(BorderStyle.THICK, region, sheet);
        RegionUtil.setLeftBorderColor(IndexedColors.LIGHT_BLUE.getIndex(), region, sheet);

        // 合并單元格上邊框樣式
        RegionUtil.setBorderTop(BorderStyle.THICK, region, sheet);
        RegionUtil.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex(), region, sheet);

        // 合并單元格右邊框樣式
        RegionUtil.setBorderRight(BorderStyle.THICK, region, sheet);
        RegionUtil.setRightBorderColor(IndexedColors.LIGHT_BLUE.getIndex(), region, sheet);

        // 合并單元格下邊框樣式
        RegionUtil.setBorderBottom(BorderStyle.THICK, region, sheet);
        RegionUtil.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex(), region, sheet);
    }
}           

截圖：