How do you delete trailing blank rows and columns only

Aspose.Cells Product Family
1

Hello,

I’m currently using the DeleteBlankRows and DeleteBlankColumns methods to remove empty rows and columns, but how do you remove empty rows and columns past the last cell containing data. I have looked at using MaxDataRow and MaxDataColumn, but these do not take into account images, shapes etc.

Example Files: BlankCells.zip (25.8 KB)

...

var document = new Workbook("/path/to/file");

foreach (Worksheet worksheet in document.Worksheets)
{
    worksheet.Cells.DeleteBlankRows();
    worksheet.Cells.DeleteBlankColumns();
}

....

Thanks

2

@uk_itprocurement_tcs_com,

Thanks for the template file and output file.

Well, the output result produced by your code segment is expected and correct. Please note Cells.DeleteBlankRows and Cells.DeleteBlankColumns method will remove all the blank rows (even it has some sort of formatting applied) the following types of rows:

  1. in between rows/columns (formatted or not formatted) --> the rows/cols which are b/w your data/values/shapes/objects.
  2. the (formatted) blank rows/columns which are after your last data/value/shape/object in the sheet.

In short, the methods are working as expected. I think for your specific needs, you should not use Cells.DeleteBlankRows method. Moreover, you got to remove the formatted rows/range (manually) using Cells.DeleteRows() method, see the sample code segment to get expected output for your reference:
e.g
Sample code:

    var document = new Workbook("e:\\test2\\BlankCells.xlsx");
    
       foreach (Worksheet worksheet in document.Worksheets)
        {
             worksheet.Cells.DeleteBlankColumns();
            //Get the only shape's lower row index (if there are many shapes, you have to write your own code to get the last shape)
            int s_index = worksheet.Shapes[0].LowerRightRow;
            worksheet.Cells.DeleteRows(s_index+1, 80);// even you can remove upto last row in the sheet (if you do not know the exact rows count to be removed)
        }

    document.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

Hi Amjad,

A worksheet can have Pictures, Shapes, Charts, Textboxes and possibly other drawing objects. So is the generalised solution to loop through all drawing objects to find the largest row/column? Just want to be sure that this is the most efficient way of doing this.

Thanks for the sample code, this is very helpful.

4

@uk_itprocurement_tcs_com,

Yes, this is a sort of generalized approach to loop through all the drawing shapes to get the row/col indices accordingly, this also depends upon your needs. The core thing is since your requirement is custom oriented so you got to build your logic and write your code to accomplish the task accordingly. Moreover, as long it works for your needs, you may go for it.

5

Perfect, thanks for your help.

6

@uk_itprocurement_tcs_com,

You are welcome.