Web-side export CSV

The front-end export files are mostly generated by the server side and then delivered to the client. But in many cases when we export CSV, we don't need back-end participation, not even backend.

　　

The students who have done Webgis often encounter this kind of scene, the user's point of interest data in the form of a CSV file to the Web application in tabular form, and can edit the property information, editing the need to download data to the local. Especially for some sensitive data, the user does not want to pass to the application server side, the whole process is done entirely on the client.

The upload process is not discussed for the moment, only the CSV generation and the download process are discussed.

　　

Generation of CSV

Question one: How are the branches broken down?

Idea: The branch uses "\ n" and uses ","

var " Col1,col2,col3\nvalue1,value2,value3 "

The actual application found that the exported CSV opened with Excel, the columns can be separated but the rows cannot be separated.

The workaround is to use encodeuricomponent encoding of the generated CSV string

str =  

Question two: The field value contains special symbols that affect the correct interpretation of the CSV file, such as: ",", "\ n"

Idea: The field containing the special symbol is wrapped in double quotation marks, such as: A, B and "B"

    var ' " ' ;     if (Value &&/[,\r\n]/g.test (value)) {            = TextField + value + TextField;          }

The actual application found less of a case, if the field value contains ' "' This symbol, after the above code processing will have problems: a" b = "a" B ". Obviously a grammatical error.

The solution is to "replace", "a" B and "a" "B"

        var ' " ' ;         if (Value &&/[", \r\n]/g.test (value)) {            value = TextField + value.replace (/(" )/g, '"') + TextField;          }

After resolving the above problem, generate the CSV string code as follows

//data: An array of arrays, each containing the field names specified in the _outfields//_outfields: Array of field namesExports.createcsvstr =function(data, _outfields) {varTextField = ' "'; varContent = ""; varLen = 0, N= 0, comma= "", Value= ""; Try{Array.foreach (_outfields,function(_field) {content= content + comma +_field; Comma= ",";      }); Content= content + "\ r \ n"; Len=data.length; N=_outfields.length;  for(vari = 0; i < Len; i++) {comma= "";  for(varm = 0; M < n; m++) {          var_field =_outfields[m]; Value=Data[i][_field]; if(!value &&typeofValue!== "Number") {Value= ""; }          if(Value &&/[", \r\n]/g.test (value)) {Value= TextField + value.replace (/(")/g, '" ") +TextField; } content= content + comma +value; Comma= ","; } content= content + "\ r \ n"; }    } Catch(Err) {console.error (err); Content= ""; }    returncontent; };

Question three: If the field contains Hebrew, French, German and other text ( ‘éà; ça; 12\nà@€; çï; 13‘ ), the exported CSV file opens in Excel, the text appears garbled

Workaround: Strictly speaking, this is not a CSV file problem, but Excel processing file encoding problem, Excel does not use UTF-8 to open the file by default, so add the BOM at the beginning of the CSV, tell the Excel file to use Utf-8 encoding method.

var BOM = "\ufeff"; var csvstr = BOM + csvstr;

The actual application found that this process works correctly when opened in Excel in Windows, but Excel on the MAC does not display correctly. There is no complete solution at the moment, but the Mac can be opened with its own numbers software without garbled problems.

　　

How to download CSV

Question one: How to solve the download problem in different browsers?

Ideas:

• IE10 below, use the ExecCommand method to save the CSV file

  var oWin = Window.top.open ("About:blank", "_blank");    OWin.document.write (encodeURIComponent (text));    OWin.document.close ();    OWin.document.execCommand (true, filename);    Owin.close ();

  In the actual application, the browser opens a new window, and the Save File dialog box appears, while the dialog box saves the type, only the HTML and text two items are optional, you need to manually add the ". csv" suffix in the file name

• IE10 and Edge Browser use Navigator.mssaveblob (BLOB), although these browsers also support the above method, but can avoid the problems encountered above.

  var BOM = "\ufeff";     var New Blob ([BOM + text], {type: ' Text/csv ' });    Navigator.mssaveblob (csvdata, filename);

  Mssaveblob is the private method of IE, only IE10 and above and Edge browser support.

