php操作excel代码封装

技术 置顶 精帖
0 442
peng49
peng49 2022-12-21 11:24:15
 
  1. <?php
  2. class tools {
  3. /**
  4. * 描述: 处理导入的excel文件
  5. */
  6. public static function importExcel($filename, callable $callback) {
  7. if (!file_exists($filename)) {
  8. throw new Exception("文件{$filename}不存在", 404);
  9. }
  10. //读取文件
  11. $fileType = PHPExcel_IOFactory::identify($filename);
  12. $objReader = PHPExcel_IOFactory::createReader($fileType);
  13. /** @var $objPHPExcel PHPExcel * */
  14. $objPHPExcel = $objReader->load($filename);
  15. $sheet = $objPHPExcel->getActiveSheet();
  16. $highestRow = $sheet->getHighestRow();
  17. $highestColumn = $sheet->getHighestColumn();
  18. $result = [];
  19. $keys = array_map('trim', $sheet->rangeToArray("A1:{$highestColumn}1", null, false, false)[0]);
  20. for ($row = 2; $row <= $highestRow; $row++) {
  21. $values = array_map('trim', $sheet->rangeToArray("A{$row}:{$highestColumn}{$row}", null, false, true)[0]);
  22. foreach ($values as $key => $value) {
  23. $column = self::int2column($key);
  24. if (preg_match('/^=\w+.*\)$/', $value)) {
  25. $values[$key] = $sheet->getCell("{$column}{$row}")->getOldCalculatedValue();
  26. }
  27. }
  28. $result[] = call_user_func($callback, array_combine($keys, $values), $sheet, $row);
  29. }
  30. return $result;
  31. }
  32. /**
  33. * 描述: 导出excel
  34. */
  35. public static function exportExcel(callable $callback, $name = '') {
  36. $objPHPExcel = new PHPExcel();
  37. $sheet = $objPHPExcel->getActiveSheet();
  38. //$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  39. call_user_func($callback, $objPHPExcel, $sheet);
  40. ob_end_clean();//清除缓冲区,避免乱码
  41. header('Content-Type: application/vnd.ms-excel');
  42. header('Content-Disposition: attachment;filename="' . $name . date('YmnHis') . '.xlsx"');
  43. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //xls 标准
  44. $objWriter->save('php://output');
  45. }
  46. /**
  47. * 描述: 整数转换为excel的列名 0 => A ,1 => B.... 27 => AA, 依次类推
  48. */
  49. public static function int2column($i) {
  50. if ($i > 26 * 26 + 25) {
  51. throw new Exception('数值过大', 400);
  52. }
  53. $number = 65 + $i;
  54. $start = intval(($number - 65) / 26);
  55. $prefix = "";
  56. if ($start >= 1) {
  57. $prefix = chr(65 + $start - 1);
  58. }
  59. return "{$prefix}" . chr($number - 26 * $start);
  60. }
  61. /**
  62. * 描述: excel 导入模板下载
  63. */
  64. public static function excelImportTemplate(array $headers, $name = '') {
  65. self::exportExcel(function (PHPExcel $objPHPExcel, PHPExcel_Worksheet $sheet) use ($headers) {
  66. $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//居中
  67. $keys = array_keys($headers);
  68. $rowCount = 1;
  69. if ($keys[0] !== 0) {
  70. foreach ($keys as $key => $title) {
  71. $column = self::int2column($key);
  72. $sheet->getColumnDimension($column)->setWidth(2.5 * (mb_strlen($title) + 1));
  73. $sheet->setCellValue("{$column}{$rowCount}", $title);
  74. }
  75. $rowCount++;
  76. }
  77. $values = array_values($headers);
  78. foreach ($values as $key => $title) {
  79. $column = self::int2column($key);
  80. if ($rowCount === 1) {
  81. $sheet->getColumnDimension($column)->setWidth(2.5 * (mb_strlen($title) + 1));
  82. }
  83. $sheet->setCellValue("{$column}{$rowCount}", $title);
  84. }
  85. }, $name);
  86. }
  87. /**
  88. * 描述: 处理Excel读取的日期数据
  89. */
  90. public static function parseExcelGetDateValue($value, $default = '2000-01-01') {
  91. if (preg_match('/\d{4}[\-\/]\d{1,2}[\-\/]\d{1,2}/', trim($value))) {// 2023-12-1
  92. return date('Y-m-d', strtotime(trim($value)));
  93. }
  94. if (preg_match('/\d{2}[\-\/]\d{2}[\-\/]\d{2}/', trim($value))) {// 02-15-22
  95. $value = preg_replace('/(\d{2})[\-\/](\d{2})[\-\/](\d{2})/', '$3-$1-$2', $value);
  96. return date('Y-m-d', strtotime(trim($value)));
  97. }
  98. if (preg_match('/^(\d+)$/', $value) && $value > 0) {
  99. return gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value));
  100. }
  101. return $default;
  102. }
  103. }
回帖
登录
忘记密码?