How to get xls cell position in PHP

userF4al1 0
Viewed 23

I am trying to import data from xls file,And for this i am using "spreadsheet library" and i am getting data successfully in following format (array)

[0] => Array
        (
            [0] => ABC
            [1] => XYZ
            ...
         )
[1] => Array
        (
            [1] => ADW
        )
...

Now i want to save "Cell" value into database,For example in "xls" file, "ABC" position is "A1" And "XYZ" is "B1",So "ADW" position is "A2",So how can i get "Cell Position" using Php ?

foreach php
1 Answers

You could compute cell position from the indexes of the array, with rows starting at 1 at index 0 and columns starting at A at index 0:

$data = array (
    0 => 
    array (
        0 => 'ABC',
        1 => 'XYZ'
    ),
    1 => 
    array (
        1 => 'ADW',
        20 => 'XYZ',
        26 => 'AA2',
        30 => 'PQR',
        60 => 'WWW',
        701 => 'ZZZ'
    )
);

$result = array();

foreach ($data as $rownum => $row) {
    foreach ($row as $colnum => $value) {
        if ($colnum >= 26) {
            $col0 = $colnum % 26;
            $col1 = intdiv($colnum, 26);
            $colstr = chr(64 + $col1) . chr(65 + $col0);
        }
        else {
            $colstr = chr(65+$colnum);
        }
        $result[$colstr . ($rownum+1)] = $value;
    }
}

print_r($result);

Output (for my sample data):

Array
(
    [A1] => ABC
    [B1] => XYZ
    [B2] => ADW
    [U2] => XYZ
    [AA2] => AA2
    [AE2] => PQR
    [BI2] => WWW
    [ZZ2] => ZZZ
)

Demo on 3v4l.org

usergAKD0
Related