PHPExcel code stuff

Beginning of work with it:

$objPHPExcel = new PHPExcel(); 
$objPHPExcel->setActiveSheetIndex(0); 
$sheet = $objPHPExcel->getActiveSheet();

Fill the row with an array:

$sheet->fromArray($arraydata,null,"A1");

Put defined value into the defined cell:

$sheet->setCellValue('A1', 'Hi, people');

Freeze panel:

$sheet->freezePane('D2');

Make text bold and with underline:

$sheet->getStyle('A1:'.$sheet->getHighestColumn().'1')
      ->getFont()
      ->setBold(true)
      ->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

Loading PHPExcel for processing a xlsx file:

$objPHPExcel = PHPExcel_IOFactory::load($_FILES['file']['tmp_name']); 
    $highestRow = $objPHPExcel->getSheet(0)->getHighestRow(); 
    $highestColumn = $objPHPExcel->getSheet(0)->getHighestColumn(); 
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

Get a value of the defined cell:

$objPHPExcel->getSheet(0)->getCellByColumnAndRow(3, 1)->getValue()

How to transform Literal index of cell in Excel file into number, which could be used in the loops for retrieving cell values, for example:

public function lettersToNumber($letters){ 
    $alphabet = range('A', 'Z'); 
    $number = 0; 
    foreach(str_split(strrev($letters)) as $key=>$char){ 
        $number = $number + (array_search($char,$alphabet)+1)*pow(count($alphabet),$key); 
    } 
    return $number; 
}

Leave a Reply