POI导出excel文件之取消合并单元格、删除列、移动列

最新推荐文章于 2023-11-10 17:18:03 发布

kelly921011

最新推荐文章于 2023-11-10 17:18:03 发布

阅读量5.3k

点赞数 2

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/kelly921011/article/details/126767809

版权

java 专栏收录该内容

49 篇文章 1 订阅

订阅专栏

1. 删除列

场景：将源文件中的某列的值、批准删除

    /**
     * 删除列
     *
     * @param sheet
     * @param columnToDelete 要删除的列号
     */
    public static void deleteColumn(Sheet sheet, int columnToDelete) {
        for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
            Row row = sheet.getRow(rId);
            if(row==null){
                continue;
            }
            for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
                Cell cOld = row.getCell(cID);
                if (cOld != null) {
                    //删除批注
                    cOld.removeCellComment();
                    row.removeCell(cOld);
                }
                Cell cNext = row.getCell(row.getLastCellNum() + 1);
                if (cNext != null) {
                    Cell cNew = row.createCell(cID, cNext.getCellType());
                    cloneCell(cNew, cNext);
                    //Set the column width only on the first row.
                    //Other wise the second row will overwrite the original column width set previously.
                    if (rId == 0) {
                        sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));

                    }
                }
            }
        }
    }

2.取消合并单元格

场景：取消某区域的合并单元格

/**
     * 取消多个合并单元格
     *
     * @param sheet
     * @param startRow    开始行号 
     * @param endRow      结束行号 
     * @param startColumn 开始列号
     * @param endColumn   结束列号
     */
    public static void removeMerged(Sheet sheet, Integer startRow, Integer endRow, Integer startColumn, Integer endColumn) {
        if(startRow==null){
            startRow= sheet.getFirstRowNum();
        }
        if(endRow==null){
            endRow= sheet.getLastRowNum();
        }
        //获取所有的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //用于保存要移除的那个合并单元格序号
        List<Integer> indexList = new ArrayList<>();
        for (int i = 0; i < sheetMergeCount; i++) {
            //获取第i个单元格
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) {
                indexList.add(i);
            }
        }
        sheet.removeMergedRegions(indexList);
    }

3.移动列

场景：将某列值移动至空列上

private static void cloneCell(Cell cNew, Cell cOld) {
        cNew.setCellComment(cNew.getCellComment());
        cNew.setCellStyle(cNew.getCellStyle());

        if (CellType.BOOLEAN == cNew.getCellType()) {
            cNew.setCellValue(cOld.getBooleanCellValue());
        } else if (CellType.NUMERIC == cNew.getCellType()) {
            cNew.setCellValue(cOld.getNumericCellValue());
        } else if (CellType.STRING == cNew.getCellType()) {
            cNew.setCellValue(cOld.getStringCellValue());
        } else if (CellType.ERROR == cNew.getCellType()) {
            cNew.setCellValue(cOld.getErrorCellValue());
        } else if (CellType.FORMULA == cNew.getCellType()) {
            cNew.setCellValue(cOld.getCellFormula());
        }
    }