php读取excel文件

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.