基于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]）;
？>