Import and Export Excel Spreadsheets in Angular

The Excel spreadsheet has been around since the 1980's. With over 30 million users, most are familiar with the Excel spreadsheet experience. Many businesses begin using Excel spreadsheets for budgeting and planning when their businesses are moderately simple. At first, there may be a small number of users involved in the process, and you may not have large quantities of data to interpret and organize. As the organization grows, you may find that it is difficult to rely on Excel's functionalities. It lacks structure, protection from errors, and security.

Benefits of a spreadsheet component

A spreadsheet component can provide increased security, data consolidation, improved data visualization, strategic performance measurements (SPM), sophisticated statistical analysis, and much more. Excel compatibility has been one of the most significant features of our Spread .NET and JavaScript components for years.

SpreadJS, our JavaScript component, offers a familiar Excel spreadsheet interface. You can import and export Excel files and you can even build your performance and business dashboards in JavaScript -- without any dependencies on Excel.

With the newly added Angular 6 support, this article demonstrates how you can import and export Excel spreadsheets with SpreadJS in an Angular environment.

First, install the SpreadJS component in your app.

Import and Export Excel files with Spreadjs

Download SpreadJS - Excel-like JavaScript components

Download Now!

Since we'll be working with SpreadJS’ Excel import and export functionality, we'll need ExcelIO component. You can install using npm.

How to import and export Excel spreadsheets in Angular

SpreadJS can be added to the html page as:

<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="workbookInit($event)">
</gc-spread-sheets>

Instantiate the SpreadJS component and create an object of ExcelIO class in app.component.ts file with following code:

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent {
  spreadBackColor = 'aliceblue';
  hostStyle = {
    width: '95vw',
    height: '80vh'
  };
  private spread: GC.Spread.Sheets.Workbook;
  private excelIO;
  constructor() {
    this.excelIO = new Excel.IO();
  }
  workbookInit(args) {
    const self = this;
    self.spread = args.spread;
    const sheet = self.spread.getActiveSheet();
    sheet.getCell(0, 0).text('Test Excel').foreColor('blue');
    sheet.getCell(1, 0).text('Test Excel').foreColor('blue');
    sheet.getCell(2, 0).text('Test Excel').foreColor('blue');
    sheet.getCell(3, 0).text('Test Excel').foreColor('blue');
    sheet.getCell(0, 1).text('Test Excel').foreColor('blue');
    sheet.getCell(1, 1).text('Test Excel').foreColor('blue');
    sheet.getCell(2, 1).text('Test Excel').foreColor('blue');
    sheet.getCell(3, 1).text('Test Excel').foreColor('blue');
    sheet.getCell(0, 2).text('Test Excel').foreColor('blue');
    sheet.getCell(1, 2).text('Test Excel').foreColor('blue');
    sheet.getCell(2, 2).text('Test Excel').foreColor('blue');
    sheet.getCell(3, 2).text('Test Excel').foreColor('blue');
    sheet.getCell(0, 3).text('Test Excel').foreColor('blue');
    sheet.getCell(1, 3).text('Test Excel').foreColor('blue');
    sheet.getCell(2, 3).text('Test Excel').foreColor('blue');
    sheet.getCell(3, 3).text('Test Excel').foreColor('blue');
 }

For import, we'll create an input element that accepts xlsx files. Let’s add the following code in the app.component.html:

<div class='loadExcelInput'>
    <p>Open Excel File</p>
  <input type="file" name="files[]" multiple id="jsonFile" accept=".xlsx" (change)="onFileChange($event)" />
</div>

ExcelIO object can open the selected file and gives result in json. This JSON data can be directly understood by SpreadJS. So, we'll write the import code in onFileChange() function for change event as shown below:

onFileChange(args) {
    const self = this, file = args.srcElement && args.srcElement.files && args.srcElement.files[0];
    if (self.spread && file) {
      self.excelIO.open(file, (json) => {
        self.spread.fromJSON(json, {});
        setTimeout(() => {
          alert('load successfully');
        }, 0);
      }, (error) => {
        alert('load fail');
      });
    }
  }

Similarly, let’s add a button which processes the export functionality. To add the export button, we can use:

<div class='exportExcel'>
    <p>Save Excel File</p>
    <button (click)="onClickMe($event)">Save Excel!</button>
</div>

We need to handle the click event of this button and write our code there. SpreadJS saves the data as JSON and that JSON can be used by ExcelIO to save it as BLOB. Later on, this blob data needs to save as per the given format using another component (file-saver).

onClickMe(args) {
    const self = this;
    const filename = 'exportExcel.xlsx';
    const json = JSON.stringify(self.spread.toJSON());
    self.excelIO.save(json, function (blob) {
      saveAs(blob, filename);
  }, function (e) {
      console.log(e);
  });
  }

Please note, I have used the file-saver component to achieve the export functionality. To include file file-saver in your project, follow the steps below:

  1. npm install file-saver –save

  2. Add this third-party lib to ‘.angular.json’

    "scripts": ["./node_modules/file-saver/FileSaver.js"]**

  3. Import the component

    import {saveAs} from 'file-saver';

Download the sample application.

If you have any questions leave a comment below. For more articles like this, demos, videos and tutorials, check out our blog page.