Repeat alphabet similar to excel columns

Question

I am creating a function that returns the alphabet to me in an array according to the size of columns passed.

At first I was only making a loop using the range("A", "Z"); but when I had more than 26 columns no longer attended me, with this, searching the internet, what I found was a suggestion here http://forum.imasters.com.br/topic/532733-repetir-o-alfabeto-varias-vezes-como-as-colunas-do-excel/ given by the reply of Willian Bruno, but that is also not very correct, at least it did not work here, dai using as a base I created mine only that also does not return me the expected, that would be the alphabet (a-z, aa-zz, aaa-zzz), similar to the columns of excel.

follow what I’m doing.

function alphaRepeat($columnsSize = 10)
{
    $i = 0;
    $j = 0;

    $abc = range("A", "Z");
    $abcSize = count($abc);

    $alpha = array();

    while ($i < $columnsSize) {

        if (((int)($i / $abcSize) - $j) > 0) {
            $j++;
        }

        $item = '';

        if ($j > 0) {
            $item.= str_repeat($abc[$j - 1], $j);
        }
        $item.= $abc[$i - ($abcSize * $j)];

        $alpha[] = $item;

        $i++;
    }

    return $alpha;
}

Answer 1

You can increment a string by its ASCII value in php as long as it is in the valid interval of (65..90 A..Z, 97..122 a..z) so`range('A', 'Z') works, here you have it more detailed explanation.

You can create a function to encapsulate this code, $maximo is number informed by the user, $inicial can be a or A

Ideone example

<?php

$inicial = 'a';

$maximo = 27;
$atual = 0;

$colunas = array();
while($maximo > $atual){
    $colunas[] = $inicial++;
    $atual++;
}

Exit:

Array
(
    [0] => a
    [1] => b
    [2] => c
    [3] => d
    [4] => e
    [5] => f
    [6] => g
    [7] => h
    [8] => i
    [9] => j
    [10] => k
    [11] => l
    [12] => m
    [13] => n
    [14] => o
    [15] => p
    [16] => q
    [17] => r
    [18] => s
    [19] => t
    [20] => u
    [21] => v
    [22] => w
    [23] => x
    [24] => y
    [25] => z
    [26] => aa
)

Answer 2

Follow a formula where you pass the column number, and get the corresponding string. Can be used to get any column, no need to calculate the previous ones.

It’s an interesting add-on to @rray’s response if you need to do multiple operations in non-consecutive columns, such as a spreadsheet update, or convert internal coordinates to coordinates to display on the screen (imagine showing a view partial of a spreadsheet, or a formula with internal references).

function CabecalhoPlanilha( $numero ) {
    $string = '';
    while( $numero ) {
        $string = chr( 65 /* 97 para minusculas */ + ( --$numero % 26 ) ) . $string;
        $numero = (int) ( $numero / 26 );
    }
    return $string;
}

Demo on IDEONE

Answer 3

You can do it like this:

for ($a = 'a';  $a != 'aaa'; $a++) {
    $colunas[] = $a;
}

This will generate a array with a sequence of a until zz.

If you need to set a limit, you can do so:

function create_cell_range($limit) {

     for ($a = 'a', $current = 0; $current < $limit; $a++,$current++) 
     {
          $columns[] = $a;
     }

    return $columns;
}


var_dump(create_cell_range(3)); //array(3){'a', 'b', 'c'}

If you are using versions of PHP higher than 5.5, do not recommend using array. In that case, it’s best to use a Generator.

Thus:

 function create_cell_range($limit) {

     for ($a = 'a', $current = 0; $current < $limit; $a++,$current++) 
     {
          yield $current => $a;
     }

}

Therefore, when necessary, it is only necessary to use foreach.

Thus:

foreach(create_cell_range(27) as $key => $value) {
     echo "$key => $value";
}

The generators however represent an absurd economy of memory, since it does not create an item for each element of a array, and yes sequentially returns an item when needed.

Answer 4

You can do it this way too :

function numberToColumnName($number){
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $abc_len = strlen($abc);

    $result = "";
    $tmp = $number;

    while($number > $abc_len) {
        $remainder = $number % $abc_len;
        $result = $abc[$remainder-1].$result;
        $number = floor($number / $abc_len);
    }
    return $abc[$number-1].$result;
}

for($i = 0; $i < 200; $i++){
    echo numberToColumnName($i)."\n";
}

See working on Ideone