I want to generate the coordinates for this
$sheet->setCellValue($pCoordinate, $pValue);
a $pCoodrinates is a letter and then a number like A1, B1 , C1 , that is for example the first row then next row is A2, B2, C2 and the next row with 3
This is the code that I have right now
class SpreadSheetHelper { private static $alphabet = 'ABCDEFGHIJKLMNOPQRSTUYVWXYZ'; public static function createSpreadSheet($data = []) { $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); foreach ($data as $rowIndex => $row) { foreach ($row as $columnIndex => $columnValue) { $pCoordinate = self::getAlphabetCoordinate($rowIndex, $columnIndex); $pValue = $columnValue; $sheet->setCellValue($pCoordinate, $pValue); } } return $spreadsheet; } private static function getAlphabetCoordinate($rowIndex, $columnIndex) { $letter = strtoupper(substr(self::$alphabet, $columnIndex, 1)); $number = $rowIndex + 1; return "$letter$number"; } }
As you can see the $alphabet is hardcoded and is limited and it reaches the last letter it should start with AA, AB, AC,AD,AE,AF , and this is what I want to generate . Any idea how to do it ?
Advertisement
Answer
You could take advantage of the ASCII-table:
<?php //Number of rows and columns $rows = 3; $cols = 40; $pcoords = array(); for($current_row=1;$current_row<$rows+1;$current_row++) { $alpha_index = 65; $alpha_pref_index = 65; $alpha_count = 0; $pref_letter = ''; for($current_col=0;$current_col<$cols;$current_col++) { $col_letter = chr($alpha_index); $pcoords[] = $pref_letter . $col_letter. $current_row; $alpha_count++; if ($alpha_count == 26) { $alpha_count = 0; $alpha_index = 65; $pref_letter = chr($alpha_pref_index); $alpha_pref_index++; } else { $alpha_index++; } } } echo '<pre>'; print_r($pcoords); echo '</pre>';