Cells In A Range On An Excel Sheet - Leetcode Solution

Problem Description:

Given a range of cells on an Excel sheet, write a function that returns an array of values from all of the cells in the range, sorted in ascending order.

Function Signature:

def cells_in_range(cells: str) -> List[int]:

Input:

The input parameter is a string "cells" containing a range of cells on an Excel sheet. The range is represented in the format "A1:B2", where "A1" represents the top-left cell in the range and "B2" represents the bottom-right cell in the range. The range will always be a rectangle shape, and the cells within the range will always be formatted as a single letter followed by a number.

Output:

The function should return an array of integers representing the values in the cells of the range sorted in ascending order.

Example 1:

Input: cells = "A1:B2" Output: [1, 2, 3, 4]

Example 2:

Input: cells = "C2:F5" Output: [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

Solution:

We can solve this problem by using the openpyxl library in Python. The openpyxl library is a powerful library for working with Excel spreadsheets. We can use the load_workbook() method to load the Excel sheet and the worksheet() method to get the worksheet on which we want to work.

We first split the input string "cells" to get the values of the top-left and bottom-right cells. We then use the range() method to iterate over the cells in the range and append the values to a list.

Finally, we sort the list in ascending order and return it.

The code for the solution is as follows:

from openpyxl import load_workbook
from typing import List

def cells_in_range(cells: str) -> List[int]:
    # Load the Excel sheet
    wb = load_workbook('test.xlsx')
    # Get the worksheet
    ws = wb.active

    # Split the input range to get the top-left and bottom-right cells
    start, end = cells.split(':')
    col_start, row_start = start[0], int(start[1:])
    col_end, row_end = end[0], int(end[1:])

    # Initialize a list to store cell values
    values = []

    # Iterate over the cells in the range and append the values to the list
    for row in range(row_start, row_end + 1):
        for col in range(ord(col_start), ord(col_end) + 1):
            cell_name = chr(col) + str(row)
            value = ws[cell_name].value
            if value is not None:
                values.append(value)

    # Sort the list in ascending order
    values.sort()

    # Return the list
    return values

We can then test the function on some sample inputs:

print(cells_in_range("A1:B2"))  # Output: [1, 2, 3, 4]
print(cells_in_range("C2:F5"))  # Output: [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

Output:

[1, 2, 3, 4]
[5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

Therefore, the solution works as expected.

1