• Use the A tag in Firefox, Chrome, and Safari to download csv with the added download attribute in HTML5

  varlink = html.create ("a", {href: ' data:attachment/csv;charset=utf-8, ' + BOM + encodeuricomponent (text), Target:' _blank ', download:filename}, This. Domnode); if(Has (' Safari ')) {        //# First create an event        varClick_ev = Document.createevent ("mouseevents"); //# Initialize the eventClick_ev.initevent ("click",true /*Bubble*/,true /*cancelable*/ ); //# trigger the evevnt/link.dispatchevent (Click_ev); } Else{Link.click (); }

  Safari does not support invoking the click Method except for elements outside of input, so we use custom events to simulate user clicks to download files. The actual application found that if the CSV string is too large, the above method will cause the browser to crash when downloading the CSV. The workaround is to use Url.createobjecturl (BLOB) to create a connection to the a tag.

  _getdownloadurl:function(text) {varBOM = "\ufeff"; //Add BOM to text for open in Excel correctly    if(Window. Blob && window. URL &&window. Url.createobjecturl) {varCSVData =NewBlob ([BOM + text], {type: ' Text/csv ') }); returnUrl.createobjecturl (CSVData); } Else {            return' Data:attachment/csv;charset=utf-8, ' + BOM +encodeuricomponent (text); }    },

In this way, the code for downloading the CSV file is as follows

_ISIE11:function() {        varIev = 0; varIeold = (/msie (\d+\.\d+);. Test (navigator.useragent)); varTrident =!! Navigator.userAgent.match (/trident\/7.0/); varRV = navigator.userAgent.indexOf ("rv:11.0"); if(ieold) {Iev= Number (regexp.$1); }        if(Navigator.appVersion.indexOf ("MSIE 10")!==-1) {Iev= 10; }        if(Trident && RV!==-1) {Iev= 11; }        returnIev = = 11; }, _isedge:function() {        return/edge\/12/. Test (navigator.useragent); }, _getdownloadurl:function(text) {varBOM = "\ufeff"; //Add BOM to text for open in Excel correctly        if(Window. Blob && window. URL &&window. Url.createobjecturl) {varCSVData =NewBlob ([BOM + text], {type: ' Text/csv ') }); returnUrl.createobjecturl (CSVData); } Else {          return' Data:attachment/csv;charset=utf-8, ' + BOM +encodeuricomponent (text); }}, Download:function(filename, text) {if(Have (' IE ') && has (' IE ') < 10) {          //has module unable identify ie11 and Edge          varOWin = Window.top.open ("About:blank", "_blank");          OWin.document.write (text);          OWin.document.close (); OWin.document.execCommand (' SaveAs ',true, filename);        Owin.close (); }Else if(Has ("ie") = = = 10 | | This. _ISIE11 () | | This. _isedge ()) {          varBOM = "\ufeff"; varCSVData =NewBlob ([BOM + text], {type: ' Text/csv ') });        Navigator.mssaveblob (csvdata, filename); } Else {          varlink = html.create ("a", {href: This. _getdownloadurl (text), Target:' _blank ', download:filename}, This. Domnode); if(Has (' Safari ')) {            //# First create an event            varClick_ev = Document.createevent ("mouseevents"); //# Initialize the eventClick_ev.initevent ("click",true /*Bubble*/,true /*cancelable*/ ); //# trigger the evevnt/link.dispatchevent (Click_ev); } Else{Link.click ();        } html.destroy (link); }      }

　　If you think this article is helpful to you, please do not hesitate to click on the recommendation below, your encouragement is my motivation to share!

Resources:

Https://github.com/DrPaulBrewer/html5csvhttp://stackoverflow.com/questions/4639372/export-to-csv-in-jqueryhttp ://stackoverflow.com/questions/15547198/export-html-table-to-csv http://stackoverflow.com/questions/21342492/ encoding-issues-for-utf8-csv-file-when-opening-excel-and-textedithttp://stackoverflow.com/questions/6588068/ which-encoding-opens-csv-files-correctly-with-excel-on-both-mac-and-windowshttp://stackoverflow.com/questions/ 6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automaticallyhttp://stackoverflow.com/ Questions/19492846/javascript-to-csv-export-encoding-issue https://github.com/b4stien/js-csv-encoding

Web-side export CSV