PHP and JS Import and export CSV table (top)

About CSV

When developing a backend management system, it is almost inevitable that you will encounter the need to import and export Excel tables. CSV is also a form of table, where dissemble is "comma delimiter file". Open in Excel as shown on the left, in Notepad open as shown on the right:

And look at the table with the special characters.

Similar to XLS or xlsx tables, CSV files are also used to represent two-dimensional tables. And the difference is:
1, CSV is a plain text file, any editor can open and read it; xls (x) is a dedicated binary file, to use the form software to open normally, otherwise garbled;
2, CSV volume is very small, such as the above table content, CSV only dozens of B, and xlsx has 8k, the old format XLS has 18k;
3, CSV expression Ability is limited, can only represent two-dimensional array (or one-dimensional), XLS (x) and so on a variety of file configuration and so on, you can refer to the comparison between JSON and XML.

Then look at the CSV format features:
1. In general, separate one-dimensional arrays (columns) with commas , separating two-dimensional arrays (rows) with a newline .
2, when the table content has, half-width comma, newline, Space, tab and other special characters, outside using double quotation marks .
3. When the table content has "double quotation marks, convert to two" "double quotation marks, enclosed in double quotation marks.
4, the last can have a blank line , or no blank line.

PHP Export CSV

Exporting Excel is almost standard functionality in the management background. PHP can be very convenient to set the HTTP header, control the form of output, and two-dimensional array into a CSV string also has a mature function library processing, not much technical difficulties, directly paste the source code. Note that this method exports the last row of CSV data with a \ n, which ends with a blank line.

<?PHP/** * Export to CSV format * @param string $filename file name, with suffix * @param array $arr 2D table data, two-dimensional array * @return directly generated file, with a tag jump To. This function uses exit to remove the debug information attached to the frame **/        functionExport_csv ($filename,$arr 2D){        Header("Content-type:text/csv"); Header("Content-disposition:attachment;filename=".$filename); Header(' Cache-control:must-revalidate,post-check=0,pre-check=0 '); Header(' expires:0 '); Header(' Pragma:public '); $fp=fopen(' Php://output ', ' W '); //Comments from PHP website, direct output text         foreach($arr 2D  as $lines) {fputcsv ($fp,$lines); }        fclose($fp); Exit; }

A little explanation:
1, HTTP header set to Text/csv means that this file is a CSV format file to the browser processing, call this function before the other output;
2, Parameter $filename is the download file name you want to set (such as: test.csv), Parameter $arr 2D is to generate a CSV two-dimensional array, CSV only support two-dimensional array;

PHP Import CSV

PHP Import CSV is also very simple, PHP also has built-in functions to help deal with. It is worth noting that this function automatically removes the last line break of the CSV (blank line)

In the Chinese coding problem , we usually use UTF8 encoding for websites and databases, and the most commonly used JSON is UTF8 encoding. But Excel and other software is based on the Microsoft ecosystem, in China is usually GBK (including GB2312) code, if not to convert code, will lead to subsequent use of inconvenience, JSON conversion data errors and so on. and users of the file upload can not be guaranteed to be UTF8 or GBK, so all need to be compatible, PHP has a built-in method mb_convert_encoding can easily do this.

<?PHP$filename=$_files[' Cvsfile '] [' Tmp_name ']; //The csvfile here corresponds to the input name= "CSVFile" in the front-end form $out= Csv2arr ($filename);//Var_dump ($out);//data processing, usually feedback to the front end to let the user confirm that the information is correct    functionCsv2arr ($filename){        $out= []; $handle=fopen($filename, ' R '); $n= 0;  while($data=Fgetcsv($handle)){            $num=Count($data);  for($i= 0;$i<$num;$i++){
                ANSI format text parsing is garbled and causes subsequent JSON conversions to fail
$data [$i] = mb_convert_encoding ($data [$i], ' utf-8 ', [' GBK ', ' utf-8 ']); $out[$n][$i] =$data[$i]; }            $n++; }        return $out; }

Note : PHP has built-in perfect fputcsv and fgetcsv functions, do not easily blindly to realize the CSV format parsing, there are many pits, the next JS processing will be explained.

Whether PHP is the best language in the world, PHP is the world's most complete library of functions →_→

The interaction between JS and PHP

The import process for an Excel table is this:

Select Table--Parse table-- fill data to form--user check--Submit form

There are several scenarios in which the "Parse tables--populate data to form" step:

First, upload the CSV file to the server, parse and generate the entire form page to the front end;

Second, upload the CSV file to the server, parse back the JSON, the front-end JS fill the form;

Third, the front-end JS Local parse CSV file generated JSON, and then populate the form;

The third solution to modern H5 browser can be achieved, and more complex, specifically in the next article again. The second scenario is now briefly described.

<formAction= "Test/csv2json"Method= "POST" >    <inputtype= "File"name= "CSVFile" /></form><inputtype= "button"onclick= "Csv1 ()"value= "Background conversion"/><Scriptsrc= "Jquery.js"></Script><Scriptsrc= "Jquery.form.js"></Script><Script>functionCsv1 () {$ ("Input[name=csvfile]"). Parent ("form"). Ajaxsubmit (function(res) {Console.log (res); //TODO: Here's what you need to do with your businessWriteForm (RES); });}</Script>

<? PHP function Csv2json () {    $filename$_files[' csvfile '] [' tmp_name ']    ; $out = Csv2arr ($filename);     $this->ajaxreturn ($out);}

The interaction here should be easier to understand, that is, the front button triggers the AJAX submission file form, and then back-end conversion, and returned in JSON format. The most complex is how to fill out the form based on JSON, different business has different logic, this is the test of the front-end DOM operation skill.

PHP and JS Import and export CSV table (top)