導(dǎo)入Excel基本思路：

接收前端傳過(guò)來(lái)的Excel文件

將Excel文件轉(zhuǎn)化為一個(gè)數(shù)組

將數(shù)組導(dǎo)入到數(shù)據(jù)庫(kù)

從百度下載一個(gè)PHPExcel類：https://github.com/PHPOffice/PHPExcel

用IOFactory.php類

//導(dǎo)入Excelpublic function importExcel(){require_once ROOT_PATH . 'PHPExcel/Classes/PHPExcel/IOFactory.php';//導(dǎo)入PHPExcel文件中的IOFactory.php類$file = request()->file('file');//獲取文件，file是請(qǐng)求的參數(shù)名$info = $file->move(ROOT_PATH . 'public' . DS . 'excel');//move將文件移動(dòng)到項(xiàng)目文件的xxxif($info){$excel_path = $info->getSaveName(); //獲取上傳文件名 // $excel_suffix = $info->getExtension(); //獲取上傳文件后綴$file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $excel_path; //上傳文件的地址$obj_PHPExcel = \PHPExcel_IOFactory::load($file_name); //加載文件內(nèi)容 // echo "<pre>";$excel_array=$obj_PHPExcel->getsheet(0)->toArray(); //轉(zhuǎn)換為數(shù)組格式array_shift($excel_array); //刪除第一個(gè)數(shù)組(標(biāo)題);$arr = reset($excel_array); //獲取字段名unset($excel_array[0]); //刪除字段名，剩下的都是存儲(chǔ)到數(shù)據(jù)庫(kù)的數(shù)據(jù)了！！$data = [];for($i = 0;$i < count($excel_array);$i++){foreach ($arr as $key => $value){$data[$i][$value] = $excel_array[$i+1][$key];//使數(shù)組的鍵值就是數(shù)據(jù)表的字段名}}$res = GoodsModel::getInstance()->insertAll($data);var_dump($res);}}

導(dǎo)出Excel：

方法一：用PHPExcel.php類

//導(dǎo)出Excelpublic function exportExcel2(){// 引入phpexcel核心類文件require_once ROOT_PATH . 'PHPExcel/Classes/PHPExcel.php';//實(shí)例化excel類$objPHPExcel = new \PHPExcel();// 操作第一個(gè)工作表$objPHPExcel->setActiveSheetIndex(0);// 設(shè)置sheet名$objPHPExcel->getActiveSheet()->setTitle('xx列表');// 設(shè)置表格寬度$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);// 列名表頭文字加粗$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);// 列表頭文字居中$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 列名賦值$objPHPExcel->getActiveSheet()->setCellValue('A1', '貨品名稱');$objPHPExcel->getActiveSheet()->setCellValue('B1', '規(guī)格型號(hào)');$objPHPExcel->getActiveSheet()->setCellValue('C1', '包裝單位');$objPHPExcel->getActiveSheet()->setCellValue('D1', '規(guī)格單位');$objPHPExcel->getActiveSheet()->setCellValue('E1', '包裝規(guī)格');$objPHPExcel->getActiveSheet()->setCellValue('F1', '標(biāo)準(zhǔn)售價(jià)(元)');$field = ['name', 'spec_model', 'pack_unit', 'spec_unit','pack_spec', 'price'];//第二行列字段內(nèi)容$res = GoodsModel::getInstance()->field($field)->where(['company_id'=>$this->company_id])->limit(2)//限制條數(shù)->select();// 數(shù)據(jù)起始行$row_num = 2;// 向每行單元格插入數(shù)據(jù)foreach($res as $value){// 設(shè)置所有垂直居中$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// 設(shè)置價(jià)格為數(shù)字格式$objPHPExcel->getActiveSheet()->getStyle('F' . $row_num)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);// 居中$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'F' . $row_num)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 設(shè)置單元格數(shù)值$objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['name']);$objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['spec_model']);$objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['pack_unit']);$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['spec_unit']);$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['pack_spec']);$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['price']); // $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['state'] ? '√' : '×'); // $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, date('Y-m-d h:i:s',$value['statetime']));$row_num++;}$outputFileName = 'goods_' . time() . '.xls';//導(dǎo)出的文件名$xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);header("Content-Type: application/force-download");//告訴瀏覽器強(qiáng)制下載header("Content-Type: application/octet-stream");header("Content-Type: application/download");header('Content-Disposition:inline;filename="' . $outputFileName . '"');//attachment作為附件下載,inline在線下載,filename設(shè)置文件名header("Content-Transfer-Encoding: binary");header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");//設(shè)置瀏覽器響應(yīng)緩存header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$xlsWriter->save("php://output");echo file_get_contents($outputFileName);}

