PHP 导出 Excel
2301 / 3 / 创建于 4年前 /
_zzh 的个人博客
由于业务需要，需要在后台添加导出Excel的功能。所以很自然的想到了PHPExcel，于是就开始动手了。
由于之前没有做过，所以基本都是借鉴网上大佬的。
-
检查是否有PHPExcel包，如果没有，请在网上下好。
2.开始写代码了。需要注意得是，一般导出数据，数据量都会较大。所以需要设置内存使用量和，超时时间。并且需要注意导出的数据会被Excel改写，如身份证号，手机号，时间戳等长数字会被显示为科学计数法，时间格式也显示为Excel的默认显示格式。脾气是大得很呐....惹不起惹不起。所以经过查资料寻得PHPExcel中有个方法（setCellValueExplicit）是可以设置导出单元格格式为文本。还有种方法是获取数据时做处理，后面会讲到。/**\ * 导出Excel文件 速度慢 * @param $fileName 导出的文件名 * @param $headArr 数据头 * @param $data 导出数据 */ function getExcel($fileName,$headArr,$data){ //设置PHP最大单线程的独立内存使用量 ini_set('memory_limit','1024M'); //程序超时设置设为不限时 ini_set('max_execution_time ','0'); //导入PHPExcel类库，因为PHPExcel没有用命名空间，所以使用vendor导入 vendor("PHPExcel.PHPExcel.IOFactory"); vendor("Excel.PHPExcel"); vendor("Excel.PHPExcel.Writer.Excel5"); vendor("Excel.PHPExcel.IOFactory.php"); //对数据进行检验 if(empty($data) || !is_array($data)){ die("data must be a array"); } //检查文件名 if(empty($fileName)){ exit; } $date = date("Y_m_d",time()); $fileName .= "_{$date}.xls"; //创建PHPExcel对象 $objPHPExcel = new \PHPExcel(); //设置表头 $key = ord("A"); foreach($headArr as $hkey => $v){ $colum = chr($key); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v); $key += 1; unset($headArr[$hkey]); } $column = 2; $objActSheet = $objPHPExcel->getActiveSheet(); foreach($data as $key => $rows){ //行写入 $span = ord("A"); foreach($rows as $keyName=>$value){// 列写入 $j = chr($span); //设置导出单元格格式为文本，避免身份证号的数据被Excel改写 $objActSheet->setCellValueExplicit($j.$column, $value); $span++; unset($rows[$keyName]); } $column++; unset($data[$key]); } $fileName = iconv("utf-8", "gb2312", $fileName); //重命名表 // $objPHPExcel->getActiveSheet()->setTitle('test'); //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); ob_end_clean(); ob_start(); header('Content-Type: application/vnd.ms-excel');//定义输出的文件类型为excel文件 header("Content-Disposition: attachment;filename=\"$fileName\"");//定义输出的文件名 header('Cache-Control: max-age=0');//强制每次请求直接发送给源服务器，而不经过本地缓存版本的校验。 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); //文件通过浏览器下载 exit; }3.调用实现,此处用的是thinkPHP。如框架不同，请自行修改语法。
public function exportexcel(){ $sqlWhere = session('H5OPERATIONLOG_TIME_WHERE'); $data = M(self::T_TABLE)->where('create_time'.$sqlWhere)->select();//导出数据 $filename="XXXXX";//导出文件名 $headArr=array("XXX","XXX1","XXX2");//数据头 getExcel($filename,$headArr,$data); } <td><a href="你的exportexcel访问路径"><input type="button" value="导出Excel"></input></a></td>
4.原本以为这样完事了。（事实上这样也算完事了）但是后来觉得，貌似导出速度有点慢。就找了时间再深入研究了一下。于是找到了另一种比较好的方法。-------导出CSV--------
5.CSV概念
CSV是一种通用的、相对简单的文件格式，被用户、商业和科学广泛应用。最广泛的应用是在程序之间转移表格数据，而这些程序本身是在不兼容的格式上进行操作的（往往是私有的和/或无规范的格式）。因为大量程序都支持某种CSV变体，至少是作为一种可选择的输入/输出格式。
6.因为CSV通常也是使用Excel查看。所以不可避免的，还是需要注意一些Excel的限制。
表数据限制：
Excel 2003及以下的版本。一张表最大支持65536行数据，256列。
Excel 2007-2010版本。一张表最大支持1048576行，16384列。
7.PHPExcel于CSV需要注意的地方
PHPexcel内存溢出：
既然数据限制在104W，那么数据分割就数据分割呗，于是你尝试50W一次导入表，然而PHPexcel内部有函数报内存溢出错误，然后你就不断的调小数据量，直到5W一次导入你都会发现有内存溢出错误。这是为什么呢，虽然你分割数据来导入多个数据表，但是最后PHPexcel内部还是一次性把所有表数据放进一个变量中来创建文件……额，这几百万数据一个变量存储，你想内存不溢出，还真有点困难。
（后来看了一些文章发现PHPExcel也有解决方案，PHPExcel_Settings::setCacheStorageMethod方法更改缓冲方式来减小内存的使用）CSV输出buffer过多：
当你用PHP原生函数putcsv()其实就使用到了输出缓存buffer，如果你把几百万的数据一直用这个函数输出，会导致输出缓存太大而报错的，因此我们每隔一定量的时候，必须进行将输出缓存中的内容取出来，设置为等待输出状态。具体操作是：
ob_flush();
flush();然后需要解决的就是，当数据量达到百万以上是。不可避免的就需要面对Excel的显示数据问题以及大量数据导出的问题。这个问题也是我在目前为止并没有解决的。参考文章中有说道一个方案就是使用yield生成器以及分段生成CSV文件然后再统一打包下载。如果有大神有这方面经验，还请不吝赐教。
8.具体实现代码如下，需要注意的是。导出CSV的方法并不像PHPExcel一样提供了转文本格式的方法。所以这里需要在传入数据是做数据处理。具体方法为：在需要处理的数据后面拼接制表符："/t"。一定要双引号。
/**
* @param array $head 数据头
* @param string $sql 对象
* @param string $mark csv文件名
* @param string $fileName 压缩文件名
* @param array $dateCountArr
*/
function putCsv($head, $sql, $mark, $fileName, $dateCountArr)
{
//处理导出Csv需要转换为文本格式的数据
$textArr = array(
//mem_loan_excel键名 = $fileName
"mem_loan_excel" => array(
//对应字段
'phone',
'id',
'time',
),
);
$Model = new \Think\Model();
//设置PHP最大单线程的独立内存使用量
ini_set('memory_limit','128M');
set_time_limit(0);
// 输出Excel文件头
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
header('Content-Disposition: attachment;filename="' . $fileName . '.zip"');
header('Cache-Control: max-age=0');
// 每隔$limit行，刷新一下输出buffer
$limit = 2000;
// buffer计数器
$sqlLimitCount = 0;
$cnt = 0;
$fileNameArr = array();
// 逐行取出数据，不浪费内存
foreach ($dateCountArr as $key => $value){
$fp = fopen($mark . '_' . $value['date'] . '.csv', 'w'); //生成临时文件
fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码 防止乱码(比如微信昵称(乱七八糟的))
$fileNameArr[] = $mark . '_' . $value['date'] . '.csv';
// 将数据通过fputcsv写到文件句柄
fputcsv($fp, $head);
$sqlLimitCount += $value['count'];
$sqlLimit = $sqlLimitCount - $value['count'];//每次计数开始的位置,所有数据相加 - 当前数据
$dataArr = $Model->table($sql . ' a')->limit($sqlLimit,$value['count'])->select();
foreach ($dataArr as $a) {
//加"\t"形成文本格式，原样输出
if($textArr[$fileName]){
foreach ($a as $key => $val){
if(in_array($key,$textArr[$fileName])){
$a[$key] = $val."\t";
}
}
}
$cnt++;
if ($limit == $cnt) {
//刷新一下输出buffer，防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
fputcsv($fp, $a);
}
fclose($fp); //每生成一个文件关闭
}
//进行多个文件压缩
$zip = new ZipArchive();
$ZipFilename = $fileName . ".zip";
$openFile = $zip->open($ZipFilename, ZipArchive::CREATE); //打开压缩包
if($openFile!=true || empty($fileNameArr)){
exit("file Error");
}
foreach ($fileNameArr as $file) {
$zip->addFile($file, basename($file)); //向压缩包中添加文件
}
$zip->close(); //关闭压缩包
foreach ($fileNameArr as $file) {
unlink($file); //删除csv临时文件
}
//输出压缩文件提供下载
header("Cache-Control: public");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename='.basename($ZipFilename)); //文件名
header("Content-Type: application/zip"); //zip格式的
header("Content-Transfer-Encoding: binary"); //告诉浏览器，这是二进制文件
header('Content-Length: '. filesize($ZipFilename)); //告诉浏览器，文件大小
ob_clean();
flush();
@readfile($ZipFilename);
unlink($ZipFilename);
exit;
}
/**
** 导出信息
**/
public function exportexcel(){
$sqlWhere = session('H5OPERATIONLOG_TIME_WHERE');//这里是我这里的条件，需要修改
$sql = M(self::T_TABLE)->where('create_time'.$sqlWhere)->buildSql();//获取到查询数据的sql
$Model = new \Think\Model();
//得到每一天的数据总数
$dateCountArr = $Model->table($sql . ' a')
->field('count(*) as count, DATE_FORMAT(a.create_time,"%Y-%m-%d") as date')
->group('DATE_FORMAT(a.create_time,"%Y-%m-%d")')
->select();
$filename="h5_operation_log_excel".time();
$mark="h5_operation_log_info";
$headArr=array("id","用户编号","数据唯一编号","数据名称","操作","操作者IP","操作批次","操作时间");
putCsv($headArr,$sql,$mark,$filename,$dateCountArr);
}
<td><a href="你的exportexcel访问路径"><input type="button" value="导出Excel"></input></a></td>
//此地方的出处找不到了，希望原作看到不要揍我。狗头保命ing......9.CSV在Excel中展示会有点小问题
解决方案：SYLK 文件时一个文本文件，开头的为“ID”或“ID_XXXX”（其中XXXX是文本字符串）。
SYLK 文件的第一个的记录是在 ID_Number 记录的。
Excel 将识别该文本在文本文件开头时, 它会将该文件解释为 SYLK 文件。
Excel 将尝试从该 SYLK 格式转换该文件，但不能这样做，因为"ID"字符后不有任何有效的 SYLK 代码。
因为 Excel 不能转换该文件，您收到错误消息。当您打开一个文本文件、 CSV 文件和文件的前两个字符是大写字母"I"，"D"时，会发生此问题。
例如文本文件可能包含以下文本：
ID, STATUS 123, open 456, closed
如果前两个字母小写"i"和"d"不会发生此问题的 。参考：https://www.cnblogs.com/A2008A/archive/201...
至此也就完成了。原本导出1W+条数据，PHPExcel需要8秒。现在换成CSV方法只需要两秒，也算能满足日常的运营需求了。当然，可以优化的地方还有很多。欢迎指正修改。
本作品采用《CC 协议》，转载必须注明作者和本文链接
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：