Php generates and downloads csv files and summarizes the problems-PHP source code

Csvis a simple excel format. We only need to generate the. CSV file after using the specified format. Although it is simple, it will also encounter many problems. The following small series will summarize it for you. Csvis a simple excel format. We only need to generate the. CSV file after using the specified format. Although it is simple, it will also encounter many problems. The following small series will summarize it for you.

Script ec (2); script

Example: Generate and download a csv file

// Array of csv files to be generated
$ CsvArr = array ();
$ CsvArr [] = array ('user No. 1', 'start date 1', 'sign-in time 1', 'sign-out time 1 ');
$ CsvArr [] = array ('user No. 2', 'working date 2', 'sign-in time 2', 'sign-off time 2 ')

Download_send_headers ("data_export _". date ("Y-m-d"). ". csv ");
$ Head = array ('user number', 'working date ', 'sign-in time', 'sign-out Time ');

Echo array2csv ($ csvArr, $ head );
Unset ($ csvArr );
Die ();

Function array2csv (array & $ array, $ head)
{
If (count ($ array) = 0 ){
Return null;
}
Ob_start ();
$ Df = fopen ("php: // output", 'w ');
If (! $ Head ){
$ Head = array_keys (reset ($ array ));
}
Fputcsv ($ df, $ head );
Foreach ($ array as $ row ){
Fputcsv ($ df, $ row );
}
Fclose ($ df );
Return ob_get_clean ();
}

Function download_send_headers ($ filename ){
// Disable caching
$ Now = gmdate ("D, d m y h: I: s ");
Header ("Expires: Tue, 03 Jul 2001 06:00:00 GMT ");
Header ("Cache-Control: max-age = 0, no-cache, must-revalidate, proxy-revalidate ");
Header ("Last-Modified: {$ now} GMT ");

// Force download
Header ("Content-Type: application/force-download ");
Header ("Content-Type: application/octet-stream ");
Header ("Content-Type: application/download ");

// Disposition/encoding on response body
Header ("Content-Disposition: attachment; filename = {$ filename }");
Header ("Content-Transfer-Encoding: binary ");
}

Generate a csv file using php array


$ Data = array (
Array ('row _ partition col_1 ', 'row _ partition col_2', 'row _ partition col_3 '),
Array ('row _ 2_col_1 ', 'row _ 2_col_2', 'row _ 2_col_3 '),
Array ('row _ 3_col_1 ', 'row _ 3_col_2', 'row _ 3_col_3 '),
);
$ Filename = "example ";

Header ("Content-type: text/csv ");
Header ("Content-Disposition: attachment; filename=#filename=.csv ");
Header ("Pragma: no-cache ");
Header ("Expires: 0 ");

OutputCSV ($ data );

Function outputCSV ($ data ){
$ OutputBuffer = fopen ("php: // output", 'w ');
Foreach ($ data as $ val ){
Foreach ($ val as $ key => $ val2 ){
$ Val [$ key] = iconv ('utf-8', 'gbk', $ val2 );
// CSV Excel supports GBK encoding and must be converted; otherwise, garbled characters
}
Fputcsv ($ outputBuffer, $ val );
}
Fclose ($ outputBuffer );
}

?>

Solve the bug of the fgetcsv function in php5.2.8.

Environment linux

The data parsed by the problem is incomplete and there are blank fields.
I found a bug in php5.2.8 on the Internet.
The solution is to use a UDF.

Function _ fgetcsv (& $ handle, $ length = null, $ d = ',', $ e = '"'){
$ D = preg_quote ($ d );
$ E = preg_quote ($ e );
$ _ Line = "";
$ Eof = false;
While ($ eof! = True ){
$ _ Line. = (empty ($ length )? Fgets ($ handle): fgets ($ handle, $ length ));
$ Itemcnt = preg_match_all ('/'. $ e. '/', $ _ line, $ dummy );
If ($ itemcnt % 2 = 0)
$ Eof = true;
}
$ _ Csv_line = preg_replace ('/(? : | [])? $/', $ D, trim ($ _ line ));
$ _ Csv_pattern = '/('. $ e. '[^'. $ e. '] * (? :'. $ E. $ e. '[^ '. $ e. '] *) *'. $ e. '| [^ '. $ d. '] *)'. $ d. '/';
Preg_match_all ($ _ csv_pattern, $ _ csv_line, $ _ csv_matches );
$ _ Csv_data = $ _ csv_matches [1];
For ($ _ csv_ I = 0; $ _ csv_ I <count ($ _ csv_data); $ _ csv_ I ++ ){
$ _ Csv_data [$ _ csv_ I] = preg_replace ('/^ '. $ e. '(. *)'. $ e. '$/s',' $ 1', $ _ csv_data [$ _ csv_ I]);
$ _ Csv_data [$ _ csv_ I] = str_replace ($ e. $ e, $ e, $ _ csv_data [$ _ csv_ I]);
}
Return empty ($ _ line )? False: $ _ csv_data;
}

Excel cannot correctly read CSV fields with a length of more than 32 KB

After the exported csv file in php is opened in excel, the product expression fields are displayed in two rows.
After checking this field, we found that this field contains more than 32 K characters. excel breaks the string into two lines. If it is smaller than 32 K, it will be displayed normally.
This is the limitation of EXCEL, and no solution has been found yet.
An excel cell contains a maximum of 32767 characters.


Solve PHP to generate UTF-8 encoded CSV file with Excel open garbled Problem


PHP generate UTF-8 encoded CSV file with Excel open Chinese display garbled, is because the output CSV file does not have BOM.

$ Now = gmdate ("D, d m y h: I: s ");
Header ("Expires: Tue, 03 Jul 2001 06:00:00 GMT ");
Header ("Cache-Control: max-age = 0, no-cache, must-revalidate, proxy-revalidate ");
Header ("Last-Modified: {$ now} GMT ");

// Force download
Header ("Content-Type: application/force-download ");
Header ("Content-Type: application/octet-stream ");
Header ("Content-Type: application/download ");

// Disposition/encoding on response body
Header ("Content-Disposition: attachment; filename = {$ filename }");
Header ("Content-Transfer-Encoding: binary ");
$ Items_data = array (
'0' => array ('title' => 'test test1 '),
'1' => array ('title' => 'test test2 '),
'2' => array ('title' => 'test test3 ')
)
Print (chr (0xEF). chr (0xBB). chr (0xBF ));
// Set UTF-8 + bom to handle garbled characters
Echo array2csv ($ items_data );

Function array2csv (array & $ array)
{
If (count ($ array) = 0 ){
Return null;
}
Ob_start ();
$ Df = fopen ("php: // output", 'w ');
Fputcsv ($ df, array_keys (reset ($ array )));
Foreach ($ array as $ row ){
Fputcsv ($ df, $ row );
}
Fclose ($ df );
Return ob_get_clean ();
}
?>

The solution for automatically changing the number of csv files to scientific notation is as follows:

In a csv file exported by a program, when a long numeric field exists in the field, it becomes a form of scientific and technical method when you use excel soft armor to view the csv file.
In fact, this problem has nothing to do with the language used to export csv files. When a number is displayed in Excel, if the number is greater than 12 digits, it is automatically converted to the scientific notation; if the number is greater than 15 digits, it is not only used for the Science and Technology fee, but also retains only 15 digits, the other bits are changed to 0.
Solve this problem
You only need to add invisible characters to the end of the numeric field, and add "\ t" to the end of the string ".
The php program can judge this way. Note that it must be "\ t", not '\ t '.
Is_numeric ($ val )? $ Val. "\ t": $ val;