<-My Blog->

In this article, we gonna learn how to import and export excel and csv in angular. 

1. Let's install @angular/cli package.

npm i -g @angular/cli

 2. Create a new angular project.

ng new angular-excel-csv

cd angular-excel-csv

3. Install xlsx npm package.

npm i xlsx

xlsx package provides a bunch of functions for reading and writing CSV/Excel files.

Parsing functions:-

      XLSX.read(data, read_opts) attempts to parse data

      XLSX.readFile(filename, read_opts) attempts to read filename and parse.

Note:- you can pass raw option to false if you want formatted data. (example - formatted date)

XLSX.read(data, { raw: false })
XLSX.readFile(filename, { raw: false })

Writing functions:-

      XLSX.write(wb, write_opts) attempts to write the workbook

      XLSX.writeFile(wb, filename, write_opts) attempts to write workbook

Utility Functions:-

      Constructing:-

            book_new creates an empty workbook

            book_append_sheet adds a worksheet to a workbook

       Importing:

           aoa_to_sheet converts an array of arrays of JS data to a worksheet

           json_to_sheet converts an array of JS objects to a worksheet

          sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.

          sheet_add_json adds an array of JS objects to an existing worksheet.

    Exporting:

          sheet_to_json converts a worksheet object to an array of JSON objects.

          sheet_to_csv generates delimiter-separated-values output.

Ref:- https://www.npmjs.com/package/xlsx

4. Open app.component.ts and add the following code.

import { Component } from '@angular/core';
import { read, utils, writeFile } from 'xlsx';

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

    handleImport($event: any) {
        const files = $event.target.files;
        if (files.length) {
            const file = files[0];
            const reader = new FileReader();
            reader.onload = (event: any) => {
                const wb = read(event.target.result);
                const sheets = wb.SheetNames;

                if (sheets.length) {
                    const rows = utils.sheet_to_json(wb.Sheets[sheets[0]]);
                    this.movies = rows;
                }
            }
            reader.readAsArrayBuffer(file);
        }
    }

    handleExport() {
        const headings = [[
            'Movie',
            'Category',
            'Director',
            'Rating'
        ]];
        const wb = utils.book_new();
        const ws: any = utils.json_to_sheet([]);
        utils.sheet_add_aoa(ws, headings);
        utils.sheet_add_json(ws, this.movies, { origin: 'A2', skipHeader: true });
        utils.book_append_sheet(wb, ws, 'Report');
        writeFile(wb, 'Movie Report.xlsx');
    }
}

In the above code, you can change the extension based on the file you want to import or export. (.xlsx, .xls, .csv)

5. Open app.component.html and add the following code.

<main>
    <div class="row mb-2 mt-5">
        <div class="col-sm-6 offset-3">
            <div class="row">
                <div class="col-md-6">
                    <div class="input-group">
                        <div class="custom-file">
                            <input type="file" name="file" class="custom-file-input" id="inputGroupFile" required (change)="handleImport($event)"
                                accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
                            <label class="custom-file-label" for="inputGroupFile">Choose file</label>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <button (click)="handleExport()" class="btn btn-primary float-right">
                        Export <i class="fa fa-download"></i>
                    </button>
                </div>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-sm-6 offset-3">
            <table class="table">
                <thead>
                    <tr>
                        <th scope="col">Id</th>
                        <th scope="col">Movie</th>
                        <th scope="col">Category</th>
                        <th scope="col">Director</th>
                        <th scope="col">Rating</th>
                    </tr>
                </thead>
                <tbody>
                    <ng-container *ngIf="movies.length; else elseBlock">
                        <tr *ngFor="let movie of movies; let i = index">
                            <th scope="row">{{ i + 1 }}</th>
                            <td>{{ movie.Movie }}</td>
                            <td>{{ movie.Category }}</td>
                            <td>{{ movie.Director }}</td>
                            <td><span class="badge bg-warning text-dark">{{ movie.Rating }}</span></td>
                        </tr>
                    </ng-container>
                    <ng-template #elseBlock>
                        <tr>
                            <td colspan="5" class="text-center">No Movies Found.</td>
                        </tr>
                    </ng-template>
                </tbody>
            </table>
        </div>
    </div>
</main>

6. Finally run the project.

ng serve --o

it will open http://localhost:4200 in the browser.

Sample Excel CSV Files:- https://github.com/ultimateakash/angular-excel-csv/tree/master/sample%20files

Checkout my full angular-excel-csv example. https://github.com/ultimateakash/angular-excel-csv

If you facing any issues. don't hesitate to comment below. I will be happy to help you.

Thanks.