当前位置：Gxlcms > PHP教程 > 基于php导出到Excel或CSV的方法

基于php导出到Excel或CSV的方法

时间：2021-07-01 10:21:17 帮助过：31人阅读

本篇文章是对php导出到Excel或CSV(附utf8、gbk 编码转换)进行了详细的分析介绍，需要的朋友参考下

php导入到excel乱码是因为utf8编码在xp系统不支持所有utf8编码转码一下就完美解决了
utf-8编码案例
Php代码

  1. <?php 
  2. header("Content-Type: application/vnd.ms-excel; charset=UTF-8"); 
  3. header("Pragma: public"); 
  4. header("Expires: 0"); 
  5. header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
  6. header("Content-Type: application/force-download"); 
  7. header("Content-Type: application/octet-stream"); 
  8. header("Content-Type: application/download"); 
  9. header("Content-Disposition: attachment;filename=11.xls "); 
  10. header("Content-Transfer-Encoding: binary "); 
  11. ?>

Php代码

  1. <?    
  2. $filename="php导入到excel-utf-8编码";    
  3. $filename=iconv("utf-8", "gb2312", $filename);    
  4. echo $filename;    
  5. ?>

gbk编码案例
Php代码

  1. <?php 
  2. header("Content-Type: application/vnd.ms-excel; charset=UTF-8"); 
  3. header("Pragma: public"); 
  4. header("Expires: 0"); 
  5. header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
  6. header("Content-Type: application/force-download"); 
  7. header("Content-Type: application/octet-stream"); 
  8. header("Content-Type: application/download"); 
  9. header("Content-Disposition: attachment;filename=11.xls "); 
  10. header("Content-Transfer-Encoding: binary "); 
  11. ?>

Php代码

  1. 0.<?    
  2. 0.$filename="php导入到excel-utf-8编码";    
  3. 0.echo $filename;    
  4. 0.?>

访问网站的时候就下载到excel里面
要弄单元格区别的话
用table表格做网页的就可以了
====================== 其他方法 =============================
1、制作简单 Excel 

  1. 0.<?php   
  2. 0.header("Content-type:application/vnd.ms-excel");   
  3. 0.header("Content-Disposition:filename=php2excel.xls");   
  4. 0.  
  5. 0.echo "A1/t B1/t C1/n";   
  6. 0.echo "A2/t B2/t C2/n";   
  7. 0.echo "A3/t B3/t C3/n";   
  8. 0.echo "A4/t B4/t C4/n";   
  9. 0.?>

2、制作简单 CSV 

  1. <?php
  2. $action =$_GET['action'];
  3. if ($action=='make'){
  4.  $fp = fopen("demo_csv.csv","a"); //打开csv文件，如果不存在则创建
  5.  $title = array("First_Name","Last_Name","Contact_Email","Telephone"); //第一行数据
  6.  $data_1 = array("42343","423432","4234","4234"); 
  7.  $data_2 = array("4234","Last_Name","Contact_Email","Telephone"); 
  8.  $title = implode(",",$title); //用 ' 分割成字符串
  9.  $data_1 = implode(",",$data_1); // 用 ' 分割成字符串
  10.  $data_2 = implode(",",$data_2); // 用 ' 分割成字符串
  11.  $data_str =$title."/r/n".$data_1."/r/n".$data_2."/r/n"; //加入换行符
  12.  fwrite($fp,$data_str); // 写入数据
  13.  fclose($fp); //关闭文件句柄
  14.  echo "生成成功";
  15. }
  16. echo "<br>";
  17. echo "<a href='?action=make'>生成csv文件</a>";
  18. ?>

也可以做一个封闭函数：
封闭函数一： 

  1. function exportToCsv($csv_data, $filename = 'export.csv') {
  2.     $csv_terminated = "/n";
  3.     $csv_separator = ",";
  4.     $csv_enclosed = '"';
  5.     $csv_escaped = "//";
  6.     // Gets the data from the database
  7.     $schema_insert = '';
  8.     $out = '';
  9.     // Format the data
  10.     foreach ($csv_data as $row)
  11.     {
  12.         $schema_insert = '';
  13.         $fields_cnt = count($row);
  14.         //printr($row);
  15.         $tmp_str = '';
  16.         foreach($row as $v)
  17.         {
  18.             $tmp_str .= $csv_enclosed.str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $v).$csv_enclosed.$csv_separator;
  19.         } // end for
  20.         $tmp_str = substr($tmp_str, 0, -1);
  21.         $schema_insert .= $tmp_str;
  22.         $out .= $schema_insert;
  23.         $out .= $csv_terminated;
  24.     } // end while
  25.     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  26.     header("Content-Length: " . strlen($out));
  27.     header("Content-type: text/x-csv");
  28.     header("Content-Disposition:filename=$filename");
  29.     echo $out;
  30. }
  31. /*
  32. $csv_data = array(array('Name', 'Address'));
  33. array_push($csv_data, array($row['name'],$row['address']));
  34. ...
  35. exportToCsv($csv_data,'new_file.csv');
  36. */

