Задача вкратце: нужно для указанного диапазона ячеек Экселя вывести массив номеров этих ячеек, отсортированный по столбцам/строкам. То есть для A1:B2 ответ будет: [ "A1", "B1", "A2", "B2"]
Тривиально? Вроде, да. Но сложности добавляет “дурацкая” нумерация столбцов в Экселе: тут после столбца Z идет столбец AA, далее AB и т.п. Ну то есть может быть такой столбец: ABXYT
То есть нам нужно уметь конвертировать строковые номера столбцов в числовые и обратно, чтобы решить данную задачу. В остальном — все просто. Поехали.
Задачка на CodeWars: https://www.codewars.com/kata/62c376ce1019024820580309/train/php
Постановка и описание решения будет на английском, пардон.
/**/
Source
https://www.codewars.com/kata/62c376ce1019024820580309
Task
Your task is to write a getCellAddresses(range) function that takes a range of Google Sheets cells as a parameter, and returns an array of addresses of all cells in the specified range.
Letters in addresses: from A-Z (Google Sheets should not be confused with Excel spreadsheets. In Excel, the «Z» column is followed by «AA», etc. In Google Sheets, the last column is «Z»)
The final array must be sorted by the number in each address (ascending)
The letters in the addresses must go in alphabetical order (A to Z).
Example
For example, if the range is «A1:C3«, the function should return:
[ "A1", "B1", "C1", "A2", "B2", "C2", "A3", "B3", "C3" ],
not
[ "B1", "A1", "C1", "C2", "B2", "A2", "A3", "C3", "B3" ]
or something else.
If an invalid range is passed to the function, the function should return []. The ranges that are considered to be invalid:
Those in which the first cell is further away than the second. Example: H7:F3 Those in which two identical cells are specified.
Example: C2:C2 In Google Sheets you cannot find such a range, because it is considered as one cell.
For this reason, such a range is invalid in this kata.
More examples
Example 1
"B1:H5": [ "B1", "C1", "D1", "E1", "F1", "G1", "H1", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "B3", "C3", "D3", "E3", "F3", "G3", "H3", "B4", "C4", "D4", "E4", "F4", "G4", "H4", "B5", "C5", "D5", "E5", "F5", "G5", "H5" ]
Example 2
"A2:B3": [ "A2", "B2", "A3", "B3" ]
Example 3
"A1:A5": [ "A1", "A2", "A3", "A4", "A5" ]
Example 4
"X8:Z20": [ "X8", "Y8", "Z8", "X9", "Y9", "Z9", "X10", "Y10", "Z10", "X11", "Y11", "Z11", "X12", "Y12", "Z12", "X13", "Y13", "Z13", "X14", "Y14", "Z14", "X15", "Y15", "Z15", "X16", "Y16", "Z16", "X17", "Y17", "Z17", "X18", "Y18", "Z18", "X19", "Y19", "Z19", "X20", "Y20", "Z20" ]
Example 5
"E345:J346": [ "E345", "F345", "G345", "H345", "I345", "J345", "E346", "F346", "G346", "H346", "I346", "J346" ]
Important!
Here is cells range as AB3:AY3 (consists of two and more letters: after Z1 is AA1, you should now it)
My solution
Here is a PHP function to get all cell addresses in a given Google Sheets range: see range.php
It uses a regex to parse the range, validates it, then generates all cell addresses row by row and column by column.
The addresses are sorted alphabetically at the end.
This allows handling all valid Google Sheets ranges correctly.
Usage / run
# run demo calcs: php run.php # run tests: phpunit RangeTest.php phpunit FuncsTest.php
SOLUTION
ExcelRange.php
<?php
/**
* Excel cells range
* @see https://www.codewars.com/kata/62c376ce1019024820580309/train/php
* @see Readme.md
*
* @fixme: make chg to use cells addr like AA1 ... ZZ3
*/
class ExcelRange
{
const ORD_START = 64; // 65 is 'A' position in alphabet
const LETTERS_COUNT = 26; // 26 is eng alphabet letters count
public function getCellAddresses(string $range): ?array
{
$matrix = $this->getCellAddressesMatrix($range);
if($matrix === []) {
return [];
}
//$this->printCellsMatrix($matrix);
$res = [];
foreach($matrix as $row) {
foreach($row as $cell) {
$res[] = $cell;
}
}
return $res;
}
/**
* Get cells addresses
* @param $range
* @return array
*/
public function getCellAddressesMatrix(string $range): ?array
{
$range = $this->getRange($range);
//print "Cells range: "; var_dump($range);
if ($range == []) {
return [];
}
list($startCol, $startRow, $endCol, $endRow) = $range;
$result = [];
for ($row = $startRow; $row <= $endRow; $row++) {
for ($col = $startCol; $col <= $endCol; $col++) {
$letter = $this->calcColLetter($col);
$result[$row - 1][] = $letter . $row;
}
}
return $result;
}
/**
* Get cells range as array
* @param string $range
* @return array
*/
public function getRange(string $range): array
{
if (!preg_match('/^([A-Z]+)(d+):([A-Z]+)(d+)$/', $range, $matches)) {
return [];
}
list($_, $startCol, $startRow, $endCol, $endRow) = $matches;
$startCol = $this->calcColNum($startCol);
$endCol = $this->calcColNum($endCol);
// validations
if ($startCol > $endCol || $startRow > $endRow) {
return [];
}
if ($startCol == $endCol && $startRow == $endRow) {
return [];
}
return [(string)$startCol, (int)$startRow, (string)$endCol, (int)$endRow];
}
/**
* Calc excel column number by it's letter: B ~ 2, AA ~ 27, etc.
* @param string $letter
* @return int
*/
public function calcColNum(string $letter): int
{
$sum = 0;
$len = strlen($letter);
for($i = 0; $i < $len; $i++) {
$num = (ord($letter[$i]) - self::ORD_START) * pow(self::LETTERS_COUNT, $len - $i - 1);
$sum += $num;
}
return $sum;
}
/**
* Opposite to calcColNum: calc column letter by it's number
* @param int $num
* @return string
*/
public function calcColLetter(int $num): string
{
$col = '';
while($num > 0) {
$rem = ($num - 1) % self::LETTERS_COUNT;
$col = chr(self::ORD_START + 1 + $rem) . $col;
$num = intdiv($num - 1, self::LETTERS_COUNT);
}
return $col;
}
/**
* @param $arr
* @return void
*/
public function printCellsMatrix(array $arr): void
{
foreach($arr as $row) {
foreach($row as $cell) {
print $cell . " ";
}
echo "n";
}
}
}
/**
* @param string $range
* @return array
*/
function getCellAddresses(string $range): ?array
{
$sheet = new ExcelRange();
$cells = $sheet->getCellAddresses($range);
//$sheet->printCells($cells);
return $cells;
}
Скриптик для проверки: run.php
<?php
require_once("ExcelRange.php");
$cells = getCellAddresses("W118:Z124");
var_dump($cells);
Тесты для утилитарных функций: FuncsTest.php
<?php
/**
* Test ExcelRange.php script
*/
use PHPUnitFrameworkTestCase;
require_once("ExcelRange.php");
class FuncsTest extends TestCase
{
/**
* @return array
*/
public static function calcColLetterProvider(): array
{
return [
"1 ~ A" => [1, "A"],
"26 ~ Z" => [26, "Z"],
"27 ~ AB" => [27, "AA"],
"29 ~ AC" => [29, "AC"],
"728 ~ AAZ" => [728, "AAZ"],
];
}
/**
* @return array
*/
public static function calcColNumProvider(): array
{
return [
"A ~ 1" => ["A", 1],
"Z ~ 26" => ["Z", 26],
"AA ~ 27" => ["AA", 27],
"AC ~ 29" => ["AC", 29],
"AAZ ~ 728" => ["AAZ", 728],
];
}
/**
* @dataProvider calcColLetterProvider
*/
public function testCalcColLetter(int $input, string $expected)
{
$obj = new ExcelRange();
$this->assertEquals($expected, $obj->calcColLetter($input));
}
/**
* @dataProvider calcColNumProvider
*/
public function testCalcColNum(string $input, int $expected)
{
$obj = new ExcelRange();
$this->assertEquals($expected, $obj->calcColNum($input));
}
/**
* Trivial test
* @return void
*/
public function testStart()
{
$this->assertEquals(true, true);
}
}
Тесты приемочные: RangeTest.php
<?php
/**
* Test ExcelRange.php script
*/
use PHPUnitFrameworkTestCase;
require_once("ExcelRange.php");
class RangeTest extends TestCase
{
public static function basicTestsProvider()
{
return [
'Range A1:A10' => ['A1:A10', [
'A1', 'A2', 'A3', 'A4', 'A5',
'A6', 'A7', 'A8', 'A9', 'A10'
]],
'Range A1:Z1' => ['A1:Z1', [
'A1', 'B1', 'C1', 'D1', 'E1',
'F1', 'G1', 'H1', 'I1', 'J1',
'K1', 'L1', 'M1', 'N1', 'O1',
'P1', 'Q1', 'R1', 'S1', 'T1',
'U1', 'V1', 'W1', 'X1', 'Y1',
'Z1'
]],
'Range F12:J17' => ['F12:J17', [
'F12', 'G12', 'H12', 'I12',
'J12', 'F13', 'G13', 'H13',
'I13', 'J13', 'F14', 'G14',
'H14', 'I14', 'J14', 'F15',
'G15', 'H15', 'I15', 'J15',
'F16', 'G16', 'H16', 'I16',
'J16', 'F17', 'G17', 'H17',
'I17', 'J17'
]],
'Range B3:D5' => ['B3:D5', [
'B3', 'C3', 'D3',
'B4', 'C4', 'D4',
'B5', 'C5', 'D5'
]],
'Range A1:B2' => ['A1:B2', ['A1', 'B1', 'A2', 'B2']],
'Range W118:Z124' => ['W118:Z124', [
'W118', 'X118', 'Y118', 'Z118',
'W119', 'X119', 'Y119', 'Z119',
'W120', 'X120', 'Y120', 'Z120',
'W121', 'X121', 'Y121', 'Z121',
'W122', 'X122', 'Y122', 'Z122',
'W123', 'X123', 'Y123', 'Z123',
'W124', 'X124', 'Y124', 'Z124'
]],
'Range H7:F3 (invalid)' => ['H7:F3', []],
'Range C2:C2 (invalid)' => ['C2:C2', []],
];
}
/**
* @dataProvider basicTestsProvider
*/
public function testBasic(string $input, array $expected)
{
$this->assertEquals($expected, getCellAddresses($input));
}
}
Проверяем
Отсылаем решение: только содержимое файла ExcelRange.php
Убеждаемся что все тесты пройдены:
Бинго ) на решение задачи у меня ушло аж 4 часа. А сколько потратили Вы? Пишите в комментах.
PS: кодить на php и решать подобые задачки вас научат на курсах “Otus php”
Нет Ответов