方法二：直接用頭部信息輸出excel格式文件，內(nèi)容以表格形式展示?。這種方法可能會(huì)導(dǎo)致excel文件格式和擴(kuò)展名不一致，文件無(wú)法用于導(dǎo)入，如果只是導(dǎo)出看看數(shù)據(jù)可用這種方法。推薦方法一

把數(shù)據(jù)從數(shù)據(jù)庫(kù)中查詢出來(lái)?

把查詢出來(lái)的數(shù)據(jù)拼接到table標(biāo)簽中

調(diào)用設(shè)置好的header(頭域)函數(shù)

//導(dǎo)出Excelpublic function exportExcel($action,$id = []){switch ($action){case 'HP':$comment = ['貨品名稱','規(guī)格型號(hào)','包裝單位','規(guī)格單位','包裝規(guī)格','標(biāo)準(zhǔn)售價(jià)(元)'];//第一行標(biāo)題內(nèi)容$field = ['name', 'spec_model', 'pack_unit', 'spec_unit','pack_spec', 'price'];//第二行列字段內(nèi)容if($id){//查詢指定的幾條數(shù)據(jù)$res = GoodsModel::getInstance()->field($field)->where(['company_id'=>$this->company_id,'id'=>['in',$id]])->select();}else{//查詢所有數(shù)據(jù)$res = GoodsModel::getInstance()->field($field)->where(['company_id'=>$this->company_id]) // ->limit(2)//限制條數(shù)->select();}break;default:$this->displayByError('行動(dòng)有誤，撤退!!');}$strTable ='<table width="500" border="1">';$strTable .= '<tr>';//第一行的標(biāo)題foreach ($comment as $key => $value){$strTable .= '<td style="text-align:left;font-size:16px;" width="*">'.$value.'</td>'."\r\n";}$strTable .= '</tr>';//第二行的列字段$strTable .= '<tr>';foreach ($field as $key => $value){$strTable .= '<td style="text-align:left;font-size:14px;" width="*">'.$value.'</td>'."\r\n";}$strTable .= '</tr>';//第三行開(kāi)始是導(dǎo)入數(shù)據(jù)庫(kù)的數(shù)據(jù)$strTable .= '<tr>';for ($i = 0;$i < count($res);$i++){for ($j = 0; $j < count($field);$j++){$strTable .= '<td style="text-align:left;font-size:12px;" rowspan="*">'.$res[$i][$field[$j]].'</td>'."\r\n";}$strTable .= '</tr>';}$strTable .='</table>';unset($res);$this->downloadExcel($strTable,'GoodsExcel');exit();}/*** 導(dǎo)出excel需要設(shè)置的header* @param $strTable /表格內(nèi)容* @param $filename /文件名*/function downloadExcel($strTable,$filename){header("Content-type: application/vnd.ms-excel");//將查詢結(jié)果導(dǎo)出到Excelheader("Content-Type: application/force-download");//告訴瀏覽器強(qiáng)制下載header("Content-Disposition: attachment; filename=".$filename."_".date('Y-m-d').".xls");//attachment作為附件下載,inline在線下載,filename設(shè)置文件名header('Expires:0');//瀏覽器不會(huì)響應(yīng)緩存header('Pragma:public');//Public指示響應(yīng)可被任何緩存區(qū)緩存。echo '<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'.$strTable.'</html>';}

導(dǎo)出Excel參考文獻(xiàn)：https://blog.csdn.net/qq_41211900/article/details/81068901

總結(jié)

以上是生活随笔為你收集整理的PHP导入Excel和导出Excel的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。