一：安装phpoffice/phpspreadsheet
composer require phpoffice/phpspreadsheet二：phpoffice/phpspreadsheet API接口详解
PhpSpreadsheet提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到
在使用phpoffice/phpspreadsheet的API接口前，确保引入了正确的文件并实例化
use PhpOffice\PhpSpreadsheet\Spreadsheet;//引入文件$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();//创建一个新的excel文档$sheet = $spreadsheet->getActiveSheet();//获取当前操作sheet的对象1：设置字体：
$sheet->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial') ->setSize(10);//将A7至B7两单元格设置为粗体字，Arial字体，10号字$sheet->getStyle('B1')->getFont()->setBold(true);//将B1单元格设置为粗体字2：设置颜色
$sheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色3：设置列宽
$sheet->getColumnDimension('A')->setWidth(20);//将A列的宽度设为20(字符)$sheet->getColumnDimension('B')->setAutoSize(true);//将B列的宽度设为自动宽度$sheet->getDefaultColumnDimension()->setWidth(12);//设置默认列宽为124：设置行高
$sheet->getRowDimension('10')->setRowHeight(100);//将第十行的高度设为100pt$sheet->getDefaultRowDimension()->setRowHeight(15);//设置默认行高为155:对齐
$sheet->getStyle('A:D')->getAlignment() ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER) //设置垂直居中 ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER) //设置水平居中 ->setWrapText(true); //设置自动换行6：合并单元格
$sheet->mergeCells('A1:D2');//A1到D2合并为一个单元格7：将合并后的单元格拆分
$sheet->unmergeCells('A1:D2');//将合并后的单元格拆分。8：使用applyFromArray实现单元格样式设置
//样式变量$style = [//设置字体样式'font' => [ 'name' => 'Arial', 'bold' => true, 'italic' => false, 'underline' => Font::UNDERLINE_DOUBLE, 'strikethrough' => false, 'color' => [ 'rgb' => '808080' ] ],//设置边框线样式'borders' => [ //allBorders所有的边框线样式 //左边框线 'bottom' => [ 'borderStyle' => Border::BORDER_DASHDOT, 'color' => [ 'rgb' => '808080' ] ], //上边框线 'top' => [ 'borderStyle' => Border::BORDER_DASHDOT, 'color' => [ 'rgb' => '808080' ] ]],//对齐样式'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true,],//是否使用前缀'quotePrefix' => true];$sheet->getStyle('A1:D1')->applyFromArray($style);9：设置工作表标题
$sheet->setTitle('Hello');;//设置当前工作表标题。10：设置单元格的格式
$sheet->getStyle('D2')->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//将D2单元格的格式设为文本格式$sheet->getStyle('A1:D2')->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//将A1到D2的单元格设置为文本格式11:换行
$sheet->getCell('A4')->setValue("hello\nworld");//将A4单元格的hello和world换行12：超链接
//将A2单元格内容设置blog并点击跳转https://www.wj0511.com$sheet->setCellValue('A2', 'blog');$sheet->getCell('A2')->getHyperlink()->setUrl('https://www.wj0511.com');13：使用函数
常用函数有：总和(SUM)，最大数(MAX)，最小数(MIN)，平均值(AVERAGE)
$sheet->setCellValue('B5', '=SUM(B1:B4)');//将B5单元格的内容设为B1到B4的之和14：设置文档属性
$spreadsheet->getProperties() ->setCreator("author") //作者 ->setLastModifiedBy("last-author") //最后修改者 ->setTitle("title") //标题 ->setSubject("subject") //副标题 ->setDescription("description") //描述 ->setKeywords("keywords") //关键字 ->setCategory("category"); //分类三：简单实现生成excel(这里我下载使用的Yii框架自带的下载方法)
<?php/** * author: wangjian * date: 2019/7/15 */namespace app\controllers;use PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Style\Alignment;use PhpOffice\PhpSpreadsheet\Style\Border;use PhpOffice\PhpSpreadsheet\Writer\Xlsx;use Yii;use yii\web\Controller;class ExcelController extends Controller{ /** * 数字转字母 （类似于Excel列标） * @param Int $index 索引值 * @param Int $start 字母起始值 * @return String 返回字母 */ public function intToChr($index, $start = 65) { $str = ''; if (floor($index / 26) > 0) { $str .= $this->intToChr(floor($index / 26)-1); } return $str . chr($index % 26 + $start); } public function actionIndex() { //头信息 $header = [ '姓名', '性别', '学历', '年龄', '身高', ]; //内容 $data = [ [ '小明', '男', '专科', '18', '175' ], [ '小红', '女', '本科', '18', '155' ], [ '小蓝', '男', '专科', '20', '170' ], [ '张三', '男', '本科', '19', '165' ], [ '李四', '男', '专科', '22', '175' ], [ '王二', '男', '专科', '25', '175' ], [ '麻子', '男', '本科', '22', '180' ], ]; $header = array_values($header); $data = array_values($data); //获取列信息 $column = []; //['A','B','C','D','E'] foreach ($header as $k => $item) { $column[$k] = $this->intToChr($k); } //获取初始列和最终列 $firstColum = $column[0]; $lastColum = $column[count($column) - 1]; //获取初始行和最终行 $firstRow = 1; $lastRow = count($data) + 1; $row = 1; $spreadsheet = new Spreadsheet();//创建一个新的excel文档 $sheet = $spreadsheet->getActiveSheet();//获取当前操作sheet的对象 $sheet->setTitle('标题'); //设置标题 $sheet->getStyle("{$firstColum}:{$lastColum}")->getAlignment() ->setVertical(Alignment::VERTICAL_CENTER) //设置垂直居中 ->setHorizontal(Alignment::HORIZONTAL_CENTER) //设置水平居中 ->setWrapText(true); //设置自动换行 //设置宽度 $sheet->getDefaultColumnDimension()->setWidth(20); $headerStyle = [ 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, ], 'font' => [ 'bold' => true, 'size' => 14, ], ]; $cellStyle = [ 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, ], 'borders' => [ 'allBorders' => [ 'borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FF000000'], ] ], 'font' => [ 'size' => 10, ], ]; //将excel的单元格格式设为文本格式 $sheet->getStyle("{$firstColum}{$firstRow}:{$lastColum}{$lastRow}")->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT); //设置头信息样式 $sheet->getRowDimension($row)->setRowHeight(30);//设置行高 $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($headerStyle); //设置头信息 foreach ($header as $key => $item) { $sheet->setCellValue("{$column[$key]}{$row}", $item); } $row++; foreach ($data as $key => $model) { $sheet->getRowDimension($row)->setRowHeight(30);//设置行高 $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($cellStyle); $i = 0; foreach ($model as $value) { $sheet->setCellValue("{$column[$i]}{$row}", $value); $i++; } $row++; } $file = '表格' . '.xlsx';//保存地址 $writer = new Xlsx($spreadsheet); $writer->save($file);//生成excel文件 Yii::$app->response->sendFile($file, '下载的excel名称.xlsx')->send(); }}四：读取excel文件
$title = [];//excel工作表标题$info = [];//excel内容$fileName = "表格.xlsx";$spreadsheet = IOFactory::load($fileName);//$worksheet = $spreadsheet->getActiveSheet(); //获取当前文件内容$sheetAllCount = $spreadsheet->getSheetCount(); // 工作表总数for ($index = 0; $index < $sheetAllCount; $index++) { //工作表标题 $title[] = $spreadsheet->getSheet($index)->getTitle();}//读取第一個工作表$whatTable = 0;$sheet = $spreadsheet->getSheet($whatTable);$highest_row = $sheet->getHighestRow(); // 取得总行数$highest_column = $sheet->getHighestColumn(); ///取得列数 字母abc...$highestColumnIndex = Coordinate::columnIndexFromString($highest_column); //转化为数字;for ($i = 1; $i <= $highestColumnIndex; $i++) { for ($j = 1; $j <= $highest_row; $j++) { $conent = $sheet->getCellByColumnAndRow($i, $j)->getCalculatedValue(); $info[$j][$i] = $conent; }}var_dump($info);参考：https://phpspreadsheet.readth...