4

The Idea

We're building an app in Angular 1.5.x and we're trying to implement a feature that would allow the user to paste a single column of cells from an excel sheet or any other spreadsheet (single column, any number of rows deep) into an input.

We've got a data table that contains inline inputs like the following: data table

The idea is that when they paste data copied from a single column of a spreadsheet into one of the inputs, the data would be parsed cell for cell and assigned to each input in descending order. I.E. If I pasted a column of cells containing [4.52, 6.235, 9.2301] into the top input containing 15.23, then 15.23 would become 4.52, 3.1234 would become 6.235, and 3.1322 would become 9.2301. We know how to assign the variables to the inputs, but we need the clipboardData in array format, not one big string.

The Problem

We've looked into doing this using the ng-paste directive and the $event.clipboardData property, but we only have access to the data as a string, and while we have a way of parsing the string, it would be much less error prone if we could access the elements coming in as an array or a list to prevent any errors on our end if we end up using delims to break up that string.

Here is a working plunker with what we're already trying.

Here is a sample data set to copy and paste into the input:

For some reason, when copying a column from an excel spreadsheet, it's got no delims between values. When we copy from multiple columns of a single row instead of multiple rows of a single column I'm able to .split() on the '\n' character and it works fine. It would be preferable to allow the user to copy both from across a single row and down a single column, however. The issue is just that there are no delims when you copy a column from excel.

0.89663.91783.91773.91773.9178

That is what pasted from an excel/google sheet^, but feel free to put those values in a single column of a spreadsheet and copy from there. We can be sure that the pasted data will be coming from a spreadsheet.

The Solution

Any ideas on how to grab that clipboard data as an array?

Community
  • 1
  • 1
M. Irvin
  • 143
  • 1
  • 13
  • You mean you need to turn string to numbers into array? This should be enough `ev.clipboardData.getData('text').split(" ").map(Number)`. – Aleksey Solovey Oct 17 '17 at 12:32
  • @AlekseySolovey the problem is that .getData('text') doesn't return a string with spaces as delims, it returns a string with no real delims between values – M. Irvin Oct 17 '17 at 12:42

2 Answers2

2

This should do the trick:

HTML:

<input ng-paste="pasteFunction($event)"><br />
<p>Input as array:</p>
<p ng-repeat="item in pasted track by $index">{{item}}</p>

JavaScript:

  $scope.pasteFunction = function(ev) {
    $scope.pasted = ev.clipboardData.getData('text').split(" ").map(Number);
    console.log($scope.pasted);
  }

Working Code: Plunker

Kyle Krzeski
  • 6,183
  • 6
  • 41
  • 52
  • 1
    He is asking to "auto-fill" the existing inputs on paste event, if I understand him correctly – Aleksey Solovey Oct 17 '17 at 13:00
  • 1
    @AlekseySolovey While that seems to be the end goal, his question (quoted from 'The Problem') was: `while we have a way of parsing the string, it would be much less error prone if we could access the elements coming in as an array or a list to prevent any errors on our end if we end up using delims to break up that string.` – Kyle Krzeski Oct 17 '17 at 13:05
  • See my response to the above answer^ same issue. I can handle auto-filling the inputs, I just need to know how to break up what we get from the .getData('text') function. When I paste from excel it's pasting a single string with no spaces or anything as delims. – M. Irvin Oct 17 '17 at 13:31
  • 1
    @M.Irvin you should edit your question then to be less confusing. Your question clearly states: `Here is a sample data set to copy and paste into the input: 0.8966 3.9178 3.9177 3.9177 3.9178` – Kyle Krzeski Oct 17 '17 at 13:32
  • 1
    Roger that. The way the column is pasting from excel into different text editors/inputs is showing up differently. When I paste it into the input in my app it looks like this: "0.89663.91783.91773.91773.9178" – M. Irvin Oct 17 '17 at 13:42
  • 1
    Update: copying from an excel column yields a string with no delims inside my input. copying from an excel row yields a string with '\n' as delims between values. I've gotten it functioning with the '\n' delim, but if there are no delims then we've still got trouble. I think we'll just require that the user paste from a row so we see the '\n' delims. Thanks all! – M. Irvin Oct 17 '17 at 13:46
1

I can see a plausible hack with additional input, instead of using existing ones. The body then becomes:

<body ng-controller="MainCtrl">
  <input ng-paste="pasteFunction($event)">
  <input ng-repeat="t in test" ng-model="t"/>
</body>

And the controller changes to: 

app.controller('MainCtrl', function($scope) {
  $scope.name = 'World';
  $scope.obj = {};
  $scope.test = [111, 222, 333];
  $scope.pasteFunction = function(ev) {
    $scope.test = ev.clipboardData.getData('text').split(" ").map(Number);
  }
  // paste this: 999 888 777
  });
Aleksey Solovey
  • 4,153
  • 3
  • 15
  • 34
  • 1
    This is assuming a few things that probably aren't true: 1) that he is only pasting in the final column first row... 2) that row is represented as an array 3) that the user wouldn't want to paste anywhere else in the table – Kyle Krzeski Oct 17 '17 at 13:08
  • I appreciate your time! This works assuming the user has copied a set of values that have spaces in between, but right now when I copy out of an excel spreadsheet there are no delims at all, and when i copy and paste out of a google sheet the delim is a return character. The user is going to be using excel, though, so the challenge is that (from Mac at least) when you copy a column from excel there are no delims between the values when you paste – M. Irvin Oct 17 '17 at 13:30
  • I stand corrected^ There is an "invisible" carriage return character. We checked for that and the code worked swimmingly. – M. Irvin Nov 08 '17 at 13:41