Managing Ranges

Introduction

In Excel, you can select multiple cells with a mouse box selection, the set of selected cells is called “Range”.

For example, you can click the left mouse button in Cell “A1” of the Excel and then drag to cell “C4”. The rectangular area you selected can also be easily created as an object by using Aspose.Cells for Python via .NET.

Here is how to create range, put value, set style, and do more operations to the “Range” object.

Managing Ranges Using Aspose.Cells for Python Excel Library

Aspose.Cells for Python via .NET provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection.

How to Create Range

When you want to create a rectangular area that extends over A1:C4, you can use the following code:

from aspose.cells import Workbook
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# Create a Workbook
workbook = Workbook()
# Get Cells
cells = workbook.worksheets[0].cells
# Create Range
range = cells.create_range("A1:C4")

How to Put Value into the Cells of the Range

Say you have a range of cells that extends over A1:C4. The matrix makes 4 * 3 = 12 cells. The individual range cells are arranged sequentially.

The following example shows how to input some values into the cells of the Range.

from aspose.cells import Workbook, Range
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# Create a Workbook
workbook = Workbook()
# Get Cells
cells = workbook.worksheets[0].cells
# Create Range
range = cells.create_range("A1:C4")
# Put value
a1 = cells.get(range.first_row, range.first_column)
a1.put_value("A1")
b1 = cells.get(range.first_row, range.first_column + 1)
b1.put_value("B1")
c1 = cells.get(range.first_row, range.first_column + 2)
c1.put_value("C1")
a4 = cells.get(range.first_row + 3, range.first_column)
a4.put_value("A4")
b4 = cells.get(range.first_row + 3, range.first_column + 1)
b4.put_value("B4")
c4 = cells.get(range.first_row + 3, range.first_column + 2)
c4.put_value("C4")
# Save the Workbook
workbook.save("RangeValueTest.xlsx")

How to Set Style of the Cells of the Range

The following example shows how to set style of the cells of the Range.

from aspose.cells import BackgroundType, Workbook
from aspose.pydrawing import Color
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# Create a Workbook
workbook = Workbook()
# Get Cells
cells = workbook.worksheets[0].cells
# Create Range
range = cells.create_range("A1:C4")
# Put value
a1 = cells.get(range.first_row, range.first_column)
a1.put_value("A1")
c4 = cells.get(range.first_row + 3, range.first_column + 2)
c4.put_value("C4")
# Set Style
style00 = workbook.create_style()
style00.pattern = BackgroundType.SOLID
style00.foreground_color = Color.red
a1.set_style(style00)
style32 = workbook.create_style()
style32.pattern = BackgroundType.HORIZONTAL_STRIPE
style32.foreground_color = Color.green
c4.set_style(style32)
# Save the Workbook
workbook.save("RangeStyleTest.xlsx")

How to Get CurrentRegion of the Range

CurrentRegion is a property that returns a Range object that represents the current region.

The current region is a range bounded by any combination of blank rows and blank columns. Read-only.

In excel, you can get CurrentRegion area by:

  1. Select an area(range1) with the mouse box.
  2. Click “Home - Editing - Find & Select - Go To Special - Currect region”, or use “Ctrl+Shift+*”, you will see excel automatically helps you select an area(range2), now you made it, range2 is the CurrentRegion of range1.

Using Aspose.Cells for Python via .NET, you can use “Range.current_region” property to perform the same function.

Please downloaded the following test file, open it in excel, use the mouse box to select an area “A1:D7”, then click “Ctrl+Shift+*”, you will see area “A1:C3” selected.

current_region.xlsx

Now please run the following example, see how it works in Aspose.Cells for Python via .NET:

from aspose.cells import Workbook
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# Create a Workbook
workbook = Workbook("current_region.xlsx")
# Get Cells
cells = workbook.worksheets[0].cells
# Create Range
src = cells.create_range("A1:D7")
# Get CurrentRegion
A1C3 = src.current_region

Advance topics