您当前的位置: 首页 > 网站编程 > PHP教程 > 基于php导出到Excel或CSV的详解(附utf8、gbk 编码转换)

基于php导出到Excel或CSV的详解(附utf8、gbk 编码转换)

作者:不详 来源:网络 发布时间: 2014-08-19 09:02 点击:
php导入到excel乱码是因为utf8编码在xp系统不支持所有utf8编码转码一下就完美解决了 utf-8编码案例 Php代码 复制代码 代码如下: ?php header(Content-Type: application/vnd.ms-excel; charset=UTF-8); header(Pragma: public); header(Expires: 0); header(Cache-Cont

基于php导出到Excel或CSV的详解(附utf8、gbk 编码转换)

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

  utf-8编码案例

  
Php代码

  复制代码 代码如下:

  <?php

  header("Content-Type: application/vnd.ms-excel; charset=UTF-8");

  header("Pragma: public");

  header("Expires: 0");

  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

  header("Content-Type: application/force-download");

  header("Content-Type: application/octet-stream");

  header("Content-Type: application/download");

  header("Content-Disposition: attachment;filename=11.xls ");

  header("Content-Transfer-Encoding: binary ");

  ?>

  Php代码

  复制代码 代码如下:

  <?

  $filename="php导入到excel-utf-8编码";

  $filename=iconv("utf-8", "gb2312", $filename);

  echo $filename;

  ?>

  gbk编码案例

  
Php代码

  复制代码 代码如下:

  <?php

  header("Content-Type: application/vnd.ms-excel; charset=UTF-8");

  header("Pragma: public");

  header("Expires: 0");

  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

  header("Content-Type: application/force-download");

  header("Content-Type: application/octet-stream");

  header("Content-Type: application/download");

  header("Content-Disposition: attachment;filename=11.xls ");

  header("Content-Transfer-Encoding: binary ");

  ?>

  Php代码

  复制代码 代码如下:

  0.<?

  0.$filename="php导入到excel-utf-8编码";

  0.echo $filename;

  0.?>

  访问网站的时候就下载到excel里面

  要弄单元格区别的话

  用table表格做网页的就可以了

  ====================== 其他方法 =============================

  1、制作简单 Excel

  


  复制代码 代码如下:

  0.<?php

  0.header("Content-type:application/vnd.ms-excel");

  0.header("Content-Disposition:filename=php2excel.xls");

  0.

  0.echo "A1/t B1/t C1/n";

  0.echo "A2/t B2/t C2/n";

  0.echo "A3/t B3/t C3/n";

  0.echo "A4/t B4/t C4/n";

  0.?>

  2、制作简单 CSV

  


  复制代码 代码如下:

  <?php

  $action =$_GET['action'];

  if ($action=='make'){

  $fp = fopen("demo_csv.csv","a"); //打开csv文件,如果不存在则创建

  $title = array("First_Name","Last_Name","Contact_Email","Telephone"); //第一行数据

  $data_1 = array("42343","423432","4234","4234");

  $data_2 = array("4234","Last_Name","Contact_Email","Telephone");

  $title = implode(",",$title); //用 ' 分割成字符串

  $data_1 = implode(",",$data_1); // 用 ' 分割成字符串

  $data_2 = implode(",",$data_2); // 用 ' 分割成字符串

  $data_str =$title."/r/n".$data_1."/r/n".$data_2."/r/n"; //加入换行符

  fwrite($fp,$data_str); // 写入数据

  fclose($fp); //关闭文件句柄

  echo "生成成功";

  }

  echo "<br>";

  echo "<a href='?action=make'>生成csv文件</a>";

  ?>

  也可以做一个封闭函数:

  封闭函数一:

  


  复制代码 代码如下:

  function exportToCsv($csv_data, $filename = 'export.csv') {

  $csv_terminated = "/n";

  $csv_separator = ",";

  $csv_enclosed = '"';

  $csv_escaped = "//";

  // Gets the data from the database

  $schema_insert = '';

  $out = '';

  // Format the data

  foreach ($csv_data as $row)

  {

  $schema_insert = '';

  $fields_cnt = count($row);

  //printr($row);

  $tmp_str = '';

  foreach($row as $v)

  {

  $tmp_str .= $csv_enclosed.str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $v).$csv_enclosed.$csv_separator;

  } // end for

  $tmp_str = substr($tmp_str, 0, -1);

  $schema_insert .= $tmp_str;

  $out .= $schema_insert;

  $out .= $csv_terminated;

  } // end while

  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

  header("Content-Length: " . strlen($out));

  header("Content-type: text/x-csv");

  header("Content-Disposition:filename=$filename");

  echo $out;

  }

  /*

  $csv_data = array(array('Name', 'Address'));

  array_push($csv_data, array($row['name'],$row['address']));

  ...

  exportToCsv($csv_data,'new_file.csv');

  */

  封闭函数二:

  


  复制代码 代码如下:

  <?

  /**

  * Simple class to properly output CSV data to clients. PHP 5 has a built

  * in method to do the same for writing to files (fputcsv()), but many times

  * going right to the client is beneficial.

  *

  * @author Jon Gales

  */

  class CSV_Writer {

  public $data = array();

  public $deliminator;

  /**

  * Loads data and optionally a deliminator. Data is assumed to be an array

  * of associative arrays.

  *

  * @param array $data

  * @param string $deliminator

  */

  function __construct($data, $deliminator = ",")

  {

  if (!is_array($data))

  {

  throw new Exception('CSV_Writer only accepts data as arrays');

  }

  $this->data = $data;

  $this->deliminator = $deliminator;

  }

  private function wrap_with_quotes($data)

  {

  $data = preg_replace('/"(.+)"/', '""$1""', $data);

  return sprintf('"%s"', $data);

  }

  /**

  * Echos the escaped CSV file with chosen delimeter

  *

  * @return void

  */

  public function output()

  {

  foreach ($this->data as $row)

  {

  $quoted_data = array_map(array('CSV_Writer', 'wrap_with_quotes'), $row);

  echo sprintf("%s/n", implode($this->deliminator, $quoted_data));

  }

  }

  /**

  * Sets proper Content-Type header and attachment for the CSV outpu

  *

  * @param string $name

  * @return void

  */

  public function headers($name)

  {

  header('Content-Type: application/csv');

  header("Content-disposition: attachment; filename={$name}.csv");

  }

  }

  /*

  //$data = array(array("one","two","three"), array(4,5,6));

  $data[] = array("one","two","three");

  $data[] = array(4,5,6);

  $csv = new CSV_Writer($data);

  $csv->headers('test');

  $csv->output();

  */

  3. 使用excel类

  


  复制代码 代码如下:

  <?php

  require_once 'Spreadsheet/Writer.php';

  $workbook = new Spreadsheet_Excel_Writer();

  /* 生成 CSV

  $filename = date('YmdHis').'.csv';

  $workbook->send($filename); // 发送 Excel 文件名供下载

  */

  // 生成 Excel

  $filename = date('YmdHis').'.xls';

  $workbook->send($filename); // 发送 Excel 文件名供下载

  $workbook->setVersion(8);

  $workbook->setBIFF8InputEncoding('UTF-8');

  $worksheet =& $workbook->addWorksheet("Sheet-1");

  $data[]= array('id','username','company','email','mob','daytime','intent');

  $data[] = array(1,'老梁','**工作室','jb51.net','1363137966*',time(),'y');

  $total_row = count($data);

  $total_col = count($data[0]);

  for ($row = 0; $row < $total_row; $row ++) {

  for ($col = 0; $col < $total_col; $col ++) {

  $worksheet->writeString($row, $col, $data[$row][$col]); // 在 sheet-1 中写入数据

  }

  }

  /*

  $worksheet =& $workbook->addWorksheet("Sheet-2");

  $data[]= array('id','username','company','email','mob','daytime','intent');

  $data[] = array(1,'老梁','**工作室','jb51.net','1363137966*',time(),'y');

  $total_row = count($data);

  $total_col = count($data[0]);

  for ($row = 0; $row < $total_row; $row ++) {

  for ($col = 0; $col < $total_col; $col ++) {

  $worksheet->writeString($row, $col, $data[$row][$col]); // 在 sheet-2 中写入数据

  }

  }

  */

  $workbook->close(); // 完成下载

  ?>

  类二

  -----函数说明

  
读取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:

  复制代码 代码如下:

  <?

  require "excel_class.php";

  Read_Excel_File("Book1.xls",$return);

  for ($i=0;$i<count($return[Sheet1]);$i++)

  {

  for ($j=0;$j<count($return[Sheet1][$i]);$j++)

  {

  echo $return[Sheet1][$i][$j]."|";

  }

  echo "<br>";

  }

  ?>

  例2:

  复制代码 代码如下:

  <?

  require "excel_class.php";

  Read_Excel_File("Book1.xls",$return);

  Create_Excel_File("ddd.xls",$return[Sheet1]);

  ?>
分享到:
本文"基于php导出到Excel或CSV的详解(附utf8、gbk 编码转换)"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4