PHPEXCEL導入導出excel文件 · 挑戰者

PHPExcel?是用來操作Office Excel 文檔的一個PHP類庫，它基于微軟的OpenXML標準和PHP語言。可以使用它來讀取、寫入不同格式的電子表格，如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等。 ### 1.下載導入源碼庫 http://phpexcel.codeplex.com/releases/view/119187 ### 2.封裝導入導出工具類這是thinkphp5下的案例，但是只要改一下導入phpexcel的問題。 ~~~ <?php /** * Created by PhpStorm. * @Author: luowangbao * @Date: 2016/6/1 10:25 * @Function:Excel導出工具 */ namespace app\admin\tool; use think\Loader; //PHPExcel/PHPExcel.php Loader::import('PHPExcel.PHPExcel',EXTEND_PATH); //PHPExcel/PHPExcel/IOFactory.php Loader::import('PHPExcel.PHPExcel.IOFactory',EXTEND_PATH); /** * Excel 操作工具 * Class Excel * @package app\admin\tool */ class Excel { private $excelCopy = 'Excel5'; //導出excel版本 private $suffix = 'xls'; private $objPHPExcel = null; //PHPExcel對象 /** * Excel constructor. */ public function __construct() { $this->objPHPExcel = new \PHPExcel(); $this->excelCopy = 'Excel5'; //判斷后綴名 if ($this->excelCopy == 'Excel5') { $this->suffix = '.xls'; } else if ($this->excelCopy == 'Excel2007') { $this->suffix = '.xlsx'; } } /** * 導出Excel文件 * @param string $fileName 導出的文件名稱 * @param array $headerArr 頭部數據：['name'=>'名稱'] * @param array $data 數據：[['name'=>'張三'],['name'=>'李四']] * @param string $sheet 工作表的名稱 */ public function export($fileName="Excel", $headerArr = array(), $data = array(),$sheet = 'Sheet1') { $this->objPHPExcel->setActiveSheetIndex(0); //設置當前的sheet $objSheet = $this->objPHPExcel->getActiveSheet(); //獲取當前活動sheet $objSheet->setTitle($sheet); //設置標題 //獲取列字母,設置第一行表頭 $headCharArr = $this->getHeaderChar($headerArr); //A B C foreach ($headCharArr as $k => $v) { $objStyle = $objSheet->getStyle($headCharArr[$k] . '1'); $objAlign = $objStyle->getAlignment(); $objAlign->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //上下居中 $objAlign->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //左對齊 $objSheet->setCellValue($headCharArr[$k] . '1', $headerArr[$k]); } //導出數據 $j = 2; foreach ($data as $k => $v) { foreach ($headerArr as $k1 => $v1) { $objStyle = $objSheet->getStyle($headCharArr[$k1] . $j); $objAlign = $objStyle->getAlignment(); $objAlign->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //上下居中 $objAlign->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $val = $v[$k1].''; $objSheet->setCellValue($headCharArr[$k1] . $j, $val); } $j++; } $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, $this->excelCopy); $fileName = $fileName . ($this->suffix); $copy = $this->excelCopy; $this->browser_export($copy, $fileName); $objWriter->save('php://output'); //輸出到瀏覽器 } /** * 獲取excel列數字母 * @param array $data * @return array */ private function getHeaderChar($data = array()) { $index = 65; //A標簽 $char = ''; $charArr = array(); foreach ($data as $k => $v) { $charArr[$k] = $char . chr($index++); if ($index == 91) { $index = 65; $char .= 'A'; } } return $charArr; } /** * 輸出到瀏覽器 * @param $copy * @param $fileName */ private function browser_export($copy, $fileName) { ob_end_clean();//清除緩沖區,避免亂碼 if ($copy == 'Excel5') { header('Content-Type: application/vnd.ms-excel;'); //告訴瀏覽器輸出Excel2003文件 } else { //告訴瀏覽器輸出Excel2007文件 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } header('Content-Disposition: attachment;filename="' . iconv('utf-8', 'gb2312', $fileName) . '"'); //文件名 header('Cache-Control: max-age=0'); //禁止瀏覽器緩存 } /** * 導入文件通過上傳的文件 * @param array $format 格式：['名稱'=>'name'] 會把列名轉化成name * @param string $inputname 上傳輸入框的名稱name * @param string $sheetName 工作表名稱，默認：Sheet1 * @return array 放回指定的數據 */ public function importByInput($format = array(),$inputname='excel', $sheetName = 'Sheet1') { $data = $this->importExcel($_FILES[$inputname]['tmp_name'], $sheetName); return $this->dealImportData($format,$data); } /** * 導入指定excel * @param array $format 格式：['名稱'=>'name'] 會把列名轉化成name * @param string $filename excel在本機的絕對路徑 * @param string $sheetName 工作表名稱，默認：Sheet1 * @return array */ public function import($format = array(),$filename='', $sheetName = 'Sheet1') { $data = $this->importExcel($filename, $sheetName); return $this->dealImportData($format,$data); } /** * 處理導入數據 * @param $format * @param $data * @return array */ private function dealImportData($format,$data){ if (!$format) { return $data; } else { $newdata=array(); foreach($data as $k=>$v){ $row=array(); foreach($v as $k2=>$v2){ //$format[$k2] 獲取key if($format[trim($k2)]){//去除數據的兩端空格 $row[$format[trim($k2)]]=trim($v2); } } $newdata[]=$row; } return $newdata; } } /** * 導入excel,返回原始二維數據 * @param $filename 文件絕對路徑 * @param string $sheetName 工作表名稱，默認：Sheet1 * @return array */ public function importExcel($filename, $sheetName = 'Sheet1') { header("Content-Type:text/html;charset=utf-8"); $fileType = \PHPExcel_IOFactory::identify($filename);//自動獲取文件的類型提供給phpexcel用 $objReader = \PHPExcel_IOFactory::createReader($fileType);//獲取文件讀取操作對象 $sheetName = array($sheetName); $objReader->setLoadSheetsOnly($sheetName);//只加載指定的sheet $objPHPExcel = $objReader->load($filename);//加載文件 $key = array(); $value = array(); foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {//循環取sheet foreach ($sheet->getRowIterator() as $row) {//逐行處理 $temp = array(); foreach ($row->getCellIterator() as $kk=> $cell) {//逐列讀取 if ($row->getRowIndex() < 2) { $key[$kk] = $cell->getValue(); } else { $data = $cell->getValue();//獲取單元格數據 $temp[$kk] = $data; } } if (!empty($temp)) { $value[] = $temp; } } } $data = array(); foreach ($value as $k => $v) { $temp = array(); foreach ($v as $k1 => $v1) { $temp[$key[$k1]] = $v1; } $data[] = $temp; } return $data; } } ~~~ ### 設置單元格導出格式問文本 ~~~ $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$j,$result[1],PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle('B'.$j)->getNumberFormat()->setFormatCode("@"); ~~~