关联文章: https://blog.csdn.net/fareast_mzh/article/details/86712896
composer require phpoffice/phpexcel
https://github.com/PHPOffice/PHPExcel
* ./app/Http/Controllers/PackageController.php
https://laravel.com/docs/5.6/controllers
php artisan make:controller PackageController--invokable<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// ...
public function exportTickets(Request $request, Response $response) {
$params = $request->getParams();
$filename = isset($params['filename']) ? $params['filename'] : 'tickets';
$ret = $this->get(TicketService::class)->exportTickets($params);
$rowMap = $ret['body']['data']['row_map'];
$tickets = $ret['body']['data']['tickets'];
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=".$filename.".xlsx");
header("Pragma: no-cache");
header("Expires: 0");
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();
set_time_limit(0);
$titles = array_values($rowMap);
$letters = array();
// excel columns
for ($i = 0, $n = count($titles); $i < $n; $i++) {
$letters[$i] = chr(ord('A')+$i);
$sheet->setCellValue($letters[$i]."1", $titles[$i]);
}
foreach ($tickets as $i => $ticket) {
$ii = 0;
foreach ($rowMap as $key => $title) {
if ($key == 'number' || $key == 'customer_id') {
$ticket[$key] = '\''.strval($ticket[$key]);
}
$sheet->setCellValue($letters[$ii].strval($i+2), $ticket[$key]);
$ii += 1;
}
}
$writer = new Xlsx($spreadsheet);
ob_clean();
flush();
$writer->save('php://output');
}
<?php
namespace App\Http\Controllers;
use App\Exports\PackagesExport;
use App\Packages;
use Illuminate\Support\Facades\Log;
// use Maatwebsite\Excel\Facades\Excel;
class PackageController extends Controller {
/**
* http://localhost:8000/package/excel
*/
public function excel() {
$pids = ['P39965','P39966','P39967','P39968','P39969','P39970','p42196','p42197','p42198','P39971','P39972',
'P39973','P39974','P39975','P39976','P41507','p42199','p42200','p42201','P39989','P40507','P39952',
'P39990','P40508','P39953','P39991','P39992','P40509','P39954','P39955','P40510','P39993','P40511',
'P39956','P39994','P40513','P39957','P40514','P39995','P39996','P41983','P41984','P41985','P41986',
'P41989','P41992','P41993','P41994','P40512'];
$data = [];
foreach ($pids as $pid) {
$p = Packages::getByPids($pid);
/** @var $it \App\Packages */
foreach ($p as $it) {
array_push($data, $it->getRowAsArray());
}
}
$title = Packages::getHeaders();
return $this->_doExcelExport($data, $title, '导出pids码2016');
}
/**
*
*/
/**
* 导出数据为excel表格
* @param array $data array 一个二维数组,结构如同从数据库查出来的数组
* @param array $title string excel的第一行标题,一个数组,如果为空则没有标题
* @param string $filename string 下载的文件名
* @return int
* @throws \PHPExcel_Reader_Exception
* @throws \PHPExcel_Writer_Exception
* @example
$stu = M ('User');
$arr = $stu->select();
$this->_doExcelExport($arr,array('id','账户','密码','昵称'),'文件名!');
*/
private function _doExcelExport(&$data=array(),$title=array(),$filename='report') {
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=".$filename.".xlsx");
header("Pragma: no-cache");
header("Expires: 0");
// 2. 实例化一个phpexcel
$objPHPExcel = new \PHPExcel();
// Limits the maximum execution time unlimit
set_time_limit(0);
// 设置 在第一个标签中写入数据 设置活动的sheet是第一个 从0开始
try {
$sheet = $objPHPExcel->setActiveSheetIndex(0);
} catch (\PHPExcel_Exception $e) {
Log::error($e->getTraceAsString());
return -1;
}
// 导出xls 开始
$letter = array();
// 根据$title个数生成excel表的列A, B, C...
if (!empty($title)) {
foreach ($title as $i => $field) {
array_push($letter, chr(ord('A') + $i));
$sheet->setCellValue($letter[$i]."1", $field);
}
} else {
foreach ($data[0] as $i => $field) {
array_push($letter, chr(ord('A') + $i));
}
}
if (!empty($data)) {
foreach ($data as $i => $row) {
$ii = 0; // from 'A',...
foreach ($data[$i] as $key => $value) {
// 从第2行开始
$sheet->setCellValue($letter[$ii].strval($i+2), "'".strval($value));
$ii++;
}
}
}
// 保存
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_clean(); // 关键
flush(); // 关键
$objWriter->save('php://output');
return 0;
}
}
* ./app/Packages.php
php artisan make:model Packages
https://blog.csdn.net/fareast_mzh/article/details/86703181 (私密文章)
<?php
// 模型文件* ./routes/web.php
<?php
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', function () {
return view('welcome');
});
Route::get('/package/index', 'PackageController@index');
Route::get('/package/export', 'PackageController@export');
Route::get('/package/excel', 'PackageController@excel');* 启动服务
E:\code\export>php artisan serve
Laravel development server started: <http://127.0.0.1:8000>
* composer.json
{
"name": "laravel/laravel",
"type": "project",
"description": "The Laravel Framework.",
"keywords": [
"framework",
"laravel"
],
"license": "MIT",
"require": {
"php": "^7.1.3",
"fideloper/proxy": "^4.0",
"laravel/framework": "5.7.*",
"laravel/tinker": "^1.0",
"maatwebsite/excel": "^3.1",
"phpoffice/phpexcel": "^1.8",
"phpoffice/phpspreadsheet": "^1.6"
},
"require-dev": {
"beyondcode/laravel-dump-server": "^1.0",
"filp/whoops": "^2.0",
"fzaninotto/faker": "^1.4",
"mockery/mockery": "^1.0",
"nunomaduro/collision": "^2.0",
"phpunit/phpunit": "^7.0"
},
"config": {
"optimize-autoloader": true,
"preferred-install": "dist",
"sort-packages": true
},
"extra": {
"laravel": {
"dont-discover": []
}
},
"autoload": {
"psr-4": {
"App\\": "app/"
},
"classmap": [
"database/seeds",
"database/factories"
]
},
"autoload-dev": {
"psr-4": {
"Tests\\": "tests/"
}
},
"minimum-stability": "dev",
"prefer-stable": true,
"scripts": {
"post-autoload-dump": [
"Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
"@php artisan package:discover --ansi"
],
"post-root-package-install": [
"@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
],
"post-create-project-cmd": [
"@php artisan key:generate --ansi"
]
}
}
* assign.js
var s = "guid, name, coursecontent, teacherids, grades, pricedescription, packageimage, servicecontent, ispublish, opentime, closetime, shelves, unshelfves, type, tutortimes, subject";
var a = s.split(', ');
var code = a.map(function(it) {
return "$a[] = $this->"+it+";";
}).join("\n");
console.log(code);output:
$a[] = $this->guid;
$a[] = $this->name;
$a[] = $this->coursecontent;
$a[] = $this->teacherids;
$a[] = $this->grades;
$a[] = $this->pricedescription;
$a[] = $this->packageimage;
$a[] = $this->servicecontent;
$a[] = $this->ispublish;
$a[] = $this->opentime;
$a[] = $this->closetime;
$a[] = $this->shelves;
$a[] = $this->unshelfves;
$a[] = $this->type;
$a[] = $this->tutortimes;
$a[] = $this->subject;
