PHP读取excel文件

我用的是PHPExcel这个库,相关的方法网上都有介绍,我这里主要是记录一下使用过程中遇到的问题

按单元格读取

先来看一段示例代码,这段示例代码是使用PHPExcel按单元格读取excel的内容

	$firstfilename = 'test.xlsx'; 
    if ( !file_exists($firstfilename)) {
        echo "$firstfilename not exits";
        exit;
    }

    // init excel reader
    $reader = new PHPExcel_Reader_Excel2007();
    // 判断是xlsx还是xls文件
    if ( !$reader->canRead($firstfilename)) {
        $reader = new PHPExcel_Reader_Excel5();
        if ( !$reader->canRead($firstfilename)) {
            exit("cannot read $firstfilename");
        }
    }
    // 加载文件
    $objexcel = $reader->load($firstfilename);
    // 获取活跃的sheet
    $sheet = $objexcel->getActiveSheet();
    // 获取最大的列号
    $maxColumns = $sheet->getHighestColumn();
    // 将列号转成数字(turn A,B.C to 0,1,2)
    $maxColumns = PHPExcel_cell::columnIndexFromString($maxColumns);
    // 获取最大的行号
    $maxRows = $sheet->getHighestRow();

    for ( $i = 1; $i <= $maxRows; $i++) {
        for ($j = 0; $j <= $maxColumns; $j++) {
        	// 读取单元格的数据
            echo $sheet->getCellByColumnAndRow($j, $i)->getValue();
            echo "  ";
        }
        echo '<br>';
    }

这样按单元格读取,在小文件的情况下,性能也还行。如果是大文件的话,就会比较慢。

按块读取

如果能一块一块地读入,而不是一个单元格一个单元格的读入,应该会稍微好一点。

按块读取,需要新建一个类,实现PHPExcel_Reader_IReadFilter里面的方法,如下图所示:

class Helper_PHPExcelReadFilter implements PHPExcel_Reader_IReadFilter
{
    private $_startRow = 0;

    private $_endRow = 0;

    public function __construct($startRow, $chunkSize)
    {
        $this->_startRow    = $startRow;
        $this->_endRow      = $startRow + $chunkSize;
    }
    
    public function setRows($startRow, $chunkSize) {

    }

    public function readCell($column, $row, $worksheetName = '') {
        if ($row >= $this->_startRow && $row < $this->_endRow) {
            return true;
        }
        return false;
    }
}

看下面的示例代码

function readExcel($chunckSize) {
	$firstfilename = 'test.xlsx'; 
    if ( !file_exists($firstfilename)) {
        echo "$firstfilename not exits";
        exit;
    }

    // init excel reader
    $reader = new PHPExcel_Reader_Excel2007();
    // 判断是xlsx还是xls文件
    if ( !$reader->canRead($firstfilename)) {
        $reader = new PHPExcel_Reader_Excel5();
        if ( !$reader->canRead($firstfilename)) {
            exit("cannot read $firstfilename");
        }
    }
    // 加载文件
    $objexcel = $reader->load($firstfilename);
    // 获取活跃的sheet
    $sheet = $objexcel->getActiveSheet();
    // 获取最大的列号
    $maxColumns = $sheet->getHighestColumn();
    // 将列号转成数字(turn A,B.C to 0,1,2)
    $maxColumns = PHPExcel_cell::columnIndexFromString($maxColumns);
    // 获取最大的行号
    $maxRows = $sheet->getHighestRow();

	$startRow = 2; // 从第二行开始读,(第一行往往是标题,不需要)
    $totalDataRowCount = $maxRows - $startRow + 1;
    // 计算按块读取需要读取几次
    if ($totalDataRowCount <= $chunckSize) {
        $chunckSize = $totalDataRowCount;
        $times = 1;
    } else {
        $times = (0 ==  $totalDataRowCount % $chunckSize ) ? ($totalDataRowCount / $chunckSize) : ((int)($totalDataRowCount / $chunckSize) + 1);
    }

    // 按块读取
	for ($i = 0; $i < $times; $i ++, $startRow +=$chunckSize) {

		// 如果是最后一次读,则最后一个块可能小于$chunckSize
        if ($i == $times - 1) {
            $lastPart = $totalDataRowCount - $i * $chunckSize;
            $chunckSize = $lastPart;
        }

        // 按块读取
        $tempChunckItemList = readChunck($filePath, $startRow, $chunckSize);

        ……
        ……
        ……
	}

}
	
