【www.bbyears.com--班主任工作总结】
例子,生成csv文件并下载
//要生成csv文件的数组
$csvArr=array();
$csvArr[]=array("用户编号1","上班日期1","签到时间1","签退时间1");
$csvArr[]=array("用户编号2","上班日期2","签到时间2","签退时间2")
download_send_headers("data_export_" . date("Y-m-d") . ".csv");
$head=array("用户编号","上班日期","签到时间","签退时间");
echo array2csv($csvArr,$head);
unset($csvArr);
die();
function array2csv(array &$array,$head)
{
if (count($array) == 0) {
return null;
}
ob_start();
$df = fopen("php://output", "w");
if(!$head){
$head=array_keys(reset($array));
}
fputcsv($df,$head);
foreach ($array as $row) {
fputcsv($df, $row);
}
fclose($df);
return ob_get_clean();
}
function download_send_headers($filename) {
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
}
php array生成csv文件
$data = array(
array( "row_1_col_1", "row_1_col_2", "row_1_col_3" ),
array( "row_2_col_1", "row_2_col_2", "row_2_col_3" ),
array( "row_3_col_1", "row_3_col_2", "row_3_col_3" ),
);
$filename = "example";
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$filename}.csv");
header("Pragma: no-cache");
header("Expires: 0");
outputCSV($data);
function outputCSV($data) {
$outputBuffer = fopen("php://output", "w");
foreach($data as $val) {
foreach ($val as $key => $val2) {
$val[$key] = iconv("utf-8", "gbk", $val2);
// CSV的Excel支持GBK编码,一定要转换,否则乱码
}
fputcsv($outputBuffer, $val);
}
fclose($outputBuffer);
}
?>
解决 fgetcsv函数在php5.2.8 中的bug
环境linux
问题解析出来的数据不完整,有为空的字段
网上查了下说是在php5.2.8 中存在bug
解决办法是使用自定义函数
function __fgetcsv(& $handle, $length = null, $d = ",", $e = """) {
$d = preg_quote($d);
$e = preg_quote($e);
$_line = "";
$eof=false;
while ($eof != true) {
$_line .= (empty ($length) ? fgets($handle) : fgets($handle, $length));
$itemcnt = preg_match_all("/" . $e . "/", $_line, $dummy);
if ($itemcnt % 2 == 0)
$eof = true;
}
$_csv_line = preg_replace("/(?: |[ ])?$/", $d, trim($_line));
$_csv_pattern = "/(" . $e . "[^" . $e . "]*(?:" . $e . $e . "[^" . $e . "]*)*" . $e . "|[^" . $d . "]*)" . $d . "/";
preg_match_all($_csv_pattern, $_csv_line, $_csv_matches);
$_csv_data = $_csv_matches[1];
for ($_csv_i = 0; $_csv_i < count($_csv_data); $_csv_i++) {
$_csv_data[$_csv_i] = preg_replace("/^" . $e . "(.*)" . $e . "$/s", "$1" , $_csv_data[$_csv_i]);
$_csv_data[$_csv_i] = str_replace($e . $e, $e, $_csv_data[$_csv_i]);
}
return empty ($_line) ? false : $_csv_data;
}
excel无法正确读取长度超过32K的CSV域问题
php 导出csv文件用excel打开后,产品表述字段分两行显示。
查看了下这个字段发现这个字段超过32K的字符,excel会把字符串打断成两行,如果小于32K,显示正常。
这是EXCEL的限制,目前还没有找到解决办法。
excel一个单元格最多是32767个字符。
解决PHP生成UTF-8编码的CSV文件用Excel打开乱码的问题
PHP生成UTF-8编码的CSV文件用Excel打开中文显示乱码,是由于输出的CSV文件中没有BOM。
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
$items_data=array(
"0"=>array("title"=>"test test test1"),
"1"=>array("title"=>"test test test2"),
"2"=>array("title"=>"test test test3")
)
print(chr(0xEF).chr(0xBB).chr(0xBF));
//设置utf-8 + bom ,处理汉字显示的乱码
echo array2csv($items_data);
function array2csv(array &$array)
{
if (count($array) == 0) {
return null;
}
ob_start();
$df = fopen("php://output", "w");
fputcsv($df, array_keys(reset($array)));
foreach ($array as $row) {
fputcsv($df, $row);
}
fclose($df);
return ob_get_clean();
}
?>
导出csv文件数字会自动变科学计数法的解决方法
用程序导出的csv文件,当字段中有比较长的数字字段存在时,在用excel软甲查看csv文件时就会变成科学技术法的表现形式。
其实这个问题跟用什么语言导出csv文件没有关系。Excel显示数字时,如果数字大于12位,它会自动转化为科学计数法;如果数字大于15位,它不仅用于科学技术费表示,还会只保留高15位,其他位都变0。
解决这个问题
只要把数字字段后面加上显示上看不见的字符即可,字符串结尾加上制表符"\t".
php 程序可以这样判断,注意一定是"\t",不是"\t".
is_numeric($val)?$val."\t":$val;