一. 基本使用

getActiveSheet();//$spreadSheet->getProperties()->setCreator("hellow");//设置作者//$spreadSheet->getSheetCount();//工作表总数//$spreadSheet->getSheetNames();//工作表名数组//$sheet = $spreadSheet->getSheetByName('Sheet1');//根据表名获取工作表//$sheet = $spreadSheet->getSheet(0);//根据表索引获取工作表//$sheet = $spreadSheet->setActiveSheetIndex(0);//切换当前工作表//$sheet = $spreadsheet->setActiveSheetIndexByName('DataSheet')//设置打印选项$sheet->getPageSetup()->setFitToWidth(1);$sheet->getPageSetup()->setFitToHeight(0);$sheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)->setPaperSize(PageSetup::PAPERSIZE_A4);;$sheet->getPageMargins()->setTop(1)->setRight(0.75)->setLeft(0.75)->setBottom(1);$sheet->getPageSetup()->setHorizontalCentered(true)->setVerticalCentered(false);$sheet->setPrintGridlines(true);$sheet->getPageSetup()->setPrintArea('A1:E5,G4:M20');//设置缩放$sheet->getSheetView()->setZoomScale(75);//设置worksheet的颜色$sheet->getTabColor()->setRGB('FF0000');//设置worksheet名字$sheet->setTitle('sheet 1');//设置默认样式$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');$spreadsheet->getDefaultStyle()->getFont()->setSize(8);//获取文档所有值$data = $sheet->toArray();//获取最大行数 $res = $sheet->getHighestRow();//获取最大列数 $res = $sheet->getHighestColumn();//单元格信息$cell = $sheet->getCellByColumnAndRow(2, 1); //获取单元格 B1$cell = $sheet->getCell('A1'); //获取单元格A1$cell->getValue(); //获取单元格的数据值$cell->getCoordinate(); //获取行列信息 A1$column = $cell->getColumn(); //获取列信息 A$row   = $cell->getRow(); //获取行信息 1$cell->getDataType(); //获取数据类型$cell->setValue('8888'); //设置值$cell->getStyle()->getFont()->getName(); //单元格样式//设置行高$sheet->getDefaultRowDimension()->setRowHeight(10, 'mm');$sheet->getRowDimension(1)->setRowHeight(10, 'mm');//设置列宽// $sheet->getColumnDimension('A')->setAutoSize(true);//$sheet->getColumnDimension('A')->setWidth(300);//设置A列的宽度$sheet->getDefaultColumnDimension()->setWidth(20); //设置列默认宽度//设置换行$sheet->setCellValue('A2', "hellow\nphp"); //设置换行 双引号+\n+setWrapText$sheet->getStyle('A2')->getAlignment()->setWrapText(true); //设置换行//合并拆分单元格$sheet->mergeCells('B2:B3');// $sheet->unmergeCells('B2:B3');//设置值// $sheet->setCellValue('A1', "hellow\nphp");$sheet->setCellValueByColumnAndRow(2, 2, '6666'); //设置B2的值// $sheet->fromArray($arr, null, 'D5'); //数组,空值时填充值,开始单元格坐标//设置超链接$sheet->setCellValue('B3', "百度"); //设置A链接$sheet->getCell('B3')->getHyperlink()->setUrl('https://www.baidu.com');// $sheet->setCellValue('E26', 'www.phpexcel.net');// $sheet->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");//设置字体粗细大小颜色$sheet->getStyle('B3')->getFont()->setBold(true)->setName('Arial')->setSize(20);$sheet->getStyle('B3')->getFont()->getName(); //字体名$sheet->getStyle('B3')->getFont()->getColor()->setRGB('#AEEEEE'); //设置颜色$sheet->getStyle('B3')->getFont()->getColor()->getRGB(); //获取颜色值$sheet->getCell('C3')->setValue('2021-03-27 23:22:59');//$sheet->getStyle('D2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);//设置日期格式 $sheet->getStyle('D3')->getNumberFormat()->setFormatCode('dd/mm/yyyy'); //设置日期格式 与上文相同//设置单元格背景色$sheet->getStyle('E3')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);//设置单元格对齐方式$sheet->getStyle('B2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);$sheet->getStyle('B2')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//设置单元格边框$sheet->getStyle('C2:E2')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);//富文本$html = 'bold, italic, bold+italic';;$wizard = new HtmlHelper();$richText = $wizard->toRichTextObject($html);$sheet->setCellValue('D5', $richText);//公式$sheet->setCellValue('A4', '=IF(C4>500,"profit","loss")');// $formula = $sheet->getCell('A4')->getValue();// $value = $sheet->getCell('A4')->getCalculatedValue();//插入删除行列$sheet->insertNewRowBefore(7, 2);$sheet->removeRow(7, 2);//写入图片$drawing = new Drawing();$drawing->setName('Logo')->setDescription('Logo')->setPath('../files/1.jpg')->setHeight(30)->setCoordinates('D6')->setOffsetX(50)->setOffsetY(6);$drawing->setRotation(25);$drawing->getShadow()->setVisible(true);$drawing->getShadow()->setDirection(45);$drawing->setWorksheet($sheet);//设置单元格数据类型$sheet->getCell('A1')->setValueExplicit('25', DataType::TYPE_NUMERIC);//格式$styleArray = [    'font' => [        'bold' => true,    ],    'alignment' => [        'horizontal' => Alignment::HORIZONTAL_RIGHT,    ],    'borders' => [        'top' => [            'borderStyle' => Border::BORDER_THIN,        ],    ],    'fill' => [        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,        'rotation' => 90,        'startColor' => [            'argb' => 'FFA0A0A0',        ],        'endColor' => [            'argb' => 'FFFFFFFF',        ],    ],];$filename = '01simple.xlsx';$sheet->getStyle('A5:E5')->applyFromArray($styleArray);// // 保存xlsx在本地// $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');// $writer->save('01simple.xlsx');// 下载xlsxheader('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="' . $filename . '"');header('Cache-Control: max-age=0');header('Cache-Control: max-age=1');header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the pastheader('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modifiedheader('Cache-Control: cache, must-revalidate'); // HTTP/1.1header('Pragma: public'); // HTTP/1.0$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');$writer->save('PHP://output');exit;

