Export Table data using PHPEXcel-PHP source code

To use PHPEXcel to export table data, you need to export the data in the table. if you find phpexcel on the Internet, it is very useful.

1. Phpexcel

 Result_array (); * $ filename: name of the saved file */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 the table 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 the file extension function _ file_extend ($ file_name) {$ extend = explode (". ", $ file_name); $ last = count ($ extend)-1; return $ extend [$ last];} // --------------------------------------------- Reserve/2011-12-21 add the 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 ;}}

2. phpexcel

 '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 => 'A', 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 => 'A', 41 => 'ao ', 42 => 'AP ', 43 => 'Aq', 44 => 'ar ', 45 => 'as', 46 => 'at', 4 7 => '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 $ E2003 = 'e2003 '; private $ E2007 = 'e2007 '; private $ ECSV = 'ecsv'; private $ tempName; // if the second row is empty when the merged file is read, take the name of the first line /******************************** * start to export data ************************************ ***************** // 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 = PHP Excel_IOFactory: createWriter ($ objPHPExcel, "Excel2007"); $ objWriter-> save ('php: // output'); // output allows data to be written to the output buffer, and print () are the same as echo. Exit;}/*** generate the 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 a 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 the title functio N _ writeTitle ($ title, $ objPHPExcel) {// header loop (title) foreach ($ title as $ tkey => $ tvalue) {$ tkey = $ tkey + 1; $ cell = $ this-> cellArray [$ tkey]. '1'; // row 1st of column $ tkey, column identifier (.. z) // Add some data // 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 loop (returned value of database query) foreach ($ data as $ key => $ value) {$ I = 1; foreach ($ value as $ mkey => $ mvalue) {// The Returned type is array ([0] => array ();, so here we need to loop its value, that is, the array $ rows = $ key + 2; // starts from the second row $ mrow = $ this-> cellArray [$ I]. $ rows; // The $ row of column $ 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 the 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 ); // return $ objPHPExcel is displayed by default ;} /********************************** end of data export *** **************************************** *********//****** ********** **************************************** ** // ** Usage, $ 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/** get a total Column */$ allColumn = $ currentSheet-> getHighestColumn ();/** get the total number of rows */$ allRow = $ currentSheet-> getHighestRow (); $ size = strlen ($ allColumn); // if the value of Z is exceeded, $ 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 = 'a'; $ currentColumn <= $ allColumn; $ currentColumn ++) {$ address = $ currentColumn. $ currentRow; $ SQL. = '"'. $ currentSheet-> getCell ($ address)-> getCalculatedValue (). '"'. "," ;}} else {for ($ currentColumn = 'a'; $ currentColumn <= $ allColumn; $ currentColumn ++) {if ($ merge) {// if it is to read the merged value, it is judged that if the value of this row is NULL, the previous tempName is assigned to $ temp; if ($ currentColumn = $ mergeC Ol) {// Here, we first specify to read the merged value from column B's name. Different adjustments will be made later. $ 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 = $ sq L. '"'. $ temp. '"'. "," ;}}$ esql = rtrim ($ SQL ,","). ')'; // echo ($ esql); // return; $ CI-> db-> simple_query ($ esql) ;}}/*** $ filePath: path for file reading * $ insertSql: spelling 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 2003 Excel */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 from reading the work thin information/*** read the name of the Excel2007 work thin */function read_EXCEL2007_Sheets ($ filePath) {return $ this-> _ get_sheetnames, $ filePath);}/*** READ the 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 the work thin information/*** 2012-1-14 ---------------------------- * // read the Reader Stream function _ get_Reader ($ name) {$ reader = null; switch ($ name) {case $ this-> E2003: $ reader = new PHPExcel_Reader_Excel5 (); break; case $ thi S-> E2007: $ reader = new PHPExcel_Reader_Excel2007 (); break; case $ this-> ECSV: $ reader = new PHPExcel_Reader_CSV (); break;} return $ reader ;} // Obtain 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, ignore. $ insertSql = $ insertSql. '"'. $ reader-> getSheetTitle (). '"'. ","; // The first field is invariably set to return array ("EXCEL" => $ PHPExcel, "SQL" => $ insertSql );} // Obtain the function _ get_sheetnames ($ name, $ filePath) {$ reader = $ this-> _ get_Reader ($ name); $ this-> _ init_Excel ($ reader, $ filePath); return $ reader-> getAllSheets ();} // load the file function _ init_Excel ($ objReader, $ filePath, $ sheet = '') {$ objReader-> setReadDataOnly (true); if (! Empty ($ sheet) {$ objReader-> setSheetIndex ($ sheet); // read the Sheet number.} Return $ objReader-> load ("$ filePath ");} // ------------------------------- 2012-1-14}/********************************* read end data ************************************** **************/

3. php code

------------------------ Import operation ----------------------/*** $ SQL = "INSERT ". mymsg: WY_MMB. "(dizhi, xingming) VALUES ("; * // function upByFile ($ SQL, $ url, $ curRow = 2, $ RIQI = true, $ merge = FALSE, $ mergeCol = 'B') {$ CI = & get_instance (); $ config ['allowed _ types '] = '*'; // charge all files $ config ['upload _ path'] = IMPORT; // only in the file path $ CI-> load-> library ('upload ', $ config); if ($ CI-> upload-> do_upload ()) {// The 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 the column x chart if (empty ($ sheet) {$ sheet = 0 ;} $ CI-> read_write-> read_Facotry ($ full_name, $ SQL, $ sheet, $ curRow, $ RIQI, $ merge, $ mergeCol ); // execute the insert command} $ this-> alert_msg (mymsg: IMPORT_SUCCESS, site_url ($ url ));}----- ------------------------- Export operation ---------------------------------- // export the specified table field public function show_export () {// ----- database field $ field = implode (",", $ this-> input-> post ("listCheckBox_show"); // database field // Display Name $ titleArray = $ this-> input-> post ("listCheckBox_field "); // the name of the displayed field (the Comment annotation name of the field, which must be filtered because some empty arrays are passed in) $ title = array (); foreach ($ titleArray as $ key => $ value) {if (! Empty ($ value) {$ title [] = $ value ;}// --- database table name $ table = $ this-> input-> post ("tableName "); // -- database table name (Comment) $ show_name = $ this-> input-> post ("tableComment "); // -- Export type $ type = $ this-> input-> post ("type "); // -- where year $ 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 data $ datas = $ this-> mcom_model-> queryByField ($ field, $ table );} // --- start to export $ this-> read_write-> write_Factory ($ title, $ datas, $ show_name, $ type );}

4.