Cells in a Range on an Excel Sheet

🏠 ⬅️ ➡️

A cell (r, c) of an excel sheet is represented as a string " " where:

  • denotes the column number c of the cell. It is represented by alphabetical letters.
    • For example, the 1st column is denoted by 'A', the 2nd by 'B', the 3rd by 'C', and so on.
  • is the row number r of the cell. The rth row is represented by the integer r.

You are given a string s in the format ": ", where represents the column c1, represents the row r1, represents the column c2, and represents the row r2, such that r1 <= r2 and c1 <= c2.

Return the list of cells (x, y) such that r1 <= x <= r2 and c1 <= y <= c2. The cells should be represented as strings in the format mentioned above and be sorted in non-decreasing order first by columns and then by rows.

Example 1:

Input: s = "K1:L2 " Output: [ "K1 ", "K2 ", "L1 ", "L2 "] Explanation: The above diagram shows the cells which should be present in the list. The red arrows denote the order in which the cells should be presented.

Example 2:

Input: s = "A1:F1 " Output: [ "A1 ", "B1 ", "C1 ", "D1 ", "E1 ", "F1 "] Explanation: The above diagram shows the cells which should be present in the list. The red arrow denotes the order in which the cells should be presented.

Constraints:

  • s.length == 5
  • 'A' <= s[0] <= s[3] <= 'Z'
  • '1' <= s[1] <= s[4] <= '9'
  • s consists of uppercase English letters, digits and ':'.
Note: This problem is from LeetCode.
❌ Compiled
❌ Executed
❌ Correct 
program main

! This program solves the following problem:
!
! A cell (r, c) of an excel sheet is represented as a string " " where:
!
! - denotes the column number c of the cell. It is represented by alphabetical letters.
!   For example, the 1st column is denoted by 'A', the 2nd by 'B', the 3rd by 'C', and so on.
! - is the row number r of the cell. The rth row is represented by the integer r.
!
! You are given a string s in the format ": ", where represents the column c1, represents the row r1, represents the column c2, and represents the row r2, such that r1 <= r2 and c1 <= c2.
!
! Return the list of cells (x, y) such that r1 <= x <= r2 and c1 <= y <= c2. The cells should be represented as strings in the format mentioned above and be sorted in non-decreasing order first by columns and then by rows.
!
! Example 1:
! Input: s =  "K1:L2 "
! Output: ["K1 ", "K2 ", "L1 ", "L2 "]
! Explanation:
! The above diagram shows the cells which should be present in the list.
! The red arrows denote the order in which the cells should be presented.
!
! Example 2:
! Input: s =  "A1:F1 "
! Output: ["A1 ", "B1 ", "C1 ", "D1 ", "E1 ", "F1 "]
! Explanation:
! The above diagram shows the cells which should be present in the list.
! The red arrow denotes the order in which the cells should be presented.
!
! Constraints:
! s.length == 5
! 'A' <= s[0] <= s[3] <= 'Z'
! '1' <= s[1] <= s[4] <= '9'
! s consists of uppercase English letters, digits and ':'.

implicit none

character(len=5), parameter :: s = "K1:L2"
type(cell_range) :: range
type(cell_list) :: list

call get_range(s, range)
call get_list(range, list)

print "(A)", list%to_string()

contains

subroutine get_range(s, range)

! Get the range from the input string.

implicit none

character(len=*), intent(in) :: s
type(cell_range), intent(out) :: range

integer :: i, j, k, l

i = index(s, ":")
j = scan(s, ":", back=.true.)
k = scan(s, ":")
l = len_trim(s)

if (i /= 2 .or. j /= l - 1) then
    stop "Invalid input string."
end if

range%start%row = s(3:i-1)
range%start%col = s(1:2)
range%end%row = s(i+1:j-1)
range%end%col = s(k+1:l)

end subroutine get_range

function get_list(range, list) result(new_list)

! Get the list of cells from the range.

implicit none

type(cell_range), intent(in) :: range
type(cell_list), intent(in) :: list
type(cell_list) :: new_list

integer :: i, j, k, n
logical :: found

n = size(list%cells)
do i = 1, n
    do j = 1, n
        if (list%cells(i)%row >= range%start%row .and. &
            list%cells(i)%row <= range%end%row .and. &
            list%cells(i)%col >= range%start%col .and. &
            list%cells(i)%col <= range%end%col) then
            call add_cell(list%cells(i), new_list)
            exit
        end if
    end do
