search

Home >Backend Development >PHP Tutorial >php uses PHPEXcel to export table data

php uses PHPEXcel to export table data

WBOY
WBOYOriginal
2016-07-25 08:41:591436browse

In the project, I need to export the data in the table. I searched online and found that phpexcel is quite easy to use. I would like to share it

PHPEXCEL

  2. if(!defined('BASEPATH')) exit('No direct script access allowed');
  3. //Material issue order details
  4. class Read_write{
  5. /**
  6. * $name: Selected type (CSV, EXCEL2003, 2007)
  7. * $titles: Title array
  8. * $querys: Array returned by query $query->result_array();
  9. * $filename: Saved file name
  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 Read workbook names (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. //Read file suffix name
  69. function _file_extend($ file_name){
  70. $extend =explode("." , $file_name);
  71. $last=count($extend)-1;
  72. return $extend[$last];
  73. }
  74. //------ ----------------------------------------Prepared to reserve
  75. //2011-12-21 Added CVS export function
  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: Title
  85. * @param $datas: Data
  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. }
Copy code

PHPEXCEL ~ 13KB Download(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. * Output to EXCEL on the page
  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. /***********************************Export data starts****************** **************************************/
  68. /**
  69. * Generate Excel2007 file
  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. * Generate Excel2003 file
  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. * Generate CSV file
  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. /***********************************End of exporting data****************** ************************************/
  162. /***********************************Reading data starts****************** *************************************/
  164. /**
  165. * Usage, $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. /**Get how many columns there are in total*/
  172. $allColumn = $currentSheet->getHighestColumn();
  173. /**Get the total number of rows*/
  174. $allRow = $currentSheet->getHighestRow();
  175. $size=strlen($allColumn);//如果超出Z,则出现不执行下去
  176. $esql="";
  178. for($currentRow = $curRow;$currentRow<=$allRow;$currentRow++){
  179. $sql=$insertSql;
  180. if($size==2){
  181. $i=1;
  182. $currentColumn='A';
  183. while ($i <= 26) {
  184. $address = $currentColumn.$currentRow;
  185. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  186. $sql.='"'.$temp.'"'.",";
  187. $currentColumn++;
  188. $i++;
  189. }
  190. for($currentColumn='AA';$currentColumn<=$allColumn;$currentColumn++){
  191. $address = $currentColumn.$currentRow;
  192. $sql.='"'.$currentSheet->getCell($address)->getCalculatedValue().'"'.",";
  193. }
  195. }else{
  196. for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
  197. if($merge){//如果是读取合并的值,则判断,如果此行的值为NULL,则把前面的tempName赋值给$temp;
  198. if($currentColumn==$mergeCol){//这里先指定从B列的名字开始读取合并了的值。以后遇到不同的再调整。
  199. $temp=$currentSheet->getCell($mergeCol.$currentRow)->getCalculatedValue();
  200. if(empty($temp)){
  201. $temp=$this->tempName;
  202. }else{
  203. $this->tempName=$temp;
  204. }
  205. }else{
  206. $address = $currentColumn.$currentRow;//getValue()
  207. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  208. }
  209. }else{
  210. $address = $currentColumn.$currentRow;//getValue()
  211. $temp=$currentSheet->getCell($address)->getCalculatedValue();
  212. }
  213. $sql=$sql.'"'.$temp.'"'.",";
  214. }
  215. }
  216. $esql=rtrim($sql,",").')';
  217. //echo($esql);
  218. //return;
  219. $CI->db->simple_query($esql);
  220. }
  221. }
  222. /**
  223. * $filePath: The path to read the file
  224. * $insertSql: Spelled SQL
  225. */
  226. function read_EXCEL2007($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  227. $objs=$this->_get_PHPExcel($this->E2007,$filePath,$sheet,$insertSql,$riqi);
  228. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
  229. }
  230. /**
  231. * Read 2003Excel
  232. */
  233. function read_2003Excel($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  234. $objs=$this->_get_PHPExcel($this->E2003,$filePath,$sheet,$insertSql,$riqi);
  235. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
  236. }
  237. /**
  238. * Read CSV
  239. */
  240. function read_CSV($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
  241. $objs=$this->_get_PHPExcel($this->ECSV,$filePath,$sheet,$insertSql,$riqi,$mergeCol);
  242. $this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge);
  243. }
  244. //--------------------------------读取工作薄信息开始
  245. /**
  246. * Read Excel2007 workbook name
  247. */
  248. function read_EXCEL2007_Sheets($filePath){
  249. return $this->_get_sheetnames($this->E2007,$filePath);
  250. }
  252. /**
  253. * Read 2003 Excel workbook name
  254. */
  255. function read_2003Excel_Sheet($filePath){
  256. return $this->_get_sheetnames($this->E2003,$filePath);
  257. }
  258. /**
  259. * Read CSV workbook name
  260. */
  261. function read_CSV_Sheet($filePath){
  262. return $this->_get_sheetnames($this->ECSV,$filePath);
  263. }
  264. //--------------------------------读取工作薄信息结束
  265. /**
  266. * 2012-1-14 --------------------------
  267. */
  268. //读取Reader流
  269. function _get_Reader($name){
  270. $reader=null;
  271. switch ($name) {
  272. case $this->E2003:
  273. $reader = new PHPExcel_Reader_Excel5();
  274. break;
  275. case $this->E2007:
  276. $reader = new PHPExcel_Reader_Excel2007();
  277. break;
  278. case $this->ECSV:
  279. $reader = new PHPExcel_Reader_CSV();
  280. break;
  281. }
  282. return $reader;
  283. }
  284. //得到$objPHPExcel文件对象
  285. function _get_PHPExcel($name,$filePath,$sheet,$insertSql,$riqi){
  286. $reader=$this->_get_Reader($name);
  287. $PHPExcel= $this->_init_Excel($reader,$filePath,$sheet);
  288. if($riqi){ //如果不需要日期,则忽略.
  289. $insertSql=$insertSql.'"'.$reader->getSheetTitle().'"'.",";//第一个字段固定是日期2012-1-9
  290. }
  291. return array("EXCEL"=>$PHPExcel,"SQL"=>$insertSql);
  292. }
  294. //得到工作薄名称
  295. function _get_sheetnames($name,$filePath){
  296. $reader=$this->_get_Reader($name);
  297. $this->_init_Excel($reader,$filePath);
  298. return $reader->getAllSheets();
  299. }
  300. //加载文件
  301. function _init_Excel($objReader,$filePath,$sheet=''){
  302. $objReader->setReadDataOnly(true);
  303. if(!empty($sheet)){
  304. $objReader->setSheetIndex($sheet);//读取第几个Sheet。
  305. }
  306. return $objReader->load("$filePath");
  307. }
  308. //---------------------------- ---2012-1-14
  309. }
  310. /***********************************End of reading data*************** *************************************/
