Home >Backend Development >PHP Tutorial >About PHPExcel, phpexcel_PHP tutorial
在学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>导入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表信息如下:
id | username | password | sex |
1 | zs | 123 | 1 |
以上就是使用PhpExcel的全部步骤,谢谢!