When I enabled filtering, Cells.MaxDisplayRange returns many of columns

Aspose.Cells Product Family
1

Description

  • Aspose.Cells does not automatically recognize the true boundaries of the data.
  • I already know the difference between MaxDisplayRange ,MaxDataRow and MaxRow, but I still need to use MaxDisplayRange, because I need both of data and shape.
  • When I remove filter from the first row, it’s OK.

Code

	var doc = new Aspose.Cells.Workbook(@"C:\Users\XCL\Desktop\2019.xlsx");
	doc.Worksheets.ToList().ForEach(sheet =>
	{
		var range = sheet.Cells.MaxDisplayRange;
		$"name:{sheet.Name},rows:{range.RowCount},col:{range.ColumnCount}".Dump();
		//output: name:Sheet1,rows:3,col:16382
		//expect: name:Sheet1,rows:3,col:10
	});

File

2019.zip (7.7 KB)

2

@xxtears,

I tested your scenario/case using your template file. Please note MaxDisplayRange will give you the maximum range including shapes/objects or filters, so it gives the output (values) right. Please open the file into MS Excel manually and check the farthest (ending) column which has filters applied, see the screenshot for your reference.
sc_shot1.png (91.5 KB)

3

Thanks a lot, can we remove these invalid filter columns by Aspose?

4

@xxtears,

I tried to remove the filters but could not do that via Aspose.Cells APIs. How did you create such filters? Anyways, You may get your desired range via Range.CurrentRegion attribute.
e.g.
Sample code:

var doc = new Aspose.Cells.Workbook("e:\\test2\\2019.xlsx");
Worksheet sheet = doc.Worksheets[0];
var range = sheet.Cells.MaxDisplayRange;
var range1 = range.CurrentRegion;//A1:J3 - Ok for your needs.

Hope, this helps a bit.

5

@xxtears,

To remove filters on unnecessary blank columns, you may try using DeleteBlankColumns() method. This will also make sure you get your expected values for MaxDisplayRange. See the following sample code for your reference:
e.g.
Sample code:

var doc = new Aspose.Cells.Workbook("e:\\test2\\2019.xlsx");
Worksheet sheet = doc.Worksheets[0];            
sheet.Cells.DeleteBlankColumns();
var range = sheet.Cells.MaxDisplayRange;
doc.Save("e:\\test2\\out1.xlsx");

The output Excel file is also fine tuned as per your needs.

6

Thanks a lot, I hope Aspose has a method to get the range which contains: data and shapes and some style(without whole row or column), because the range seems reasonable for user to find or replace or copy and so on.

7

@xxtears,

Please try any of the devised approaches which I shared above for your needs.

8

Here I am again, I think Aspose should optimize this problem, although it seems like there are countless columns, Aspose should automatically recognize them. If you have difficulty, you can consider adding a new API method, such as: TrimEndColumns or TrimEndRows.
Thanks a lot !

9

@xxtears
Those empty cells contains a style which is different from default style.
We have to check whether we can ignore them when calculating max display range.
We will respond soon.

1 Like
10

@xxtears
For your special requirement(removing some kinds of objects but not for some others, removing some styles but not for some others, …etc.), we are afraid it is hard to provide one general api. Even for the suggested methods such as TrimEndColumns/Rows, they are still ambiguous for details such as whether it should remove those rows/columns that contain special styles for the row/column or some cells.

We are afraid maybe you have to enumerate those objects to gather the expected range. For example:

Checking Cell.IsStyleSet to gather the maximum row/column position for cells that contains custom styles.
Checking Shape.LowerRightRow and Shape.LowerRightColumn to get the last shape needs to be included into the range
…
Also you can gather the row ranges and column ranges separately in this way to determine those rows/columns that need to be removed(by Cells.DeleteRows and Cells.DeleteColumns) if it is needed.