Copy code

[PHP] code

  1. ------------------------Import operation----------------------------- ----
  2. /**
  3. * $sql="INSERT INTO ".mymsg::WY_MMB." (dizhi,xingming) VALUES (";
  4. */
  5. //Upload first and then read the file
  6. function upByFile($sql, $url, $curRow = 2, $RIQI = true,$merge = FALSE,$mergeCol= 'B')
  7. {
  9. $CI = &get_instance();
  10. $config['allowed_types'] = '*'; //Allow all files
  11. $config['upload_path'] = IMPORT; //Only files The path
  12. $CI->load->library('upload', $config);
  14. if ($CI->upload->do_upload()) { //The default name is: userfile
  15. $data = $CI->upload->data();
  17. $full_name = $data['full_path']; //Get the saved path
  18. $full_name = mb_convert_encoding($full_name, "GBK", "UTF- 8");
  19. $sheet = $CI->input->post("sheet"); //Read the xth column chart
  20. if (empty($sheet)) {
  21. $sheet = 0;
  22. }
  24. $CI->read_write->read_Facotry($full_name, $sql, $sheet, $curRow, $RIQI,$merge,$mergeCol); //Execute the insert command
  26. }
  28. $this-> alert_msg(mymsg::IMPORT_SUCCESS, site_url($url));
  29. }
  31. ---------------------------------Export operation ----------------------------------
  32. //Export the specified table fields
  33. public function show_export(){
  34. //-----Database field
  35. $field=implode(",",$this->input->post("listCheckBox_show"));//Database field
  36. //Display name
  37. $titleArray=$ this->input->post("listCheckBox_field");//Displayed field name (field Comment annotation name, because some empty arrays are passed in, so they must be filtered)
  38. $title=array();
  39. foreach ( $titleArray as $key => $value) {
  40. if (!empty($value)) {
  41. $title[]=$value;
  42. }
  43. }
  44. //---Database table name
  45. $table=$ this->input->post("tableName");
  46. //--Database table name (Comment)
  47. $show_name=$this->input->post("tableComment");
  48. // --Export type
  49. $type=$this->input->post("type");
  51. //--where year and month
  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. //--Written data
  58. $datas=$this->mcom_model->queryByField($field,$table);
  59. }
  61. //---Start export
  62. $this ->read_write->write_Factory($title,$datas,$show_name,$type);
  63. }
Copy code

php, PHPEXcel


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：php gets the day of the week that today or a certain date isNext article：php gets the day of the week that today or a certain date is

Related articles

See more