Bug 62961

Summary: Sheet.groupColumn creates incorrect grouping and changes cell widths in XLSX file (since 3.17 and also in 4.0)
Product: POI Reporter: Torsten Haase <torsten.haase>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: regression    
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: correct and incorrect result

Description Torsten Haase 2018-11-28 11:52:26 UTC
Created attachment 36282 [details]
correct and incorrect result

Sheet.groupColumn messes up XLSX file (since 3.17 and also in 4.0)

It creates an incorrect grouping and the column widths are modified. The entire sheet looks messy. See examples.

simplified sample code:

function setCellValue(xlsSheet, iRow, iCol, sText)
{
    var xlsCell=xlsSheet.cell(iRow, iCol);
    xlsCell.setCellValue(sText);
}

var iCol=0;
var iRow=0;

var iColGroupStart1=0;
var iColGroupStart2=0;

for (var iCtr1=1; iCtr1<=5; ++iCtr1)
{
    for (var iCtr2=1; iCtr2<=5; ++iCtr2)
    {
        var sText="Zelle " + iCtr1 + "." + iCtr2;
        
        setCellValue(xlsSheet, iRow, iCol, sText, xlsStyleData)
        xlsSheet.setColumnWidth(iCol++, 4500);
        
        if (iCtr2==2)
            iColGroupStart2=iCol;
        
        if (iCtr2==4)
            xlsSheet.groupColumn(iColGroupStart2, iCol-2);
    }
    
    xlsSheet.groupColumn(iColGroupStart1, iCol-2);
    iColGroupStart1=iCol;
}
--------------------------------------------------------------------------
In POI 3.13 the behaviour is correct. The groupings are sorted correctly before insertion and the column widths are preserved. 
To be fixed in org.apache.poi.xssf.usermodel.helpers.ColumnHelper:
use the following methods from 3.13 instead of the new ones:
--------------------------------------------------------------------------
    public CTCols addCleanColIntoCols(CTCols cols, CTCol col) {
        CTCols newCols = CTCols.Factory.newInstance();
        for (CTCol c : cols.getColArray()) {
            cloneCol(newCols, c);
        }
        cloneCol(newCols, col);
        sortColumns(newCols);
        CTCol[] colArray = newCols.getColArray();
        CTCols returnCols = CTCols.Factory.newInstance();
        sweepCleanColumns(returnCols, colArray, col);
        colArray = returnCols.getColArray();
        cols.setColArray(colArray);
        return returnCols;
    }
    public static void sortColumns(CTCols newCols) {
        CTCol[] colArray = newCols.getColArray();
        Arrays.sort(colArray, CTColComparator.BY_MIN_MAX);
        newCols.setColArray(colArray);
    }

    public CTCol cloneCol(CTCols cols, CTCol col) {
        CTCol newCol = cols.addNewCol();
        newCol.setMin(col.getMin());
        newCol.setMax(col.getMax());
        setColumnAttributes(col, newCol);
        return newCol;
    }
    public void setColumnAttributes(CTCol fromCol, CTCol toCol) {
        if(fromCol.isSetBestFit()) toCol.setBestFit(fromCol.getBestFit());
        if(fromCol.isSetCustomWidth()) toCol.setCustomWidth(fromCol.getCustomWidth());
        if(fromCol.isSetHidden()) toCol.setHidden(fromCol.getHidden());
        if(fromCol.isSetStyle()) toCol.setStyle(fromCol.getStyle());
        if(fromCol.isSetWidth()) toCol.setWidth(fromCol.getWidth());
        if(fromCol.isSetCollapsed()) toCol.setCollapsed(fromCol.getCollapsed());
        if(fromCol.isSetPhonetic()) toCol.setPhonetic(fromCol.getPhonetic());
        if(fromCol.isSetOutlineLevel()) toCol.setOutlineLevel(fromCol.getOutlineLevel());
        toCol.setCollapsed(fromCol.isSetCollapsed());
    }
    /**
     * @see <a href="http://en.wikipedia.org/wiki/Sweep_line_algorithm">Sweep line algorithm</a>
     */
    private void sweepCleanColumns(CTCols cols, CTCol[] flattenedColsArray, CTCol overrideColumn) {
        List<CTCol> flattenedCols = new ArrayList<CTCol>(Arrays.asList(flattenedColsArray));
        TreeSet<CTCol> currentElements = new TreeSet<CTCol>(CTColComparator.BY_MAX);
        ListIterator<CTCol> flIter = flattenedCols.listIterator();
        CTCol haveOverrideColumn = null;
        long lastMaxIndex = 0;
        long currentMax = 0;
        while (flIter.hasNext()) {
            CTCol col = flIter.next();
            long currentIndex = col.getMin();
            long colMax = col.getMax();
            long nextIndex = (colMax > currentMax) ? colMax : currentMax;
            if (flIter.hasNext()) {
                nextIndex = flIter.next().getMin();
                flIter.previous();
            }
            Iterator<CTCol> iter = currentElements.iterator();
            while (iter.hasNext()) {
                CTCol elem = iter.next();
                if (currentIndex <= elem.getMax()) break; // all passed elements have been purged
                iter.remove();
            }
            if (!currentElements.isEmpty() && lastMaxIndex < currentIndex) {
                // we need to process previous elements first
                insertCol(cols, lastMaxIndex, currentIndex - 1, currentElements.toArray(new CTCol[currentElements.size()]), true, haveOverrideColumn);
            }
            currentElements.add(col);
            if (colMax > currentMax) currentMax = colMax;
            if (col.equals(overrideColumn)) haveOverrideColumn = overrideColumn;
            while (currentIndex <= nextIndex && !currentElements.isEmpty()) {
                Set<CTCol> currentIndexElements = new HashSet<CTCol>();
                long currentElemIndex;

                {
                    // narrow scope of currentElem
                    CTCol currentElem = currentElements.first();
                    currentElemIndex = currentElem.getMax();
                    currentIndexElements.add(currentElem);

                    while (true) {
                        CTCol higherElem = currentElements.higher(currentElem);
                        if (higherElem == null || higherElem.getMax() != currentElemIndex)
                            break;
                        currentElem = higherElem;
                        currentIndexElements.add(currentElem);
                        if (colMax > currentMax) currentMax = colMax;
                        if (col.equals(overrideColumn)) haveOverrideColumn = overrideColumn;
                    }
                }


                if (currentElemIndex < nextIndex || !flIter.hasNext()) {
                    insertCol(cols, currentIndex, currentElemIndex, currentElements.toArray(new CTCol[currentElements.size()]), true, haveOverrideColumn);
                    if (flIter.hasNext()) {
                        if (nextIndex > currentElemIndex) {
                            currentElements.removeAll(currentIndexElements);
                            if (currentIndexElements.contains(overrideColumn)) haveOverrideColumn = null;
                        }
                    } else {
                        currentElements.removeAll(currentIndexElements);
                        if (currentIndexElements.contains(overrideColumn)) haveOverrideColumn = null;
                    }
                    lastMaxIndex = currentIndex = currentElemIndex + 1;
                } else {
                    lastMaxIndex = currentIndex;
                    currentIndex = nextIndex + 1;
                }

            }
        }
        sortColumns(cols);
    }
    /*
     * Insert a new CTCol at position 0 into cols, setting min=min, max=max and
     * copying all the colsWithAttributes array cols attributes into newCol
     */
    private CTCol insertCol(CTCols cols, long min, long max,
                            CTCol[] colsWithAttributes, boolean ignoreExistsCheck, CTCol overrideColumn) {
        if(ignoreExistsCheck || !columnExists(cols,min,max)){
            CTCol newCol = cols.insertNewCol(0);
            newCol.setMin(min);
            newCol.setMax(max);
            for (CTCol col : colsWithAttributes) {
                setColumnAttributes(col, newCol);
            }
            if (overrideColumn != null) setColumnAttributes(overrideColumn, newCol);
            return newCol;
        }
        return null;
    }
    private boolean columnExists(CTCols cols, long min, long max) {
        for (CTCol col : cols.getColArray()) {
            if (col.getMin() == min && col.getMax() == max) {
                return true;
            }
        }
        return false;
    }