苏州网站建设及推广,门户地方网站 策略,怎么做文化传播公司网站,怎样做投资网站首先POI没有提供删除列的API#xff0c;所以就需要用其他的方式实现。
在 java - Apache POI xls column Remove - Stack Overflow 这里找到了实现方式#xff1a;
先将该列所有值都清空#xff0c;然后将该列之后的所有列往前移动。 下面的工具类中
deleteColumns(Inpu…首先POI没有提供删除列的API所以就需要用其他的方式实现。
在 java - Apache POI xls column Remove - Stack Overflow 这里找到了实现方式
先将该列所有值都清空然后将该列之后的所有列往前移动。 下面的工具类中
deleteColumns(InputStream excelStream, ListString 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 ListString getTitle(Sheet sheet) {ListString 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, ListString 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, ListString delColumnTitleList) {ListString 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 doesnt 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;}}}}
