phpspreadsheet Composer引入方法:

  1. composer require phpoffice/phpspreadsheet

引入命名空间

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

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

将数据处理好后,可以通过额外配置,将导出的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, $fileName = '', $options = [])
  20. {
  21. try {
  22. if (empty($datas)) {
  23. return false;
  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. if (isset($options['print']) && $options['print']) {
  40. /* 设置打印为A4效果 */
  41. $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
  42. /* 设置打印时边距 */
  43. $pValue = 1 / 2.54;
  44. $activeSheet->getPageMargins()->setTop($pValue / 2);
  45. $activeSheet->getPageMargins()->setBottom($pValue * 2);
  46. $activeSheet->getPageMargins()->setLeft($pValue / 2);
  47. $activeSheet->getPageMargins()->setRight($pValue / 2);
  48. }
  49. /* 行数据处理 */
  50. foreach ($datas as $sKey => $sItem) {
  51. /* 默认文本格式 */
  52. $pDataType = DataType::TYPE_STRING;
  53. /* 设置单元格格式 */
  54. if (isset($options['format']) && !empty($options['format'])) {
  55. $colRow = Coordinate::coordinateFromString($sKey);
  56. /* 存在该列格式并且有特殊格式 */
  57. if (isset($options['format'][$colRow[0]]) &&
  58. NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
  59. $activeSheet->getStyle($sKey)->getNumberFormat()
  60. ->setFormatCode($options['format'][$colRow[0]]);
  61. if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
  62. is_numeric(str_replace(['¥', ','], '', $sItem))) {
  63. /* 数字格式转换为数字单元格 */
  64. $pDataType = DataType::TYPE_NUMERIC;
  65. $sItem = str_replace(['¥', ','], '', $sItem);
  66. }
  67. } elseif (is_int($sItem)) {
  68. $pDataType = DataType::TYPE_NUMERIC;
  69. }
  70. }
  71. $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);
  72. /* 存在:形式的合并行列,列入A1:B2,则对应合并 */
  73. if (false !== strstr($sKey, ":")) {
  74. $options['mergeCells'][$sKey] = $sKey;
  75. }
  76. }
  77. unset($datas);
  78. /* 设置锁定行 */
  79. if (isset($options['freezePane']) && !empty($options['freezePane'])) {
  80. $activeSheet->freezePane($options['freezePane']);
  81. unset($options['freezePane']);
  82. }
  83. /* 设置宽度 */
  84. if (isset($options['setWidth']) && !empty($options['setWidth'])) {
  85. foreach ($options['setWidth'] as $swKey => $swItem) {
  86. $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
  87. }
  88. unset($options['setWidth']);
  89. }
  90. /* 设置背景色 */
  91. if (isset($options['setARGB']) && !empty($options['setARGB'])) {
  92. foreach ($options['setARGB'] as $sItem) {
  93. $activeSheet->getStyle($sItem)
  94. ->getFill()->setFillType(Fill::FILL_SOLID)
  95. ->getStartColor()->setARGB(Color::COLOR_YELLOW);
  96. }
  97. unset($options['setARGB']);
  98. }
  99. /* 设置公式 */
  100. if (isset($options['formula']) && !empty($options['formula'])) {
  101. foreach ($options['formula'] as $fKey => $fItem) {
  102. $activeSheet->setCellValue($fKey, $fItem);
  103. }
  104. unset($options['formula']);
  105. }
  106. /* 合并行列处理 */
  107. if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
  108. $activeSheet->setMergeCells($options['mergeCells']);
  109. unset($options['mergeCells']);
  110. }
  111. /* 设置居中 */
  112. if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
  113. $styleArray = [
  114. 'alignment' => [
  115. 'horizontal' => Alignment::HORIZONTAL_CENTER,
  116. 'vertical' => Alignment::VERTICAL_CENTER,
  117. ],
  118. ];
  119. foreach ($options['alignCenter'] as $acItem) {
  120. $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
  121. }
  122. unset($options['alignCenter']);
  123. }
  124. /* 设置加粗 */
  125. if (isset($options['bold']) && !empty($options['bold'])) {
  126. foreach ($options['bold'] as $bItem) {
  127. $activeSheet->getStyle($bItem)->getFont()->setBold(true);
  128. }
  129. unset($options['bold']);
  130. }
  131. /* 设置单元格边框,整个表格设置即可,必须在数据填充后才可以获取到最大行列 */
  132. if (isset($options['setBorder']) && $options['setBorder']) {
  133. $border = [
  134. 'borders' => [
  135. 'allBorders' => [
  136. 'borderStyle' => Border::BORDER_THIN, // 设置border样式
  137. 'color' => ['argb' => 'FF000000'], // 设置border颜色
  138. ],
  139. ],
  140. ];
  141. $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
  142. $activeSheet->getStyle($setBorder)->applyFromArray($border);
  143. unset($options['setBorder']);
  144. }
  145. $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');
  146. if (!isset($options['savePath'])) {
  147. /* 直接导出Excel,无需保存到本地,输出07Excel文件 */
  148. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  149. header(
  150. "Content-Disposition:attachment;filename=" . iconv(
  151. "utf-8", "GB2312//TRANSLIT", $fileName
  152. )
  153. );
  154. header('Cache-Control: max-age=0');//禁止缓存
  155. $savePath = 'php://output';
  156. } else {
  157. $savePath = $options['savePath'];
  158. }
  159. ob_clean();
  160. ob_start();
  161. $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
  162. $objWriter->save($savePath);
  163. /* 释放内存 */
  164. $objSpreadsheet->disconnectWorksheets();
  165. unset($objSpreadsheet);
  166. ob_end_flush();
  167. return true;
  168. } catch (Exception $e) {
  169. return false;
  170. }
  171. }

以上,便是比较通用的导入导出Excel操作了,同时,可以十分方便的根据自己需要做扩展和调整。

分类: PHP

标签:   php