Java Apache POI xls 列删除

Apache POI xls column Remove

提问by

I don't find how to remove a column with the Apache POI API.
I would appreciate a sample code or help on this point.

我没有找到如何使用 Apache POI API 删除列。
我将不胜感激在这一点上的示例代码或帮助。

回答by jsight

I think you have to go down each HSSFRow and call HSSFRow.getCell and then HSSFRow.removeCell. The API is oriented towards rows, rather than columns, and very few operations work at the whole column level.

我认为您必须沿着每个 HSSFRow 调用 HSSFRow.getCell，然后调用 HSSFRow.removeCell。API 是面向行的，而不是面向列的，很少有操作是在整个列级别工作的。

Sample code (untested):

示例代码（未经测试）：

HSSFSheet sheet = ...
int colToRemove = 5;
Iterator rowIter = sheet.iterator();
while (rowIter.hasNext()) {
   HSSFRow row = (HSSFRow)rowIter.next();
   HSSFCell cell = row.getCell(colToRemove);
   row.removeCell(cell);
}

回答by cporte

Alan Williamson on the mailing listwrote a small helper for column removal

邮件列表上的 Alan Williamson写了一个删除列的小帮手

package org.alanwilliamson.openbd.plugin.spreadsheet;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;


/*
 * Helper functions to aid in the management of sheets
 */
public class SheetUtility extends Object {


    /**
     * Given a sheet, this method deletes a column from a sheet and moves
     * all the columns to the right of it to the left one cell.
     * 
     * Note, this method will not update any formula references.
     * 
     * @param sheet
     * @param column
     */
    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) );
        }
    }


    /*
     * Takes an existing Cell and merges all the styles and forumla
     * into the new one
     */
    private static void cloneCell( Cell cNew, Cell cOld ){
        cNew.setCellComment( cOld.getCellComment() );
        cNew.setCellStyle( cOld.getCellStyle() );

        switch ( cNew.getCellType() ){
            case Cell.CELL_TYPE_BOOLEAN:{
                cNew.setCellValue( cOld.getBooleanCellValue() );
                break;
            }
            case Cell.CELL_TYPE_NUMERIC:{
                cNew.setCellValue( cOld.getNumericCellValue() );
                break;
            }
            case Cell.CELL_TYPE_STRING:{
                cNew.setCellValue( cOld.getStringCellValue() );
                break;
            }
            case Cell.CELL_TYPE_ERROR:{
                cNew.setCellValue( cOld.getErrorCellValue() );
                break;
            }
            case Cell.CELL_TYPE_FORMULA:{
                cNew.setCellFormula( cOld.getCellFormula() );
                break;
            }
        }

    }
}

回答by codewing

The answer of cporte is perfectly fine but imho a bit hard to read.

cporte 的答案非常好，但恕我直言有点难以阅读。

---

---

The Idea:

理念：

For every row, delete the cell representing the column which shall be deleted and move all cells to the right of this column one to the left.

对于每一行，删除代表应删除列的单元格，并将该列右侧的所有单元格向左移动一个。

---

---

The simplified Implementation:

简化的实现：

//Variables for completeness
Sheet sheet;
int columnToDelete;

for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
    Row row = sheet.getRow(rId);
    for (int cID = columnToDelete; cID < row.getLastCellNum(); cID++) {
        Cell cOld = row.getCell(cID);
        if (cOld != null) {
            row.removeCell(cOld);
        }
        Cell cNext = row.getCell(cID + 1);
        if (cNext != null) {
            Cell cNew = row.createCell(cID, cNext.getCellType());
            cloneCell(cNew, cNext);
            sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
        }
    }
}

---

---

为了完整起见，从另一个答案复制的克隆单元方法：

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

    switch ( cNew.getCellType() ){
        case Cell.CELL_TYPE_BOOLEAN:{
            cNew.setCellValue( cOld.getBooleanCellValue() );
            break;
        }
        case Cell.CELL_TYPE_NUMERIC:{
            cNew.setCellValue( cOld.getNumericCellValue() );
            break;
        }
        case Cell.CELL_TYPE_STRING:{
            cNew.setCellValue( cOld.getStringCellValue() );
            break;
        }
        case Cell.CELL_TYPE_ERROR:{
            cNew.setCellValue( cOld.getErrorCellValue() );
            break;
        }
        case Cell.CELL_TYPE_FORMULA:{
            cNew.setCellFormula( cOld.getCellFormula() );
            break;
        }
    }

}

回答by Hari Samala

codewing's solution worked for me like a charm with the following minor changes:

codewing 的解决方案对我很有用，但有以下小改动：

1. When we clone the cell, the call should be cloneCell(cNew, cNext)
2. We should set the column width only for the first row.
3. I'm using version 3.17 of the api, so a few things changed (like CellType changed from int to an enum).

1. 当我们克隆单元格时，调用应该是 cloneCell(cNew, cNext)
2. 我们应该只为第一行设置列宽。
3. 我使用的是 3.17 版的 api，所以一些事情发生了变化（比如 CellType 从 int 更改为 enum）。

Full code is below (for clarity):

完整代码如下（为清楚起见）：

private void deleteColumn(Sheet sheet, int columnToDelete) {
    for (int rId = 0; rId < sheet.getLastRowNum(); rId++) {
        Row row = sheet.getRow(rId);
        for (int cID = columnToDelete; cID < row.getLastCellNum(); cID++) {
            Cell cOld = row.getCell(cID);
            if (cOld != null) {
                row.removeCell(cOld);
            }
            Cell cNext = row.getCell(cID + 1);
            if (cNext != null) {
                Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
                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));

                }
            }
        }
    }
}

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

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