这篇文章主要介绍了php实现的操作excel类,较为详细的分析说明了PHP操作excel的具体技巧,包括PHP针对excel的创建、打开、读取、修改等,需要的朋友可以参考下。

本文实例讲述了php实现的操作excel类，分享给大家供大家参考，具体如下：

1. <?php
2. class Excel
3. {
4. static $instance=null;
5. private $excel=null;
6. private $workbook=null;
7. private $workbookadd=null;
8. private $worksheet=null;
9. private $worksheetadd=null;
10. private $sheetnum=1;
11. private $cells=array();
12. private $fields=array();
13. private $maxrows;
14. private $maxcols;
15. private $filename;
16. //构造函数
17. private function Excel()
18. {
19. $this->excel = new COM("Excel.Application") or die("Did Not Connect");
20. }
21. //类入口
22. public static function getInstance()
23. {
24. if(null == self::$instance)
25. {
26. self::$instance = new Excel();
27. }
28. return self::$instance;
29. }
30. //设置文件地址
31. public function setFile($filename)
32. {
33. return $this->filename=$filename;
34. }
35. //打开文件
36. public function Open()
37. {
38. $this->workbook=$this->excel->WorkBooks->Open($this->filename);
39. }
40. //设置Sheet
41. public function setSheet($num=1)
42. {
43. if($num>0)
44. {
45. $this->sheetnum=$num;
46. $this->worksheet=$this->excel->WorkSheets[$this->sheetnum];
47. $this->maxcols=$this->maxCols();
48. $this->maxrows=$this->maxRows();
49. $this->getCells();
50. }
51. }
52. //取得表所有值并写进数组
53. private function getCells()
54. {
55. for($i=1;$i<$this->maxcols;$i++)
56. {
57. for($j=2;$j<$this->maxrows;$j++)
58. {
59. $this->cells[$this->worksheet->Cells(1,$i)->value][]=(string)$this->worksheet->Cells($j,$i)->value;
60. }
61. }
62. return $this->cells;
63. }
64. //返回表格内容数组
65. public function getAllData()
66. {
67. return $this->cells;
68. }
69. //返回制定单元格内容
70. public function Cell($row,$col)
71. {
72. return $this->worksheet->Cells($row,$col)->Value;
73. }
74. //取得表格字段名数组
75. public function getFields()
76. {
77. for($i=1;$i<$this->maxcols;$i++)
78. {
79. $this->fields[]=$this->worksheet->Cells(1,$i)->value;
80. }
81. return $this->fields;
82. }
83. //修改制定单元格内容
84. public function editCell($row,$col,$value)
85. {
86. if($this->workbook==null || $this->worksheet==null)
87. {
88. echo "Error:Did Not Connect!";
89. }else{
90. $this->worksheet->Cells($row,$col)->Value=$value;
91. $this->workbook->Save();
92. }
93. }
94. //修改一行数据
95. public function editOneRow($row,$arr)
96. {
97. if($this->workbook==null || $this->worksheet==null || $row>=2)
98. {
99. echo "Error:Did Not Connect!";
100. }else{
101. if(count($arr)==$this->maxcols-1)
102. {
103. $i=1;
104. foreach($arr as $val)
105. {
106. $this->worksheet->Cells($row,$i)->Value=$val;
107. $i++;
108. }
109. $this->workbook->Save();
110. }
111. }
112. }
113. //取得总列数
114. private function maxCols()
115. {
116. $i=1;
117. while(true)
118. {
119. if(0==$this->worksheet->Cells(1,$i))
120. {
121. return $i;
122. break;
123. }
124. $i++;
125. }
126. }
127. //取得总行数
128. private function maxRows()
129. {
130. $i=1;
131. while(true)
132. {
133. if(0==$this->worksheet->Cells($i,1))
134. {
135. return $i;
136. break;
137. }
138. $i++;
139. }
140. }
141. //读取制定行数据
142. public function getOneRow($row=2)
143. {
144. if($row>=2)
145. {
146. for($i=1;$i<$this->maxcols;$i++)
147. {
148. $arr[]=$this->worksheet->Cells($row,$i)->Value;
149. }
150. return $arr;
151. }
152. }
153. //关闭对象
154. public function Close()
155. {
156. $this->excel->WorkBooks->Close();
157. $this->excel=null;
158. $this->workbook=null;
159. $this->worksheet=null;
160. self::$instance=null;
161. }
162. };
163. /*
164. $excel = new COM("Excel.Application");
165. $workbook = $excel->WorkBooks->Open('D://Apache2//htdocs//wwwroot//MyExcel.xls');
166. $worksheet = $excel->WorkSheets(1);
167. echo $worksheet->Cells(2,6)->Value;
168. $excel->WorkBooks->Close();
169. */
170. $excel=Excel::getInstance();
171. $excel->setFile("D://kaka.xls");
172. $excel->Open();
173. $excel->setSheet();
174. for($i=1;$i<16;$i++ )
175. {
176. $arr[]=$i;
177. }
178. //$excel->editOneRow(2,$arr);
179. //print_r($excel->getAllData());
180. $str=$excel->getAllData();
181. include_once('mail.class.php');
182. $smtpserver="smtp.yeah.net";
183. $smtpserverport=25;
184. $smtpuseremail="yanqihu58@yeah.net";
185. $smtpemailto="yanqihu@139.com";
186. $smtpuser="yanqihu58";
187. $smtppwd="123456789";
188. $mailtype="HTML";
189. $smtp=new smtp($smtpserver,$smtpserverport,true,$smtpuser,$smtppwd);
190. $message="你好";
191. //$message.="首页连接地址为：".$this->link_url."<br>";
192. //$message.="电子邮箱为：".$this->link_email."<br>";
193. //$message.="商务联系QQ：".$this->link_qq."<br>";
194. //$message.="商务电话QQ：".$this->link_tel."<br>";
195. //$message.="联系人：".$this->link_people."<br>";
196. $smtp->debug=false;
197. foreach($str['email'] as $key=>$value){
198. $smtpemailto=$value;
199. @$smtp->sendmail($smtpemailto,$smtpuseremail,$mailsubject,$message,$mailtype);
200. exit;
201. }
202. //exit;
203. $excel->Close();
204. ?>