DeleteBlankRows() is deleting rows even if it has shapes on it

Aspose.Cells Product Family
42

In the attached below code it is giving exception that’s why I guess it is not working as per expected for me. Please find attached the solution to debug.

processing_error.zip (7.4 MB)

43

@nraj,

Thanks for the sample app and template files.

I tested your scenario/case using the exact project and found the issue/exception “Row number or column number cannot be zero” for “input5.xlsx” template file. Then, I analyzed the file “input5.xlsx” a bit. I noticed that for “SheetEmpty1” sheet there is no data or formatting set for it and only shapes/smart art and chart(s). That’s why both Worksheet.Cells.MaxRow and Worksheet.Cells.MaxColumn attributes are evaluated as “-1” which consequently results in the error because it can’t create the range. Please note if there is no data or formatting exists on the worksheet, MaxRow and MaxColumn would be evaluated as “-1”. I think you may simply add a check in “TransferCells” method accordingly.

44

Thanks for the suggestion,
Actually we are having the cells.count check in our code.

I figured out we have a method PrepareWorksheet(), we are seeing the issue getting reproduced when I have updated the method as we are doing some adhustment inside it.

Please find the attached solution to reproduce the issue of overlapping cell content with image in “Sheet100”

ExcelOverlappingImage.zip (3.9 MB)

Pleasefind below the screenshpt of the output

output_screenshot.zip (1.2 MB)

45

@nraj,

Thanks for the resource files.

Let us evaluate your issue in details using your resource files and then we will get back to you.

46

@nraj
By analyzing the sample code, we found that after removing empty rows and columns, the sample code adjusted the row height and column width. This is the reason for the overlap. Please adjust the row height and column width first, and then delete blank rows and columns. By testing with the following code, we can obtain the correct results. Please refer to the attachment. out_net.zip (3.5 MB)

    private static void PostProcessWorksheet(PreFlightInfo preFlightInfo, Worksheet targetWorksheet, Worksheet sourceWorksheet)
    {
        // Ensures shapes are correctly positioned when deleting blank rows/columns
        //ShapeCollection shapes = targetWorksheet.Shapes;
        //int shapeCount = shapes.Count;
        //for (int i = 0; i < shapeCount; i++)
        //{
        //    Shape shape = shapes[i];
        //    shape.Placement = PlacementType.MoveAndSize;
        //}

        int totalRows, totalColumns;

        DeleteBlankOptions options = new DeleteBlankOptions();
        //Set UpdateReference property to true;
        options.UpdateReference = true;
        options.DrawingsAsBlank = false;

        totalRows = targetWorksheet.Cells.MaxDataRow;
        totalColumns = targetWorksheet.Cells.MaxDataColumn;

        if (targetWorksheet.Cells.MaxDataColumn != totalColumns)
        {
            preFlightInfo.HasBlankColumns = true;
        }
        if (targetWorksheet.Cells.MaxDataRow != totalRows)
        {
            preFlightInfo.HasBlankRows = true;
        }

        // Adjust column widths and row heights
        foreach (Column sourceColumn in sourceWorksheet.Cells.Columns)
        {
            if (sourceColumn.Width != 0)
            {
                targetWorksheet.Cells.Columns[sourceColumn.Index].Width = sourceColumn.Width;
            }
        }
        foreach (Row sourceRow in sourceWorksheet.Cells.Rows)
        {
            if (sourceRow.Height != 0)
            {
                targetWorksheet.Cells.Rows[sourceRow.Index].Height = sourceRow.Height;
            }
        }

        //We need to remove from both so adjusting height and width is correct
        targetWorksheet.Cells.DeleteBlankColumns(options);
        targetWorksheet.Cells.DeleteBlankRows(options);
        sourceWorksheet.Cells.DeleteBlankColumns(options);
        sourceWorksheet.Cells.DeleteBlankRows(options);
        
    }

Hope helps a bit.