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

This article describes how to export php code to Excel or CSV (with UTF-8 and gbk encoding conversion, if you need a friend, refer to importing php to excel with garbled characters because utf8 encoding does not support all utf8 encoding and transcoding in the XP system.
UTF-8 encoding case
Php code

The code is as follows:

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 code

The code is as follows:

$ Filename = "php import to excel-utf-8 encoding ";
$ Filename = iconv ("UTF-8", "gb2312", $ filename );
Echo $ filename;
?>

Gbk encoding case
Php code

The code is as follows:

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 code

The 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

The code is as follows:

0. 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

The code is as follows:

$ 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"
";
Echo "generate csv file ";
?>

You can also create a closed function:
Closed function 1:

The 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:

The 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

The code is as follows:

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:

The code is as follows:

Require "excel_class.php ";
Read_Excel_File ("Book1.xls", $ return );
For ($ I = 0; $ I {
For ($ j = 0; $ j {
Echo $ return [Sheet1] [$ I] [$ j]. "| ";
}
Echo"
";
}
?>

Example 2:

The code is as follows:

Require "excel_class.php ";
Read_Excel_File ("Book1.xls", $ return );
Create_Excel_File ("ddd.xls", $ return [Sheet1]);
?>