当前位置:Gxlcms > PHP教程 > php使用PHPEXcel导出表数据

php使用PHPEXcel导出表数据

时间:2021-07-01 10:21:17 帮助过:28人阅读

项目中需要将表中的数据导出,在网上找了找发现phpexcel蛮好用的.特此分享

PHPEXCEL

  1. if(!defined('BASEPATH')) exit('No direct script access allowed');
  2. //物资发料单明细
  3. class Read_write{
  4. /**
  5. * $name:选择的类型(CSV,EXCEL2003,2007)
  6. * $titles:标题数组
  7. * $querys:查询返回的数组 $query->result_array();
  8. * $filename:保存的文件名称
  9. */
  10. function write_Factory($titles,$querys,$filename,$name="EXCEL2003"){
  11. $CI = &get_instance();
  12. $filename=mb_convert_encoding($filename, "GBK","UTF-8");
  13. switch ($name) {
  14. case "CSV":
  15. $CI->excel->write_CSV($titles,$querys,$filename);
  16. break;
  17. case "EXCEL2003":
  18. $CI->excel->write_EXCEL2003($titles,$querys,$filename);
  19. break;
  20. case "EXCEL2007":
  21. $CI->excel->write_EXCEL2007($titles,$querys,$filename);
  22. break;
  23. }
  24. }
  25. /**
  26. * $name:
  27. */
  28. function read_Facotry($filePath,$sql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  29. $CI = &get_instance();
  30. $name=$this->_file_extend($filePath);
  31. switch ($name) {
  32. case "csv":
  33. $CI->excel->read_CSV($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
  34. break;
  35. case "xls":
  36. $CI->excel->read_2003Excel($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
  37. break;
  38. case "xlsx":
  39. $CI->excel->read_EXCEL2007($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
  40. break;
  41. }
  42. $CI->mytool->import_info("filePath=$filePath,sql=$sql");
  43. }
  44. /**
  45. * 2012-1-14 读取工作薄名称(sheetnames)
  46. */
  47. function read_sheetNames($filePath){
  48. $CI = &get_instance();
  49. $name=$this->_file_extend($filePath);
  50. $sheetnames;
  51. switch ($name) {
  52. case "csv":
  53. $sheetnames=$CI->excel->read_CSV_Sheet($filePath);
  54. break;
  55. case "xls":
  56. $sheetnames=$CI->excel->read_2003Excel_Sheet($filePath);
  57. break;
  58. case "xlsx":
  59. $sheetnames=$CI->excel->read_EXCEL2007_Sheets($filePath);
  60. break;
  61. }
  62. return $sheetnames;
  63. }
  64. //读取文件后缀名
  65. function _file_extend($file_name){
  66. $extend =explode("." , $file_name);
  67. $last=count($extend)-1;
  68. return $extend[$last];
  69. }
  70. //-----------------------------------------------预备保留
  71. //2011-12-21新增CVS导出功能
  72. public function export_csv($filename,$title,$datas, $delim = ",", $newline = "\n", $enclosure = '"'){
  73. $CI = &get_instance();
  74. $cvs= $this->_csv_from_result($title,$datas,$delim,$newline,$enclosure);
  75. $CI->load->helper('download');
  76. $name=mb_convert_encoding($filename, "GBK","UTF-8");
  77. force_download($name, $cvs);
  78. }
  79. /**
  80. * @param $titles:标题
  81. * @param $datas:数据
  82. */
  83. function _csv_from_result($titles,$datas, $delim = ",", $newline = "\n", $enclosure = '"'){
  84. $out = '';
  85. // First generate the headings from the table column names
  86. foreach ($titles as $name){
  87. $name=mb_convert_encoding($name, "GBK","UTF-8");
  88. $out .= $enclosure.str_replace($enclosure, $enclosure.$enclosure, $name).$enclosure.$delim;
  89. }
  90. $out = rtrim($out);
  91. $out .= $newline;
  92. // Next blast through the result array and build out the rows
  93. foreach ($datas as $row)
  94. {
  95. foreach ($row as $item)
  96. {
  97. $item=mb_convert_encoding($item, "GBK","UTF-8");
  98. $out .= $enclosure.str_replace($enclosure, $enclosure.$enclosure, $item).$enclosure.$delim;
  99. }
  100. $out = rtrim($out);
  101. $out .= $newline;
  102. }
  103. return $out;
  104. }
  105. }

PHPEXCEL ~ 13KB 下载(28)

  1. /**
  2. * PHPExcel
  3. *
  4. * Copyright (C) 2006 - 2010 PHPExcel
  5. *
  6. * This library is free software; you can redistribute it and/or
  7. * modify it under the terms of the GNU Lesser General Public
  8. * License as published by the Free Software Foundation; either
  9. * version 2.1 of the License, or (at your option) any later version.
  10. *
  11. * This library is distributed in the hope that it will be useful,
  12. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  14. * Lesser General Public License for more details.
  15. *
  16. * You should have received a copy of the GNU Lesser General Public
  17. * License along with this library; if not, write to the Free Software
  18. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  19. *
  20. * @category PHPExcel
  21. * @package PHPExcel
  22. * @copyright Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
  23. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  24. * @version 1.7.4, 2010-08-26
  25. */
  26. /** Error reporting */
  27. error_reporting(E_ALL);
  28. date_default_timezone_set ('Asia/Shanghai');
  29. /** PHPExcel */
  30. require_once 'Classes/PHPExcel.php';
  31. require_once 'Classes/PHPExcel/IOFactory.php';
  32. /**
  33. * 输出到页面上的EXCEL
  34. */
  35. /**
  36. * CI_Excel
  37. *
  38. * @package ci
  39. * @author admin
  40. * @copyright 2011
  41. * @version $Id$
  42. * @access public
  43. */
  44. class CI_Excel
  45. {
  46. //列头,Excel每列上的标识
  47. private $cellArray = array(
  48. 1=>'A', 2=>'B', 3=>'C', 4=>'D', 5=>'E',
  49. 6=>'F', 7=>'G', 8=>'H', 9=>'I',10=>'J',
  50. 11=>'K',12=>'L',13=>'M',14=>'N',15=>'O',
  51. 16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',
  52. 21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',
  53. 26=>'Z',
  54. 27=>'AA', 28=>'AB', 29=>'AC', 30=>'AD', 31=>'AE',
  55. 32=>'AF', 33=>'AG', 34=>'AH', 35=>'AI',36=>'AJ',
  56. 37=>'AK',38=>'AL',39=>'AM',40=>'AN',41=>'AO',
  57. 42=>'AP',43=>'AQ',44=>'AR',45=>'AS',46=>'AT',
  58. 47=>'AU',48=>'AV',49=>'AW',50=>'AX',51=>'AY',
  59. 52=>'AZ', 53=>'BA', 54=>'BB', 55=>'BC', 56=>'BD', 57=>'BE',
  60. 58=>'BF', 59=>'BG', 60=>'BH', 61=>'BI', 62=>'BJ', 63=>'BK', 64=>'BL');
  61. private $E2003 = 'E2003';
  62. private $E2007 = 'E2007';
  63. private $ECSV = 'ECSV';
  64. private $tempName; //当读取合并文件时,如果第二行为空,则取第一行的名称
  65. /*********************************导出数据开始****************************************************/
  66. /**
  67. * 生成Excel2007文件
  68. */
  69. function write_EXCEL2007($title='',$data='',$name='')
  70. {
  71. $objPHPExcel=$this->_excelComm($title,$data,$name);
  72. // Redirect output to a client’s web browser (Excel2007)
  73. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
  74. header("Content-Disposition: attachment;filename=$name.xlsx");
  75. header('Cache-Control: max-age=0');
  76. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
  77. $objWriter->save('php://output'); //output 允许向输出缓冲机制写入数据,和 print() 与 echo() 的方式相同。
  78. exit;
  79. }
  80. /**
  81. * 生成Excel2003文件
  82. */
  83. function write_EXCEL2003($title='',$data='',$name=''){
  84. $objPHPExcel=$this->_excelComm($title,$data,$name);
  85. //Redirect output to a client’s web browser (Excel5)
  86. header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
  87. header("Content-Disposition: attachment;filename=$name.xls");
  88. header('Cache-Control: max-age=0');
  89. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  90. $objWriter->save('php://output');
  91. }
  92. /**
  93. * 生成CSV文件
  94. */
  95. function write_CSV($title='',$data='',$name=''){
  96. $objPHPExcel=$this->_excelComm($title,$data,$name);
  97. header("Content-Type: text/csv;charset=UTF-8");
  98. header("Content-Disposition: attachment; filename=$name.csv");
  99. header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
  100. header('Expires:0');
  101. header('Pragma:public');
  102. $objWriter = new PHPExcel_Writer_CSV($objPHPExcel,'CSV');
  103. $objWriter->save("php://output");
  104. exit;
  105. }
  106. function _excelComm($title,$data,$name){
  107. // Create new PHPExcel object
  108. $objPHPExcel = new PHPExcel();
  109. $objPHPExcel=$this->_writeTitle($title,$objPHPExcel);
  110. $objPHPExcel=$this->_writeDatas($data,$objPHPExcel);
  111. $objPHPExcel=$this->_write_comm($name,$objPHPExcel);
  112. return $objPHPExcel;
  113. }
  114. //输出标题
  115. function _writeTitle($title,$objPHPExcel){
  116. //表头循环(标题)
  117. foreach ($title as $tkey => $tvalue){
  118. $tkey = $tkey+1;
  119. $cell = $this->cellArray[$tkey].'1'; //第$tkey列的第1行,列的标识符(a..z)
  120. // Add some data //表头
  121. // $tvalue=mb_convert_encoding($tvalue, "UTF-8","GBK");
  122. $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $tvalue); //设置第$row列的值(标题)
  123. }
  124. return $objPHPExcel;
  125. }
  126. //输出内容
  127. function _writeDatas($data,$objPHPExcel){
  128. //内容循环(数据库查询的返回值)
  129. foreach($data as $key =>$value) {
  130. $i = 1;
  131. foreach ($value as $mkey =>$mvalue){ //返回的类型是array([0]=>array());,所以此处要循环它的value,也就是里面的array
  132. $rows = $key+2; //开始是第二行
  133. $mrow = $this->cellArray[$i].$rows; //第$i列的第$row行
  134. // $mvalue=mb_convert_encoding($mvalue, "GBK","UTF-8");
  135. // print_r($mrow."--->".$mvalue);
  136. $objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit($mrow, $mvalue);
  137. $i++;
  138. }
  139. }
  140. return $objPHPExcel;
  141. }
  142. function _write_comm($name,$objPHPExcel){
  143. // Rename sheet(左下角的标题)
  144. //$objPHPExcel->getActiveSheet()->setTitle($name);
  145. // Set active sheet index to the first sheet, so Excel opens this as the first sheet
  146. $objPHPExcel->setActiveSheetIndex(0); //默认显示
  147. return $objPHPExcel;
  148. }
  149. /*********************************导出数据结束****************************************************/
  150. /*********************************读取数据开始****************************************************/
  151. /**
  152. * 使用方法,$insertSql:insert xx (x1,x2,x3,x4) value (
  153. */
  154. // function _comm_insert($objReader,$filePath,$insertSql,$sheet=2,$curRow=2,$riqi=TRUE){
  155. function _comm_insert($objPHPExcel,$insertSql,$curRow,$merge=FALSE,$mergeCol='B'){
  156. $CI = &get_instance();
  157. $currentSheet = $objPHPExcel->getSheet();//得到指定的激活
  158. /**取得一共有多少列*/
  159. $allColumn = $currentSheet->getHighestColumn();
  160. /**取得一共有多少行*/
  161. $allRow = $currentSheet->getHighestRow();
  162. $size=strlen($allColumn);//如果超出Z,则出现不执行下去
  163. $esql="";
  164. for($currentRow = $curRow;$currentRow<=$allRow;$currentRow++){
  165. $sql=$insertSql;
  166. if($size==2){
  167. $i=1;
  168. $currentColumn='A';
  169. while ($i <= 26) {
  170. $address = $currentColumn.$currentRow;
  171. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  172. $sql.='"'.$temp.'"'.",";
  173. $currentColumn++;
  174. $i++;
  175. }
  176. for($currentColumn='AA';$currentColumn<=$allColumn;$currentColumn++){
  177. $address = $currentColumn.$currentRow;
  178. $sql.='"'.$currentSheet->getCell($address)->getCalculatedValue().'"'.",";
  179. }
  180. }else{
  181. for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
  182. if($merge){//如果是读取合并的值,则判断,如果此行的值为NULL,则把前面的tempName赋值给$temp;
  183. if($currentColumn==$mergeCol){//这里先指定从B列的名字开始读取合并了的值。以后遇到不同的再调整。
  184. $temp=$currentSheet->getCell($mergeCol.$currentRow)->getCalculatedValue();
  185. if(empty($temp)){
  186. $temp=$this->tempName;
  187. }else{
  188. $this->tempName=$temp;
  189. }
  190. }else{
  191. $address = $currentColumn.$currentRow;//getValue()
  192. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  193. }
  194. }else{
  195. $address = $currentColumn.$currentRow;//getValue()
  196. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  197. }
  198. $sql=$sql.'"'.$temp.'"'.",";
  199. }
  200. }
  201. $esql=rtrim($sql,",").')';
  202. //echo($esql);
  203. //return;
  204. $CI->db->simple_query($esql);
  205. }
  206. }
  207. /**
  208. * $filePath:读取文件的路径
  209. * $insertSql:拼写的SQL
  210. */
  211. function read_EXCEL2007($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  212. $objs=$this->_get_PHPExcel($this->E2007,$filePath,$sheet,$insertSql,$riqi);
  213. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
  214. }
  215. /**
  216. * 读取2003Excel
  217. */
  218. function read_2003Excel($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  219. $objs=$this->_get_PHPExcel($this->E2003,$filePath,$sheet,$insertSql,$riqi);
  220. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
  221. }
  222. /**
  223. * 读取CSV
  224. */
  225. function read_CSV($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  226. $objs=$this->_get_PHPExcel($this->ECSV,$filePath,$sheet,$insertSql,$riqi,$mergeCol);
  227. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge);
  228. }
  229. //--------------------------------读取工作薄信息开始
  230. /**
  231. * 读取Excel2007工作薄名称
  232. */
  233. function read_EXCEL2007_Sheets($filePath){
  234. return $this->_get_sheetnames($this->E2007,$filePath);
  235. }
  236. /**
  237. * 读取2003Excel工作薄名称
  238. */
  239. function read_2003Excel_Sheet($filePath){
  240. return $this->_get_sheetnames($this->E2003,$filePath);
  241. }
  242. /**
  243. * 读取CSV工作薄名称
  244. */
  245. function read_CSV_Sheet($filePath){
  246. return $this->_get_sheetnames($this->ECSV,$filePath);
  247. }
  248. //--------------------------------读取工作薄信息结束
  249. /**
  250. * 2012-1-14 --------------------------
  251. */
  252. //读取Reader流
  253. function _get_Reader($name){
  254. $reader=null;
  255. switch ($name) {
  256. case $this->E2003:
  257. $reader = new PHPExcel_Reader_Excel5();
  258. break;
  259. case $this->E2007:
  260. $reader = new PHPExcel_Reader_Excel2007();
  261. break;
  262. case $this->ECSV:
  263. $reader = new PHPExcel_Reader_CSV();
  264. break;
  265. }
  266. return $reader;
  267. }
  268. //得到$objPHPExcel文件对象
  269. function _get_PHPExcel($name,$filePath,$sheet,$insertSql,$riqi){
  270. $reader=$this->_get_Reader($name);
  271. $PHPExcel= $this->_init_Excel($reader,$filePath,$sheet);
  272. if($riqi){ //如果不需要日期,则忽略.
  273. $insertSql=$insertSql.'"'.$reader->getSheetTitle().'"'.",";//第一个字段固定是日期2012-1-9
  274. }
  275. return array("EXCEL"=>$PHPExcel,"SQL"=>$insertSql);
  276. }
  277. //得到工作薄名称
  278. function _get_sheetnames($name,$filePath){
  279. $reader=$this->_get_Reader($name);
  280. $this->_init_Excel($reader,$filePath);
  281. return $reader->getAllSheets();
  282. }
  283. //加载文件
  284. function _init_Excel($objReader,$filePath,$sheet=''){
  285. $objReader->setReadDataOnly(true);
  286. if(!empty($sheet)){
  287. $objReader->setSheetIndex($sheet);//读取第几个Sheet。
  288. }
  289. return $objReader->load("$filePath");
  290. }
  291. //-------------------------------2012-1-14
  292. }
  293. /*********************************读取数据结束****************************************************/

