Php Excel Export and Import Excel usage

The PHPExcel class is a php excel table processing plug-in. Next I will introduce how to use the PHPExcel class to import and export excel tables, if you have any questions, please do not refer to it (PHPExcel Baidu download here is not introduced ).

Excel export usage

// Set the environment variable (Added PHPExcel)

The Code is as follows:	Copy code
Set_include_path ('.'. PATH_SEPARATOR. Yii: app ()-> basePath. '/lib/PHPExcel'. PATH_SEPARATOR. Get_include_path ()); // Note: In yii, you can also directly use Yii: import ("application. lib. PHPExcel .*"); // Introduce PHPExcel files Require_once "PHPExcel. php "; Require_once 'phpexcel/IOFactory. php '; Require_once 'phpexcel/Writer/excel5.php ';

// Put the content to be exported to the table

The Code is as follows:

Copy code

// Create $ ResultPHPExcel = new PHPExcel (); // Set parameters // Set the value $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('a1', 'quarterly '); $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('b1 ', 'name '); $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('c1 ', 'qty '); $ I = 2; Foreach ($ data as $ item ){ $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('A'. $ I, $ item ['quarter ']); $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('B'. $ I, $ item ['name']); $ ResultPHPExcel-> getActiveSheet ()-> setCellValue ('C'. $ I, $ item ['number']); $ I ++; }

Set export parameters

The Code is as follows:

Copy code

// Set the exported file name $ OutputFileName = 'total.xls '; $ XlsWriter = new PHPExcel_Writer_Excel5 ($ resultPHPExcel ); // Ob_start (); ob_flush (); Header ("Content-Type: application/force-download "); Header ("Content-Type: application/octet-stream "); Header ("Content-Type: application/download "); Header ('content-Disposition: inline; filename = "'. $ outputFileName .'"'); Header ("Content-Transfer-Encoding: binary "); Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT "); Header ("Last-Modified:". gmdate ("D, d m y h: I: s"). "GMT "); Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 "); Header ("Pragma: no-cache "); $ XlsWriter-> save ("php: // output ");

The output is incorrect.

The default value is $ xlsWriter-> save ("php: // output"). The cache may not be large enough, but the cache is incomplete. Therefore, you can use the following method:

The Code is as follows:	Copy code
$ FinalFileName = (Yii: app ()-> basePath. '/runtime/'.time().'.xls '; $ XlsWriter-> save ($ finalFileName ); Echo file_get_contents ($ finalFileName );

// The file_get_contents () function reads the entire file into a string. Like file (), the difference is that file_get_contents ()

Read a string.

Excel import usage

The Code is as follows:

Copy code

<? If ($ _ POST ['leadexcel '] = "true ") { $ Filename = $ _ FILES ['input'] ['name']; $ Tmp_name = $ _ FILES ['input'] ['tmp _ name']; $ Msg = uploadFile ($ filename, $ tmp_name ); Echo $ msg; } // Import an Excel file Function uploadFile ($ file, $ filetempname) { // Set the path for storing uploaded files $ FilePath = 'upfile /'; $ Str = ""; // The following path is modified according to your PHPExcel path. Require_once '../PHPExcel. php '; Require_once '../PHPExcel/IOFactory. php '; Require_once '../PHPExcel/Reader/excel5.php '; // Set the time zone. $ Time = date ("y-m-d-H-I-s"); // The current upload time. // Obtain the extension of the uploaded file $ Extend = strrchr ($ file ,'.'); // Name of the uploaded file $ Name = $ time. $ extend; $ Uploadfile = $ filePath. $ name; // address of the uploaded file name // The move_uploaded_file () function moves the uploaded file to a new location. If yes, true is returned. Otherwise, false is returned. $ Result = move_uploaded_file ($ filetempname, $ uploadfile); // if the file is uploaded to the current directory // Echo $ result; If ($ result) // if the file is uploaded successfully, import the file to excel. { Include "conn. php "; $ ObjReader = PHPExcel_IOFactory: createReader ('excel5'); // use excel2007 for 2007 format $ ObjPHPExcel = $ objReader-> load ($ uploadfile ); $ Sheet = $ objPHPExcel-> getSheet (0 ); $ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows. $ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of Columns /* Method 1 // Read the excel file cyclically, read one, and insert one For ($ j = 1; $ j <= $ highestRow; $ j ++) // read data from the first row { For ($ k = 'a'; $ k <= $ highestColumn; $ k ++) // read data from column { // This method is simple, but there is something wrong. Use ''to merge as an array and then split it into Field Values and insert them into the database. In excel, if the value of a cell contains imported data, it is null. // $ Str. = $ objPHPExcel-> getActiveSheet ()-> getCell ("$ k $ j")-> getValue (). ''; // read a cell } // Echo $ str; die (); // Explode: The function separates strings into arrays. $ Strs = explode ("", $ str ); $ SQL = "INSERT INTO te ('1', '2', '3', '4', '5') VALUES ( '{$ Strs [0]}', '{$ Strs [1]}', '{$ Strs [2]}', '{$ Strs [3]}', '{$ Strs [4]}') "; // Die ($ SQL ); If (! Mysql_query ($ SQL )) { Return false; Echo 'SQL statement error '; } $ Str = ""; } Unlink ($ uploadfile); // delete an uploaded excel File $ Msg = "Import successful! "; */ /* Method 2 */ $ ObjWorksheet = $ objPHPExcel-> getActiveSheet (); $ HighestRow = $ objWorksheet-> getHighestRow (); Echo 'hehestrow = '. $ highestRow; Echo "<br> "; $ HighestColumn = $ objWorksheet-> getHighestColumn (); $ HighestColumnIndex = PHPExcel_Cell: columnIndexFromString ($ highestColumn); // The total number of Columns Echo 'hehestcolumnindex = '. $ highestColumnIndex; Echo "<br> "; $ Headtitle = array (); For ($ row = 1; $ row <= $ highestRow; $ row ++) { $ Strs = array (); // Note that the index of the highestColumnIndex column starts from 0. For ($ col = 0; $ col <$ highestColumnIndex; $ col ++) { $ Strs [$ col] = $ objWorksheet-> getCellByColumnAndRow ($ col, $ row)-> getValue (); } $ SQL = "INSERT INTO te ('1', '2', '3', '4', '5') VALUES ( '{$ Strs [0]}', '{$ Strs [1]}', '{$ Strs [2]}', '{$ Strs [3]}', '{$ Strs [4]}') "; // Die ($ SQL ); If (! Mysql_query ($ SQL )) { Return false; Echo 'SQL statement error '; } } } Else { $ Msg = "Import failed! "; } Return $ msg; } ?>

HTML webpage code

The Code is as follows:	Copy code
<Form action = "upload. php" method = "post" enctype = "multipart/form-data"> <Input type = "hidden" name = "leadExcel" value = "true"> <Table align = "center" width = "90%" border = "0"> <Tr> <Td> <Input type = "file" name = "inputExcel"> <input type = "submit" value = "import data"> </Td> </Tr> </Table> </Form>