对于 excel 文件的的生成于数据读取,我们可以用到 PHPExcel 插件。地址:http://phpexcel.codeplex.com/releases/view/107442 ,选第一个下载。然后解压的文件目录如下:
初学者应该先去看示例,代码不是很难理解。方法名都是观其名,知其用。在看过些许例子的基础上,我才想到封装一下,用着方便。鉴于我自己也只是入门。所以写了个相对入门的封装。
<span 1</span> <form method="post" enctype="multipart/form-data" > <span 2</span> <span 3</span> 文件:<input type="file" name="files" /></li> <span 4</span> <input type="submit" value="提交" /> <span 5</span> </form> <span 6</span> <span 7</span> <?<span php </span><span 8</span> <span 9</span> <span //</span><span set_include_path(get_include_path() . PATH_SEPARATOR . 'Excel/Classes/');</span> <span 10</span> <span 11</span> <span require_once</span> 'Excel/Classes/PHPExcel.php'<span ; </span><span 12</span> <span 13</span> <span class</span> myExcel <span extends</span><span PHPExcel{ </span><span 14</span> <span 15</span> <span private</span> <span $file</span><span ; </span><span 16</span> <span private</span> <span $fileType</span><span ; </span><span 17</span> <span 18</span> <span private</span> <span $objProperty</span>; <span //</span><span 文档属性对象 </span> <span 19</span> <span private</span> <span $objReader</span><span ; </span><span 20</span> <span private</span> <span $objWriter</span><span ; </span><span 21</span> <span 22</span> <span public</span> <span function</span><span __construct(){ </span><span 23</span> parent::<span __construct(); </span><span 24</span> <span 25</span> <span } </span><span 26</span> <span 27</span> <span /*</span> <span 28</span> <span * 设置文档属性 </span><span 29</span> <span * $property = array('title'=>'标题', 'creator' => '作者'); </span><span 30</span> <span */</span> <span 31</span> <span public</span> <span function</span> setProperty(<span $property</span><span ){ </span><span 32</span> <span $this</span>->objProperty = <span $this</span>-><span getProperties(); </span><span 33</span> <span if</span>(!<span empty</span>(<span $property</span>['creator']))<span $this</span>->objProperty->setCreator(<span $property</span>['creator'<span ]); </span><span 34</span> <span if</span>(!<span empty</span>(<span $property</span>['title'])) <span $this</span>->objProperty->setTitle(<span $property</span>['title'<span ]); </span><span 35</span> <span if</span>(!<span empty</span>(<span $property</span>['subject']))<span $this</span>->objProperty->setSubject(<span $property</span>['subject'<span ]); </span><span 36</span> <span if</span>(!<span empty</span>(<span $property</span>['laster']))<span $this</span>->objProperty->setLastModifiedBy(<span $property</span>['laster'<span ]); </span><span 37</span> <span if</span>(!<span empty</span>(<span $property</span>['description']))<span $this</span>->objProperty->setDescription(<span $property</span>['description'<span ]); </span><span 38</span> <span if</span>(!<span empty</span>(<span $property</span>['keyword']))<span $this</span>->objProperty->setKeywords(<span $property</span>['keyword'<span ]); </span><span 39</span> <span if</span>(!<span empty</span>(<span $property</span>['category']))<span $this</span>->objProperty->setCategory(<span $property</span>['category'<span ]); </span><span 40</span> <span } </span><span 41</span> <span 42</span> <span /*</span> <span 43</span> <span * 添加数据 </span><span 44</span> <span * $data = array( 'a1'=>'a111', 'b1'=>'b222', 'c1'=>'c111',); </span><span 45</span> <span * </span><span */</span> <span 46</span> <span public</span> <span function</span> addData(<span $data</span>, <span $index</span> = <span null</span><span ){ </span><span 47</span> <span $objAdd</span> = (<span $index</span>)? <span $this</span>->setActiveSheetIndex(<span $index</span>) : <span $this</span>-><span getActiveSheet(); </span><span 48</span> <span foreach</span>(<span $data</span> <span as</span> <span $key</span> => <span $val</span><span ){ </span><span 49</span> <span if</span>(<span empty</span>(<span $key</span>) || <span empty</span>(<span $val</span>)) <span continue</span><span ; </span><span 50</span> <span $objAdd</span>->setCellValue(<span $key</span>, <span $val</span><span ); </span><span 51</span> <span } </span><span 52</span> <span } </span><span 53</span> <span 54</span> <span //</span><span 生成文件</span> <span 55</span> <span public</span> <span function</span> saveFile(<span $file</span>, <span $path</span> = <span null</span>, <span $type</span> = 'Excel5'<span ){ </span><span 56</span> <span $this</span>->objWriter = PHPExcel_IOFactory::createWriter(<span $this</span>, <span $type</span><span ); </span><span 57</span> <span 58</span> <span $filePath</span> = <span $path</span>.<span $file</span><span ; </span><span 59</span> <span $this</span>->objWriter->save(<span $filePath</span><span ); </span><span 60</span> <span } </span><span 61</span> <span 62</span> <span /*</span><span -------------------------------------- 读取文件 ---------------------------- </span><span */</span> <span 63</span> <span 64</span> <span //</span><span 设置读对象</span> <span 65</span> <span private</span> <span function</span> setReader(<span $file</span>, <span $type</span> = <span null</span><span ){ </span><span 66</span> <span $this</span>-><span file</span> = <span $file</span><span ; </span><span 67</span> <span if</span>(<span $type</span><span ){ </span><span 68</span> <span $this</span>-><span fileType</span> = <span $type</span><span ; </span><span 69</span> <span $this</span>->objReader = PHPExcel_IOFactory::createReader(<span $type</span>)->load(<span $file</span><span ); </span><span 70</span> }<span else</span><span { </span><span 71</span> <span $this</span>-><span fileType</span> = PHPExcel_IOFactory::identify(<span $file</span><span ); </span><span 72</span> <span $this</span>->objReader = PHPExcel_IOFactory::load(<span $file</span><span ); </span><span 73</span> <span } </span><span 74</span> <span } </span><span 75</span> <span 76</span> <span 77</span> <span //</span><span 加载文件</span> <span 78</span> <span public</span> <span function</span> loadFile(<span $file</span>, <span $type</span> = <span null</span><span ){ </span><span 79</span> <span $this</span>->setReader(<span $file</span>, <span $type</span><span ); </span><span 80</span> <span $this</span>->sheetData = <span $this</span>->objReader->getActiveSheet()->toArray(<span null</span>,<span true</span>,<span true</span>,<span true</span><span ); </span><span 81</span> <span } </span><span 82</span> <span 83</span> <span //</span><span 返回需要的数据</span> <span 84</span> <span public</span> <span function</span> dataFormat(<span $meed</span>, <span $start</span> = 1, <span $end</span> = <span null</span><span ){ </span><span 85</span> <span foreach</span>(<span $this</span>->sheetData <span as</span> <span $line</span> => <span $row</span><span ){ </span><span 86</span> <span if</span>(<span $start</span> && <span $line</span> < <span $start</span>) <span continue</span><span ; </span><span 87</span> <span if</span>(<span $end</span> && <span $line</span> > <span $end</span>) <span break</span><span ; </span><span 88</span> <span foreach</span>(<span $row</span> <span as</span> <span $key</span> => <span $val</span><span ){ </span><span 89</span> <span if</span>(<span array_key_exists</span>(<span $key</span>, <span $meed</span><span )){ </span><span 90</span> <span $data</span>[<span $line</span>][<span $meed</span>[<span $key</span>]] = <span $val</span><span ; </span><span 91</span> <span } </span><span 92</span> <span } </span><span 93</span> <span } </span><span 94</span> <span return</span> <span array_merge</span>(<span $data</span><span ); </span><span 95</span> <span } </span><span 96</span> <span 97</span> <span //</span><span 工作表信息</span> <span 98</span> <span public</span> <span function</span> sheetInfo(<span $file</span> = <span null</span><span ){ </span><span 99</span> (<span $file</span>)? <span null</span> : <span $file</span> = <span $this</span>-><span file</span><span ; </span><span 100</span> <span $info</span> = <span $this</span>->objReader->listWorksheetInfo(<span $file</span><span ); </span><span 101</span> <span return</span> <span $info</span><span ; </span><span 102</span> <span } </span><span 103</span> <span 104</span> <span } </span><span 105</span> <span 106</span> <span 107</span> <span if</span>(<span $_FILES</span><span ){ </span><span 108</span> <span $upDir</span> = './upImg/'<span ; </span><span 109</span> <span $path</span> = <span $upDir</span>.'aaa.xls'<span ; </span><span 110</span> <span if</span>(!<span is_dir</span>(<span $upDir</span>)) <span mkdir</span>(<span $upDir</span>, 0777, <span true</span>) or <span exit</span>('上传目录创建失败!'<span ); </span><span 111</span> <span $temp</span> = <span $_FILES</span>['files']['tmp_name'<span ]; </span><span 112</span> <span move_uploaded_file</span>(<span $temp</span>, <span $path</span>);<span //</span><span 移到指定目录</span> <span 113</span> <span 114</span> <span $property</span> = <span array</span><span ( </span><span 115</span> 'title'=>'the title', <span 116</span> 'creator' => '作者' <span 117</span> <span ); </span><span 118</span> <span $data</span> = <span array</span><span ( </span><span 119</span> 'A1'=>'a1111', <span 120</span> 'A2'=>'a222', <span 121</span> 'B1'=>'b111', <span 122</span> 'E1'=>'e111', <span 123</span> 'f2'=>'f22222' <span 124</span> <span ); </span><span 125</span> <span 126</span> <span $data2</span> = <span array</span><span ( </span><span 127</span> <span 128</span> 'g2'=>'f22222', <span 129</span> 'h1'=>'hhh', <span 130</span> 'j1'=>'jjjjj' <span 131</span> <span ); </span><span 132</span> <span 133</span> <span $test</span> = <span new</span><span myExcel(); </span><span 134</span> <span //</span><span $test->setProperty($property); </span><span 135</span> <span //$test->addData($data, 0); </span><span 136</span> <span //$test->addData($data2); </span><span 137</span> <span //$test->saveFile('aaa.xls');</span> <span 138</span> <span 139</span> <span $test</span>->loadFile('upImg/aaa.xls'<span ); </span><span 140</span> <span $meed</span> = <span array</span>('A'=>'name','E'=>'age','F'=>'address'<span ); </span><span 141</span> <span $data</span> = <span $test</span>->dataFormat(<span $meed</span>, <span null</span>, 5<span ); </span><span 142</span> <span var_dump</span>(<span $data</span><span ); </span><span 143</span> <span } </span><span 144</span> ?>
数据返回的配置,可以看下图: