Import and Export excel files using php

: This article describes how to import and export excel files in php. For more information about PHP tutorials, see. To operate an excel table, you must use the PHPExcel plug-in. I generally use the CI framework to introduce it.

First, introduce related PHPExcel files:

$this->load->library('PHPExcel/IOFactory');$this->load->model('search_model');

Next, we will introduce how to export an excel file. First, we need to create a worksheet and initialize it:

// Create a worksheet $ objPHPExcel = new PHPExcel (); // operate the first worksheet $ objPHPExcel-> setActiveSheetIndex (0); // Set the workbook name $ objPHPExcel-> getActiveSheet () -> setTitle ("set"); // set the default font and size $ objPHPExcel-> getDefaultStyle ()-> getFont ()-> setName (" "); $ objPHPExcel-> getDefaultStyle ()-> getFont ()-> setSize (10 );

You can also leave the header unspecified. The first parameter of the setCellValueByColumnAndRow ($ col, $ row, $ name) method is the operated column (starting with 0 ), the second parameter is the operation line (starting from 1), and the third parameter is the value you want to set:

$ ObjPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (0, 1, ""); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (1, 1, "IMSI "); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (2, 1, "IMEI"); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (3, 1, "device number"); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (4, 1, "segment"); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (5, 1, ""); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (6, 1, "carrier"); $ objPHPExcel-> getActiveSheet ()-> setCellValueByColumnAndRow (7, 1, "Time ");

After the header is set, the value is assigned row by row. The operation is the same as above:

$row = 2;$i = 0;foreach($data as $item){$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $i+1);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $item['QQ']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $item['MSN']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $item['DeviceID']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $item['RTX']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $item['Area']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $item['Type']);$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $item['TimeStamp']);$row++;$i++;}

Finally, output the file to the page for download:

$ ObjWriter = IOFactory: createWriter ($ objPHPExcel, 'excel5'); ob_end_clean (); header ("Pragma: public"); header ("Expires: 0 "); header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0"); header ("Content-Type: application/force-download "); header ("Content-Type: application/vnd. ms-excel; charset = UTF-8 "); header (" Content-Type: application/octet-stream "); header (" Content-Type: application/download "); header ("Content-Disposition: attachment?filename=result.xls"); // define the file name header ("Content-Transfer-Encoding: binary"); $ objWriter-> save ("php: // output ");

The import method is similar. first, upload the file to the background, obtain the path, and then open excel:

$ Orig_name = explode ('. ', $ upload_data ['orig _ name']) [0]; // original file name $ extend = strrchr ($ upload_data ['File _ name'],'. '); // get the file type $ readerType = ($ extend = ". xlsx ")? "Excel2007": "Excel5"; $ path = $ config ['upload _ path']. $ upload_data ['File _ name']; // file path $ objPHPExcel = new IOFactory (); $ objReader = $ objPHPExcel: createReader ($ readerType) -> load ($ path); $ sheet = $ objReader-> getSheet (0 );

Then read the data cyclically and import the data to the database:

$ HighestRow = $ sheet-> getHighestRow (); // get the total number of rows, but many blank rows without data are also read, therefore, this method $ highestColumn = $ sheet-> getHighestColumn (); // get the total number of columns $ colspan = range ('A', $ highestColumn ); $ rowArray = $ sheet-> getRowDimensions (); // get the total number of rows $ rowCount = count ($ rowArray ); // read the excel file cyclically (because the header is added to the first line, the data is always read from the second line) for ($ j = 2; $ j <= $ rowCount; $ j ++) {$ array = array (); foreach ($ colspan as $ value) {$ array [] = $ objReader-> getActiveSheet ()-> getCell ($ value. $ j)-> getValue ();} // insert $ SQL = "insert into im_blacklist ('name', 'mac', 'imsi', 'imei ', 'groupid') values ('". $ array [1]. "','". $ array [2]. "',". $ array [3]. ",". $ array [4]. ",". $ id. ")"; $ this-> db-> query ($ SQL );}

However, this method is associated with the row where the excel cursor is located. it is best to open excel and move the cursor to the bottom right corner of the table, and then close excel to read all the data correctly.

The above section describes how to import and export excel files in php, including some content. if you are interested in PHP tutorials, please help.