Get Max Range In A Worksheet
When reading data from the worksheet, we need to know the maximum area.
When copying all data from a worksheet, we need to know the maximum area.
When exporting a specified area to html and pdf, we need to know the maximum area.
Aspose.Cells for Python via .NET contains different ways to find max range in a worksheet.
How to Get Max Range
In Aspose.Cells for Python via .NET ,if the row and column objects are initialized, these rows and columns will be counted to the maximum area, even if there is no data in empty rows or columns.
| from aspose.cells import Workbook | |
| workbook = Workbook("Book1.xlsx") | |
| # Get all the worksheets in the book. | |
| worksheets = workbook.worksheets | |
| sheet = worksheets[0] | |
| # Gets the max data range. | |
| maxRow = sheet.cells.max_row | |
| maxColumn = sheet.cells.max_column | |
| # The range is A1:B3. | |
| range = sheet.cells.create_range(0, 0, maxRow + 1, maxColumn + 1) | |
| sheet.cells.get("A10").put_value(None) | |
| maxRow = sheet.cells.max_row | |
| maxColumn = sheet.cells.max_column | |
| # The range is udpated to A1:B10. | |
| range = sheet.cells.create_range(0, 0, maxRow + 1, maxColumn + 1) |
How to Get Max Data Range
In most cases, we only need to obtain all the ranges containing all the data, even if the empty cells outside the range are formatted. And the settings about shapes, tables and pivottables will be ignored.
| from aspose.cells import Workbook | |
| # Instantiate a new Workbook. | |
| workbook = Workbook("Book1.xlsx") | |
| # Get all the worksheets in the book. | |
| worksheets = workbook.worksheets | |
| sheet = worksheets[0] | |
| # Gets the max data range. | |
| maxRow = sheet.cells.max_data_row | |
| maxColumn = sheet.cells.max_data_column | |
| # The range is A1:B3. | |
| range = sheet.cells.create_range(0, 0, maxRow + 1, maxColumn + 1) | |
| sheet.cells.get("A10").put_value(None) | |
| maxRow = sheet.cells.max_data_row | |
| maxColumn = sheet.cells.max_data_column | |
| # The range is still A1:B3. | |
| range = sheet.cells.create_range(0, 0, maxRow + 1, maxColumn + 1) |
How to Get Max Display Range
When we export all data from the worksheet to HTML, PDF, or images, we need to obtain an area containing all visible objects, including data, styles, graphics, tables, and pivot tables. The following codes show how to render the max diplay range to html:
| from aspose.cells import CellArea, HtmlSaveOptions, Workbook | |
| # Instantiate a new Workbook. | |
| workbook = Workbook("Book1.xlsx") | |
| # Get all the worksheets in the book. | |
| worksheets = workbook.worksheets | |
| # Gets the max display range. | |
| range = worksheets[0].cells.max_display_range | |
| # Save the range to html | |
| saveOptions = HtmlSaveOptions() | |
| saveOptions.export_active_worksheet_only = True | |
| saveOptions.export_area = CellArea.create_cell_area(range.first_row, range.first_column, range.first_row + range.row_count - 1, range.first_column + range.column_count - 1) | |
| # Save the range. | |
| workbook.save("html.html", saveOptions) |
Here is source excel file.