首页-教程-开发语言

如何使用PhpSpreadsheet导入导出Excel

 4674

本篇文章给大家介绍一下使用PhpSpreadsheet导入导出Excel的方法。有一定的参考价值，有需要的朋友可以参考一下，希望对大家有所帮助。


如何使用PhpSpreadsheet导入导出Excel


PHP对Excel导入&导出操作

最近公司要做报表功能，各种财务报表、工资报表、考勤报表等，复杂程度让人头大，于是特地封装适用各大场景的导入&导出操作，希望各界大神支出不足之处，以便小弟继续完善。


phpspreadsheet 引入

由于PHPExcel早就停止更新维护，所以适用phpspreadsheet。不知道如何通过composer拉取项目包的同学，可以查看Composer学习一文。引入方法：

  1. composer require phpoffice/phpspreadsheet


引入命名空间

由于本人项目中需要居中、背景、单元格格式等各种操作，所以引入较多，大家使用的时候，可以根据自己实际需要引入。

  1. use PhpOffice\PhpSpreadsheet\Reader\Xlsx;use PhpOffice\PhpSpreadsheet\Reader\Xls;use PhpOffice\PhpSpreadsheet\IOFactory;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;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;


Excel导入操作(importExcel)

除了单纯的处理Excel数据外，还可以将Excel中的合并项、公式项、单元格格式提取，提取后可根据业务需求做对应处理后存储起来，以便后续的各种操作。

  1. /**
  2.  * 使用PHPEXECL导入
  3.  *
  4.  * @param string $file      文件地址
  5.  * @param int    $sheet     工作表sheet(传0则获取第一个sheet)
  6.  * @param int    $columnCnt 列数(传0则自动获取最大列)
  7.  * @param array  $options   操作选项
  8.  *                          array mergeCells 合并单元格数组
  9.  *                          array formula    公式数组
  10.  *                          array format     单元格格式数组
  11.  *
  12.  * @return array
  13.  * @throws Exception
  14.  */
  15. function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = []){
  16.     try {
  17.         /* 转码 */
  18.         $file = iconv("utf-8", "gb2312", $file);
  19.  
  20.         if (empty($file) OR !file_exists($file)) {
  21.             throw new \Exception('文件不存在!');
  22.         }
  23.  
  24.         /** @var Xlsx $objRead */
  25.         $objRead = IOFactory::createReader('Xlsx');
  26.  
  27.         if (!$objRead->canRead($file)) {
  28.             /** @var Xls $objRead */
  29.             $objRead = IOFactory::createReader('Xls');
  30.  
  31.             if (!$objRead->canRead($file)) {
  32.                 throw new \Exception('只支持导入Excel文件！');
  33.             }
  34.         }
  35.  
  36.         /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
  37.         empty($options) && $objRead->setReadDataOnly(true);
  38.         /* 建立excel对象 */
  39.         $obj = $objRead->load($file);
  40.         /* 获取指定的sheet表 */
  41.         $currSheet = $obj->getSheet($sheet);
  42.  
  43.         if (isset($options['mergeCells'])) {
  44.             /* 读取合并行列 */
  45.             $options['mergeCells'] = $currSheet->getMergeCells();
  46.         }
  47.  
  48.         if (0 == $columnCnt) {
  49.             /* 取得最大的列号 */
  50.             $columnH = $currSheet->getHighestColumn();
  51.             /* 兼容原逻辑，循环时使用的是小于等于 */
  52.             $columnCnt = Coordinate::columnIndexFromString($columnH);
  53.         }
  54.  
  55.         /* 获取总行数 */
  56.         $rowCnt = $currSheet->getHighestRow();
  57.         $data   = [];
  58.  
  59.         /* 读取内容 */
  60.         for ($_row = 1; $_row <= $rowCnt; $_row++) {
  61.             $isNull = true;
  62.  
  63.             for ($_column = 1; $_column <= $columnCnt; $_column++) {
  64.                 $cellName = Coordinate::stringFromColumnIndex($_column);
  65.                 $cellId   = $cellName . $_row;
  66.                 $cell     = $currSheet->getCell($cellId);
  67.  
  68.                 if (isset($options['format'])) {
  69.                     /* 获取格式 */
  70.                     $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
  71.                     /* 记录格式 */
  72.                     $options['format'][$_row][$cellName] = $format;
  73.                 }
  74.  
  75.                 if (isset($options['formula'])) {
  76.                     /* 获取公式，公式均为=号开头数据 */
  77.                     $formula = $currSheet->getCell($cellId)->getValue();
  78.  
  79.                     if (0 === strpos($formula, '=')) {
  80.                         $options['formula'][$cellName . $_row] = $formula;
  81.                     }
  82.                 }
  83.  
  84.                 if (isset($format) && 'm/d/yyyy' == $format) {
  85.                     /* 日期格式翻转处理 */
  86.                     $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
  87.                 }
  88.  
  89.                 $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
  90.  
  91.                 if (!empty($data[$_row][$cellName])) {
  92.                     $isNull = false;
  93.                 }
  94.             }
  95.  
  96.             /* 判断是否整行数据为空，是的话删除该行数据 */
  97.             if ($isNull) {
  98.                 unset($data[$_row]);
  99.             }
  100.         }
  101.  
  102.         return $data;
  103.     } catch (\Exception $e) {
  104.         throw $e;
  105.     }
  106. }

