composer下载就不说了
安装了composer
打开cmd 运行composer
有没有安装上composer 输入composer -v
切换到项目目录 composer下载 composer require phpoffice/phpspreadsheet
<?php
namespace frontend\controllers;
use Yii;
use yii\web\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
/**
* 导出Excel
*/
class ExcelController extends Controller
{
public function actionIndex()
{
$data = [
['title_A' => 'A', 'title_B' => 'B', 'title_C' => 'C', 'title_D' => 'D', 'title_E' => 'E', 'title_F' => 'F', 'title_G' => 'G'],
['title_A' => 'A1', 'title_B' => 'B1', 'title_C' => 'C1', 'title_D' => 'D1', 'title_E' => 'E1', 'title_F' => 'F1', 'title_G' => 'G1'],
['title_A' => 'A2', 'title_B' => 'B2', 'title_C' => 'C2', 'title_D' => 'D2', 'title_E' => 'E2', 'title_F' => 'F2', 'title_G' => 'G2'],
];
$data2 = [
['title_A' => 'A', 'title_B' => 'B', 'title_C' => 'C', 'title_D' => 'D', 'title_E' => 'E', 'title_F' => 'F', 'title_G' => 'G'],
['title_A' => 'A1', 'title_B' => 'B1', 'title_C' => 'C1', 'title_D' => 'D1', 'title_E' => 'E1', 'title_F' => 'F1', 'title_G' => 'G1'],
['title_A' => 'A2', 'title_B' => 'B2', 'title_C' => 'C2', 'title_D' => 'D2', 'title_E' => 'E2', 'title_F' => 'F2', 'title_G' => 'G2'],
];
$data3 = [
['title_A' => 'A', 'title_B' => 'B'],
['title_A' => 'A1', 'title_B' => 'B1'],
['title_A' => 'A2', 'title_B' => 'B2'],
];
$title = ['类别', '子类', '需求编号', '需求', '建议采用', '确认是否采用', '不采用原因'];
$title2 = ['类别', '子类', '需求编号', '需求', '建议采用', '确认是否采用', '不采用原因'];
$title3 = ['需求编号', '需求详解'];
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet()->setTitle('基础需求');
$sheet2 = $spreadsheet->createSheet(1)->setTitle('场景需求');
$sheet3 = $spreadsheet->createSheet(2)->setTitle('需求说明');
// 使用 setCellValueByColumnAndRow
//设置单元格内容
//设置表头
foreach ($title as $key => $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
}
foreach ($title2 as $key => $value) {
$sheet2->setCellValueByColumnAndRow($key + 1, 1, $value);
}
foreach ($title3 as $key => $value) {
$sheet3->setCellValueByColumnAndRow($key + 1, 1, $value);
}
// sheet1 基础安全需求
$row = 2; // 从第二行开始
foreach ($data as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
// sheet2 场景安全需求
$rowOfSheet2 = 2; // 从第二行开始
foreach ($data2 as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$sheet2->setCellValueByColumnAndRow($column, $rowOfSheet2, $value);
$column++;
}
$rowOfSheet2++;
}
// sheet3 安全需求详解
$rowOfSheet3 = 2; // 从第二行开始
foreach ($data3 as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$sheet3->setCellValueByColumnAndRow($column, $rowOfSheet3, $value);
$column++;
}
$rowOfSheet3++;
}
//设定样式
//所有sheet的表头样式 加粗
$font = [
'font' => [
'bold' => true,
],
];
//所有sheet的内容样式 加黑色边框
$borders = [
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'black'],
],
],
];
$sheet->getStyle('A1:G1')->applyFromArray($font);
$sheet->getColumnDimension('C')->setWidth(12);
$sheet->getColumnDimension('D')->setWidth(101);
$sheet->getColumnDimension('F')->setWidth(25);
$sheet->getColumnDimension('G')->setWidth(25);
$sheet2->getStyle('A1:G1')->applyFromArray($font);
$sheet2->getColumnDimension('C')->setWidth(12);
$sheet2->getColumnDimension('D')->setWidth(101);
$sheet2->getColumnDimension('F')->setWidth(25);
$sheet2->getColumnDimension('G')->setWidth(25);
$sheet3->getStyle('A1:B1')->applyFromArray($font);
$sheet3->getColumnDimension('A')->setWidth(15);
$sheet3->getColumnDimension('B')->setWidth(130);
$sheet->getStyle('A1:G' . ($row - 1))->applyFromArray($borders);
$sheet2->getStyle('A1:G' . ($rowOfSheet2 - 1))->applyFromArray($borders);
$sheet3->getStyle('A1:B' . ($rowOfSheet3 - 1))->applyFromArray($borders)->getAlignment()->setWrapText(true);
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="安全需求.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
}
}