我没有找到如何与Apache POI API中删除列。
我希望一个示例代码或在这一点上有所帮助。
Answer 1:
阿兰·威廉姆森在邮件列表上写一个小帮手塔移出
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;
}
}
}
}
Answer 2:
cporte的答案是完全正常的，但恕我直言，有点难以阅读。
理念：
对于每一行，删除表示这应被删除列的单元格，将所有的细胞来此列一个向左的权利。
简化的实施：
//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(cNext, cNew);
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;
}
}
}
Answer 3:
codewing的解决方案为我喜欢用下面的轻微变化的魅力：
- 当我们克隆细胞，电话应该是cloneCell（cNew，CNEXT）
- 我们应该只在第一行设置列宽。
- 我使用的API的3.17版本，所以一些事情改变（如格类型从int到枚举改变）。
全部代码如下（为清楚起见）：
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());
}
}
Answer 4:
我认为你必须下井每个HSSFRow并呼吁HSSFRow.getCell然后HSSFRow.removeCell。 该API是面向行，而不是列，很少在操作整列一级的工作。
示例代码（未经测试）：
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);
}
文章来源: Apache POI xls column Remove
