[Bug 62961] New: Sheet.groupColumn creates incorrect grouping and changes cell widths in XLSX file (since 3.17 and also in 4.0)

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[Bug 62961] New: Sheet.groupColumn creates incorrect grouping and changes cell widths in XLSX file (since 3.17 and also in 4.0)

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62961

            Bug ID: 62961
           Summary: Sheet.groupColumn creates incorrect grouping and
                    changes cell widths in XLSX file (since 3.17 and also
                    in 4.0)
           Product: POI
           Version: 3.17-FINAL
          Hardware: PC
            Status: NEW
          Severity: critical
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 36282
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36282&action=edit
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;
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]