PHP 一键导入导出Excel表格

PHPExcel类库下载

一键导入

HTML代码

<div id="myTabContent" class="tab-content">
     <div class="form-group" >
             <form action="...../exls" method="post" enctype="multipart/form-data">
                 <div class="form-group">
                     <label for="exampleInputFile">一键导入</label><br>
                     <font color="red">*从左到右为：姓名,电话,金额</font><br>
                     <font color="red">*一行一个玩家,纵向排列</font><br>
                     <input type="file" id="file" name="file">
                   </div>
                 <input type="submit" name="submit" value="Submit"/>
             </form>
         </div>
 </div>

PHP代码

	//一键导入xls表格
	public function exls(){
		/**
		 * 符合条件的文件格式
		 */
		$filename = $_FILES['file']['name'];
		/**
		 * 文件名
		 */
		$ext = preg_split("/\./", $filename);
		/**
		 * 分割文件名
		 */
		$ext = strtolower($ext[1]);
		/*
		* 获取文件后缀名
		*/
		$allowed_types = array("xls", "xlsx");
		$filePath = dirname(__FILE__) . "/" . $_FILES["file"]["name"];
		if (!in_array($ext, $allowed_types)) {
			echo "File type is wrong!";
			die;
		} else if (file_exists($filePath)) {
			echo "A file with this name already exists!";
			die;
		} else {
			move_uploaded_file($_FILES["file"]["tmp_name"], $filePath);
		}
		/**
		 * @param $val
		 * @return string
		 * 检查文件名是否符合要求，如果符合，就保存到指定路径。如果不符合，报错。
		 */
		import('phpexcel.PHPExcel', EXTEND_PATH);
		//$filePath=$_FILES["file"]["tmp_name"];
		//sleep(50);
		$PHPExcel = new \PHPExcel();
		//默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
		$PHPReader = new \PHPExcel_Reader_Excel2007();
		if (!$PHPReader->canRead($filePath)) {
			$PHPReader = new PHPExcel_Reader_Excel5();
			if (!$PHPReader->canRead($filePath)) {
				return $this->error(null, "no file");
			}
		}
		$PHPExcel = $PHPReader->load($filePath);
		$sheetCount = $PHPExcel->getSheetCount();
		$sheetNames = $PHPExcel->getSheetNames();
		//var_dump($sheetNames);
		//die;
		/**读取excel文件中有多少个sheet*/
		$objExcel = array();
		for ($SheetID = 0; $SheetID < $sheetCount; $SheetID++) {
			/**读取excel文件中的工作表*/
			$name = $sheetNames[$SheetID];
			$currentSheet = $PHPExcel->getSheetByName($name);
			$name = iconv("utf-8", "gb2312", $name);
			/**取得最大的列号*/
			$allColumn = $currentSheet->getHighestColumn();
			/**取得一共有多少行*/
			$allRow = $currentSheet->getHighestRow();
			for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
				/**从第A列开始输出*/
				for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
					$val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
					$val = iconv("utf-8", "gb2312", $val);
					$objExcel[$name][$currentRow - 1][ord($currentColumn) - 65] = $val;
					//编码需要转换成gb2312
					/*
					$val = urlencode($val);
					$objExcel[$name][$currentRow - 1][ord($currentColumn) - 65] = urldecode($val);
					*/
				}
			}
		}
		unlink($filePath);
		/*
		* $objExcel = json_encode($objExcel);
		* $objExcel = urldecode($objExcel);
		* 读取文件中的内容，因为json_encode()的参数必须是utf-8编码。为了保证汉字输出的结果，这里先将数组中的内容用urlencode进行编码，
		* 在被json_encode()编码之后，再用urldecode解码。
		*/
		//return $objExcel;
		$datas = $objExcel['Sheet1'];
		dump($datas);
	}

一键导出Excel表格

public function Excel($data)//传入 导出数据（数组）
    {
        // 引入类库
        import('phpexcel.PHPExcel', EXTEND_PATH);
        // 文件名和文件类型
        $fileName = "小明和他的同学";
        $fileType = "xlsx";
        // 模拟获取数据
        $data = $data;
        $obj = new \PHPExcel();
        // 以下内容是excel文件的信息描述信息
        $obj->getProperties()->setCreator(''); //设置创建者
        $obj->getProperties()->setLastModifiedBy(''); //设置修改者
        $obj->getProperties()->setTitle(''); //设置标题
        $obj->getProperties()->setSubject(''); //设置主题
        $obj->getProperties()->setDescription(''); //设置描述
        $obj->getProperties()->setKeywords('');//设置关键词
        $obj->getProperties()->setCategory('');//设置类型
        // 设置当前sheet
        $obj->setActiveSheetIndex(0);
        // 设置当前sheet的名称
        $obj->getActiveSheet()->setTitle('student');
        // 列标
        $list = ['A', 'B', 'C','D'];
        // 填充第一行数据
        $obj->getActiveSheet()
            ->setCellValue($list[0] . '1', '姓名')
            ->setCellValue($list[1] . '1', '年级')
            ->setCellValue($list[2] . '1', '年龄')
            ->setCellValue($list[3] . '1', '班级')
        // 填充第n(n>=2, n∈N*)行数据
        $length = count($data);
        for ($i = 0; $i < $length; $i++) {
            $obj->getActiveSheet()->setCellValue($list[0] . ($i + 2), $data[$i]['orderId'], \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
            $obj->getActiveSheet()->setCellValue($list[1] . ($i + 2), $data[$i]['channel_id']);
            $obj->getActiveSheet()->setCellValue($list[2] . ($i + 2), $data[$i]['custId']);
            $obj->getActiveSheet()->setCellValue($list[3] . ($i + 2), $data[$i]['amount']); 
        }
        // 设置加粗和左对齐
        foreach ($list as $col) {
            // 设置第一行加粗
            $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
            // 设置第1-n行，左对齐
            for ($i = 1; $i <= $length + 1; $i++) {
                $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            }
        }
        // 设置列宽
        $obj->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $obj->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $obj->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $obj->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        // 导出
        ob_clean();
        if ($fileType == 'xls') {
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
            header('Cache-Control: max-age=1');
            $objWriter = new \PHPExcel_Writer_Excel5($obj);
            $objWriter->save('php://output');
            exit;
        } elseif ($fileType == 'xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
            header('Cache-Control: max-age=1');
            $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
            $objWriter->save('php://output');
            exit;
        }
    }