JAVA删除excel指定列

首先POI没有提供删除列的API，所以就需要用其他的方式实现。

在 java - Apache POI xls column Remove - Stack Overflow 这里找到了实现方式：

先将该列所有值都清空，然后将该列之后的所有列往前移动。

下面的工具类中 

deleteColumns(InputStream excelStream, List<String> delColumnTitleList)方法实现了批量删除列的逻辑。

import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description
 * @ClassName ExcelUtil
 * @Date 2022/12/23 11:38
 */
public class ExcelUtil {

    /**
     * 获取sheet表头
     * @param sheet
     * @return
     */
    public static List<String> getTitle(Sheet sheet) {
        List<String> titleList = new ArrayList<>();
        if (sheet.getPhysicalNumberOfRows() > 0) {
            Row headerRow = sheet.getRow(0); // 获取第一行（表头行）

            for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    String headerText = cell.getStringCellValue();
                    titleList.add(headerText);
                }
            }
        }
        return titleList;
    }

    /**
     * 删除excel指定列
     * @param excelStream excel流
     * @param delColumnTitleList 需要删除的列的表头
     * @return
     */
    @SneakyThrows
    public static ByteArrayOutputStream deleteColumns(InputStream excelStream, List<String> delColumnTitleList) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        Workbook workbook = new XSSFWorkbook(excelStream);
        // 获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        deleteColumns(sheet, delColumnTitleList);
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
        return outputStream;
    }

    /**
     * 删除sheet指定的列
     * @param sheet
     * @param delColumnTitleList
     */
    public static void deleteColumns(Sheet sheet, List<String> delColumnTitleList) {
        List<String> titleList = getTitle(sheet);
        for (String delTitle : delColumnTitleList) {
            int i = titleList.indexOf(delTitle);
            if (i >= 0) {
                deleteColumn(sheet, i);
            }
            //由于是循环删除，删除后，列所在位置索引会变化，所以titleList相应也移除删除的列
            titleList.remove(delTitle);
        }
    }

    /**
     * 删除指定列
     * poi没有提供删除指定列的api，所以先将该列清空，然后将后续的列往前移动，这样达到删除列的效果
     * @param sheet
     * @param columnToDelete
     */
    public static void deleteColumn(Sheet sheet, int columnToDelete) {
        int maxColumn = 0;
        for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
            Row row = sheet.getRow(r);

            // if no row exists here; then nothing to do; next!
            if (row == null) {
                continue;
            }

            // if the row doesn't have this many columns then we are good; next!
            int lastColumn = row.getLastCellNum();
            if (lastColumn > maxColumn) {
                maxColumn = lastColumn;
            }

            if (lastColumn < columnToDelete) {
                continue;
            }

            for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
                Cell oldCell = row.getCell(x - 1);
                if (oldCell != null) {
                    row.removeCell(oldCell);
                }

                Cell nextCell = row.getCell(x);
                if (nextCell != null) {
                    Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                    cloneCell(newCell, nextCell);
                }
            }
        }

        // Adjust the column widths
        for (int c = 0; c < maxColumn; c++) {
            sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
        }
    }

    /**
     * 右边列左移
     */
    private static void cloneCell(Cell cNew, Cell cOld) {
        cNew.setCellComment(cOld.getCellComment());
        cNew.setCellStyle(cOld.getCellStyle());

        switch (cNew.getCellTypeEnum()) {
            case BOOLEAN: {
                cNew.setCellValue(cOld.getBooleanCellValue());
                break;
            }
            case NUMERIC: {
                cNew.setCellValue(cOld.getNumericCellValue());
                break;
            }
            case STRING: {
                //这样不丢样式
                cNew.setCellValue(cOld.getRichStringCellValue());
                break;
            }
            case ERROR: {
                cNew.setCellValue(cOld.getErrorCellValue());
                break;
            }
            case FORMULA: {
                cNew.setCellFormula(cOld.getCellFormula());
                break;
            }
        }

    }

}