Introduction to PHP reading Excel file (. csv) example

PHP has its own analysis. csv function: fgetcsv

Array fgetcsv (int $handle [, int $length [, String $delimiter [, String $enclosure]]]
Handle a valid file pointer generated by fopen (), Popen (), or fsockopen ().
Length (optional) must be greater than the longest row in the CVS file. This parameter is optional in PHP 5. If the parameter is omitted (set to 0 in a later version of PHP 5.0.4), there is no limit to the length, but the execution efficiency may be affected.
Delimiter (optional) Sets the field delimiter (only one character is allowed), and the default value is a comma.
Enclosure (optional) Sets the field wrap character (only one character is allowed), and the default value is double quotes. This parameter is added in the PHP 4.3.0. Similar to Fgets (), only the fgetcsv () resolves the read rows and finds the CSV-formatted fields and returns an array containing those fields.
Fgetcsv () returns FALSE when an error occurs, including when the end of the file is encountered.
Note: The empty row in the CSV file is returned as an array containing a single null field and is not treated as an error.

Cases

The code is as follows	Copy Code
<?php $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

Baidu statistics and webmaster tools used in the process will involve a lot of CSV files, such as we download Baidu Webmaster Tool 404 Statistics, can directly use the following PHP script read csv file and then update submit.

PHP reads the Excel file (. csv) Reference code:

The code is as follows

Copy Code

<?php function Getcsvdata ($filename) { $row = 1;//First line start if (($handle = fopen ($filename, "R"))!== false) { while (($DATASRC = Fgetcsv ($handle))!== false) { $num = count ($DATASRC); For ($c =0 $c < $num; $c + +)/column columns { if ($row = = 1)//First row as 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.111cn.net. csv '); foreach ($aData as $k => $v) { echo "http://". $v [' a ']. " <br> "; } ?>

PHP Custom Class

Advantages: cross-platform. Some classes support write operations. Support. xls binaries
The commonly used classes are phpexcelreader and Phpexcel. The latter support reading and writing, but need to php5.2 the above version.

Phpexcelreader is specifically used to read files. Returns an array that contains all the contents of the table.
This class uses the method to refer to the website download back to the example.php in the archive.

Example 3.php data Import Export Excel

Upload CVS and import into the database, test success (some code is not specification, such as php_self there to rewrite into

The code is as follows

Copy Code

$_server["Php_self"]) PHP code <?php $fname = $_files[' MyFile ' [' name ']; $do = Copy ($_files[' MyFile '] [' tmp_name '], $fname); if ($do) { echo "Successfully imported data "; } else { echo ""; } ?> <form enctype= "Multipart/form-data" action= "<?php echo" "". $PHP _self. ""; ?> "method=" POST "> Import CVS data <input name= "MyFile" type= "file" > <input value= "submitted" type= "Submit" > </form> ? error_reporting (0); Import a file in CSV format $connect =mysql_connect ("localhost", "a0530093319", "123456") or Die ("could 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); ?>

Using PHP to export the database to Excel, the test is completely successful

PHP code

The code is as follows

Copy Code

<?php $DB _server = www.111cn.net;//This 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, Datasheet: $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 <mysql_num_fields ($result); $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); ?>