PHP reading excel file (.csv) example introduction

The csv file is a plain text file, but it can be used as an excel file by opening it with an excel file. Below I will summarize some commonly used PHP examples for reading excel.

PHP has its own analysis.csv function: fgetcsv

array fgetcsv ( int $handle [, int $length [, string $delimiter [, string $enclosure]]] )
handle A valid file pointer produced by fopen(), popen(), or fsockopen().
length (optional) must be greater than the longest line in the CVS file. This parameter is optional in PHP 5. If this parameter is omitted (set to 0 in PHP 5.0.4 and later versions), there is no limit to the length, but execution efficiency may be affected.
delimiter (optional) sets the field delimiter (only one character allowed), default value is comma.
enclosure (optional) sets the field wrapping character (only one character allowed), the default value is double quotes. This parameter was added in PHP 4.3.0. Similar to fgets(), except fgetcsv() parses the read line and finds the fields in CSV format and returns an array containing those fields.
fgetcsv() returns FALSE on error, including when the end of file is encountered.
Note: Empty lines 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

Copy code

代码如下	复制代码
$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); ?>

$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, when we download the 404 statistics of Baidu webmaster tools, we can directly use the following php script to read the csv file and then update and submit it.

PHP reads excel file (.csv) reference code:

The code is as follows	Copy code

代码如下	复制代码
function getCSVdata($filename) { $row = 1;//第一行开始 if(($handle = fopen($filename, "r")) !== false) { while(($dataSrc = fgetcsv($handle)) !== false) { $num = count($dataSrc); for ($c=0; $c < $num; $c++)//列 column { if($row === 1)//第一行作为字段 { $dataName[] = $dataSrc[$c];//字段名称 } else { foreach ($dataName as $k=>$v) { if($k == $c)//对应的字段 { $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

Advantages: Cross-platform. Some classes support write operations. Supports .xls binaries
Commonly used classes include phpExcelReader and PHPExcel. The latter supports reading and writing, but requires php5.2 or above.

phpExcelReader is specially used to read files. Returns an array containing all contents of the table.
The method used by this class can be found in example.php in the compressed file downloaded from the website.

Example 3.php data import and export to excel

Upload cvs and import it into the database, the test is successful (some codes are not standardized, such as PHP_SELF, which needs to be rewritten as

$do = copy($_FILES['MyFile']['tmp_name'],$fname);

The code is as follows

代码如下

复制代码

$_SERVER["PHP_SELF"] ）
PHP代码
$fname = $_FILES['MyFile']['name'];
$do = copy($_FILES['MyFile']['tmp_name'],$fname);
if ($do)
{
echo"导入数据成功
";
} else {
echo "";
}
?>

error_reporting(0);
//导入CSV格式的文件
$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);
?>

Copy code

$_SERVER["PHP_SELF"] )

PHP code

代码如下

复制代码

$DB_Server = www.bKjia.c0m;//这里是你的数据连接
$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 = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$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);
?>

$fname = $_FILES['MyFile']['name'];

if ($do)<🎜> {<🎜> echo "Import data successfully <🎜> ";<🎜> } else {<🎜> echo "";<🎜> }<🎜> ?> error_reporting(0);<🎜> //Import files 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);<🎜> ?> Export the database to excel using php, and the test was completely successful PHP code

The code is as follows

Copy code

$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);<🎜> ?>

PHP reading excel file (.csv) example introduction_PHP tutorial