PhpSpreadsheet 基本使用和导入导出模版生成Excel文件

withoutfear

2024-04-24 帮助1人

一. 基本使用

<?php
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.百度.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;

二. 导出(写入)

<?php
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;
}
}

三. 导入(读取)

<?php
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);
}
}
}
}

四. 模版导出(读写)

<?php
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');
}
}
}

这篇好文章是转载于：学新通技术网

PhpSpreadsheet 基本使用和导入导出模版生成Excel文件

一. 基本使用

二. 导出(写入)

三. 导入(读取)

四. 模版导出(读写)

photoshop保存的图片太大微信发不了怎么办

Android 11 保存文件到外部存储，并分享文件

word里面弄一个表格后上面的标题会跑到下面怎么办

《学习通》视频自动暂停处理方法

photoshop扩展功能面板显示灰色怎么办

微信公众号没有声音提示怎么办

excel下划线不显示怎么办

excel打印预览压线压字怎么办

怎样阻止微信小程序自动打开

TikTok加速器哪个好免费的TK加速器推荐

PhpSpreadsheet 基本使用和导入 导出 模版生成Excel文件

一. 基本使用

二. 导出(写入)

三. 导入(读取)

四. 模版导出(读写)

photoshop保存的图片太大微信发不了怎么办

Android 11 保存文件到外部存储，并分享文件

word里面弄一个表格后上面的标题会跑到下面怎么办

《学习通》视频自动暂停处理方法

photoshop扩展功能面板显示灰色怎么办

微信公众号没有声音提示怎么办

excel下划线不显示怎么办

excel打印预览压线压字怎么办

怎样阻止微信小程序自动打开

TikTok加速器哪个好免费的TK加速器推荐

PhpSpreadsheet 基本使用和导入导出模版生成Excel文件