doujing6053 2014-07-22 18:13
浏览 260
已采纳

如何限制PHPExcel读取的列?

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

?>
  • 写回答

1条回答 默认 最新

  • dongzan9069 2014-07-22 18:19
    关注

    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'); 
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 Python安装cvxpy库出问题
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题