[PHP]代码

  1. ------------------------导入操作------------------------
  2. /**
  3. * $sql="INSERT INTO ".mymsg::WY_MMB." (dizhi,xingming) VALUES (";
  4. */
  5. //先上传再读取文件
  6. function upByFile($sql, $url, $curRow = 2, $RIQI = true,$merge = FALSE,$mergeCol='B')
  7. {
  8. $CI = &get_instance();
  9. $config['allowed_types'] = '*'; //充许所有文件
  10. $config['upload_path'] = IMPORT; // 只在文件的路径
  11. $CI->load->library('upload', $config);
  12. if ($CI->upload->do_upload()) { //默认名是:userfile
  13. $data = $CI->upload->data();
  14. $full_name = $data['full_path']; //得到保存后的路径
  15. $full_name = mb_convert_encoding($full_name, "GBK", "UTF-8");
  16. $sheet = $CI->input->post("sheet"); //读取第x列图表
  17. if (empty($sheet)) {
  18. $sheet = 0;
  19. }
  20. $CI->read_write->read_Facotry($full_name, $sql, $sheet, $curRow, $RIQI,$merge,$mergeCol); //执行插入命令
  21. }
  22. $this->alert_msg(mymsg::IMPORT_SUCCESS, site_url($url));
  23. }
  24. ------------------------------导出操作----------------------------------
  25. //导出指定的表字段
  26. public function show_export(){
  27. //-----数据库字段
  28. $field=implode(",",$this->input->post("listCheckBox_show"));//数据库字段
  29. //显示名称
  30. $titleArray=$this->input->post("listCheckBox_field");//显示的字段名称(字段Comment注解名,因为传进来的有些空数组,所以必须过滤)
  31. $title=array();
  32. foreach ($titleArray as $key => $value) {
  33. if (!empty($value)) {
  34. $title[]=$value;
  35. }
  36. }
  37. //---数据库表名
  38. $table=$this->input->post("tableName");
  39. //--数据库表名称(Comment注释)
  40. $show_name=$this->input->post("tableComment");
  41. //--导出类型
  42. $type=$this->input->post("type");
  43. //--where 年月
  44. $y_month=$this->input->post("year_month");
  45. if(!empty($y_month)){
  46. $where["riqi"]=$y_month;
  47. $datas=$this->mcom_model->queryByWhereReField($field,$where,$table);
  48. }else{
  49. //--写出的数据
  50. $datas=$this->mcom_model->queryByField($field,$table);
  51. }
  52. //---开始导出
  53. $this->read_write->write_Factory($title,$datas,$show_name,$type);
  54. }

php, PHPEXcel

人气教程排行