Based on PHP export to Excel or CSV details (with UTF8, GBK code conversion) _php Tips

Source: Internet
Author: User
Tags php script sprintf
PHP Import to Excel garbled is because UTF8 encoding in XP system does not support all UTF8 code transcoding once on the perfect solution
Utf-8 Coding Case
PHP code
Copy Code code as follows:

<?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
Copy Code code as follows:

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

GBK Coding Case
PHP code
Copy Code code as follows:

<?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
Copy Code code as follows:

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

Download to Excel when you visit the website
To make a cell difference.
You can use the table Form as a Web page.
====================== Other methods =============================
1, the production of simple Excel
Copy Code code as follows:

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
Copy Code code as follows:

<?php
$action =$_get[' action '];
if ($action = = ' Make ') {
$fp = fopen ("Demo_csv.csv", "a"); Open the CSV file and create it if it does not exist
$title = Array ("First_Name", "last_name", "Contact_email", "telephone"); 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 strings '
$data _1 = Implode (",", $data _1); Use ' split into strings '
$data _2 = Implode (",", $data _2); Use ' split into strings '
$data _str = $title. " /r/n ". $data _1." /r/n ". $data _2." /r/n "; Add line Feed
Fwrite ($fp, $data _str); Write Data
Fclose ($FP); Close file handle
echo "Build Success";
}
echo "<br>";
echo "<a href= '? action=make ' > Generate CSV file </a>";
?>

You can also do a closed function:
closed function One:
Copy Code code as follows:

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 ');
*/

closed function Two:
Copy Code code as follows:

?
/**
* Simple class to properly output CSV data to clients. PHP 5 has a built
* In-do "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 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;
}
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 ', ' 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. Use Excel Class
Copy Code code as follows:

<?php
Require_once ' spreadsheet/writer.php ';
$workbook = new Spreadsheet_excel_writer ();
/* Generate CSV
$filename = Date (' Ymdhis '). CSV ';
$workbook->send ($filename); Send Excel file name for download
*/
Build 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->writestring ($row, $col, $data [$row] [$col]); Writing 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]); Writing data in Sheet-2
}
}
*/
$workbook->close (); Download complete
?>

Class Two
-----function Description
Reading Excel files
function Read_excel_file ($ExcelFile, $Result)
$ExcelFile Excel file name
Results returned by $Result
function return value normal return 0, otherwise return error message
Array of returned values
The value of the $result [sheet] [line] [column] is the value of the corresponding Excel cell

Building Excel Files
function Create_excel_file ($ExcelFile, $Data)
$ExcelFile Excel file name
$Data Excel table Data
Please write the function at the beginning of the PHP script
Example 1:
Copy Code code as follows:

?
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:
Copy Code code as follows:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.