Java POI实现合并单元格

最近碰到个需要合并单元格的需求，要合并相同内容的单元格，前提是合并同一个订单的（第5列）

1.先合并第5列OrderNo的列的单元格

2.读取合并后的单元格，然后合并其它需要合并的列

 

代码如下：

String[] titles = { "orderList"};

// 对已生成的excel进行合并单元格操作，先合并第5列OrderNo的列的单元格

ExcelUtil.mergeCell(filepath + "/" + filename, 5);

 

注释：如果只合并一列相同内容的单元格，只调用上面的方法就可以了。如果还要合并其它的列，再调用下面的ExcelUtil.readMergedCell()方法即可。

 

int[] columns = {0, 1, 2, 3, 4, 6, 7, 8, 9, 10};
// 读取合并后的单元格，然后合并其它需要合并的列
ExcelUtil.readMergedCell(filepath + "/" + filename, titles, columns);

FileUtils.download(filepath + "/" + filename, response);

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
    
    /**
     * 合并单元格
     *
     * @param filePath excel文件路径
     * @param mergeNum 要合并的列数
     */
    public static void mergeCell(String filePath, int mergeNum) throws Exception{
        try {
            File file = new File(filePath);
            FileInputStream fis = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            if (workbook != null){
                // 设置样式
                XSSFCellStyle cellStyle = createCellStyle(workbook);
                XSSFFont font = createFont(workbook);
                cellStyle.setFont(font);
                XSSFSheet sheet = workbook.getSheetAt(0);
                if (null != sheet) {
                    //开始查找的行
                    int currentRow = 1;
                    //totalRow 总行数
                    int totalRow = sheet.getLastRowNum();
                    String cellValue;
                    for (int p = 1; p <= totalRow; p++) {
                        XSSFRow row = sheet.getRow(p);
                        if (null != row) {
                            XSSFCell currentCell = row.getCell(mergeNum);
                            if (null != currentCell) {
                                String current = getStringCellValue(currentCell);
                                XSSFCell nextCell = null;
                                String next = "";
                                if (p < totalRow + 1) {
                                    XSSFRow nowRow = sheet.getRow(p + 1);
                                    if (nowRow != null) {
                                        nextCell = nowRow.getCell(mergeNum);
                                        next = getStringCellValue(nextCell);
                                    } else {
                                        next = "";
                                    }
                                } else {
                                    next = "";
                                }
                                // 比对是否相同
                                if (current.equals(next)) {
                                    currentCell.setCellValue("");
                                    continue;
                                } else {
                                    if (currentRow == p && !current.equals(next)){
                                        currentRow = p + 1;
                                        continue;
                                    }
                                    XSSFCell xssfCell = row.getCell(mergeNum);
                                    cellValue = getStringCellValue(xssfCell);
                                    List<Integer> list = new ArrayList<>();
                                    for(int i = currentRow; i<= p; i++){
                                        list.add(i);
                                    }
                                    for (Integer rowNum: list) {
                                        XSSFCell cell1 = sheet.getRow(rowNum).getCell(mergeNum);
                                        // 判断是否是几行同一个订单的第一个单元格
                                        if (rowNum.equals(currentRow)){
                                            cell1.setCellValue(cellValue);
                                            cell1.setCellStyle(cellStyle);
                                        } else {
                                            sheet.getRow(rowNum).removeCell(cell1);
                                        }
                                    }
                                    // 合并单元格
                                    sheet.addMergedRegion(new CellRangeAddress(currentRow, p, mergeNum, mergeNum));
                                    XSSFCell nowCell = sheet.getRow(currentRow).getCell(mergeNum);
                                    nowCell.setCellValue(cellValue);
                                    nowCell.setCellStyle(cellStyle);
                                    currentRow = p + 1;
                                }
                            }
                        }
                    }
                }
            }

            FileOutputStream fos = new FileOutputStream(file);
            workbook.write(fos);

            fis.close();
            fos.close();
            workbook.close();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

    /**
     * 读取合并后的单元格
     *
     * @param filePath excel文件路径
     * @param sheetNames sheet列名称列表，如果只有一个sheet的话，此参数可不传，可使用workbook.getSheetAt(0)获取XSSFSheet对象
     * @param mergeColumns 需要合并的列数组
     * @return
     */
    public static void readMergedCell(String filePath, String[] sheetNames, int[] mergeColumns){
        // 读取合并后的订单excel内容
        File file;
        FileInputStream fis = null;
        FileOutputStream fos = null;
        XSSFWorkbook workbook = null;
        try {
            file = new File(filePath);
            fis = new FileInputStream(file);
            workbook = new XSSFWorkbook(fis);
            // 设置样式
            XSSFCellStyle cellStyle = createCellStyle(workbook);
            XSSFFont font = createFont(workbook);
            cellStyle.setFont(font);
            for (String sheetName : sheetNames) {
                String cellValue;
                XSSFSheet sheet = workbook.getSheet(sheetName);
                List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
                for (CellRangeAddress cellRangeAddress: mergedRegions){
                    int firstRow = cellRangeAddress.getFirstRow();
                    int lastRow = cellRangeAddress.getLastRow();
                    List<Integer> rowNumList = new ArrayList<>();
                    for(int i = firstRow; i<= lastRow; i++){
                        rowNumList.add(i);
                    }
                    for (int columnNum: mergeColumns){
                        XSSFCell cell = sheet.getRow(firstRow).getCell(columnNum);
                        cellValue = getStringCellValue(cell);
                        // 合并单元格
                        for (Integer rowNum: rowNumList) {
                            XSSFCell cell1 = sheet.getRow(rowNum).getCell(columnNum);
                            // 判断是否是几行同一个订单的第一个单元格
                            if (rowNum.equals(firstRow)){
                                if (Integer.valueOf(XSSFCell.CELL_TYPE_NUMERIC).equals(cell.getCellType())){
                                    cell1.setCellValue(cell.getNumericCellValue());
                                }else{
                                    cell1.setCellValue(cellValue);
                                }
                                cell1.setCellStyle(cellStyle);
                            } else {
                                if(sheet.getRow(rowNum) != null && cell1 != null ){
                                    sheet.getRow(rowNum).removeCell(cell1);
                                }
                            }
                        }
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, columnNum, columnNum));
                        XSSFCell nowCell = sheet.getRow(firstRow).getCell(columnNum);
                        if (Integer.valueOf(XSSFCell.CELL_TYPE_NUMERIC).equals(cell.getCellType())){
                            nowCell.setCellValue(cell.getNumericCellValue());
                        } else{
                            nowCell.setCellValue(cellValue);
                        }
                        nowCell.setCellStyle(cellStyle);
                    }
                }
            }
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            try {
                if (fis != null){
                    fis.close();
                }
                if (fos != null){
                    fos.close();
                }
                if (workbook != null){
                    workbook.close();
                }
            } catch (Exception e){
                LOGGER.error(e.getMessage(), e);
            }
        }

    }

    /**
     * 将单元格中的所有值，都转化为String类型的值
     *
     * @param cell
     * @return
     */
    public static String getStringCellValue(XSSFCell cell) {
        String strCell = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    strCell = String.valueOf(cell.getNumericCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    strCell = String.valueOf(cell.getBooleanCellValue());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    strCell = "";
                    break;
                default:
                    strCell = "";
                    break;
            }
            if (StringUtils.isEmpty(strCell)) {
                return "";
            }
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 设置样式
     *
     * @param workbook
     */
    public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook){
        // 设置样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // 居中样式
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 垂直样式
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        return cellStyle;
    }

    /**
     * 设置字体
     *
     * @param workbook
     */
    public static XSSFFont createFont(XSSFWorkbook workbook){
        // 设置字体:
        XSSFFont font = workbook.createFont();
        font.setFontName("Calibri");
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        return font;
    }
}