Bug 53877 - POI 3.8 error attempting to program a "delete columns" capability
Summary: POI 3.8 error attempting to program a "delete columns" capability
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.12-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-09-15 16:15 UTC by Every Moment
Modified: 2016-02-17 18:36 UTC (History)
1 user (show)



Attachments
Demonstrates an error, possibly in HSSFrow.moveCell() (4.56 KB, text/plain)
2012-09-15 16:15 UTC, Every Moment
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Every Moment 2012-09-15 16:15:09 UTC
Created attachment 29380 [details]
Demonstrates an error, possibly in HSSFrow.moveCell()

The attached java file POI_error_in_column_deletes.java attempts to demonstrate one way to program column deletes in POI 3.8. This java program does the following: 
  - creates a new workbook
  - creates 10 rows each containing 10 empty cells
  - fills in range A3:E3 with "A", "B", "C", "D", and "E"
  - runs code designed to delete columns B and C,
      but instead of getting ADE in the third row it gets ACE
  - outputs a workbook testDeleteColumns.xls

It get the same incorrect result in POI 3.8 and in the poi-3.9-beta1-20120915.jar nightly.

The inner loop of deleting columns, which contains the error, is the method deleteColumnFromRow:


/** Removes a cell at START_COL and shifts remainder of row to the left by one.
 * @param row row from which to delete and shift cells
 * @param START_COL index of cell to remove
 */
private static void deleteColumnFromRow(HSSFRow row, final int START_COL){
    if(START_COL >= COLS){
        return;
    }
    HSSFCell check = row.getCell(START_COL);
    if(null!=check){
        row.removeCell(check);
    }
    System.err.println(
        "        deleteColumnFromRow: row = "+
        row.getRowNum());
    HSSFCell cell = null;
    for(int col = START_COL+1; col < COLS; col++){
        int source = col;
        int destination = col-1;
        cell = row.getCell(source);
        if(null != row.getCell(destination)){
            row.removeCell(row.getCell(destination));
            // Because the API says the destination cannot exist,
            // the destination has been deleted. The API reference is
            // poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html
        }
        if(null != cell){
            // I THINK THE ERROR MIGHT BE IN moveCell().
            row.moveCell(cell,(short)(destination));
        }
    }
}
Comment 1 monzarc 2013-05-12 23:11:26 UTC
I've also had difficulty getting a working 'deleteColumns()' method, and would very much like to know if a solution is on offer.
Comment 2 Dominik Stadler 2013-08-27 20:05:04 UTC
When I run your test-program with the latest trunk-version of poi I get A D E correctly, it seems the bug was fixed at some point. Please reopen or report a new bug with updated information if there is still a problem for you with snapshot builds or the next upcoming release (likely 3.10).
Comment 3 monzarc 2015-09-14 01:51:00 UTC
This bug recurs in 3.12.

Using the previously-attached POI_error_in_column_deletes class, I created a batch file comprising the following commands:

set c1=poi-3.12-20150511.jar
set c2=POI_error_in_column_deletes
del %c2%.class
del testDeleteColumns.xls
javac -cp %c1% %c2%.java
java -cp %c1%; %c2%

The result is a new .xls file whose columns read 'ACE', instead of the proper output of 'ADE'.

The java version running is 1.8.0_60. The POI version is 3.12-20150511.
Comment 4 Dominik Stadler 2016-02-17 18:36:03 UTC
I tried it again at it does work for me, not sure where the difference is to what you see. Please use the following unit test and show how it needs to be adjusted to cause the problem if this is really still a problem for you.

This test verifies that "A", "D", "E" end up in the columns, but I also checked with LibreOffice (sorry, no MS Office on this machine)

    private static final int ROWS = 10;
    private static final int COLS = 10;

    @Test
    public void test_deleteColumns() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sh = wb.createSheet("sheet1");
        HSSFRow r = null;
        for (int row = 0; row < ROWS; row++) {
            r = sh.createRow(row);
            for (int col = 0; col < COLS; col++) {
                r.createCell(col);
            }
        }
        sh.getRow(2).getCell(0).setCellValue("A");
        sh.getRow(2).getCell(1).setCellValue("B");
        sh.getRow(2).getCell(2).setCellValue("C");
        sh.getRow(2).getCell(3).setCellValue("D");
        sh.getRow(2).getCell(4).setCellValue("E");
        // Put in ABCDE into consecutive cells;
        // deletion should have gotten ADE but got ACE instead.
        for (int deleteIndex = 0; deleteIndex < 2; deleteIndex++) {
            HSSFRow row = sh.getRow(2);
            if (null != row) {
                deleteColumnFromRow(row, 1);
            }
        }
        
        assertEquals("A", sh.getRow(2).getCell(0).getStringCellValue());
        assertEquals("D", sh.getRow(2).getCell(1).getStringCellValue());
        assertEquals("E", sh.getRow(2).getCell(2).getStringCellValue());
        
        wb.close();
    }


    private void deleteColumnFromRow(HSSFRow row, final int START_COL) {
        if (START_COL >= COLS) {
            return;
        }
        HSSFCell check = row.getCell(START_COL);
        if (null != check) {
            row.removeCell(check);
        }
        System.err.println(
                "        deleteColumnFromRow: row = " + row.getRowNum());
        HSSFCell cell = null;
        for (int col = START_COL + 1; col < COLS; col++) {
            int source = col;
            int destination = col - 1;
            cell = row.getCell(source);
            if (null != row.getCell(destination)) {
                row.removeCell(row.getCell(destination));
                // Because the API says the destination cannot exist,
                // the destination has been deleted. The API reference is
                // poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRow.html
            }
            if (null != cell) {
                // moveCell is most likely where the error occurs.
                row.moveCell(cell, (short) (destination));
            }
        }
    }