Bug 62961 - Sheet.groupColumn creates incorrect grouping and changes cell widths in XLSX file (since 3.17 and also in 4.0)
Summary: Sheet.groupColumn creates incorrect grouping and changes cell widths in XLSX ...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-11-28 11:52 UTC by Torsten Haase
Modified: 2019-06-20 08:44 UTC (History)
0 users



Attachments
correct and incorrect result (6.98 KB, application/zip)
2018-11-28 11:52 UTC, Torsten Haase
Details

Note You need to log in before you can comment on or make changes to this bug.
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;
    }