PHPExcel花费很长时间读取Excel文件 [英] PHPExcel taking an extremely long time to read Excel file

问题描述

我正在使用PHPExcel 1.7.8，PHP 5.4.14，Windows 7和Excel 2007电子表格.该电子表格包含750行，列A至BW，大小约为600KB.这是我打开电子表格的代码-相当标准:

I'm using PHPExcel 1.7.8, PHP 5.4.14, Windows 7, and an Excel 2007 spreadsheet. The spreadsheet consists of 750 rows, columns A through BW, and is about 600KB in size. This is my code for opening the spreadsheet--pretty standard:

//Include PHPExcel_IOFactory
include 'PHPExcel/IOFactory.php';
include 'PHPExcel.php';

$inputFileName = 'C:\xls\lspimport\GetLSP1.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//set active worksheet
$objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');

$j = 0;

for($i = 2; $i < 3; $i++)
{
...
}

最后，我最终希望遍历电子表格中的每一行，但是就目前而言，当我完善脚本时，我只遍历了一行.问题是，此脚本需要30分钟才能执行.我在代码的每个部分之后都echo发送消息，以便可以看到正在处理的内容和时间，而我的脚本在此部分基本上要等待30分钟:

In the end, I eventually want to loop through each row in the spreadsheet, but for the time being while I perfect the script, I'm only looping through one row. The problem is, it takes 30 minutes for this script to execute. I echo'd messages after each section of code so I could see what was being processed and when, and my script basically waits for 30 minutes at this part:

$objPHPExcel = $objReader->load($inputFileName);

配置不正确吗?我不知道为什么需要30分钟才能加载电子表格.非常感谢您的帮助.

Have a configured something incorrectly? I can't figure out why it takes 30 minutes to load the spreadsheet. I appreciate any and all help.

推荐答案

PHPExcel在识别excel文件的末尾位置时出现问题.确切地说，Excel很难知道其结尾在哪里.如果您触摸A:1000000处的单元格，则认为它需要读取该距离.

PHPExcel has a problem with identifying where the end of your excel file is. Or rather, Excel has a hard time knowing where the end of itself is. If you touch a cell at A:1000000 it thinks it needs to read that far.

我过去做过2件事来解决此问题:

I have done 2 things in the past to fix this:

1)将所需数据剪切并粘贴到新的excel文件中. 2)指定要阅读的确切尺寸.

1) Cut and past the data you need into new excel file. 2) Specify the exact dimensions you want to read.

编辑方法2

public function readExcelDataToArray($excelFilePath, $maxRowNumber=-1, $maxColumnNumber=-1)
{
    $objPHPExcel = PHPExcel_IOFactory::load($excelFilePath);
    $objWorksheet = $objPHPExcel->getActiveSheet();

    //Get last row and column that have data
    if ($maxRowNumber == -1){
    $lastRow = $objWorksheet->getHighestDataRow();
    } else {
        $lastRow = $maxRowNumber;
    }

    if ($maxColumnNumber == -1){
        $lastCol = $objWorksheet->getHighestDataColumn();
        //Change Column letter to column number
        $lastCol = PHPExcel_Cell::columnIndexFromString($lastCol);      
    } else {
        $lastCol = $maxColumnNumber;
    }   

    //Get Data Array
    $dataArray = array();

    for ($currentRow = 1; $currentRow <= $lastRow; $currentRow++){
        for ($currentCol = 0; $currentCol <= $lastCol; $currentCol++){
            $dataArray[$currentRow][$currentCol] = $objWorksheet->getCellByColumnAndRow($currentCol,, $currentRow)->getValue();
        }
    }
    return $dataArray;
}

不幸的是，这些解决方案不是很动态.

Unfortunately these solutions aren't very dynamic.

请注意，现代的excel文件实际上只是一个扩展名为xlsx的zip文件.我已经编写了PHPExcel扩展，可以将其解压缩，并修改某些xml文件以获得所需的行为.

Note that a modern excel file is really just a zip with an xlsx extension. I have written extensions to PHPExcel that unzip them, and modify certain xml files to get the kinds of behaviors I want.

第三个建议是监视每一行的内容，并在出现空白行时停止.

A third suggestion for you would be to monitor the contents of each row and stop when you get an empty one.

这篇关于PHPExcel花费很长时间读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！