end do

end function get_list

subroutine add_cell(cell, list)

! Add a cell to the list
❌ Compiled
❌ Executed
❌ Correct 
program excel_cells
      implicit none

      character(len=5) :: s
      character(len=2) :: c1, c2
      integer :: r1, r2
      integer :: i, j

      read(*,*) s

      ! Extract the column numbers
      c1 = s(1:2)
      c2 = s(3:4)

      ! Extract the row numbers
      read(s(5:6),*) r1, r2

      ! Initialize the list of cells
      allocate(cells(r2-r1+1, c2-c1+1))

      ! Fill the list of cells
      do i = r1, r2
         do j = c1, c2
            write(cells(i-r1+1,j-c1+1),'(A,I0.2)') c(j), i
         end do
      end do

      ! Sort the list of cells
      call sort_cells(cells, r2-r1+1, c2-c1+1)

      ! Print the list of cells
      do i = 1, r2-r1+1
         do j = 1, c2-c1+1
            write(*,'(A)') cells(i,j)
         end do
      end do

      contains

      subroutine sort_cells(cells, m, n)
      implicit none
      character(len=2), intent(inout) :: cells(m,n)
      integer, intent(in) :: m, n
      integer :: i, j, k
      character(len=2) :: temp

      ! Sort the cells by columns
      do i = 1, n
         do j = 1, m-1
            if (cells(j,i) > cells(j+1,i)) then
               temp = cells(j,i)
               cells(j,i) = cells(j+1,i)
               cells(j+1,i) = temp
            end if
         end do
      end do

      ! Sort the cells by rows
      do i = 1, m
         do j = 1, n-1
            if (cells(i,j) > cells(i,j+1)) then
               temp = cells(i,j)
               cells(i,j) = cells(i,j+1)
               cells(i,j+1) = temp
            end if
         end do
      end do

      end subroutine sort_cells

      end program excel_cells

      function c(i) result(c)
      implicit none
      integer, intent(in) :: i
      character(len=2) :: c

      ! Convert the integer to a column string
      if (i < 27) then
         c = char(i+64)
      else
         c = char(i/26+64)
         c = c // char(mod(i,26)+64)
      end if

      end function c

      end
🌐 Data from online sources 
def cell_range(s):
    r1 = int(s[1])
    r2 = int(s[4])
    c1 = ord(s[0]) - ord('A') + 1
    c2 = ord(s[3]) - ord('A') + 1

    res = []
    for c in range(c1, c2 + 1):
        for r in range(r1, r2 + 1):
            res.append(chr(ord('A') + c - 1) + str(r))
    return res

The algorithm is relatively straightforward: First, extract each cell's row and column from the input string s using indexed access on the input string, which works identically in all languages. The row values are parsed from a character to the integer it represents. The column values need to be zero-indexed and then shifted by 1, so subtract the ASCII value of 'A' (or 'A'.charCodeAt(0) in JavaScript) and then add 1.

Next, create a nested loop to iterate over all columns (c) and rows (r). In the inner loop, push the column and row as a formatted string to the results array, reusing the method from the column extraction to create the column character for output.

Finally, return the array of sorted cell strings.

🌐 Data from online sources 
#include <vector>
#include <string>

std::vector<std::string> cellRange(const std::string& s) {
    int r1 = s[1] - '0';
    int r2 = s[4] - '0';
    int c1 = s[0] - 'A' + 1;
    int c2 = s[3] - 'A' + 1;

    std::vector<std::string> res;
    for (int c = c1; c <= c2; ++c) {
        for (int r = r1; r <= r2; ++r) {
            res.push_back(std::string(1, 'A' + c - 1) + std::to_string(r));
        }
    }
    return res;
}

The algorithm is relatively straightforward: First, extract each cell's row and column from the input string s using indexed access on the input string, which works identically in all languages. The row values are parsed from a character to the integer it represents. The column values need to be zero-indexed and then shifted by 1, so subtract the ASCII value of 'A' (or 'A'.charCodeAt(0) in JavaScript) and then add 1.

Next, create a nested loop to iterate over all columns (c) and rows (r). In the inner loop, push the column and row as a formatted string to the results array, reusing the method from the column extraction to create the column character for output.

Finally, return the array of sorted cell strings.