Methods for exporting to Excel or CSV based on PHP

This article is for PHP export to Excel or CSV (with UTF8, GBK encoding conversion) for a detailed analysis of the introduction, the need for a friend reference

PHP Import into Excel garbled because UTF8 encoding in the XP system does not support all UTF8 encoding transcoding a perfect solution
Utf-8 Coding Case
PHP code

<?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 code

<?    $filename = "PHP Import to Excel-utf-8 encoding";    $filename =iconv ("Utf-8", "gb2312", $filename);    echo $filename;    ? >

GBK Coding Case
PHP code

<?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 code

0.<?    0. $filename = "PHP Import to Excel-utf-8 encoding";    0.echo $filename;    0.?>

Download to Excel when you visit the website
To make the cell difference.
You can use a table table to make a Web page.
====================== Other methods =============================
1. Make Simple 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. Make a simple CSV

<?php$action =$_get[' action '];if ($action = = ' make ') {$fp = fopen ("Demo_csv.csv", "a");//Open the CSV file and create $title if it does not exist = Array ("First_Name", "last_name", "Contact_email", "telephone"); The first row of data $data _1 = Array ("42343", "423432", "4234", "4234");  $data _2 = Array ("4234", "last_name", "Contact_email", "Telephone");  $title = Implode (",", $title); Use ' split into string $data _1 = Implode (",", $data _1); Use ' split into string $data _2 = Implode (",", $data _2); Use ' split into string $data _str = $title. ' /r/n ". $data _1." /r/n ". $data _2." /r/n "; Added newline character fwrite ($fp, $data _str); Write Data fclose ($FP); Close file handle echo "Build succeeded";} echo "<br>"; echo "<a href= '? action=make ' > Generate CSV file </a>";? >

You can also do a closed function:
Closed function One:

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 (' Name ', ' address '); Array_push ($csv _data, Array ($row [' name '], $row [' address ']); Exporttocsv ($csv _data, ' new_file.csv ') );*/

closed function two:

<?/** * Simple class to properly output CSV data to clients. PHP 5 have a built * in method to does the same for writing to files (Fputcsv ()) and 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 being 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;        The Private Function wrap_with_quotes ($data) {$data = Preg_replace ('/"(. +)"/', ' "" $ "" "", $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 ', ' W            Rap_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", "one", "three"), Array (4,5,6)), $data [] = Array ("One", "one", "three"), $data [] = Array ( 4,5,6); $csv = new Csv_writer ($data); $csv->headers (' test '); $csv->output (); * *

3. Using the Excel Class

<?phprequire_once ' spreadsheet/writer.php '; $workbook = new Spreadsheet_excel_writer ();/* Generate csv$filename = date (' Ymdhis '). CSV '; $workbook->send ($filename); Send Excel file name for download *///Generate Excel$filename = date (' Ymdhis '). XLS '; $workbook->send ($filename); Send Excel file name for download $workbook->setversion (8); $workbook->setbiff8inputencoding (' UTF-8 '); $worksheet =& $ Workbook->addworksheet ("Sheet-1"); $data []= Array (' ID ', ' username ', ' company ', ' email ', ' mob ', ' daytime ', ' intent ') ; $data [] = array (1, ' Old beam ', ' * * studio ', ' 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-&gt ; WriteString ($row, $col, $data [$row] [$col]); Write data in sheet-1}}/* $worksheet =& $workbook->addworksheet ("Sheet-2"), $data []= array (' ID ', ' username ', ' Company ', ' email ', ' mob ', ' daytime ', ' intent '), $data [] = array (1, ' Old beam ', ' * * studio ', ' 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]);//write data in sheet-2}}*/$workbook->close (); Complete the Download?>

Class Two
-----function Description
Reading Excel files
function Read_excel_file ($ExcelFile, $Result)
$ExcelFile Excel file name
Results returned by $Result
function return value returns 0 normally, otherwise an error message is returned
Returns an array of values
$result [sheet name] [row] [column] value is the value of the corresponding Excel cell

Create an Excel file
function Create_excel_file ($ExcelFile, $Data)
$ExcelFile Excel file name
$Data Excel Tabular Data
Please write the function at the beginning of the PHP script
Example 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>";}? >

Example 2:

<?require "excel_class.php"; Read_excel_file ("Book1.xls", $return); Create_excel_file ("Ddd.xls", $return [Sheet1]);? >

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!