CODEWITHSUNDEEP
phpphpexcel
user4401361
user4401361

Reputation:

PHPEXCel Read only one sheet to Array

I am using PHPExcel to only read values from excel sheets if i use this code , it works fine without no problem:

  function ReadUploadedFile($Uploadedfile,$fileExtension)
    {
            class MyReadFilter implements PHPExcel_Reader_IReadFilter
            {
                public function readCell($column, $row, $worksheetName = '')
                 {
                    // Read rows 1 to 7 and columns A to E only
                    if ($row>=1 && $row<=100) {
                        if (in_array($column,range('A','Z'))) {
                            return true;
                        }
                    }
                    return false;
                }
            }
}

        $filterSubset = new MyReadFilter();
        $inputFileType="";

        $inputFileType = 'Excel5';
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objReader->setReadFilter($filterSubset);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load('myExcelsheet.xls');
        $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$sheetData now is an array and i can use it with no problem.

what if i have many worksheets ,and i need to specify only one , as per documentation from PHPEXCEL , they say to use setLoadSheetsOnly()

i try the code blow but it doesn't work.

$inputFileType = 'Excel2007';   
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadFilter($filterSubset);
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly("Summary"); //my worksheet name is Summary
$objPHPExcel = $objReader->load('myExcelsheet.xlsx');

so what should i write after the above line to convert this object to Array i try this 

$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

but it gives this error

Call to a member function cellExists() on a non-object

and when i try this 

$sheetData = $objPHPExcel->toArray(null,true,true,true);

Call to undefined method PHPExcel::toArray()

Upvotes: 0

Views: 25479

Answers (2)

bamossza
bamossza

Reputation: 3926

Work for me.

I'm using: rangeToArray();

"phpoffice/phpspreadsheet": "^1.3"

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load('report.xlsx');
$worksheet = $spreadsheet->setActiveSheetIndex(0);
$highestRow = $worksheet->getHighestRow();
$highestCol = $worksheet->getHighestColumn();

print_r($worksheet->rangeToArray("A4:$highestCol$highestRow", null, true, false, false));

// If you want to format data e.g. 2450 to 2,450
// You could set rangeToArray parameter at 4 = true

enter image description here

Upvotes: 2

Antonio Oramas
Antonio Oramas

Reputation: 31

/** Here is my code work: */

ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br/>');

/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$inputFileType = 'Excel2007';
$inputFileName = 'file.xlsx';
$sheetname = 'mysheey'; // I DON'T WANT TO USE SHEET NAME HERE

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
echo ' Highest Column ' . $getHighestColumn = $objPHPExcel->setActiveSheetIndex()->getHighestColumn(); // Get Highest Column
echo ' Get Highest Row ' . $getHighestRow = $objPHPExcel->setActiveSheetIndex()->getHighestRow(); // Get Highest Row

echo "<pre>";
print_r($sheetData);
echo "</pre>";

Upvotes: 3

Related Questions