php使用phpSpreadsheet读写excel
本文转载自https://www.cnblogs.com/ryuma/p/14586372.html 和 https://www.cnblogs.com/woods1815/p/11372007.html
phpSpreadsheet
GitHub: https://github.com/PHPOffice/PhpSpreadsheet
composer require phpoffice/phpspreadsheet '1.19.0'
引入文件
<?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\IOFactory;//用于载入已有的xlsx文件 use PhpOffice\PhpSpreadsheet\Spreadsheet; use Phpoffice\PhpSpreadsheet\Shared\Date;//日期操作类 use PhpOffice\PhpSpreadsheet\Style\NumberFormat;//单元格格式类型 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;//保存xlsx文件
excel文档操作
$inputFileName = 'xabc.xlsx'; $spreadSheet = IOFactory::load($inputFileName);//载入xlsx文件 $spreadSheet = new Spreadsheet();//新建一个xlsx文件 $spreadSheet->getProperties()->setCreator("hellow");//设置xlsx作者 $resp = $spreadSheet->getSheetCount();//工作表总数 $resp = $spreadSheet->getSheetNames();//工作表名数组 $sheet = $spreadSheet->getSheetByName('Sheet1');//根据表名获取工作表 $sheet = $spreadSheet->getSheet(0);//根据表索引获取工作表 $sheet = $spreadSheet->setActiveSheetIndex(0);//切换当前工作表 $sheet = $spreadSheet->getActiveSheet();
工作表操作
//常用读取操作 $cell = $sheet->getCell('A1'); //获取单元格A1的值 $cell = $sheet->getCellByColumnAndRow(2, 1); //获取单元格 B1 $data = $sheet->toArray(); //获取文档所有值 $resp = $sheet->getHighestRow(); //最大行数 19 $resp = $sheet->getHighestColumn(); //最大列数 C //常用写操作 $sheet->setTitle('Hello'); //设置标题 $sheet->mergeCells('E7:F10'); //单元格合并 $sheet->unmergeCells('B7:C10'); //拆分单元格 $resp = $sheet->setCellValue('A1', "hellow\nphp"); //设置值 $resp = $sheet->setCellValueByColumnAndRow(2, 1, '6666'); //设置B2的值 $resp = $sheet->fromArray($arr, null, 'D5'); //数组,空值时填充值,开始单元格坐标 $sheet->getColumnDimension('A')->setWidth(300); //设置A列的宽度 $sheet->getDefaultColumnDimension()->setWidth(50); //设置列默认宽度 $sheet->getRowDimension(10)->setRowHeight(300); //设置第一行的宽度 $sheet->getDefaultRowDimension()->setRowHeight(50); //设置行默认高度 但不会修改已设置过高度/已有值的行 $resp = $sheet->setCellValue('B2', "hellow\nphp"); //设置换行 双引号+\n+setWrapText $resp = $sheet->getStyle('B2')->getAlignment()->setWrapText(true); //设置换行 $resp = $sheet->setCellValue('B2', "百度"); //设置A链接 $resp = $sheet->getCell('B3')->getHyperlink()->setUrl('https://www.baidu.com'); //设置换行 $resp = $sheet->getStyle('A1')->getFont()->setBold(true)->setName('Arial')->setSize(10); //设置字体加粗大小 $resp = $sheet->getStyle('A1')->getFont()->getName(); //字体名 $resp = $sheet->getStyle('B2')->getFont()->getColor()->setRGB('#AEEEEE'); //设置颜色 $resp = $sheet->getStyle('A1')->getFont()->getColor()->getRGB(); //获取颜色值 $sheet->getCell('D1')->setValue('2021-03-27 23:22:59'); $sheet->getCell('D2')->setValue(Date::PHPToExcel(time())); //设置日期 $sheet->getStyle('D2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY); //设置日期格式 $sheet->getStyle('D2')->getNumberFormat()->setFormatCode('dd/mm/yyyy');//设置日期格式 与上文相同
单元格操作
//常用读 $cell = $sheet->getCell('A1');//获取单元格A1的值 $resp = $cell->getValue();//获取单元个值 $resp = $cell->getCoordinate();//获取行列信息 A1 $column = $cell->getColumn();//获取列信息 A $row = $cell->getRow();//获取行信息 1 list($column, $row) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 ['A','1']; $resp = $cell->getDataType();//获取数据类型 //常用写 $resp = $cell->setValue('8888');//设置值 $cell->getStyle()->getFont()->getName();//单元个样式设置 参考工作表设置 只是不需要设置单元格位置而已 //设置A标签与换行参考工作表设置
图像操作
//图片读取与存储 $draws = $sheet->getDrawingCollection();//获取所有图像 $imageFilePath = 'uploads/'; foreach ($draws as $drawing) {//$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例; $coordi = $drawing->getCoordinates();//获取图像坐标 eg A4 list($startColumn, $startRow) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 ['A','4']; $imageFileName = $imageFilePath.$coordi.'_'. mt_rand(1000, 9999); switch ($drawing->getExtension()) { case 'jpg': case 'jpeg': $imageFileName .= '.jpg'; $source = imagecreatefromjpeg($drawing->getPath()); $res = imagejpeg($source, $imageFileName); break; case 'gif': $imageFileName .= '.gif'; $source = imagecreatefromgif($drawing->getPath()); imagegif($source, $imageFileName); break; case 'png': $imageFileName .= '.png'; $source = imagecreatefrompng($drawing->getPath()); $res = imagepng($source, $imageFileName); break; } } //图像写入操作 $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setWorksheet($sheet); $drawing->setPath(dirname(__FILE__).'/'.$imageFileName); $drawing->setWidth(200);//图片宽 $drawing->setHeight(200);//图片高 $drawing->setOffsetX(100);//设置图片偏移量 $drawing->setCoordinates('A5');//将图片放置于单元格
excel文件导出实例
/** * excel文件导出 */ function export() { require_once __DIR__ . '/vendor/autoload.php'; $data = [ ['title1' => '111', 'title2' => '222'], ['title1' => '111', 'title2' => '222'], ['title1' => '111', 'title2' => '222'] ]; $title = ['第一行标题', '第二行标题']; // Create new Spreadsheet object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // 方法一,使用 setCellValueByColumnAndRow //表头 //设置单元格内容 foreach ($title as $key => $value) { // 单元格内容写入 $sheet->setCellValueByColumnAndRow($key + 1, 1, $value); } $row = 2; // 从第二行开始 foreach ($data as $item) { $column = 1; foreach ($item as $value) { // 单元格内容写入 $sheet->setCellValueByColumnAndRow($column, $row, $value); $column++; } $row++; } // 方法二,使用 setCellValue //表头 //设置单元格内容 $titCol = 'A'; foreach ($title as $key => $value) { // 单元格内容写入 $sheet->setCellValue($titCol . '1', $value); $titCol++; } $row = 2; // 从第二行开始 foreach ($data as $item) { $dataCol = 'A'; foreach ($item as $value) { // 单元格内容写入 $sheet->setCellValue($dataCol . $row, $value); $dataCol++; } $row++; } // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="01simple.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 = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); exit; }
excel文件保存到本地
/** * excel文件保存到本地 */ function save() { require_once __DIR__ . '/vendor/autoload.php'; $data = [ ['title1' => '111', 'title2' => '222'], ['title1' => '111', 'title2' => '222'], ['title1' => '111', 'title2' => '222'] ]; $title = ['第一行标题', '第二行标题']; // Create new Spreadsheet object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //表头 //设置单元格内容 $titCol = 'A'; foreach ($title as $key => $value) { // 单元格内容写入 $sheet->setCellValue($titCol . '1', $value); $titCol++; } $row = 2; // 从第二行开始 foreach ($data as $item) { $dataCol = 'A'; foreach ($item as $value) { // 单元格内容写入 $sheet->setCellValue($dataCol . $row, $value); $dataCol++; } $row++; } // Save $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('01simple.xlsx'); }
读取excel文件内容
/** * 读取excel文件内容 */ function read() { require_once __DIR__ . '/vendor/autoload.php'; $inputFileName = dirname(__FILE__) . '/01simple.xlsx'; $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName); // 方法二 $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); return $sheetData; }