function readChunck() {
	$objReader = new PHPExcel_Reader_Excel2007();
    // 判断是xlsx还是xls文件
    if ( !$objReader->canRead($firstfilename)) {
        $objReader = new PHPExcel_Reader_Excel5();
    }
    $perf           = new Helper_PHPExcelReadFilter($startRow, $chunckSize);
    $objReader->setReadFilter($perf);
    $objPHPExcel = $objReader->load($filePath);
    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
    return $sheetData;
}

这样就实现了按块读取的功能。不过有一个要注意的点,按块读取的时候,只有该块里面有数据,其他是没有数据的,但是key=>value的key还是在的。距离俩说明,比如说我excel文件有两列,读取了第一块是第2行到第5行,那么readChunck这个方法返回的内容是这样的,如下所示:

[
	'1' => [
		'A' => NULL,
		'B' => NULL
	],
	'2' => [
		'A' => 'xxx',
		'B' => 'xxx',
	],
	'3' => [
		'A' => 'xxx',
		'B' => 'xxx',
	],
	'4' => [
		'A' => 'xxx',
		'B' => 'xxx',
	],
	'5' => [
		'A' => 'xxx',
		'B' => 'xxx',
	],
	'6' => [
		'A' => NULL,
		'B' => NULL,
	],
	'7' => [
		'A' => NULL,
		'B' => NULL,
	],
	'8' => [
		'A' => NULL,
		'B' => NULL,
	],
	……
]

413 request entity too large、502 bad gateway

当excel文件比较大的时候,会报错413 request entity too large或者会报502 bad gateway,这个时候就要注意修改两个地方的配置参数

413 request entity too large基本是因为上传的文件太大,超过了配置里面设置的最大上传文件大小

502 bad gateway可能是因为文件太大,在读文件的时候内存占用过高,导致响应新请求的worker进程分配不到内存

如果要上传大文件,建议改以下配置文件

  • nginx配置文件的

    • 比如说你的服务的匹配规则是location /,那么在里面添加几个超时时间的设定,把fastcgi_connect_timeout,fastcgi_send_timeout,fastcgi_read_timeout, send_timeout,超时时间改得长一点。如下方代码所示:
     location / {
        root                    /home/nemo/testproject;
        fastcgi_pass            127.0.0.1:9000;
        fastcgi_connect_timeout 300;
        fastcgi_send_timeout 300;
        fastcgi_read_timeout 300;
        send_timeout 300;
        fastcgi_index           index.php;
        include                 fastcgi_params;
        fastcgi_param           SCRIPT_FILENAME $document_root/index.php;
        fastcgi_param           PATH_INFO $fastcgi_path_info;
    } 
    
    • 还可能需要修改nginx.conf里面的如下配置:
    client_max_body_size 50m;
    client_body_buffer_size 8m;
    keepalive_timeout  300;   
    proxy_connect_timeout 300s;
    proxy_read_timeout 300s;
    proxy_send_timeout 300s;  
    
  • php配置文件的改动

    • 需要修改php.ini配置文件的如下配置
    post_max_size = 50M
    upload_max_filesize = 50M
    file_uploads = On
    max_execution_time = 300
    max_input_time = 300
    memory_limit = 512M
    
    • 修改php-fpm.conf配置文件的如下配置
    request_terminate_timeout = 300s
    

但是如果是比较大的文件,可能php就没办法“实时”返回相关检查结果了。比如说我试过一个18M的excel文件,里面有30W行数据,我的低配开发机半个小时都没处理完╮(╯_╰)╭。

建议比较大的文件,从业务设计上避免让php处理。

如果是比较小的文件,还有一种方法来提高效率,就是让前端通过类似js-xlsx之类的库,将excel的内容转成数组传给php.