PHPExcel早就停止更新維護，github上用的是phpspreadsheet。
composer安裝地址

composer require phpoffice/phpspreadsheet

<?php namespace Home\Controller;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; use Think\Controller;class OfficeController extends Controller {/*** 使用PHPEXECL導入** @param string $file 文件地址* @param int $sheet 工作表sheet(傳0則獲取第一個sheet)* @param int $columnCnt 列數(傳0則自動獲取最大列)* @param array $options 操作選項* array mergeCells 合并單元格數組* array formula 公式數組* array format 單元格格式數組** @return array* @throws Exception*/public static function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = []){try {/* 轉碼 */$file = iconv("utf-8", "gb2312", $file);if (empty($file) OR !file_exists($file)) {throw new \Exception('文件不存在!');}/** @var Xlsx $objRead */$objRead = IOFactory::createReader('Xlsx');if (!$objRead->canRead($file)) {/** @var Xls $objRead */$objRead = IOFactory::createReader('Xls');if (!$objRead->canRead($file)) {throw new \Exception('只支持導入Excel文件！');}}/* 如果不需要獲取特殊操作，則只讀內容，可以大幅度提升讀取Excel效率 */empty($options) && $objRead->setReadDataOnly(true);/* 建立excel對象 */$obj = $objRead->load($file);/* 獲取指定的sheet表 */$currSheet = $obj->getSheet($sheet);if (isset($options['mergeCells'])) {/* 讀取合并行列 */$options['mergeCells'] = $currSheet->getMergeCells();}if (0 == $columnCnt) {/* 取得最大的列號 */$columnH = $currSheet->getHighestColumn();/* 兼容原邏輯，循環時使用的是小于等于 */$columnCnt = Coordinate::columnIndexFromString($columnH);}/* 獲取總行數 */$rowCnt = $currSheet->getHighestRow();$data = [];/* 讀取內容 */for ($_row = 1; $_row <= $rowCnt; $_row++) {$isNull = true;for ($_column = 1; $_column <= $columnCnt; $_column++) {$cellName = Coordinate::stringFromColumnIndex($_column);$cellId = $cellName . $_row;$cell = $currSheet->getCell($cellId);if (isset($options['format'])) {/* 獲取格式 */$format = $cell->getStyle()->getNumberFormat()->getFormatCode();/* 記錄格式 */$options['format'][$_row][$cellName] = $format;}if (isset($options['formula'])) {/* 獲取公式，公式均為=號開頭數據 */$formula = $currSheet->getCell($cellId)->getValue();if (0 === strpos($formula, '=')) {$options['formula'][$cellName . $_row] = $formula;}}if (isset($format) && 'm/d/yyyy' == $format) {/* 日期格式翻轉處理 */$cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');}$data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());if (!empty($data[$_row][$cellName])) {$isNull = false;}}/* 判斷是否整行數據為空，是的話刪除該行數據 */if ($isNull) {unset($data[$_row]);}}return $data;} catch (\Exception $e) {throw $e;}}/*** Excel導出，TODO 可繼續優化** @param array $datas 導出數據，格式['A1' => 'XXXX公司報表', 'B1' => '序號'] * // $data["A1"] = "證件";* // $data["B1"] = "姓名";* // $data["C1"] = "案件";* @param string $fileName 導出文件名稱* @param array $options 操作選項，例如：* bool print 設置打印格式* string freezePane 鎖定行數，例如表頭為第一行，則鎖定表頭輸入A2* array setARGB 設置背景色，例如['A1', 'C1']* array setWidth 設置寬度，例如['A' => 30, 'C' => 20]* bool setBorder 設置單元格邊框* array mergeCells 設置合并單元格，例如['A1:J1' => 'A1:J1']* array formula 設置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']* array format 設置格式，整列設置，例如['A' => 'General']* array alignCenter 設置居中樣式，例如['A1', 'A2']* array bold 設置加粗樣式，例如['A1', 'A2']* string savePath 保存路徑，設置后則文件保存到服務器，不通過瀏覽器下載*/public static function exportExcel(array $datas, string $fileName = '', array $options = []): bool{try {if (empty($datas)) {return false;}set_time_limit(0);/** @var Spreadsheet $objSpreadsheet */$objSpreadsheet = new Spreadsheet();/* 設置默認文字居左，上下居中 */$styleArray = ['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT,'vertical' => Alignment::VERTICAL_CENTER,],];$objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);/* 設置Excel Sheet */$activeSheet = $objSpreadsheet->setActiveSheetIndex(0);/* 打印設置 */if (isset($options['print']) && $options['print']) {/* 設置打印為A4效果 */$activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);/* 設置打印時邊距 */$pValue = 1 / 2.54;$activeSheet->getPageMargins()->setTop($pValue / 2);$activeSheet->getPageMargins()->setBottom($pValue * 2);$activeSheet->getPageMargins()->setLeft($pValue / 2);$activeSheet->getPageMargins()->setRight($pValue / 2);}/* 行數據處理 */foreach ($datas as $sKey => $sItem) {/* 默認文本格式 */$pDataType = DataType::TYPE_STRING;/* 設置單元格格式 */if (isset($options['format']) && !empty($options['format'])) {$colRow = Coordinate::coordinateFromString($sKey);/* 存在該列格式并且有特殊格式 */if (isset($options['format'][$colRow[0]]) &&NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {$activeSheet->getStyle($sKey)->getNumberFormat()->setFormatCode($options['format'][$colRow[0]]);if (false !== strpos($options['format'][$colRow[0]], '0.00') &&is_numeric(str_replace(['￥', ','], '', $sItem))) {/* 數字格式轉換為數字單元格 */$pDataType = DataType::TYPE_NUMERIC;$sItem = str_replace(['￥', ','], '', $sItem);}} elseif (is_int($sItem)) {$pDataType = DataType::TYPE_NUMERIC;}}$activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);/* 存在:形式的合并行列，列入A1:B2，則對應合并 */if (false !== strstr($sKey, ":")) {$options['mergeCells'][$sKey] = $sKey;}}unset($datas);/* 設置鎖定行 */if (isset($options['freezePane']) && !empty($options['freezePane'])) {$activeSheet->freezePane($options['freezePane']);unset($options['freezePane']);}/* 設置寬度 */if (isset($options['setWidth']) && !empty($options['setWidth'])) {foreach ($options['setWidth'] as $swKey => $swItem) {$activeSheet->getColumnDimension($swKey)->setWidth($swItem);}unset($options['setWidth']);}/* 設置背景色 */if (isset($options['setARGB']) && !empty($options['setARGB'])) {foreach ($options['setARGB'] as $sItem) {$activeSheet->getStyle($sItem)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_YELLOW);}unset($options['setARGB']);}/* 設置公式 */if (isset($options['formula']) && !empty($options['formula'])) {foreach ($options['formula'] as $fKey => $fItem) {$activeSheet->setCellValue($fKey, $fItem);}unset($options['formula']);}/* 合并行列處理 */if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {$activeSheet->setMergeCells($options['mergeCells']);unset($options['mergeCells']);}/* 設置居中 */if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {$styleArray = ['alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,'vertical' => Alignment::VERTICAL_CENTER,],];foreach ($options['alignCenter'] as $acItem) {$activeSheet->getStyle($acItem)->applyFromArray($styleArray);}unset($options['alignCenter']);}/* 設置加粗 */if (isset($options['bold']) && !empty($options['bold'])) {foreach ($options['bold'] as $bItem) {$activeSheet->getStyle($bItem)->getFont()->setBold(true);}unset($options['bold']);}/* 設置單元格邊框，整個表格設置即可，必須在數據填充后才可以獲取到最大行列 */if (isset($options['setBorder']) && $options['setBorder']) {$border = ['borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, // 設置border樣式'color' => ['argb' => 'FF000000'], // 設置border顏色],],];$setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();$activeSheet->getStyle($setBorder)->applyFromArray($border);unset($options['setBorder']);}$fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');if (!isset($options['savePath'])) {/* 直接導出Excel，無需保存到本地，輸出07Excel文件 */header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=gb2312');header("Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName));header('Cache-Control: max-age=0');//禁止緩存$savePath = 'php://output';} else {$savePath = $options['savePath'];}ob_clean();ob_start();$objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');$objWriter->save($savePath);/* 釋放內存 */$objSpreadsheet->disconnectWorksheets();unset($objSpreadsheet);ob_end_flush();return true;} catch (\Exception $e) {return false;}}} 在這里插入代碼片

示例：

<?php namespace Home\Controller;use Think\Controller;class IndexController extends Controller {public function exout(){$data = M('user')->field('uid,username,location')->select();$head = array(array('uid'=>'用戶id','username'=>'用戶名','location'=>'地址'));$data = array_merge($head,$data); // dump($data);die();$exdata =array();foreach ($data as $k=>$v){$cell = $k+1;$exdata['A'.$cell] = $v['uid'];$exdata['B'.$cell] = $v['username'];$exdata['C'.$cell] = $v['location'];} // dump($exdata);die();$re = OfficeController::exportExcel($exdata,'testuser.xlsx');dump($re);}public function exin(){$data = OfficeController::importExecl('./testuser (1).xlsx');dump($data);} }

參考地址：https://blog.csdn.net/DestinyLordC/article/details/84071456

總結

以上是生活随笔為你收集整理的PHP对Excel导入导出操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。