Paste Tab-Separated Values from Excel

Some scenarios require you to transfer data from Microsoft Excel by using the Clipboard.

Latest Browser Versions

  1. Access the data that is copied from Excel in the paste event.
  2. Convert the raw data to a plain text format (tab-separated values) by calling the clipboardData.getData('text') method.

The following example demonstrates how to use the paste event to receive the data and then bind the Kendo UI Grid for Angular to it.

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

@Component({
    selector: 'my-app',
    template: `
        <ol>
          <li>
            Select the sample TSV snippet below:
            <pre>
A&#9;1
B&#9;2
C&#9;3
D&#9;4
            </pre>
          </li>
          <li>
            Click on the Grid and paste it with Ctrl+V or ⌘+V
        </ol>

        <kendo-grid [data]="gridData" (excelPaste)="onExcelPaste($event)">
            <kendo-grid-column field="Name">
            </kendo-grid-column>
            <kendo-grid-column field="Age">
            </kendo-grid-column>
        </kendo-grid>
    `
})
export class AppComponent {
    public gridData: any[] = [];

    public onExcelPaste(data: any[]): void {
      this.gridData = data;
    }
}

import { enableProdMode } from '@angular/core';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { AppModule } from './app.module';

enableProdMode();

const platform = platformBrowserDynamic();
platform.bootstrapModule(AppModule);

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
import { GridModule } from '@progress/kendo-angular-grid';

import { AppComponent } from './app.component';
import { ExcelPasteDirective } from './excel-paste.directive';

@NgModule({
  imports: [ BrowserModule, BrowserAnimationsModule, GridModule ],
  declarations: [ AppComponent, ExcelPasteDirective ],
  bootstrap: [ AppComponent ]
})

export class AppModule { }

import {
    Directive,
    EventEmitter,
    HostListener,
    Output
} from '@angular/core';

@Directive({
  selector: '[excelPaste]'
})
export class ExcelPasteDirective {
  @Output()
  public excelPaste: EventEmitter<any[]> = new EventEmitter<any[]>();

  @HostListener('paste', [ '$event' ])
  public onPaste(e: any) {
    if (e.target.tagName && e.target.tagName.match(/(input|textarea)/i)) {
      // Do not handle past when an input element is currently focused
      return;
    }

    // Get clipboard data as text
    const data = e.clipboardData.getData('text');

    // Simplified parsing of the TSV data with hard-coded columns
    const rows = data.split('\n');
    const result = rows.map(row => {
      const cells = row.split('\t');
      return {
        Name: cells[0],
        Age: cells[1]
      };
    });

    this.excelPaste.emit(result);
  }
}

Legacy Browsers

The suggested approach is not supported in Internet Explorer version 11 or earlier. To paste TSV from Excel when in legacy browsers, use a hidden textarea element instead.

The suggested workaround for legacy browsers is a last-resort solution because it prevents the focusing of the Grid elements.

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

@Component({
    selector: 'my-app',
    template: `
        <ol>
          <li>
            Select the sample TSV snippet below:
            <pre>
A   1
B   2
C   3
D   4
            </pre>
          </li>
          <li>
            Click on the Grid and paste it with Ctrl+V or ⌘+V
        </ol>

        <kendo-grid [data]="gridData" (excelPaste)="onExcelPaste($event)">
            <kendo-grid-column field="Name">
            </kendo-grid-column>
            <kendo-grid-column field="Age">
            </kendo-grid-column>
        </kendo-grid>
    `
})
export class AppComponent {
    public gridData: any[] = [];

    public onExcelPaste(data: any[]): void {
      this.gridData = data;
    }
}

import { enableProdMode } from '@angular/core';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { AppModule } from './app.module';

enableProdMode();

const platform = platformBrowserDynamic();
platform.bootstrapModule(AppModule);

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
import { GridModule } from '@progress/kendo-angular-grid';

import { AppComponent } from './app.component';
import { ExcelPasteDirective } from './excel-paste.directive';

@NgModule({
  imports: [ BrowserModule, BrowserAnimationsModule, GridModule ],
  declarations: [ AppComponent, ExcelPasteDirective ],
  bootstrap: [ AppComponent ]
})

export class AppModule { }

import {
    Directive,
    ElementRef,
    EventEmitter,
    HostListener,
    NgZone,
    Output,
    Renderer2
} from '@angular/core';

@Directive({
  selector: '[excelPaste]'
})
export class ExcelPasteDirective {
  @Output()
  public excelPaste: EventEmitter<any[]> = new EventEmitter<any[]>();

  private textarea: HTMLTextAreaElement;

  constructor (
    private element: ElementRef,
    private zone: NgZone,
    private renderer: Renderer2
  ) {}

  @HostListener('focusin')
  public onFocusin(): void {
    if (this.textarea) {
      return;
    }

    const el = this.element.nativeElement;
    const bounds = el.getBoundingClientRect();

    this.textarea = document.createElement('textarea');
    Object.assign(this.textarea.style, {
      border: 'none',
      opacity: '0',
      top: bounds.top + 'px',
      left: bounds.left + 'px',
      position: 'absolute',
      width: el.offsetWidth + 'px',
      height: el.offsetHeight + 'px'
    });

    this.zone.runOutsideAngular(() => {
      this.bindTextArea();
    });
  }

  private bindTextArea(): void {
    const unsubPaste = this.renderer.listen(this.textarea, 'paste', () => {
      setTimeout(() => this.onData(this.textarea.value));
    });

    const unsubFocus = this.renderer.listen(this.textarea, 'focusout', () => {
      unsubFocus();
      unsubPaste();
      document.body.removeChild(this.textarea);
      this.textarea = null;
    });

    document.body.appendChild(this.textarea);
    setTimeout(() => this.textarea.focus());
  }

  private onData(value: string): void {
    const rows = value.split('\n');
    const data = rows.map(row => {
      const cells = row.split('\t');
      return {
        Name: cells[0],
        Age: cells[1]
      };
    });

    this.textarea.blur();
    this.zone.run(() => this.excelPaste.emit(data));
  }
}

In this article