recherche

Maison >développement back-end >tutoriel php >php 使用PHPEXcel导出表数据

php 使用PHPEXcel导出表数据

WBOY
WBOYoriginal
2016-07-25 08:41:591427parcourir

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

PHPEXCEL

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

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

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

php, PHPEXcel


Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Article précédent：php 获取当天或某个日期是星期几 Article suivant：封装了get post 方法的PHP代码

Articles Liés

Voir plus