PhpSpreadsheet 基本使用和导入 导出 模版生成Excel文件
一. 基本使用
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\Drawing; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Border; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Helper\Html as HtmlHelper; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; $spreadsheet = new Spreadsheet(); //新建文件 // $spreadSheet = IOFactory::load($inputFileName); //载入文件 $sheet = $spreadsheet->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'); // 下载xlsx header('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 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;
二. 导出(写入)
namespace app\admin\controller\Index; use app\common\controller\Backend; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Csv; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Helper\Sample; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Border; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; class Index extends Backend { // 导出(写入) public function export(){ $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->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; } }
三. 导入(读取)
namespace app\admin\controller\Index; use app\common\controller\Backend; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Csv; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Helper\Sample; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Border; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; class Index extends Backend { // 导入(读取) public function import(){ $reader = IOFactory::createReader('Xlsx'); $reader->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); } } } }
四. 模版导出(读写)
namespace app\admin\controller\Index; use app\common\controller\Backend; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Csv; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Helper\Sample; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\Alignment; use PhpOffice\PhpSpreadsheet\Style\Border; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; class Index extends Backend { // 模版导出(读写) public function templateExport() { $template_path = __DIR__ . '/PayrollTemplate.xls'; // 读取模板 $spreadsheet = IOFactory::load($template_file_address); // 以下代码行将活动工作表索引设置为第一个工作表:设置Excel Sheet $spreadsheet->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'); } } }