PHP reading excelfile (.csv) instance introduction _ PHP Tutorial

Phpread excelfile (.csv) instance introduction. Csv files are plain text files, but they can be opened as excel files. below I will summarize several common php instances for reading excel files. The analy.csv function csv file with php self-contained is a plain text file. However, opening an excel file can be used as an excel file. below I will summarize several common php instances for reading excel.

PHP self-contained analysis. csv function: fgetcsv

Array fgetcsv (int $ handle [, int $ length [, string $ delimiter [, string $ enclosure])
Handle is a valid file pointer generated by fopen (), popen (), or fsockopen.
Length (optional) must be greater than the maximum row in the CVS file. This parameter is optional in PHP 5. If this parameter is ignored (set to 0 in PHP 5.0.4 and later versions), the length is not limited, but the execution efficiency may be affected.
Delimiter (optional) sets the field delimiter (only one character is allowed). The default value is comma.
Enclosure (optional) sets the field surround character (only one character is allowed). The default value is double quotation marks. This parameter is added in PHP 4.3.0. Similar to fgets (), only the rows read by fgetcsv () are parsed, fields in CSV format are found, and an array containing these fields is returned.
If an error occurs in fgetcsv (), FALSE is returned, including when the file ends.
Note: empty rows in the CSV file will be returned as an array containing a single null field and will not be treated as an error.

Example

The code is as follows:
$ Row = 1; $ Handle = fopen ("test.csv", "r "); While ($ data = fgetcsv ($ handle, 1000 ,",")){ $ Num = count ($ data ); Echo" $ Num fields in line $ row: n "; $ Row ++; For ($ c = 0; $ c <$ num; $ c ++ ){ Echo $ data [$ c]. "n "; } } Fclose ($ handle ); ?>

Example 2

Many csv files are involved in the use of Baidu Statistics and Webmaster Tools. for example, if we download the 404 statistics of Baidu webmaster tool, we can directly use the following php script to read the csv file and then update and submit it.

PHP reading excelfile (.csv) reference code:

The code is as follows:

Function getCSVdata ($ filename) { $ Row = 1; // start with the first line If ($ handle = fopen ($ filename, "r "))! = False) { While ($ dataSrc = fgetcsv ($ handle ))! = False) { $ Num = count ($ dataSrc ); For ($ c = 0; $ c <$ num; $ c ++) // column { If ($ row = 1) // The first row is used as the field { $ DataName [] = $ dataSrc [$ c]; // field name } Else { Foreach ($ dataName as $ k => $ v) { If ($ k = $ c) // corresponding field { $ Data [$ v] = $ dataSrc [$ c]; } } } } If (! Empty ($ data )) { $ DataRtn [] = $ data; Unset ($ data ); } $ Row ++; } Fclose ($ handle ); Return $ dataRtn; } } $ AData = getCSVdata ('All _ www. bKjia. c0m. csv '); Foreach ($ aData as $ k =>$ v ){ Echo "http: //". $ v ['A']." "; } ?>

PHP custom class

Advantage: cross-platform. Some classes support write operations. Supports. xls binary files
Common classes include phpExcelReader and PHPExcel. The latter supports read/write, but php5.2 or above is required.

PhpExcelReader is used to read files. Returns an array containing all the contents of the table.
The usage of this class can refer to example. php in the compressed file downloaded from the website.

Example 3. excel for php Data import and export

Upload cvs and import it to the database. the test is successful (some codes are not standard, such as PHP_SELF, which must be rewritten

The code is as follows:

$ _ SERVER ["PHP_SELF"]) PHP code $ Fname = $ _ FILES ['myfile'] ['name']; $ Do = copy ($ _ FILES ['myfile'] ['tmp _ name'], $ fname ); If ($ do) { Echo "data imported successfully "; } Else { Echo ""; } ?> Error_reporting (0 ); // Import a CSV file $ Connect = mysql_connect ("localhost", "a0530093319", "123456") or die ("cocould not connect to database "); Mysql_select_db ("a0530093319", $ connect) or die (mysql_error ()); $ Fname = $ _ FILES ['myfile'] ['name']; $ Handle = fopen ("$ fname", "r "); While ($ data = fgetcsv ($ handle, 10000 ,",")) { $ Q = "insert into test (code, name, date) values ('$ data [0]', '$ data [1]', '$ data [2]') "; Mysql_query ($ q) or die (mysql_error ()); } Fclose ($ handle ); ?>

Export the database to excel using php. the test is successful.

PHP code

The code is as follows:

$ DB_Server = www. bKjia. c0m; // Here is your data connection $ DB_Username = "a0530093319 "; $ DB_Password = "123456 "; $ DB_DBName = "a0530093319 "; $ DB_TBLName = "member "; $ Savename = date ("YmjHis "); $ Connect = @ mysql_connect ($ DB_Server, $ DB_Username, $ DB_Password) Or die ("Couldn't connect ."); Mysql_query ("Set Names 'gb2312 '"); $ File_type = "vnd. ms-excel "; $ File_ending = "xls "; Header ("Content-Type: application/$ file_type "); Header ("Content-Disposition: attachment; filename =". $ savename. ". $ file_ending "); Header ("Pragma: no-cache "); Header ("Expires: 0 "); $ Now_date = date ("Y-m-j H: I: s "); $ Title = "database name: $ DB_DBName, Data Table: $ DB_TBLName, backup date: $ now_date "; $ SQL = "Select * from $ DB_TBLName "; $ ALT_Db = @ mysql_select_db ($ DB_DBName, $ Connect) Or die ("Couldn't select database "); $ Result = @ mysql_query ($ SQL, $ Connect) Or die (mysql_error ()); Echo ("$ title "); $ Sep = ""; For ($ I = 0; $ I <mysql_num_fields ($ result); $ I ++ ){ Echo mysql_field_name ($ result, $ I ).""; } Print (""); $ I = 0; While ($ row = mysql_fetch_row ($ result )){ $ Schema_insert = ""; For ($ j = 0; $ j If (! Isset ($ row [$ j]) $ Schema_insert. = "NULL". $ sep; Elseif ($ row [$ j]! = "") $ Schema_insert. = "$ row [$ j]". $ sep; Else $ Schema_insert. = "". $ sep; } $ Schema_insert = str_replace ($ sep. "$", "", $ schema_insert ); $ Schema_insert. = ""; Print (trim ($ schema_insert )); Print ""; $ I ++; } Return (true ); ?>

Bytes. PHP self-contained analysis. csv function...