关于PHPExcel，phpexcel-php手册-PHP中文网

首页

php教程

php手册

关于PHPExcel，phpexcel

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 13, 2016 am 08:52 AM

phpexcel关于在查的

关于PHPExcel，phpexcel

　　在学PHPExcel的时候，在网上查了很多资料，花了很多时间，下面是我想要分享给大家的，我找到的并进行了一定修改的亲身实践成功的资料，希望大家对大家有所帮助。

　　首先，需要下载PhpExcel资料，下载资料可以在这里下载，http://download.csdn.net/detail/www122930/9207061

　　第一，将PHPExcel文件夹，和PHPExcel.php文件放在，一个新建的文件夹Excel中，将Excel文件夹放在，E:\Workspace\PHP\thinkphp2\ThinkPHP\Extend\Vendor，E:\Workspace\PHP\thinkphp2\这一部分是你创建Thinkphp的工作目录。

　　第二，编写一个ExcelToArray.class.php文件，将它放在E:\Workspace\PHP\thinkphp2\ThinkPHP\Extend\Library\ORG\Util，这个目录下，ExcelToArray.class.php文件的源代码如下：

<?php
class ExcelToArray {
  public function __construct() {
		Vendor("Excel.PHPExcel");//引入phpexcel类(注意你自己的路径)
		Vendor("Excel.PHPExcel.IOFactory"); 	
  }
  public function read($filename,$encode,$file_type){
	        if(strtolower ( $file_type )=='xls')//判断excel表类型为2003还是2007
			{
				Vendor("Excel.PHPExcel.Reader.Excel5"); 
				$objReader = PHPExcel_IOFactory::createReader('Excel5');
			}elseif(strtolower ( $file_type )=='xlsx')
			{
				Vendor("Excel.PHPExcel.Reader.Excel2007"); 
				$objReader = PHPExcel_IOFactory::createReader('Excel2007');
			}
			$objReader->setReadDataOnly(true);
			$objPHPExcel = $objReader->load($filename);
			$objWorksheet = $objPHPExcel->getActiveSheet();
			$highestRow = $objWorksheet->getHighestRow();
			$highestColumn = $objWorksheet->getHighestColumn();
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
			$excelData = array();
			for ($row = 1; $row <= $highestRow; $row++) {
				for ($col = 0; $col < $highestColumnIndex; $col++) {
					$excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
					}
			}
			return $excelData;
	}
	  
	public function push($data,$name='Excel'){

          error_reporting(E_ALL);
          //date_default_timezone_set('Europe/London');
         $objPHPExcel = new PHPExcel();

        /*以下是一些设置 ，什么作者  标题啊之类的*/
         $objPHPExcel->getProperties()->setCreator("转弯的阳光")
                               ->setLastModifiedBy("转弯的阳光")
                               ->setTitle("usertable")
                               ->setSubject("数据EXCEL导出")
                               ->setDescription("备份数据")
                               ->setKeywords("excel")
                              ->setCategory("result file");
		
		
		//
		$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'username')
        ->setCellValue('B1', 'password')
        ->setCellValue('C1', 'sex');

         /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        for ($i = 0; $i < count($data) - 1; $i++) {
			$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $data[$i]['username']);
			$objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), $data[$i]['password']);
			$objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $data[$i]['sex']);
		}

            $objPHPExcel->getActiveSheet()->setTitle('User');
            $objPHPExcel->setActiveSheetIndex(0);
			ob_end_clean(); //清除缓冲区，避免乱码
             header('Content-Type: application/vnd.ms-excel');
             header('Content-Disposition: attachment;filename="'.$name.'.xls"');
             header('Cache-Control: max-age=0');
             $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
             $objWriter->save('php://output');
             exit;
    }
}

　　这里有两部分，一部分read function，就是读入Excel中，即将数据库中内容导入到Excel，另一部分，push function，就是讲Excel数据上传到数据库。

　　第三，创建一个ExcelAction.clsaa.php，在目录E:\Workspace\PHP\thinkphp2\Home\Lib\Action下面，ExcelAction.clsaa.php源代码如下：

