XSSF ShiftRows destroy merged cells

[cdebel2005]

I would like to report a really old bug that was fixed in POI about 4 years ago. For some reasons, the bug is still present in NPOI.
Shifting up rows destroy merged cells. The content is still there, but the merged dissappeared.

Here's an example of 2 ShiftRows made on my sheet

Anyone can confirm that they have the same problem?

I'm using NPOI 2.5.3

[cdebel2005]

Shifting rows, 1 by 1 seems to work. But not when you got to delete row 5 for example and that you got to move up everything from 6-15. I had to write this fix in my code for that:

private void deleteRow(ref ISheet iSheet, int iRow, int iCount)  
{  
            for (int _count = 1; _count <= iCount; _count++)  
                for (int i = iRow; i <= iSheet.PhysicalNumberOfRows; i++)  
                {  
                    iSheet.ShiftRows(i, i, -1);  
                }  
}  

[cdebel2005]

i've struggled also with the insertion of a new line. Let's say i insert a line between lines 9 and 10, i want to keep the same columns merged that i had on line 9. I hope it will help someone, until it is fixed in the next 5-10 years. Seriously, i was surprised that this bug exists in 2017 and still present in the latest version.

        public void NPOIInsertRows(ref ISheet iSheet, int iFromRow, int iCount)
        {
            for (int _count = 1; _count <= iCount; _count++)
            {
                int _lines_count = iSheet.PhysicalNumberOfRows;
                for (int _row = _lines_count; _row >= iFromRow-1; _row--)
                {
                    iSheet.ShiftRows(_row, _row, 1);

                    // Take a sample of the style from previous line, create a new line and copy everything from the sample
                    IRow _row_source = iSheet.GetRow(_row - 1);
                    IRow _row_insert = iSheet.CreateRow(_row);
                    _row_insert.Height = _row_source.Height;
                    for (int colIndex = 0; colIndex < _row_source.LastCellNum; colIndex++)
                    {
                        ICell cellSource = _row_source.GetCell(colIndex);
                        ICell cellInsert = _row_insert.CreateCell(colIndex);

                        //  Make sure that merged cells are merged too in the new line
                        if (cellSource.IsMergedCell)
                        {
                            foreach (CellRangeAddress _range in iSheet.MergedRegions)
                            {
                                if (_range.IsInRange(cellSource.RowIndex, cellSource.ColumnIndex))
                                {
                                    if (cellInsert.ColumnIndex == _range.FirstColumn)
                                    {
                                        CellRangeAddress _new_range = new CellRangeAddress(cellInsert.RowIndex, cellInsert.RowIndex, cellInsert.ColumnIndex, _range.LastColumn);
                                        iSheet.AddMergedRegion(_new_range);
                                    }
                                }
                            }
                        }

                        if (cellSource != null)
                        {
                            cellInsert.CellStyle = cellSource.CellStyle;
                        }
                    }
                }
            }
        }

[cdebel2005]

my deleteRow isn't perfect too. If we have various row heights, you will lose them. I've made a better/longer version which copy the styles & heights so it leave most of my sheet intact... but this ShiftRows is seriously a pain in the ass.

[DarkTwistter]

Affirm, the same behaviour took place in my sheet.

This bug was also known at POI, at newest versions

https://stackoverflow.com/questions/55980407/apache-poi-shiftrows-corrupts-file-and-deletes-content

[serii833]

Same for me, ShiftRows destroys merged cells.
from my understanding problem is in RowShifter.ShiftMergedRegions,
It does something weird or I just don't get it.

I've tried to replicate excel-like behavior by reimplementing RowShifter:

• when merged cell is below insertion it just shifted down and stays merged
• when inserted row intersects merged cell then merged cell gets stretched

here is what I've got

it seems to work with advanced layouts

[kyrlouca]

As a workaround, you can copy and paste the merged regions. You can do that even in different sheets
void MergeRegions()
{
foreach (var orgMerged in OriginSheet.MergedRegions)
{
var destMerged = new CellRangeAddress(orgMerged.FirstRow - OffsetRow, orgMerged.LastRow - OffsetRow, orgMerged.FirstColumn - OffsetCol, orgMerged.LastColumn - OffsetCol);
DestSheet.AddMergedRegion(destMerged);
}
}

[tonyqus]

https://bz.apache.org/bugzilla/show_bug.cgi?id=59740

[zzy0471]

我把最新的代码（2022年6月28日）拿下来编译了dll试了下，还是不行，如果我没试错的话。但是我把2.4.1版本的代码找到替换

RowShifter rowShifter = new HSSFRowShifter(this);
rowShifter.ShiftMergedRegions(startRow, endRow, n);

为老代码就好使了。

[tonyqus]

@serii833 What's your issue here? Can you clarify? I don't see the destroy behavior in your screenshot. I need the exact reproduce steps in order to fix this issue.

[tonyqus]

@zzy0471 Can you screenshot your excel to help me understand your issue?

[zzy0471]

@tonyqus OK. I have a .xls file like this:

I want to insert 'B' between 'A' and 'C', here is my code:

private static void InsertBBetweenAAndC(ISheet sheet)
{
    sheet.ShiftRows(1, sheet.LastRowNum, 1);

    var row = sheet.GetRow(1);
    var cell = row.GetCell(0);
    if (cell == null)
    {
        cell = row.CreateCell(0);
    }
    cell.SetCellValue("B");
}

Then I get a file like this:

Demo:
NpoiDemo.zip

[serii833]

@tonyqus sorry, I didn't make it clear. those screenshots shows "expected behavior"

here is the problem
left side - before inserting row
right side - after inserting row

I've made a pull request #836 for this.
PR is similar to what I use in production

[tonyqus]

A new PR is created #877.

I have tested all your three cases. This fix works very well.