PHP import and export Excel code, php import and export excel_PHP tutorial

PHP imports and exports Excel code, php imports and exports excel. PHP import and export Excel code, php import and export excel 1. to import and export data, you must use a component that can read Excel. There are also good online components. here I will share my use: Download extraction code: PHP import and export Excel code, php import and export excel

1. import

To import data, you must use a component that can read Excel. There are also good components on the internet. here, I will share my use: Download extraction code: vxyn. (Note that there is a reference relationship between the two files)

<? Php // input the name of the Excel file to be imported. function import_to_DB ($ filename) {require_once 'reader. php '; $ data = new Spreadsheet_Excel_Reader (); // Create an Excel reading object $ data-> setOutputEncoding ('utf-8 '); // set the character encoding $ data-> read ("data/Excel/{$$filename}.xls") after reading the Excel content; $ db = mysql_connect ('localhost', 'username ', 'password') or die ("cocould not connect to database. "); // connect to the database mysql_query (" set names 'uft8' "); // output Chinese mysql_select_db ('database name'); // Select Database error_reporting (E_ALL ^ E_NOTICE); for ($ I = 1; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++) {echo $ data-> sheets [0] ['cells '] [$ I] [number of columns]; // you can insert values of corresponding columns in each row to the database, for example: /* $ SQL = "insert" table name "values (corresponding item ...) "; mysql_query ($ SQL); error judgment can be added */}?>

In short, you can read the value of the corresponding column $ data-> sheet [0] [row] [column] in each row in the table, and the insert operation is easy.

II. export

You can use the MIME protocol to easily export table files without relying on any components. Set the header in the following format to export the Excel file and download it in the browser.

Header ('content-type: text/html; charset = utf-8 '); header ("Content-type: application/vnd. ms-excel; charset = UTF-8 "); // application/vnd. ms-excel specifies the output Excel format header ("Content-Disposition: filename .xls"); // The name of the output table

The complete code is as follows:

<? Php header ('content-type: text/html; charset = utf-8 '); header ("Content-type: application/vnd. ms-excel; charset = UTF-8 "); header (" Content-Disposition: filename1_table file name .xls "); $ conn = mysql_connect (" localhost "," root "," database password ") or die ("cannot connect to the database"); mysql_select_db ("database name", $ conn); mysql_query ("set names 'utf-8 '"); $ SQL = "select * from table name where condition"; $ result = mysql_query ($ SQL); echo "Header 1 \ t header 2 \ t header 3 \ n "; while ($ row = mysql_fetch _ Array ($ result) {echo $ row [0]. "\ t ". $ row [1]. "\ t ". $ row [2]. "\ n" ;}?>

Here \ t is actually a change, \ n is a line feed. Set the php file link in a webpage. when you click it, the browser automatically saves the uploaded stream as an Excel file.

PHPExcel is a PHP class library used to operate Office Excel documents. it is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write workbooks of different formats.

Export class

PHPExcel-type excel export, while some simplified PHPExcel processing, basically can meet the data export excel function

The code is as follows:

<? Php // load the PHPExcel class require '. /phpexcel/PHPExcel. php '; // Create an excel Object instance $ objPHPExcel = new PHPExcel (); // set the basic attributes of the document $ objProps = $ objPHPExcel-> getProperties (); $ objProps-> setCreator ("Lao Mao"); $ objProps-> setLastModifiedBy ("Lao Mao"); $ objProps-> setTitle ("Office XLS Test Document "); $ objProps-> setSubject ("Office XLS Test Document, Demo"); $ objProps-> setDescription ("Test document, generated by PHPExcel. "); $ objProps-> SetKeywords ("office excel PHPExcel"); $ objProps-> setCategory ("Test"); // sets the current sheet index for subsequent content operations. // Display the call only when multiple sheets are used. // By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0 $ objPHPExcel-> setActiveSheetIndex (0 ); // Set the name of the current active sheet $ objActSheet = $ objPHPExcel-> getActiveSheet (); $ objActSheet-> setTitle ('test Sheet '); // Set the cell content example-> getActiveSheet ()-> SetCellValue ('A1', 'A1'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('A2 ', 'A2 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('A3', 'A3 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('A4 ', 'A4 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('a5', 'a5 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('b1 ', 'b1 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('B2', 'B2'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('b3 ', 'b3'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('B4 ', 'B4'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('b5 ', 'b5 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('C1', 'C1'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('C2 ', 'C2 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('C3', 'C3'); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('c26 ', 'C4 '); $ objPHPExcel-> getActiveSheet ()-> SetCellValue ('c5', 'c5 '); // output document $ objWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel ); // Set the header information and output it to the browser // header ('content-Type: application/vnd. ms-excel '); // header ("Content-Disposition: attachment; filename=demo.xls"); // header ('cache-Control: max-age = 0 '); // $ objWriter-> save ('php: // output'); // save to a location $ objWriter-> save (dirname (_ FILE __). '/demo.xls ');

How to import excel:

Code:

<? Php // load the PHPExcel class include (dirname (_ FILE __). '/phpexcel/PHPExcel. php '); $ Obj = new PHPExcel_Reader_Excel5 (); $ Obj-> setReadDataOnly (true ); // read the demo.xls FILE $ phpExcel = $ Obj-> load (dirname (_ FILE __). '/output.xls'); // get the current activity sheet $ objWorksheet = $ phpExcel-> getActiveSheet (); // get the number of rows $ highestRow = $ objWorksheet-> getHighestRow (); // get the number of columns $ highestColumn = $ objWorksheet-> getHighestColumn (); $ highestColumnIndex = PHPExcel_Cell: columnIndexFromString ($ highestColumn); // export $ data = array (); for ($ row = 1; $ row <= $ highestRow; ++ $ row) {for ($ col = 0; $ col <$ highestColumnIndex; ++ $ col) {$ val = $ objWorksheet-> getCellByColumnAndRow ($ col, $ row)-> getValue (); $ data [$ row] [$ col] = trim ($ val) ;}} echo'
';print_r($data);echo '
';

However, this method is flawed. the old version of excel has a data limit of up to 65536 rows. in this case, we cannot use excel to export big data, but I guess there aren't a few of them who have such data. of course, you can consider using csv for operations.

The above is all the content of this article. I hope you will like it.

Tip 1. you need to use a component that can read Excel to import data. There are also good components on the internet. here I will share my usage: Download extraction code :...