CODEWITHSUNDEEP
phpphpexcel
jision
jision

Reputation: 181

How to read a downdown values in an Excel using PHPExcel

I have a excel file where one column has a dropdown values.I am using PhpExcel so i need to read the dropdown values from it.How can this be achieved.

when i am reading the rows as 

$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE);

I m just getting the text when i dump the row i need the dropdown values too.

Any help will be appreciated.

Upvotes: 0

Views: 1534

Answers (2)

Ken Lee
Ken Lee

Reputation: 8073

The Phpexcel's getformula1() seems having bugs. I come across the same problem (it returns nothing even the cell is having formula, strange... ).

I have double-checked and found that Phpspreadsheet (PHPSpreadsheet is the newest version of PHPExcel) have fixed this and it works

So assuming that the filename is "test1.xlsx", the cell with dropdown is Sheet1!B1 and the dropdown list is Sheet2!C2:C5, then the code will be :

enter image description here

enter image description here

<?php

//require_once('./PHPExcel1.8/Classes/PHPExcel.php');
//include './PHPExcel1.8/Classes/PHPExcel/IOFactory.php';

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();


$file = 'test1.xlsx';

$Excel = $reader->load($file);

$validation = $Excel->getSheetByName('Sheet1')->getCell('B1')->getDataValidation();

echo $validation->getformula1();
echo "<br>";

$validation2 = $Excel->getSheetByName('Sheet2')->rangeToArray('C2:C5');


$index=0;
while ($index < count($validation2))
{
    echo $validation2[$index][0];
    echo "<br>";
    $index++;
}

In the above code, getformula1 will give you Sheet2!$C$2:$C$5 so you can easily parse it and use the information in the line rangeToArray()

The last while loop above will display all the data in the array.

Amend the code to suit your needs. Enjoy.

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212452

If you need all the values from a cell dropdown, not simply the selected value, then you have to read the dropdown itself. This is a datavalidation object.

Assuming that the dropdown is in cell B1:

$objValidation = $objPHPExcel->getActiveSheet()
    ->getCell('B1')
    ->getDataValidation();

will return the data validation object

You can then read the rules for that data validation using methods like getType(), getOperator(), getFormula1() and getFormula2().... note that not all data validations are dropdowns; you'd be looking for a type of PHPExcel_Cell_DataValidation::TYPE_LIST for a dropdown list. You might also need to use the calculation engine to evaluate the formula used to build the list if it was derived from data elsewhere in the spreadsheet.

Alternatively, because it's unclear from your question, it could be a autofilter dropdown that you're asking about. You can retrieve the autofilter object for a worksheet using:

$autoFilter = $objPHPExcel->getActiveSheet()
    ->getAutoFilter();

Again, there are a series of methods for accessing the filter rules for each column.

$autoFilter->getColumn('C');

Will return the autofilter rules for column C, with methods like getFilterType() and getRules() providing the rule definitions for that autofilter.

The autofilter examples like 10autofilter-selection-display.php show how to define autofilters; but there is also a useful showHideRows() method that can be used the filter to hide/display the rows that match those rules

Upvotes: 2

Related Questions