将数据处理好后，可以通过额外配置，将导出的Excel做各种不同的配置，例如打印样式、锁定行、背景色、宽度等。


Excel导出操作(exportExcel)

  1. /**
  2.  * Excel导出，TODO 可继续优化
  3.  *
  4.  * @param array  $datas      导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
  5.  * @param string $fileName   导出文件名称
  6.  * @param array  $options    操作选项，例如：
  7.  *                           bool   print       设置打印格式
  8.  *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
  9.  *                           array  setARGB     设置背景色，例如['A1', 'C1']
  10.  *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
  11.  *                           bool   setBorder   设置单元格边框
  12.  *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
  13.  *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
  14.  *                           array  format      设置格式，整列设置，例如['A' => 'General']
  15.  *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
  16.  *                           array  bold        设置加粗样式，例如['A1', 'A2']
  17.  *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
  18.  */
  19. function exportExcel(array $datas, string $fileName = '', array $options = []): bool{
  20.     try {
  21.         if (empty($datas)) {
  22.             return false;
  23.         }
  24.  
  25.         set_time_limit(0);
  26.         /** @var Spreadsheet $objSpreadsheet */
  27.         $objSpreadsheet = app(Spreadsheet::class);
  28.         /* 设置默认文字居左，上下居中 */
  29.         $styleArray = [
  30.             'alignment' => [
  31.                 'horizontal' => Alignment::HORIZONTAL_LEFT,
  32.                 'vertical'   => Alignment::VERTICAL_CENTER,
  33.             ],
  34.         ];
  35.         $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
  36.         /* 设置Excel Sheet */
  37.         $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);
  38.  
  39.         /* 打印设置 */
  40.         if (isset($options['print']) && $options['print']) {
  41.             /* 设置打印为A4效果 */
  42.             $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
  43.             /* 设置打印时边距 */
  44.             $pValue = 1 / 2.54;
  45.             $activeSheet->getPageMargins()->setTop($pValue / 2);
  46.             $activeSheet->getPageMargins()->setBottom($pValue * 2);
  47.             $activeSheet->getPageMargins()->setLeft($pValue / 2);
  48.             $activeSheet->getPageMargins()->setRight($pValue / 2);
  49.         }
  50.  
  51.         /* 行数据处理 */
  52.         foreach ($datas as $sKey => $sItem) {
  53.             /* 默认文本格式 */
  54.             $pDataType = DataType::TYPE_STRING;
  55.  
  56.             /* 设置单元格格式 */
  57.             if (isset($options['format']) && !empty($options['format'])) {
  58.                 $colRow = Coordinate::coordinateFromString($sKey);
  59.  
  60.                 /* 存在该列格式并且有特殊格式 */
  61.                 if (isset($options['format'][$colRow[0]]) &&
  62.                     NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
  63.                     $activeSheet->getStyle($sKey)->getNumberFormat()
  64.                         ->setFormatCode($options['format'][$colRow[0]]);
  65.  
  66.                     if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
  67.                         is_numeric(str_replace(['￥', ','], '', $sItem))) {
  68.                         /* 数字格式转换为数字单元格 */
  69.                         $pDataType = DataType::TYPE_NUMERIC;
  70.                         $sItem     = str_replace(['￥', ','], '', $sItem);
  71.                     }
  72.                 } elseif (is_int($sItem)) {
  73.                     $pDataType = DataType::TYPE_NUMERIC;
  74.                 }
  75.             }
  76.  
  77.             $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);
  78.  
  79.             /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
  80.             if (false !== strstr($sKey, ":")) {
  81.                 $options['mergeCells'][$sKey] = $sKey;
  82.             }
  83.         }
  84.  
  85.         unset($datas);
  86.  
  87.         /* 设置锁定行 */
  88.         if (isset($options['freezePane']) && !empty($options['freezePane'])) {
  89.             $activeSheet->freezePane($options['freezePane']);
  90.             unset($options['freezePane']);
  91.         }
  92.  
  93.         /* 设置宽度 */
  94.         if (isset($options['setWidth']) && !empty($options['setWidth'])) {
  95.             foreach ($options['setWidth'] as $swKey => $swItem) {
  96.                 $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
  97.             }
  98.  
  99.             unset($options['setWidth']);
  100.         }
  101.  
  102.         /* 设置背景色 */
  103.         if (isset($options['setARGB']) && !empty($options['setARGB'])) {
  104.             foreach ($options['setARGB'] as $sItem) {
  105.                 $activeSheet->getStyle($sItem)
  106.                     ->getFill()->setFillType(Fill::FILL_SOLID)
  107.                     ->getStartColor()->setARGB(Color::COLOR_YELLOW);
  108.             }
  109.  
  110.             unset($options['setARGB']);
  111.         }
  112.  
  113.         /* 设置公式 */
  114.         if (isset($options['formula']) && !empty($options['formula'])) {
  115.             foreach ($options['formula'] as $fKey => $fItem) {
  116.                 $activeSheet->setCellValue($fKey, $fItem);
  117.             }
  118.  
  119.             unset($options['formula']);
  120.         }
  121.  
  122.         /* 合并行列处理 */
  123.         if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
  124.             $activeSheet->setMergeCells($options['mergeCells']);
  125.             unset($options['mergeCells']);
  126.         }
  127.  
  128.         /* 设置居中 */
  129.         if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
  130.             $styleArray = [
  131.                 'alignment' => [
  132.                     'horizontal' => Alignment::HORIZONTAL_CENTER,
  133.                     'vertical'   => Alignment::VERTICAL_CENTER,
  134.                 ],
  135.             ];
  136.  
  137.             foreach ($options['alignCenter'] as $acItem) {
  138.                 $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
  139.             }
  140.  
  141.             unset($options['alignCenter']);
  142.         }
  143.  
  144.         /* 设置加粗 */
  145.         if (isset($options['bold']) && !empty($options['bold'])) {
  146.             foreach ($options['bold'] as $bItem) {
  147.                 $activeSheet->getStyle($bItem)->getFont()->setBold(true);
  148.             }
  149.  
  150.             unset($options['bold']);
  151.         }
  152.  
  153.         /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
  154.         if (isset($options['setBorder']) && $options['setBorder']) {
  155.             $border    = [
  156.                 'borders' => [
  157.                     'allBorders' => [
  158.                         'borderStyle' => Border::BORDER_THIN, // 设置border样式
  159.                         'color'       => ['argb' => 'FF000000'], // 设置border颜色
  160.                     ],
  161.                 ],
  162.             ];
  163.             $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
  164.             $activeSheet->getStyle($setBorder)->applyFromArray($border);
  165.             unset($options['setBorder']);
  166.         }
  167.  
  168.         $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');
  169.  
  170.         if (!isset($options['savePath'])) {
  171.             /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
  172.             header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  173.             header(
  174.                 "Content-Disposition:attachment;filename=" . iconv(
  175.                     "utf-8", "GB2312//TRANSLIT", $fileName
  176.                 )
  177.             );
  178.             header('Cache-Control: max-age=0');//禁止缓存
  179.             $savePath = 'php://output';
  180.         } else {
  181.             $savePath = $options['savePath'];
  182.         }
  183.  
  184.         ob_clean();
  185.         ob_start();
  186.         $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
  187.         $objWriter->save($savePath);
  188.         /* 释放内存 */
  189.         $objSpreadsheet->disconnectWorksheets();
  190.         unset($objSpreadsheet);
  191.         ob_end_flush();
  192.  
  193.         return true;
  194.     } catch (Exception $e) {
  195.         return false;
  196.     }
  197. }


TAG标签：
本文网址：https://www.zztuku.com/index.php/detail-8900.html
站长图库 - 如何使用PhpSpreadsheet导入导出Excel
申明：如有侵犯，请 联系我们 删除。

评论（0）条

您还没有登录，请 登录 后发表评论！

提示：请勿发布广告垃圾评论，否则封号处理！！

    编辑推荐