php抓取网页表格信息(日常开发中导入excel文件进行批量数据操作的步骤和注意事项 )
优采云 发布时间: 2021-09-29 14:42php抓取网页表格信息(日常开发中导入excel文件进行批量数据操作的步骤和注意事项
)
在日常开发中，尤其是管理后台的开发中，经常会遇到导入excel文件进行批量数据操作，导出数据到excel的场景。本文主要讲我在开发过程中总结的php阅读excel的步骤和步骤。需要注意的问题。
一、常规方式
读取excel的过程很简单，就是接收文件、解析文件、数据处理、返回结果。但是，由于导入文件的自由度大于在页面上填写表格，服务器可能会收到各种形式的文件，因此数据的有效性就显得尤为重要。另外，由于excel导入文件通常是对大量数据进行批量操作，因此处理页面超时、服务器内存溢出、容错等问题也很重要。
导入excel的一般处理步骤如下
接收验证文件（类型、大小、存在：直接报错）查空表（无数据，整行为空字段：跳过此行）查数据行数（不能超过允许的数据量）待处理）检查空字段（只读必填列，检查空字段：记录它们） 检查字段（长度，类型：记录） 检查重复（去重）类型转换，xss过滤，数据处理，重组数据 字段有效性检查（例如产品是否存在），调整业务操作结果处理界面，反馈异常处理的操作结果（生成日志和错误提示或错误表）
上图左边是php校验和解析excel文件，右边是校验和操作数据。
看起来很繁琐，其实前7步就是数据校验和预处理。在开发过程中，要尽量对用户的误操作给予友好的提示，尤其是在excel操作过程中，极高的自由度会导致出错的概率很高。用户不是开发人员，通常不知道如何产生系统理解的数据。在开发和维护过程中，我们遇到过各种可笑的“误操作”，所以系统应该尽量引导用户检查和修改。
1、接收和验证文件
通过检查接收到的文件的MIME和文件大小，判断用户上传的文件是否有效，防止用户上传格式错误的文件或超出系统处理能力的大文件。服务器。上传大文件很容易造成服务器内存溢出。
2、检查空列表
用户在编辑表格时往往会删除每个单元格的整行，而不是直接删除整行，从而导致留下一些空白行。这些空白行毫无意义。系统应该过滤它们并且只保留有数据的行。.
3、检查数据线
受限于服务器的处理能力和业务的复杂性，我们需要限制允许的数据行数，比如10万行。如果超过，很可能会导致服务器内存溢出或页面超时错误。同时，我们也可以将excel的服务器处理超时和内存限制设置大一些。
4、检查空字段
检查系统要求的非空字段。有时系统不需要读取表的所有列，添加一些列供用户查看，对系统没有意义的列不需要验证（但可能需要读取和临时存储，例如发生错误时，需要提示用户在哪一行出错，该行的内容是什么）。
5、检查数据格式
根据业务需求验证字段类型、长度等。
6、卸重
根据业务需求执行数据行重复数据删除或错误报告。
7、类型转换、xss过滤、数据处理、数据重组
经过前6步验证，基本可以确定数据的有效性，然后对数据进行预处理，使数据符合操作界面的要求。比如xss过滤，用户填写的文本需要转换成界面需要的数字，数据结构调整为界面需要的形式。
8、数据有效性检查
对数据进行业务相关的验证。例如，如果需要修改产品，首先要查询该产品是否存在，在执行订单发货操作时，首先要查询该订单是否存在。这种情况是兼容的，即使产品或订单不存在，直接调整接口也不会有问题）。
9、 调优界面
调整业务操作界面。如果接口不能一次处理所有数据，则需要批量调整。如果接口有频率限制，则需要注意在周期内保持适当的时间间隔。
10、结果处理，异常处理
根据接口返回的信息，组装成合适的数据格式，用于提示用户哪些数据操作成功，哪些数据操作失败，以及失败的原因。
11、反馈操作结果
批量操作中经常会遇到部分成功和部分失败的情况。因此，导出不正确的表是一种更直观的方式。可以在表格中增加一列来说明操作失败的原因，用户可以根据提示重新编辑表格。并导入系统。如果发生系统错误，还应记录操作日志，以便及时定位和处理。
二、注释1、科学记数法
Excel 编辑软件通常以科学计数法显示订单号和 ID 号等大数字。这是系统的非法数据格式。如果可以进行转换，系统应该尽量允许这种格式并把它转换成正确的数据。但是，当转换结果不准确时，应提示用户将数据格式设置为文本类型。比如有些数字字符串太长，用excel编辑软件转换成科学记数法，就会失去一定的准确性。即使将它们转换为字符串，数据也会丢失。这是不对的。例如，原来的ID号“4211116”在Microsoft Excel中会转为“4.21022E+17”，再转一个数值为“4200000”。
2、超时和内存溢出
在进行批量操作时，当数据量较大或操作界面时间较长时，可能会遇到页面超时错误或服务器内存溢出。一方面，我们可以设置超时时间和内存限制，另一方面，我们需要根据实际测试设置一个数据量限制。
set_time_limit(120);
ini_set('memory_limit','512M');
这里设置的超时时间只是php的超时时间，页面超时时间也受nginx配置的超时时间限制，取较小者。
3、空白单元格
在使用系统的过程中，可能会遇到用户上传的excel文件明明只有几百行，但是内存溢出。可能是因为用户在编辑excel的过程中修改了单元格格式，导致出现很多空行或空列的无用信息，导致服务器解析excel文件时内存溢出。这时候可以把原来的excel文件的内容粘贴到新的excel文件中，丢弃无用的信息。
三、优化
如果处理的数据量太大，接口不能在规定时间内处理数据，肯定会造成超时。这时候我们就可以优化系统了。共有三种优化方案。
1、使用异步任务
解析完表后，使用异步任务调用接口。服务器直接将结果返回给前端页面，页面会定时自动查询运行状态。这样，一个请求被分多次，避免了页面超时的问题，用户不需要在页面上等待，甚至可以关闭页面。该方法需要编写异步任务，还需要反转任务状态，并提供查询任务状态的接口。相对来说，实现会稍微复杂一些。
2、多个请求
文件上传后，服务器不进行数据操作，只对excel文件中的数据进行解析，解析完成后将数据返回页面或暂存到redis中。前端页面循环发送请求，将数据拆分成多个副本进行处理。需要注意的是，如果数据返回到页面并临时存放在浏览器内存中，则请求过程需要注意http请求的参数限制。post请求默认接收1000个参数，所以页面传递给服务端时参数要转换为String（如果直接传递多维数组，数组的每个子元素都会作为一个参数处理，并且很容易超过限制）。
3、使用csv格式
因为excel会有一些额外的格式，比如单元格宽度、背景色、边框、字体等，所以在解析excel文件的时候会占用服务器内存（虽然用户可能不会设置），通常系统不会关心这个信息，所以把excel转成csv再导入系统会大大增加系统可以处理的数据量。
四、相关php代码
下面是我封装的一个将excel文件解析成数组的方法。该方法接收一个excel文件并返回一个数组。包括的功能是
/**
* 解析excel表格为数组
* @param array $params
* int max_size: 限制文件大小（M）
* int count_col:需要读取表格前多少列，防止读到后面的空白列导致内存溢出
* bool need_head: 是否需要读取表头
* 将会跳过空行
* @return array
*/
function parse_excel($params = array()){
$fileType = array("application/vnd.ms-excel","application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","application/kset");
$max_size = isset($params['max_size']) ? $params['max_size'] : 2;
if (!(isset($_FILES["file"]) && 0 == $_FILES["file"]["error"])) {
cilog('debug', '上传失败：isset($_FILES["file"])：'.isset($_FILES["file"]).'上传文件$_FILES["file"]["error"]：'.$_FILES["file"]["error"]);
$result = array(
'errcode' =>1001,
'errmsg' =>"文件上传失败",
);
} elseif ($_FILES["file"]["size"] > $max_size * 1024 * 1024) {
cilog('debug', '上传文件大小：'.$_FILES["file"]["size"]);
$result = array(
'errcode' =>1002,
'errmsg' =>"上传文件不得超过{$max_size}Mb",
);
} elseif (!in_array($_FILES["file"]["type"],$fileType)) {
cilog('debug', '上传文件MIME：'.$_FILES["file"]["type"]);
$result = array(
'errcode' =>1003,
'errmsg' =>"文件类型错误，请上传.xls或.xlsx文件",
);
} else{
$file = $_FILES['file']['tmp_name'];
require_once BASEPATH . '/libraries/phpexcel/PHPExcel.php';
$excelReader = new PHPExcel_Reader_Excel2007();
if (!$excelReader->canRead($file)) {
$excelReader = new PHPExcel_Reader_Excel5();
}
$sheet = $excelReader->load($file)->getSheet(0); //sheet1操作
$excelCont = array(
'highestCol' => $sheet->getHighestColumn(), //列
'highestRow' => $sheet->getHighestRow(), //行
'highestColumnIndex' => PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()) // 几列
);
$countCol = isset($params['count_col']) ? $params['count_col'] : $excelCont['highestRow'];//有效列的数目，读取时只取前$countCol列
$startRow = isset($params['need_head']) && $params['need_head'] ? 1 : 2;//从第1行开始读，读取表头
// $excelCont['highestRow'] 有几行
// 表示第一行第一列 $sheet->getCellByColumnAndRow(0, 1)->getValue()的值
$rightArr = array();
// 总共导入多少行 $row - $emptyLine -1
$row = $excelCont['highestRow'];
$emptyLine = 0;
for ($j = $startRow; $j < intval($row) + 1; $j++) {
$retArr = array();//该行的各个单元格的数据
$emptyCol = 0;
for($i = 0; $i < $countCol;$i++){//循环该行的单元格
$retArr[$i] = $sheet->getCellByColumnAndRow($i, $j)->getValue();
$retArr[$i] = isset($retArr[$i]) ? trim($retArr[$i]) : $retArr[$i];
if($retArr[$i] === null){
$emptyCol++;
}
}
if($emptyCol == $countCol){//这行为空行，不放入任何数组
$emptyLine++;
}else {
$rightArr[] = $retArr;
}
}
if(empty($rightArr)){
$result = array(
'errcode' =>1004,
'errmsg' =>"上传文件内没有有效数据",
);
}else{
$result = array(
'errcode' =>0,
'errmsg' =>"",
'data' =>$rightArr,
);
}
}
return $result;
}