<span> 1</span> <?<span>php
</span><span> 2</span> <span>class</span> ExcelAction <span>extends</span><span> Action {
</span><span> 3</span>     <span>public</span> <span>function</span><span> __construct()
</span><span> 4</span> <span>    {
</span><span> 5</span>         import('ORG.Util.ExcelToArray');<span>//</span><span>导入excelToArray类</span>
<span> 6</span> <span>    }
</span><span> 7</span>     
<span> 8</span>     <span>public</span> <span>function</span><span> index()
</span><span> 9</span> <span>    {
</span><span>10</span>         <span>$this</span>-><span>display();
</span><span>11</span> <span>    }
</span><span>12</span>     <span>public</span> <span>function</span><span> add()
</span><span>13</span> <span>    {    
</span><span>14</span>         dump(<span>$_FILES</span><span>);
</span><span>15</span>         
<span>16</span>         <span>$tmp_file</span> = <span>$_FILES</span> ['file_stu'] ['tmp_name'<span>];
</span><span>17</span>         <span>$file_types</span> = <span>explode</span> ( ".", <span>$_FILES</span> ['file_stu'] ['name'<span>] );
</span><span>18</span>         <span>$file_type</span> = <span>$file_types</span> [<span>count</span> ( <span>$file_types</span> ) - 1<span>];
</span><span>19</span>     
<span>20</span>          <span>/*</span><span>判别是不是.xls文件，判别是不是excel文件</span><span>*/</span>
<span>21</span>          <span>if</span> (<span>strtolower</span> ( <span>$file_type</span> ) != "xlsx" && <span>strtolower</span> ( <span>$file_type</span> ) != "xls"<span>)              
</span><span>22</span> <span>         {
</span><span>23</span>               <span>$this</span>->error ( '不是Excel文件，重新上传'<span> );
</span><span>24</span> <span>         }
</span><span>25</span>     
<span>26</span>          <span>/*</span><span>设置上传路径</span><span>*/</span>
<span>27</span>          <span>$savePath</span> = 'E:\Workspace\PHP\thinkphp\Uploads\\'<span>;
</span><span>28</span>          <span>/*</span><span>以时间来命名上传的文件</span><span>*/</span>
<span>29</span>          <span>$str</span> = <span>date</span> ( 'Ymdhis'<span> ); 
</span><span>30</span>          <span>$file_name</span> = <span>$str</span> . "." . <span>$file_type</span><span>;
</span><span>31</span>          
<span>32</span>          <span>/*</span><span>是否上传成功</span><span>*/</span>
<span>33</span>          <span>if</span> (! <span>copy</span> ( <span>$tmp_file</span>, <span>$savePath</span> . <span>$file_name</span><span> )) 
</span><span>34</span> <span>          {
</span><span>35</span>               <span>$this</span>->error ( '上传失败'<span> );
</span><span>36</span> <span>          }
</span><span>37</span>         <span>$ExcelToArray</span>=<span>new</span> ExcelToArray();<span>//</span><span>实例化</span>
<span>38</span>         <span>$res</span>=<span>$ExcelToArray</span>->read(<span>$savePath</span>.<span>$file_name</span>,"UTF-8",<span>$file_type</span>);<span>//</span><span>传参,判断office2007还是office2003</span>
<span>39</span>         <span>foreach</span> ( <span>$res</span> <span>as</span> <span>$k</span> => <span>$v</span> ) <span>//</span><span>循环excel表</span>
<span>40</span> <span>        {　　<br />　　　　　　　　　//这一步判断，是为了在Excel内第一行一定是行标题，这里将第一行忽略，直接从第二行读入数据，若没有行标题，则不需要进行if判断，且$k=$k-1;
</span><span>41</span>             <span>if</span>(<span>$k</span>!=1<span>){
</span><span>42</span>                 <span>$k</span>=<span>$k</span>-2;<span>//</span><span>addAll方法要求数组必须有0索引</span>
<span>43</span>                 <span>$data</span>[<span>$k</span>]['username'] = <span>$v</span>[0];<span>//</span><span>创建二维数组</span>
<span>44</span>                 <span>$data</span>[<span>$k</span>]['password'] = <span>$v</span>[1<span>];
</span><span>45</span>                 <span>$data</span>[<span>$k</span>]['sex'] = <span>$v</span> [2<span>];
</span><span>46</span> <span>            }
</span><span>47</span> <span>        }
</span><span>48</span>         
<span>49</span>           <span>//</span><span>dump($data[0]);</span>
<span>50</span>           <span>$kucun</span>=M('User');<span>//</span><span>M方法</span>
<span>51</span>           <span>$result</span>=<span>$kucun</span>->addAll(<span>$data</span><span>);
</span><span>52</span>           <span>if</span>(! <span>$result</span><span>)
</span><span>53</span> <span>          {
</span><span>54</span>               <span>$this</span>->error('导入数据库失败'<span>);
</span><span>55</span>               <span>exit</span><span>();
</span><span>56</span> <span>          }
</span><span>57</span>           <span>else</span>
<span>58</span> <span>          {
</span><span>59</span>               <span>$this</span>->success ( '导入成功'<span> );    
</span><span>60</span> <span>          }
</span><span>61</span> <span>    }
</span><span>62</span>     
<span>63</span>     <span>public</span> <span>function</span><span> load(){
</span><span>64</span>         <span>$data</span>= M('User')->select();   <span>//</span><span>查出数据</span>
<span>65</span>         dump(<span>$data</span><span>);
</span><span>66</span>         <span>$name</span>='Usertable';    <span>//</span><span>生成的Excel文件文件名</span>
<span>67</span>         <span>$ExcelToArray</span>=<span>new</span> ExcelToArray();<span>//</span><span>实例化</span>
<span>68</span>         <span>$res</span>=<span>$ExcelToArray</span>->push(<span>$data</span>,<span>$name</span><span>);
</span><span>69</span> <span>    }
</span><span>70</span> }

　　第四，就是创建相应的模板，在目录E:\Workspace\PHP\thinkphp2\Home\Tpl下，创建Excel文件夹，新建index.html文件，源代码如下：

<span>1</span> <form method="post" action="__APP__/Excel/add" enctype="multipart/form-data">
<span>2</span>          <h3 id="导入Excel表">导入Excel表：</h3><input  type="file" name="file_stu" />
<span>3</span> 
<span>4</span>            <input type="submit"  value="导入" />
<span>5</span> </form>
<span>6</span> <form method="post" action="__APP__/Excel/load" enctype="multipart/form-data">
<span>7</span>            <input type="submit"  value="导出" />
<span>8</span> </form>

　　最后，只需要进行测试就可以了。

　　ps，数据库信息如下：

　　例如：新建数据库thinkphp，建立表user，user表信息如下：