Php-based export to Excel or CSV (with utf8 and gbk encoding conversion)

Php imports garbled characters into excel because utf8 encoding does not support all utf8 transcoding In the XP system.
UTF-8 encoding case
Php code Copy codeThe Code is as follows: <? Php
Header ("Content-Type: application/vnd. ms-excel; charsets = 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 codeCopy codeThe Code is as follows: <?
$ Filename = "php import to excel-utf-8 encoding ";
$ Filename = iconv ("UTF-8", "gb2312", $ filename );
Echo $ filename;
?>

Gbk encoding case
Php codeCopy codeThe Code is as follows: <? Php
Header ("Content-Type: application/vnd. ms-excel; charsets = 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 codeCopy codeThe Code is as follows: 0. <?
0. $ filename = "php import to excel-utf-8 encoding ";
0. echo $ filename;
0.?>

Download to excel when visiting the website
Cell differences
You can create a webpage using a table.
=================================== Other methods ==================
1. Make a simple Excel File
Copy codeThe Code is 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 codeThe Code is as follows: <? Php
$ Action = $ _ GET ['action'];
If ($ action = 'make '){
$ Fp = fopen ("demo_csv.csv", "a"); // open the csv file. If it does not exist, create
$ Title = 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 'to separate the string
$ Data_1 = implode (",", $ data_1); // use 'to separate data into strings.
$ Data_2 = implode (",", $ data_2); // use 'to split the string
$ Data_str = $ title. "/r/n". $ data_1. "/r/n". $ data_2. "/r/n"; // Add a line break
Fwrite ($ fp, $ data_str); // write data
Fclose ($ fp); // close the file handle
Echo "generated successfully ";
}
Echo "<br> ";
Echo "<a href = '? Action = make '> Generate a csv file </a> ";
?>

You can also create a closed function:
Closed function 1:
Copy codeThe Code is 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

$ 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 2:
Copy codeThe Code is as follows: <?
/**
* 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 every 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 _ write', '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 ("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
Copy codeThe Code is as follows: <? Php
Require_once 'spreadsheet/Writer. php ';
$ Workbook = new Spreadsheet_Excel_Writer ();
/* Generate CSV
$ Filename = date('ymdhis'hangzhou.'.csv ';
$ Workbook-> send ($ filename); // send the Excel file name for download
*/
// Generate an Excel file
$ Filename = date('ymdhis'hangzhou.'.xls ';
$ Workbook-> send ($ filename); // send the 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', '2017 *', 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-1
}
}
/*
$ Worksheet = & $ workbook-> addWorksheet ("Sheet-2 ");
$ Data [] = array ('id', 'username', 'company', 'email ', 'mob', 'daytime', 'intent ');
$ Data [] = array (1, 'old beam ',' ** studio ', 'jb51. net', '2017 *', 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 (); // download completed
?>

Category 2
----- Function Description
Read Excel files
Function Read_Excel_File ($ ExcelFile, $ Result)
$ ExcelFile Excel file name
Result returned by $ Result
If the return value of the function is normal, 0 is returned. Otherwise, an error message is returned.
Returned value Array
$ Result [sheet name] [row] [column] is the value of the corresponding Excel Cell.

Create an Excel file
Function Create_Excel_File ($ ExcelFile, $ Data)
$ ExcelFile Excel file name
$ Data Excel table Data
Write the function at the beginning of the PHP script
Example 1:Copy codeThe Code is 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 codeThe Code is as follows: <?
Require "excel_class.php ";
Read_Excel_File ("Book1.xls", $ return );
Create_Excel_File ("ddd.xls", $ return [Sheet1]);
?>