CODEWITHSUNDEEP
phpphpexcel
Ryan_W4588
Ryan_W4588

Reputation: 668

How can you limit the columns PHPExcel reads?

I have a large excel file (two worksheets of ~4000 rows and columns out to AF). The excel table is formatted terribly, and I have no control over it. There are hundreds of blank columns going all the way out to XFC.

How can I have PHPExcel choose which columns it reads/writes based on the code, below? I tried using what the documentation said, but it obviously isn't working.

Code:

<?php 

require('./Classes/PHPExcel/IOFactory.php');


ini_set('max_execution_time', 800);
ini_set('memory_limit', 200M);  

$inputFileType = 'Excel2007';
$inputFileName = $_FILES['uploaded']['tmp_name'];

//٧٧ this is what documentation suggested ٧٧//
class MyReadFilter implements PHPExcel_Reader_IReadFilter {     
    public function readCell($column, $row, $worksheetName = '') {
          // Read columns from 'A' to 'AF'
          if ($column >= '0' && $column <= '32'){
              return true;
          }
          return false;
      }
}
//^^this is what documentation suggested^^//

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcelReader = $objReader->load($inputFileName);

$loadedSheetNames = $objPHPExcelReader->getSheetNames();

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcelReader, 'CSV');

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
                          $objWriter->setSheetIndex($sheetIndex);
                          $objWriter->save('abc.csv');}

$files = fopen('abc.csv', 'r'); 
    while (($line = fgetcsv($files)) !== FALSE) {
    $csv_array[] = array_combine(range(1, count($line)), array_values($line));
    }

?>

Upvotes: 4

Views: 5786

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

Simply creating a class isn't enough: you need to tell PHPExcel to actually use your MyReadFilter class

/**  Create an Instance of the Read Filter  **/ 
$filterSubset = new MyReadFilter(); 

/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/**  Tell the Reader that we want to use the Read Filter  **/ 
$objReader->setReadFilter($filterSubset); 
/**  Load only the rows and columns that match our filter to PHPExcel  **/ 
$objPHPExcel = $objReader->load($inputFileName);

See section 5.3 - Reading Only Specific Columns and Rows from a File (Read Filters) - of the PHPExcel User Documentation - Reading Spreadsheet Files document

EDIT

If you're working with columns, then you need to work with column letters, because the column id passed into the readfilter is a column ID, not a column number

Either convert to a number (inefficient if you do it in readCell()):

class MyReadFilter implements PHPExcel_Reader_IReadFilter {
    public function readCell($column, $row, $worksheetName = '') {
          // Read columns from 'A' to 'AF'
          if (PHPExcel_Cell::columnIndexFromString($column) -1 >= 0) &&
              PHPExcel_Cell::columnIndexFromString($column) -1 <= 32) {
              return true;
          }
          return false;
      }
}

or compare as a column ID

class MyReadFilter implements PHPExcel_Reader_IReadFilter {

    public function __construct($fromColumn, $toColumn) {
        $this->columns = array();
        $toColumn++;
        while ($fromColumn !== $toColumn) {
            $this->columns[] = $fromColumn++;
        }
    }

    public function readCell($column, $row, $worksheetName = '') {
          // Read columns from 'A' to 'AF'
          if (in_array($column, $this->columns)) {
              return true;
          }
          return false;
      }
}

and instantiate the read filter using:

$filterSubset = new MyReadFilter('A', 'AF');

Upvotes: 5

Related Questions