封闭函数二： 

  1. <?
  2. /**
  3.  * Simple class to properly output CSV data to clients. PHP 5 has a built
  4.  * in method to do the same for writing to files (fputcsv()), but many times
  5.  * going right to the client is beneficial.
  6.  *
  7.  * @author Jon Gales
  8.  */
  9. class CSV_Writer {
  10.     public $data = array();
  11.     public $deliminator;
  12.     /**
  13.      * Loads data and optionally a deliminator. Data is assumed to be an array
  14.      * of associative arrays.
  15.      *
  16.      * @param array $data
  17.      * @param string $deliminator
  18.      */
  19.     function __construct($data, $deliminator = ",")
  20.     {
  21.         if (!is_array($data))
  22.         {
  23.             throw new Exception('CSV_Writer only accepts data as arrays');
  24.         }
  25.         $this->data = $data;
  26.         $this->deliminator = $deliminator;
  27.     }
  28.     private function wrap_with_quotes($data)
  29.     {
  30.         $data = preg_replace('/"(.+)"/', '""$1""', $data);
  31.         return sprintf('"%s"', $data);
  32.     }
  33.     /**
  34.      * Echos the escaped CSV file with chosen delimeter
  35.      *
  36.      * @return void
  37.      */
  38.     public function output()
  39.     {
  40.         foreach ($this->data as $row)
  41.         {
  42.             $quoted_data = array_map(array('CSV_Writer', 'wrap_with_quotes'), $row);
  43.             echo sprintf("%s/n", implode($this->deliminator, $quoted_data));
  44.         }
  45.     }
  46.     /**
  47.      * Sets proper Content-Type header and attachment for the CSV outpu
  48.      *
  49.      * @param string $name
  50.      * @return void
  51.      */
  52.     public function headers($name)
  53.     {
  54.         header('Content-Type: application/csv');
  55.         header("Content-disposition: attachment; filename={$name}.csv");
  56.     }
  57. }
  58. /*
  59. //$data = array(array("one","two","three"), array(4,5,6));
  60. $data[] = array("one","two","three");
  61. $data[] = array(4,5,6);
  62. $csv = new CSV_Writer($data);
  63. $csv->headers('test');
  64. $csv->output();
  65. */

3. 使用excel类

  1. <?php
  2. require_once 'Spreadsheet/Writer.php';
  3. $workbook = new Spreadsheet_Excel_Writer();
  4. /* 生成 CSV
  5. $filename = date('YmdHis').'.csv';
  6. $workbook->send($filename); // 发送 Excel 文件名供下载
  7. */
  8. // 生成 Excel
  9. $filename = date('YmdHis').'.xls';
  10. $workbook->send($filename); // 发送 Excel 文件名供下载
  11. $workbook->setVersion(8);
  12. $workbook->setBIFF8InputEncoding('UTF-8');
  13. $worksheet =& $workbook->addWorksheet("Sheet-1");
  14. $data[]= array('id','username','company','email','mob','daytime','intent');
  15. $data[] = array(1,'老梁','**工作室','jb51.net','1363137966*',time(),'y');
  16. $total_row = count($data);
  17. $total_col = count($data[0]);
  18. for ($row = 0; $row < $total_row; $row ++) {
  19.    for ($col = 0; $col < $total_col; $col ++) {
  20.   $worksheet->writeString($row, $col, $data[$row][$col]); // 在 sheet-1 中写入数据
  21.    }
  22. }
  23. /*
  24. $worksheet =& $workbook->addWorksheet("Sheet-2");
  25. $data[]= array('id','username','company','email','mob','daytime','intent');
  26. $data[] = array(1,'老梁','**工作室','jb51.net','1363137966*',time(),'y');
  27. $total_row = count($data);
  28. $total_col = count($data[0]);
  29. for ($row = 0; $row < $total_row; $row ++) {
  30.    for ($col = 0; $col < $total_col; $col ++) {
  31.   $worksheet->writeString($row, $col, $data[$row][$col]); // 在 sheet-2 中写入数据
  32.    }
  33. }
  34. */
  35. $workbook->close(); // 完成下载
  36. ?>

类二
-----函数说明
读取Excel文件
function Read_Excel_File($ExcelFile,$Result)
$ExcelFile Excel文件名
$Result 返回的结果
函数返回值 正常返回0，否则返回错误信息
返回的值数组
$result[sheet名][行][列] 的值为相应Excel Cell的值

建立Excel文件
function Create_Excel_File($ExcelFile,$Data)
$ExcelFile Excel文件名
$Data Excel表格数据
请把函数写在PHP脚本的开头
例1： 

  1. <?
  2. require "excel_class.php";
  3. Read_Excel_File("Book1.xls",$return);
  4. for ($i=0;$i<count($return[Sheet1]);$i++)
  5. {
  6.     for ($j=0;$j<count($return[Sheet1][$i]);$j++)
  7.     {
  8.         echo $return[Sheet1][$i][$j]."|";
  9.     }
  10.     echo "<br>";
  11. }
  12. ?>

例2：

  1. <?
  2. require "excel_class.php";
  3. Read_Excel_File("Book1.xls",$return);
  4. Create_Excel_File("ddd.xls",$return[Sheet1]);
  5. ?>

以上就是本文的全部内容，希望对大家的学习有所帮助，更多相关内容请关注PHP中文网！

相关推荐：

ThinkPHP利用PHPExcel实现Excel数据的导入导出

以上就是基于php导出到Excel或CSV的方法的详细内容，更多请关注Gxl网其它相关文章！

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！