Summary of csv files generated by PHP downloads and problems

CSV files were first used in a simple database. because of their simple format and strong openness, they were first marked by their own gallery by the scanner. A CSV file is a plain text file. each line represents many attributes of an image. The following small series will introduce you to the csv file generated by PHP downloads and the problem summary. a project worker displays the information of the order, and can store the relevant information in a. csv file and download it to the browser. Although csvis a simple excel format, you only need to generate the .csv file by following the specified format, but you will also encounter many problems during use. below we will share with you the PHP csv file download and summary of the problems

First, let's take a look at the example to generate and download a csv file.

// Array of csv files to be generated $ csvArr = array (); $ csvArr [] = array ('User ID 1', 'working date 1', 'check-in time 1 ', 'Sign-off time 1'); $ csvArr [] = array ('User number 2', 'working date 2', 'sign-in time 2', 'sign-in 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

<? Php $ data = 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 code} 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.

<? Php $ 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 downloadheader (" Content-Type: application/force-download "); header (" Content-Type: application/octet-stream "); header (" Content-Type: application/download "); // disposition/encoding on response B Odyheader ("Content-Disposition: attachment; filename = {$ filename}"); header ("Content-Transfer-Encoding: binary "); $ items_data = array ('0' => array ('title' => 'test test test1 '), '1' => array ('title' => 'test test test2'), '2' => array ('title' => 'test test test3 ')) print (chr (0xEF ). chr (0xBB ). chr (0xBF); // sets UTF-8 + bom to handle garbled characters in Chinese characters. echo array2csv ($ items_data); function array2csv (array & $ array) {if (count ($ array) = 0) {ret Urn 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 '.

The code is as follows:

Is_numeric ($ val )? $ Val. "\ t": $ val;

The above code is the csv file generated by PHP download and a summary of the problems. I hope you will like it.