[Moka Notes] exporting and importing PHPexcel, mokaphpexcel

[Moka Notes] exporting and importing PHPexcel, mokaphpexcel

Original case from http://www.sucaihuo.com/modified

1. directory structure (the file can be understood without explanation, and the code can be viewed directly)

2. Pay attention to the database table (you need to modify the database configuration in connect. php)

Create a database table

CREATE TABLE `user` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `username` varchar(15) NOT NULL,  `email` varchar(20) DEFAULT NULL,  `grade` int(11) DEFAULT NULL,  `password` varchar(32) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

3. Code

View index. php

<Div class = "demo"> 

4. import (import. php)
<? Phpinclude_once ('connect. php '); $ tmp = $ _ FILES ['file'] ['tmp _ name']; if (empty ($ tmp )) {echo 'select the Excel file to import! '; Exit;} $ save_path = "uploads/"; $ filename = $ save_path. date ('ymdhis '). ". xls "; // save path and name of the uploaded file if (copy ($ tmp, $ filename) {require_once 'phpexcel. class. php '; require_once 'phpexcel/Reader/excel5.php'; $ PHPReader = new PHPExcel_Reader_Excel5 (); // PHPExcel_Reader_Excel2007 PHPExcel_Reader_Excel5 // load the file $ PHPExcel = $ PHPReader-> load ($ filename); // obtain the first worksheet in the table. If you want to obtain the second worksheet, change 0 to 1, and so on $ currentSheet = $ PH PExcel-> getSheet (0); // get the total number of columns $ allColumn = $ currentSheet-> getHighestColumn (); // get the total number of rows $ allRow = $ currentSheet-> getHighestRow (); // obtain the data in the table cyclically. $ currentRow indicates the current row and the row from which the data is read. The index value starts from 0 for ($ currentRow = 1; $ currentRow <= $ allRow; $ currentRow ++) {// from which column, A indicates the first column for ($ currentColumn = 'a'; $ currentColumn <= $ allColumn; $ currentColumn ++) {// data coordinate $ address = $ currentColumn. $ currentRow; // read data, saved to the array $ arr $ da Ta [$ currentRow] [$ currentColumn] = $ currentSheet-> getCell ($ address)-> getValue () ;}$ add_time = date ('Y-m-d H: i: s', time (); foreach ($ data as $ k = >$ v) {if ($ k> 1) {$ SQL = "insert into user (username, email, password) values ('". $ v ['B']. "','". $ v ['C']. "','". $ v ['D']. "')"; mysql_query ($ SQL) ;}$ SQL = "SELECT * FROM user"; $ result = mysql_query ($ SQL ); $ tip = 'user imported successfully '. ', now '. mysql _ Num_rows ($ result). 'data record! '; Echo "<script> alert ('". $ tip. "'); history. go (-1); </script>"; exit ;}
5. export (export. php)
<? Php/*** PHPEXCEL excel file generation * @ author: material fire http://blog.csdn.net/winter13292/article/details/8123755 * @ desc support any row and column data to generate an excel file, no cell style and alignment added yet */shortde_once ('connect. php '); // $ list = array (// array ('id' => '1', 'e-mail' => "87423050@qq.com ", 'Password' => 'sucaihuo. com '), // array ('id' => '2', 'email' => '2017 @ qq.com ', 'Password' => 'hjl666666 '), // array ('id' => '3', 'email '=> 'zhangliao @ 163.com', 'passw Ord '=> 'hangzhou'), //); $ query = mysql_query ("select * from user limit 50"); $ I = 0; $ list = array (); while ($ row = mysql_fetch_array ($ query) {$ list [$ I] ['id'] = $ row ['id']; $ list [$ I] ['username'] = $ row ['username']; $ list [$ I] ['email '] = $ row ['email']; $ list [$ I] ['Password'] = $ row ['Password']; $ I ++;} $ title = array ('id', 'name ', 'mailbox ', 'Password'); // sets the header exportExcel ($ list, 'list table', $ title) for the excel file to be exported; function exportE Xcel ($ data, $ savefile = null, $ title = null, $ sheetname = 'sheet1') {require_once 'phpexcel. class. php '; // if no file name is specified, the current timestamp if (is_null ($ savefile) {$ savefile = time () ;}// if the excel header is specified, then, append the form to the front of the body by going to if (is_array ($ title) {array_unshift ($ data, $ title) ;}$ objPHPExcel = new PHPExcel (); // Excel content $ head_num = count ($ data); foreach ($ data as $ k = >$ v) {$ obj = $ objPHPExcel-> setActiveSheetIndex (0); $ r Ow = $ k + 1; // row $ nn = 0; foreach ($ v as $ vv) {$ col = chr (65 + $ nn ); // column $ obj-> setCellValue ($ col. $ row, $ vv); // column, row, value $ nn ++ ;}/// set the column header title for ($ I = 0; $ I <$ head_num-1; $ I ++) {$ alpha = chr (65 + $ I); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ($ alpha) -> setAutoSize (true); // adaptive unit width $ objPHPExcel-> getActiveSheet ()-> getStyle ($ alpha. '1')-> getFont ()-> setName ("Candara"); // set the font $ objPHPExcel-> GetActiveSheet ()-> getStyle ($ alpha. '1')-> getFont ()-> setSize (12); // set the size $ objPHPExcel-> getActiveSheet ()-> getStyle ($ alpha. '1')-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_BLACK); // set the color $ objPHPExcel-> getActiveSheet () -> getStyle ($ alpha. '1')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); // horizontally centered $ objPHPExcel-> getActiveSheet ()-> getStyle ($ alpha. '1')-> g EtAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); // vertically center $ objPHPExcel-> getActiveSheet ()-> getStyle ($ alpha. '1')-> getFont ()-> setBold (true); // bold} $ objPHPExcel-> getActiveSheet ()-> setTitle ($ sheetname ); // question $ objPHPExcel-> setActiveSheetIndex (0); // set the current sheet header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "'. $ savefile. '.xls "' ); // File name header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 '); // Excel5 $ objWriter-> save ('php: // output');}?>
6. other files
Source code: http://share.weiyun.com/590da4cea5e53e679fe52e013ab38f6e (password: oJzV)
If you have any questions, contact QQ1727728211.