Skip to content

Instantly share code, notes, and snippets.

@barryvdh

barryvdh/Excel.php

Last active December 23, 2024 18:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save barryvdh/7989606 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save barryvdh/7989606 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. barryvdh revised this gist Apr 2, 2014. 1 changed file with 24 additions and 30 deletions.
    54 changes: 24 additions & 30 deletions Excel.php
    View file
    Original file line number Diff line number Diff line change
    @@ -20,36 +20,30 @@ public function setActiveSheetIndex($index){
    $this->objPHPExcel->setActiveSheetIndex($index);
    }

    public function toArray(){
    $keys = array();
    $items = array();
    $worksheet = $this->objPHPExcel->getActiveSheet();
    $highestRow = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, 1);
    $val = $cell->getFormattedValue();
    $keys[$col] = trim($val);
    }


    for ($row = 2; $row <= $highestRow; ++ $row) {
    $item = array();
    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);
    $val = $cell->getValue();
    $key = $keys[$col];
    $item[$key] = $val;
    }
    $items[] = $item;
    }

    return $items;
    /**
    * Create array from worksheet
    *
    * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
    * @param boolean $calculateFormulas Should formulas be calculated?
    * @param boolean $formatData Should formatting be applied to cell values?
    * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
    * True - Return rows and columns indexed by their actual row and column IDs
    * @return array
    */
    public function toArray($nullValue = null, $calculateFormulas = true, $formatData = false){

    $rows = $this->objPHPExcel->getActiveSheet()->toArray($nullValue,$calculateFormulas,$formatData,false);
    $headers = array_shift($rows);

    array_walk($rows, function(&$values) use($headers){
    $values = array_combine($headers, $values);
    });

    return $rows;
    }

    public function toJson($options = 0){
    return json_encode($this->toArray(), $options);


    public function toJson($options = 0, $nullValue = null, $calculateFormulas = true, $formatData = false){
    return json_encode($this->toArray($nullValue,$calculateFormulas,$formatData), $options);
    }
    }
  2. barryvdh revised this gist Jan 2, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Excel.php
    View file
    Original file line number Diff line number Diff line change
    @@ -30,7 +30,7 @@ public function toArray(){

    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, 1);
    $val = $cell->getValue();
    $val = $cell->getFormattedValue();
    $keys[$col] = trim($val);
    }

  3. barryvdh created this gist Dec 16, 2013.
    55 changes: 55 additions & 0 deletions Excel.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,55 @@
    <?php

    use Illuminate\Support\Contracts\ArrayableInterface;
    use Illuminate\Support\Contracts\JsonableInterface;
    class Excel implements ArrayableInterface, JsonableInterface{

    protected $objPHPExcel;
    public function __construct($file){
    if($file instanceof \SplFileInfo){
    $filename = $file->getRealPath();
    }else{
    $filename = $file;
    }

    $this->objPHPExcel = PHPExcel_IOFactory::load($filename);
    $this->objPHPExcel->setActiveSheetIndex(0);
    }

    public function setActiveSheetIndex($index){
    $this->objPHPExcel->setActiveSheetIndex($index);
    }

    public function toArray(){
    $keys = array();
    $items = array();
    $worksheet = $this->objPHPExcel->getActiveSheet();
    $highestRow = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, 1);
    $val = $cell->getValue();
    $keys[$col] = trim($val);
    }


    for ($row = 2; $row <= $highestRow; ++ $row) {
    $item = array();
    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);
    $val = $cell->getValue();
    $key = $keys[$col];
    $item[$key] = $val;
    }
    $items[] = $item;
    }

    return $items;
    }

    public function toJson($options = 0){
    return json_encode($this->toArray(), $options);
    }
    }
    6 changes: 6 additions & 0 deletions example.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,6 @@
    <?php
    //Assuming you have autoloaded everything via PSR-0 or whatever

    $excel = new Excel('path/to/file.xls');

    var_dump($excel->toArray());