Load file excel

require_once "phpexcel/Classes/PHPExcel.php"; $tmpfname = "BC tuan 17.8-23.8.18.xls"; $excelReader = PHPExcel_IOFactory::createReaderForFile($tmpfname); $excelObj = $excelReader->load($tmpfname);

PHPExcel how to get column index from cell

$colIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());

Convert Number to Column Name

Bắt đầu từ 0

function getNameFromNumber($num) { $numeric = $num % 26; $letter = chr(65 + $numeric); $num2 = intval($num / 26); if ($num2 > 0) { return getNameFromNumber($num2 - 1) . $letter; } else { return $letter; } } echo getNameFromNumber(728);

Bắt đầu từ 1

function getNameFromNumber($num) { $numeric = ($num - 1) % 26; $letter = chr(65 + $numeric); $num2 = intval(($num - 1) / 26); if ($num2 > 0) { return getNameFromNumber($num2) . $letter; } else { return $letter; } } echo getNameFromNumber(1462);

Get Sheet by index

$worksheet = $excelObj->getSheet($sheetnum); $worksheet = $excelObj->getSheet(4);

Get Sheet by name

$worksheet = $excelObj->getSheetByName('Red Rose');

Get Max column

$worksheet->getHighestDataColumn();

Get Max row

$worksheet->getHighestRow();

Get Cell value

$worksheet->getCell($col.$row)->getValue(); $worksheet->getCell('B'.$row)->getValue();

Get Cell Format value

$worksheet->getCell($col.$row)->getFormattedValue(); $worksheet->getCell('B'.$row)->getFormattedValue();

Một số hàm mình tự viết để sử dụng trong quá trình làm việc với Excel

Hàm lấy giá trị một cell

function getcell_value($excelObj,$sheetnum,$col,$row){ if(is_numeric($sheetnum)){ $worksheet = $excelObj->getSheet($sheetnum); }else{ $worksheet = $excelObj->getSheetByName($sheetnum); } $v=$worksheet->getCell($col.$row)->getValue(); return $v; }

Hàm lấy giá trị của formatted cell

function getcell_Formatvalue($excelObj,$sheetnum,$col,$row){ if(is_numeric($sheetnum)){ $worksheet = $excelObj->getSheet($sheetnum); }else{ $worksheet = $excelObj->getSheetByName($sheetnum); } $v=$worksheet->getCell($col.$row)->getFormattedValue(); return $v; }

Hàm lấy max row

function getMaxrow($excelObj,$sheetnum){ if(is_numeric($sheetnum)){ $worksheet = $excelObj->getSheet($sheetnum); }else{ $worksheet = $excelObj->getSheetByName($sheetnum); } return $worksheet->getHighestRow(); }

Hàm lấy max column

function getMaxcol($excelObj,$sheetnum){ if(is_numeric($sheetnum)){ $worksheet = $excelObj->getSheet($sheetnum); }else{ $worksheet = $excelObj->getSheetByName($sheetnum); } return $worksheet->getHighestDataColumn(); }

Ngoài ra có một số hàm khác mình sưu tầm được

$alphabet = range('A', 'Z'); echo $alphabet[3]; // returns D echo array_search('D', $alphabet); // returns 3 $col = 341; print (($n = (int)(($col - 1) / 26)) ? chr($n + 64) : '') . chr((($col - 1) % 26) + 65);

Tham khảo: https://stackoverflow.com/questions/3458978/phpexcel-how-to-get-column-index-from-cell   -soiqualang_chentreu-]]>