二. 导出(写入)

getActiveSheet();        $sheet->setCellValue('A1', 'a1');        $sheet->setCellValue('A2', 'a2');        $sheet->setCellValue('A3', 'a3');        $sheet->setCellValue('A4', 'a4');        $sheet->setCellValue('B1', 'b1');        $sheet->setCellValue('B2', 'b2');        $sheet->setCellValue('B3', 'b3');        $sheet->setCellValue('B4', 'b4');        $writer = IOFactory::createWriter($spreadsheet, 'Xls');        $writer->save('php://output');        return;    }}

三. 导入(读取)

setReadDataOnly(TRUE);        $spreadsheet = $reader->load('Public/hello.xlsx'); //载入excel表格        $sheet = $spreadsheet->getSheet(0); // 读取第一個工作表        $highest_row = $sheet->getHighestRow(); // 取得总行数        $highest_columm = $sheet->getHighestColumn(); // 取得总列数        for ($row = 1; $row <= $highest_row; $row++){ //行号从1开始            for ($column = 'A'; $column <= $highest_columm; $column++){ //列数是以A列开始                $str = $sheet->getCell($column . $row)->getValue();                dump($str);            }        }    }}

四. 模版导出(读写)

setActiveSheetIndex(0);         // 指向激活的工作表        $worksheet = $spreadsheet->getActiveSheet();        $worksheet->getCell('A1')->setValue('John');        $worksheet->getCell('A2')->setValue('Smith');         // 样式设置 加边框        $styleThinBlackBorderOutline = array(            'borders' => [                'allBorders' => [                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,                    'color' => ['argb' => '666666'],                ],            ]        );        $worksheet->getStyle( 'A6:H'.$i)->applyFromArray($styleThinBlackBorderOutline);         // 结束 进行导出        ob_end_clean(); // 清空（擦除）缓冲区并关闭输出缓冲        ob_start(); // 打开输出控制缓冲         $date = date("Ymd",time());        $listname = 'hello'.$date;         header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');        header('Content-Disposition: attachment;filename="'.$listname . '.xlsx"');        header('Cache-Control: max-age=0');         $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');        $writer->save('php://output');         }    }}

来源地址：https://blog.csdn.net/withoutfear/article/details/127900108