Correct way to export data to excel?

Question

I am currently trying to export some data from my application in either Excel or CSV. What is the best way to accomplish this? Should I export from the backend, or export once I have the data client side using a library within Angular 2? My Web API 2 controller currently produces a list and then sends it as JSON to the front end.

That all works, I am just struggling with exporting the list.

Here is a sample of what I am doing

 [HttpGet]
 [Route("/api/preview/{item}")]
 public IActionResult Preview(string item)
 {      
     if (item!= null)
     {
         var preview = _context.dbPreview.FromSql("Exec sampleStoredProcedure {0}, 1", item).ToList();

         return Ok(preview);
     }
 }

That is how I am generating my data that is sent to Angular 2.

I can provide any Angular 2 code if it is necessary but it is just a normal service. Was not sure if there was some library that worked well with Angular 2 to do an export. I've seen some things for javascript but alaSQL but it does not seem like it would work with Angular 2.

Any ideas?

Answer 1

I've looked at the source code from PrimeNG DataTable and I think you can use the exportCSV code for exporting a csv of your data.

The "trick" is to generate a string starting with data:text/csv;charset=utf-8 and make this downloadable by the user.

Something like the following code should work for you (maybe you need to modify it a bit so it fits to your data).

Most of the code is copied from PrimeNG except the download method. That method is copied from a SO answer.

import { Component } from '@angular/core';

@Component({
    selector: 'app-root',
    templateUrl: './app.component.html',
    styleUrls: ['./app.component.css']
})
export class AppComponent {
    title = 'app works!';

    csvSeparator = ';';

    value = [
        { name: 'A3', year: 2013, brand: 'Audi' },
        { name: 'Z3', year: 2015, brand: 'BMW' }
    ];

    columns = [
        { field: 'name', header: 'Name' },
        { field: 'year', header: 'Production data' },
        { field: 'brand', header: 'Brand' },
    ];

    constructor() {

        console.log(this.value);
        this.exportCSV('cars.csv'); // just for show casing --> later triggered by a click on a button
    }

    download(text, filename) {
        let element = document.createElement('a');
        element.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(text));
        element.setAttribute('download', filename);

        element.style.display = 'none';
        document.body.appendChild(element);

        element.click();

        document.body.removeChild(element);
    }

    exportCSV(filename) {
        let data = this.value, csv = '';
        // csv = "data:text/csv;charset=utf-8,";

        //headers
        for (let i = 0; i < this.columns.length; i++) {
            if (this.columns[i].field) {
                csv += this.columns[i].field;

                if (i < (this.columns.length - 1)) {
                    csv += this.csvSeparator;
                }
            }
        }

        //body        
        this.value.forEach((record, j) => {
            csv += '\n';
            for (let i = 0; i < this.columns.length; i++) {
                if (this.columns[i].field) {
                    console.log(record[this.columns[i].field]);
                    // resolveFieldData seems to check if field is nested e.g. data.something --> probably not needed
                    csv += record[this.columns[i].field]; //this.resolveFieldData(record, this.columns[i].field);

                    if (i < (this.columns.length - 1)) {
                        csv += this.csvSeparator;
                    }
                }
            }
        });
        // console.log(csv);
        // window.open(encodeURI(csv)); // doesn't display a filename!
        this.download(csv, filename);
    }

    // resolveFieldData(data: any, field: string): any {
    //     if(data && field) {
    //         if(field.indexOf('.') == -1) {
    //             return data[field];
    //         }
    //         else {
    //             let fields: string[] = field.split('.');
    //             let value = data;
    //             for(var i = 0, len = fields.length; i < len; ++i) {
    //                 value = value[fields[i]];
    //             }
    //             return value;
    //         }
    //     }
    //     else {
    //         return null;
    //     }
    // }


}

Answer 2

AWolfs answer got me on the right track but I did some tweaking to get it working with Internet Explorer.

This function converts my array to my string for my csv file. (I had to create a new object that was my column headers). I then just pass the data that is generated by my service to the function and it does the parsing for me. For more complex data I believe you would need to do some additional logic but I have basic text so it all worked out for me.

exportCSV(filename, CsvData) {
    let data = CsvData, csv = '';

    console.log(data);
    //headers
    for (let i = 0; i < this.columns.length; i++) {
        if (this.columns[i].field) {
            csv += this.columns[i].field;

            if (i < (this.columns.length - 1)) {
                csv += this.csvSeparator;
            }
        }
    }

    //body        
    CsvData.forEach((record, j) => {
        csv += '\n';
        for (let i = 0; i < this.columns.length; i++) {
            if (this.columns[i].field) {
                console.log(record[this.columns[i].field]);
                csv += record[this.columns[i].field]; 
                if (i < (this.columns.length - 1)) {
                    csv += this.csvSeparator;
                }
            }
        }
    });
    this.DownloadFile(csv, filename);
}

That was pretty much the same as AWolfs answer but I had to make some modifications to the DownloadFile function to get it to work with additional browsers. This function just accepts the huge string that makes up your .CSV file and the filename.

 DownloadFile(text, filename) {
    console.log(text);
    var blob = new Blob([text], { type: 'text/csv;charset=utf-8;' });
    if (navigator.msSaveBlob) { // IE 10+
        navigator.msSaveBlob(blob, filename);
    }
    else //create a link and click it
    {
        var link = document.createElement("a");
        if (link.download !== undefined) // feature detection
        {
            // Browsers that support HTML5 download attribute
            var url = URL.createObjectURL(blob);
            link.setAttribute("href", url);
            link.setAttribute("download", filename);
            link.style.visibility = 'hidden';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    }
}

This code needs cleaned up but I wanted to update my question with an answer for anyone who was struggling with the same thing. This should at least get you started. This works in both Chrome and IE.

Thanks.