php - 如何限制 PHPExcel 读取的列？

Question

我有一个很大的 excel 文件(两个工作表，大约 4000 行和列到 AF)。 excel表格的格式很糟糕，我无法控制它。有数百个空白列一直延伸到 XFC。

我怎样才能让 PHPExcel 根据下面的代码选择它读/写的列？我尝试使用文档所说的内容，但显然不起作用。

代码:

<?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));
    }

?>

Answer 1

仅仅创建一个类是不够的:您需要告诉 PHPExcel 实际使用您的 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); 

参见第 5.3 节 - Reading Only Specific Columns and Rows from a File (Read Filters) - 的 PHPExcel User Documentation - Reading Spreadsheet Files文档

编辑

如果您正在处理列，那么您需要处理列字母，因为传递给 readfilter 的列 ID 是列 ID，而不是列号

要么转换为数字(如果在 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;
      }
}

或 作为列 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;
      }
}

并使用以下方法实例化读取过滤器:

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