PHP导出excel

系统管理员 2023-07-16 11:55 26阅读 0赞

PHP导出excel。

用到phpspreadsheet包。

composer引入phpspreadsheet包：

方法1：
    // 命令行进入到项目跟目录
    cd F:\wamp\www\my_project
    
    // 引入phpspreadsheet包
    composer require phpoffice/phpspreadsheet
    
    
    方法2：
    编辑composer.json文件，加入引入phpspreadsheet包：
    {
        "require": {
            "phpoffice/phpspreadsheet": "*"
        }
    }
    
    // 命令行进入到项目跟目录，执行更新
    composer update

\*如果composer下载速度慢，可以使用国内的库地址：

// 进入到项目跟目录，设置使用国内库地址
    composer config repo.packagist composer https://mirrors.aliyun.com/composer/
    
    // 执行后composer.json文件会自动写入：
    "repositories": {
        "packagist": {
            "type": "composer",
            "url": "https://mirrors.aliyun.com/composer/"
        }
    }

使用代码：

自定义excel类：

<?php
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    class Mexcel{
    
    	static $field_enum = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
    
    	/* 导出excel */
    	public static function exportExcel($data, $excel_title = array(), $file_name = 'data.xlsx') {
    		$spreadsheet = new Spreadsheet();
    		$sheet = $spreadsheet->getActiveSheet();
    		$start_index = 1;
    		if(!empty($excel_title)) {
    			foreach ($excel_title as $key => $value) {
    				$sheet->setCellValue(self::$field_enum[$key] . '1', $value);
    			}
    			$start_index = 2;
    		}
    		foreach ($data as $key => $value) {
    			$field_index = 0;
    			foreach ($value as $k => $v) {
    				$sheet->setCellValue(self::$field_enum[$field_index] . ($key + $start_index), $v);
    				$field_index++;
    			}
    		}
    
    		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    		header('Content-Disposition: attachment;filename="' . $file_name . '"');
    		header('Cache-Control: max-age=0');//禁止缓存
    		$writer = new Xlsx($spreadsheet);
    		$writer->save('php://output');
    	}
    
    
    
    
    }

调用：

$excel_list = array(
    	array('name' => 'test1', 'age' => 23, 'sex' => '男'),
    	array('name' => 'test2', 'age' => 33, 'sex' => '男'),
    	array('name' => 'test3', 'age' => 28, 'sex' => '女'),
    	array('name' => 'test4', 'age' => 24, 'sex' => '男'),
    );
    $excel_title = array('姓名', '年龄', '性别');
    \Mexcel::exportExcel($excel_list, $excel_title);