PHP uses Phpexcel to export table data

The project needs to export the data in the table, found on the Internet find phpexcel good use. Share now Phpexcel if (!defined (' BasePath ')) exit (' No Direct script access allowed '); Material Hair Material List details Class read_write{ /** * $name: Selected type (csv,excel2003,2007) * $titles: Header array * $querys: The array returned by the query $query->result_array (); * $filename: Saved file name */ function Write_factory ($titles, $querys, $filename, $name = "EXCEL2003") { $CI = &get_instance (); $filename =mb_convert_encoding ($filename, "GBK", "UTF-8"); Switch ($name) { Case "CSV": $CI->excel->write_csv ($titles, $querys, $filename); Break Case "EXCEL2003": $CI->excel->write_excel2003 ($titles, $querys, $filename); Break Case "EXCEL2007": $CI->excel->write_excel2007 ($titles, $querys, $filename); Break } } /** * $name: */ function Read_facotry ($filePath, $sql, $sheet =0, $curRow =2, $riqi =true, $merge =false, $mergeCol = "B") { $CI = &get_instance (); $name = $this->_file_extend ($filePath); Switch ($name) { Case "CSV": $CI->excel->read_csv ($filePath, $sql, $sheet, $curRow, $riqi, $merge, $mergeCol); Break Case "XLS": $CI->excel->read_2003excel ($filePath, $sql, $sheet, $curRow, $riqi, $merge, $mergeCol); Break Case "xlsx": $CI->excel->read_excel2007 ($filePath, $sql, $sheet, $curRow, $riqi, $merge, $mergeCol); Break } $CI->mytool->import_info ("Filepath= $filePath, sql= $sql"); } /** * 2012-1-14 Read workbook name (sheetnames) */ function Read_sheetnames ($filePath) { $CI = &get_instance (); $name = $this->_file_extend ($filePath); $sheetnames; Switch ($name) { Case "CSV": $sheetnames = $CI->excel->read_csv_sheet ($filePath); Break Case "XLS": $sheetnames = $CI->excel->read_2003excel_sheet ($filePath); Break Case "xlsx": $sheetnames = $CI->excel->read_excel2007_sheets ($filePath); Break } return $sheetnames; } Read file suffix name function _file_extend ($file _name) { $extend =explode (".", $file _name); $last =count ($extend)-1; return $extend [$last]; } -----------------------------------------------ready to keep 2011-12-21 New CVS export feature Public Function Export_csv ($filename, $title, $datas, $delim = ",", $newline = "\ n", $enclosure = ' "') { $CI = &get_instance (); $cvs = $this->_csv_from_result ($title, $datas, $delim, $newline, $enclosure); $CI->load->helper (' Download '); $name =mb_convert_encoding ($filename, "GBK", "UTF-8"); Force_download ($name, $cvs); } /** * @param $titles: Title * @param $datas: Data */ function _csv_from_result ($titles, $datas, $delim = ",", $newline = "\ n", $enclosure = ' "') { $out = "; First generate the headings from the table column names foreach ($titles as $name) { $name =mb_convert_encoding ($name, "GBK", "UTF-8"); $out. = $enclosure. Str_replace ($enclosure, $enclosure. $enclosure, $name). $enclosure. $delim; } $out = RTrim ($out); $out. = $newline; Next blast through the result array and build out the rows foreach ($datas as $row) { foreach ($row as $item) { $item =mb_convert_encoding ($item, "GBK", "UTF-8"); $out. = $enclosure. Str_replace ($enclosure, $enclosure. $enclosure, $item). $enclosure. $delim; } $out = RTrim ($out); $out. = $newline; } return $out; } } Copy Code phpexcel ~ 13KB Download ($) /** * Phpexcel * * Copyright (C) 2006-2010 Phpexcel * * This library was free software; You can redistribute it and/or * Modify it under the terms of the GNU Lesser general public * License as published by the Free software Foundation; Either * Version 2.1 of the License, or (at your option) any later version. * * This library was distributed in the hope that it'll be useful, * but without any WARRANTY; Without even the implied warranty of * merchantability or FITNESS for A particular PURPOSE. See the GNU * Lesser general public License for more details. * * You should has received a copy of the GNU Lesser general public * License along with this library; If not, write to the free software * Foundation, Inc., Wuyi Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * * @category Phpexcel * @package Phpexcel * @copyright Copyright (c) 2006-2010 Phpexcel (Http://www.codeplex.com/PHPExcel) * @license Http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.7.4, 2010-08-26 */ /** Error Reporting */ Error_reporting (E_all); Date_default_timezone_set (' Asia/shanghai '); /** Phpexcel */ Require_once ' classes/phpexcel.php '; Require_once ' classes/phpexcel/iofactory.php '; /** * Output to Excel on the page */ /** * Ci_excel * * @package CI * @author Admin * @copyright 2011 * @version $Id $ * @access Public */ Class Ci_excel { column header, identity on each column of Excel Private $cellArray = Array ( 1=> ' A ', 2=> ' B ', 3=> ' C ', 4=> ' D ', 5=> ' E ', 6=> ' F ', 7=> ' G ', 8=> ' H ', 9=> ' I ',10=> ' J ', 11=> ' K ',12=> ' L ',13=> ' M ',14=> ' N ',15=> ' O ', 16=> ' P ',17=> ' Q ',18=> ' R ',19=> ' S ',20=> ' T ', 21=> ' U ',22=> ' V ',23=> ' W ',24=> ' X ',25=> ' Y ', 26=> ' Z ', 27=> ' AA ', 28=> ' AB ', 29=> ' AC ', 30=> ' AD ', 31=> ' AE ', 32=> ' AF ', 33=> ' AG ', 34=> ' AH ', 35=> ' AI ',36=> ' AJ ', 37=> ' AK ',38=> ' AL ',39=> ' AM ',40=> ' an ',41=> ' AO ', 42=> ' AP ',43=> ' AQ ',44=> ' AR ',45=> ' as ',46=> ' at ', 47=> ' AU ',48=> ' AV ',49=> ' AW ',50=> ' AX ',51=> ' AY ', 52=> ' AZ ', 53=> ' BA ', 54=> ' BB ', 55=> ' BC ', 56=> ' BD ', 57=> ' be ', 58=> ' BF ', 59=> ' BG ', 60=> ' BH ', 61=> ' BI ', 62=> ' BJ ', 63=> ' BK ', 64=> ' BL '); Private $E 2003 = ' E2003 '; Private $E = ' E2007 '; Private $ECSV = ' ecsv '; Private $tempName; When the merge file is read, if the second behavior is empty, the name of the first row is taken /********************************* Export Data Start ****************************************************/ /** * Generate Excel2007 file */ function write_excel2007 ($title = ", $data =", $name = ") { $objPHPExcel = $this->_excelcomm ($title, $data, $name); Redirect output to a client ' s Web browser (Excel2007) Header (' Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8 '); Header ("Content-disposition:attachment;filename= $name. xlsx"); Header (' cache-control:max-age=0 '); $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, "Excel2007"); $objWriter->save (' php://output '); Output allows data to be written to the export buffering mechanism in the same way as print () and Echo (). Exit } /** * Generate Excel2003 file */ function write_excel2003 ($title = ', $data = ', $name = ') { $objPHPExcel = $this->_excelcomm ($title, $data, $name); Redirect output to a client ' s Web browser (EXCEL5) Header (' Content-type:application/vnd.ms-excel;charset=utf-8 '); Header ("Content-disposition:attachment;filename= $name. xls"); Header (' cache-control:max-age=0 '); $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 '); $objWriter->save (' php://output '); } /** * Generate CSV file */ function write_csv ($title = ', $data = ', $name = ') { $objPHPExcel = $this->_excelcomm ($title, $data, $name); Header ("Content-type:text/csv;charset=utf-8"); Header ("content-disposition:attachment; Filename= $name. csv "); Header (' cache-control:must-revalidate,post-check=0,pre-check=0 '); Header (' expires:0 '); Header (' Pragma:public '); $objWriter = new Phpexcel_writer_csv ($objPHPExcel, ' CSV '); $objWriter->save ("Php://output"); Exit } function _excelcomm ($title, $data, $name) { Create New Phpexcel Object $objPHPExcel = new Phpexcel (); $objPHPExcel = $this->_writetitle ($title, $objPHPExcel); $objPHPExcel = $this->_writedatas ($data, $objPHPExcel); $objPHPExcel = $this->_write_comm ($name, $objPHPExcel); return $objPHPExcel; } Output title function _writetitle ($title, $objPHPExcel) { Header Loop (header) foreach ($title as $tkey = = $tvalue) { $tkey = $tkey +1; $cell = $this->cellarray[$tkey]. ' 1 '; The 1th row of column $tkey, the identifier of the column (a). Z ADD some data//table header $tvalue =mb_convert_encoding ($tvalue, "UTF-8", "GBK"); $objPHPExcel->setactivesheetindex (0)->setcellvalue ($cell, $tvalue); Set the value of column $row (title) } return $objPHPExcel; } Output content function _writedatas ($data, $objPHPExcel) { Content loops (return values for database queries) foreach ($data as $key = = $value) { $i = 1; The foreach ($value as $mkey + $mvalue) {//return type is array ([0]=>array ()), so here it is to loop its value, that is, the array inside $rows = $key +2; Start is the second line $mrow = $this->cellarray[$i]. $rows; Line $row of section $i $mvalue =mb_convert_encoding ($mvalue, "GBK", "UTF-8"); Print_r ($mrow. " ---> ". $mvalue); $objPHPExcel->setactivesheetindex (0)->setcellvalueexplicit ($mrow, $mvalue); $i + +; } } return $objPHPExcel; } function _write_comm ($name, $objPHPExcel) { Rename sheet (title in lower-left corner) $objPHPExcel->getactivesheet ()->settitle ($name); Set Active sheet Index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setactivesheetindex (0); Default display return $objPHPExcel; } /********************************* Export Data End ****************************************************/ /********************************* Read Data start ****************************************************/ /** * Method of Use, $INSERTSQL: Insert XX (x1,x2,x3,x4) value ( */ function _comm_insert ($objReader, $filePath, $INSERTSQL, $sheet =2, $curRow =2, $riqi =true) { function _comm_insert ($objPHPExcel, $INSERTSQL, $curRow, $merge =false, $mergeCol = ' B ') { $CI = &get_instance (); $currentSheet = $objPHPExcel->getsheet ();//Get the specified activation How many columns does the/** get altogether? * $allColumn = $currentSheet->gethighestcolumn (); How many lines does the/** get altogether? */ $allRow = $currentSheet->gethighestrow (); $size =strlen ($allColumn);//If the z is exceeded, no execution occurs $esql = ""; for ($currentRow = $curRow; $currentRow <= $allRow; $currentRow + +) { $sql = $INSERTSQL; if ($size ==2) { $i = 1; $currentColumn = ' A '; while ($i <= 26) { $address = $currentColumn. $currentRow; $temp = $currentSheet->getcell ($address)->getcalculatedvalue (); $sql. = ' "'. $temp. '". ","; $currentColumn + +; $i + +; } for ($currentColumn = ' AA '; $currentColumn <= $allColumn; $currentColumn + +) { $address = $currentColumn. $currentRow; $sql. = ' "'. $currentSheet->getcell ($address)->getcalculatedvalue (). '". ","; } }else{ for ($currentColumn = ' A '; $currentColumn <= $allColumn; $currentColumn + +) { if ($merge) {//If the value of the merge is read, it is determined that if the value of this row is null, the preceding tempname is assigned to $temp; if ($currentColumn = = $mergeCol) {//Here first specifies that the merged values begin reading from the first name of column B. Later encountered different re-adjustments. $temp = $currentSheet->getcell ($mergeCol. $currentRow)->getcalculatedvalue (); if (empty ($temp)) { $temp = $this->tempname; }else{ $this->tempname= $temp; } }else{ $address = $currentColumn. $currentRow;//getvalue () $temp = $currentSheet->getcell ($address)->getcalculatedvalue (); } }else{ $address = $currentColumn. $currentRow;//getvalue () $temp = $currentSheet->getcell ($address)->getcalculatedvalue (); } $sql = $sql. ' ". $temp. ' ".", "; } } $esql =rtrim ($sql, ","). ') '; Echo ($esql); Return $CI->db->simple_query ($esql); } } /** * $filePath: Read the path to the file * $INSERTSQL: Spelled sql */ function read_excel2007 ($filePath, $INSERTSQL, $sheet =0, $curRow =2, $riqi =true, $merge =false, $mergeCol = "B") { $OBJS = $this->_get_phpexcel ($this->e2007, $filePath, $sheet, $INSERTSQL, $riqi); $this->_comm_insert ($objs ["EXCEL"], $objs ["SQL"], $curRow, $merge, $mergeCol); } /** * Read 2003Excel */ function Read_2003excel ($filePath, $INSERTSQL, $sheet =0, $curRow =2, $riqi =true, $merge =false, $mergeCol = "B") { $OBJS = $this->_get_phpexcel ($this->e2003, $filePath, $sheet, $INSERTSQL, $riqi); $this->_comm_insert ($objs ["EXCEL"], $objs ["SQL"], $curRow, $merge, $mergeCol); } /** * Read CSV */ function Read_csv ($filePath, $INSERTSQL, $sheet =0, $curRow =2, $riqi =true, $merge =false, $mergeCol = "B") { $OBJS = $this->_get_phpexcel ($this->ecsv, $filePath, $sheet, $INSERTSQL, $riqi, $mergeCol); $this->_comm_insert ($objs ["EXCEL"], $objs ["SQL"], $curRow, $merge); } --------------------------------Start reading workbook information /** * Read Excel2007 workbook name */ function Read_excel2007_sheets ($filePath) { return $this->_get_sheetnames ($this->e2007, $filePath); } /** * Read 2003Excel workbook name */ function Read_2003excel_sheet ($filePath) { return $this->_get_sheetnames ($this->e2003, $filePath); } /** * Read the CSV workbook name */ function Read_csv_sheet ($filePath) { return $this->_get_sheetnames ($this->ecsv, $filePath); } --------------------------------End of Reading workbook information /** * 2012-1-14-------------------------- */ Read Reader stream function _get_reader ($name) { $reader =null; Switch ($name) { Case $this->e2003: $reader = new Phpexcel_reader_excel5 (); Break Case $this->e2007: $reader = new phpexcel_reader_excel2007 (); Break Case $this->ecsv: $reader = new Phpexcel_reader_csv (); Break } return $reader; } Get the $objphpexcel file object function _get_phpexcel ($name, $filePath, $sheet, $INSERTSQL, $riqi) { $reader = $this->_get_reader ($name); $PHPExcel = $this->_init_excel ($reader, $filePath, $sheet); if ($riqi) {//If no date is required, then ignore. $INSERTSQL = $insertSql. ' ". $reader->getsheettitle (). ' "'.", ";//first field fixed is date 2012-1-9 } Return Array ("EXCEL" = $PHPExcel, "SQL" = = $INSERTSQL); } Get the name of the work book function _get_sheetnames ($name, $filePath) { $reader = $this->_get_reader ($name); $this->_init_excel ($reader, $filePath); return $reader->getallsheets (); } Loading files function _init_excel ($objReader, $filePath, $sheet = ") { $objReader->setreaddataonly (TRUE); if (!empty ($sheet)) { $objReader->setsheetindex ($sheet);//Read the first few sheet. } return $objReader->load ("$filePath"); } -------------------------------2012-1-14 } /********************************* Read Data End ****************************************************/ Copy Code [PHP] Code ------------------------the import Operation------------------------ /** * $sql = "INSERT into". Mymsg::wy_mmb. " (dizhi,xingming) VALUES ("; */ Upload and read files first function Upbyfile ($sql, $url, $curRow = 2, $RIQI = true, $merge = FALSE, $mergeCol = ' B ') { $CI = &get_instance (); $config [' allowed_types '] = ' * '; All documents are allowed $config [' upload_path '] = IMPORT; Only the path of the file $CI->load->library (' upload ', $config); if ($CI->upload->do_upload ()) {//default name is: UserFile $data = $CI->upload->data (); $full _name = $data [' Full_path ']; Get the Saved path $full _name = mb_convert_encoding ($full _name, "GBK", "UTF-8"); $sheet = $CI->input->post ("sheet"); Read Column x Chart if (empty ($sheet)) { $sheet = 0; } $CI->read_write->read_facotry ($full _name, $sql, $sheet, $curRow, $RIQI, $merge, $mergeCol); Execute Insert Command } $this->alert_msg (mymsg::import_success, Site_url ($url)); } ------------------------------the export Operation---------------------------------- Export the specified table field Public Function Show_export () { -----database Fields $field =implode (",", $this->input->post ("Listcheckbox_show"));//Database field Display Name $titleArray = $this->input->post ("Listcheckbox_field");//Field name displayed (field comment Note name, because there are some empty arrays passed in, so must be filtered) $title =array (); foreach ($titleArray as $key = = $value) { if (!empty ($value)) { $title []= $value; } } ---database table name $table = $this->input->post ("TableName"); --database table name (Comment comment) $show _name= $this->input->post ("tablecomment"); --Export type $type = $this->input->post ("type"); --where Month $y _month= $this->input->post ("Year_month"); if (!empty ($y _month)) { $where ["Riqi"]= $y _month; $datas = $this->mcom_model->querybywhererefield ($field, $where, $table); }else{ --Write the data $datas = $this->mcom_model->querybyfield ($field, $table); } ---start exporting $this->read_write->write_factory ($title, $datas, $show _name, $type); } Copy Code

PHP, Phpexcel

