Apache POI xls colonne Supprimer

Alan Williamson sur la liste de diffusion a écrit une petite aide pour la colonne de suppression

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;
}
}
}
}

OriginalL'auteur cporte

codewing la solution a fonctionné pour moi comme un charme avec les petites modifications suivantes:

1. Quand on clone de la cellule, l'appel doit être cloneCell(cnouvelle, cNext)
2. Nous devons définir la largeur de colonne que pour la première ligne.
3. J'utilise la version 3.17 de l'api, donc un peu de choses ont changé (comme CellType changé de int à un enum).

Code complet est ci-dessous (pour plus de clarté):

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());
}
}

OriginalL'auteur Hari Samala