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*/@SneakyThrowspublic static ByteArrayOutputStream deleteColumns(InputStream excelStream, List<String> delColumnTitleList) {ByteArrayOutputStream outputStream = new ByteArrayOutputStream();Workbook workbook = new XSSFWorkbook(excelStream);// 获取第一个sheetSheet 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 widthsfor (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.getStringCellValue());break;}case ERROR: {cNew.setCellValue(cOld.getErrorCellValue());break;}case FORMULA: {cNew.setCellFormula(cOld.getCellFormula());break;}}}}