Cannot Paste from External Source like Excel or Google Spreadsheet

Tags: #<Tag:0x00007f34472ec818>

I am new to Angular development and I’ve just handled an already existing application. Right now, I am encountering an error of pasting values from external sources like Excel or Google Spreadsheet on my Angular Handsontable app.

First, here are the details:

Windows 7 Enterprise
Excel version: 14
Chrome version: 88.0.4324.190 (Official Build) (64-bit)
FF version: 85.0 (64-bit)

Angular Application:
@handsontable/angular”: “^3.0.0”, (<-- I believe the latest version is 9.0)
“handsontable”: “^6.2.2”,

Previously, it was working and we didn’t change or upgrade versions but then it was suddenly became an issue to us and I don’t have any idea what causes it. As much as I want to upgrade right away, I’m afraid it will introduce a more serious problem.

Appreciated if you can help me on this.

Thank you.

Hi @noel.antoniooo18

Regarding

Right now, I am encountering an error of pasting values from external sources like Excel or Google Spreadsheet on my Angular Handsontable app.

Would you please paste the error message (possibly from a non-minified script)? Also, a set of the settings in your project would help. Then I will be able to test the same on my device.

1 Like

Hi @aleksandra_budnik,

You mean my handsontable settings right?

On my typescript file:

Declaration:

defaultSettings: Handsontable.GridSettings = {
colHeaders: [],
columns: [],
dataSchema: {},
rowHeaders: false,
height: 0,
stretchH: ‘all’,
columnSorting: true,
fillHandle: false,
};
hotId = ‘hotInstance’;
hotSettings = {
…this.defaultSettings,
};
private hotRegisterer = new HotTableRegisterer();

Usage:

this.hotSettings = {
…this.hotSettings,
…columns,
data: statementData,
maxRows: statementData.length,
height: 550,
cells: (row, column): object => {
// some logic here
},
};
this.hotSettings[‘filterData’] = this.filterData;

this.hotRegisterer
.getInstance(this.hotId)
.updateSettings(this.hotSettings, false);

Then I can’t see any errors in console log but only this:
image_2021_07_15T02_18_04_462Z

Also, I have a patch for handsontable-6.2.2.patch:

> diff --git a/node_modules/handsontable/dist/handsontable.js b/node_modules/handsontable/dist/handsontable.js
> index 4e9622a..03f79ab 100644
> --- a/node_modules/handsontable/dist/handsontable.js
> +++ b/node_modules/handsontable/dist/handsontable.js
> @@ -52099,7 +52099,14 @@ function (_BasePlugin) {
>          var textHTML = event.clipboardData.getData('text/html');
>  
>          if (textHTML && /(<table)|(<TABLE)/.test(textHTML)) {
> -          pastedData = (0, _utils.tableToArray)(textHTML);
> +          /**
> +           */
> +          //pastedData = (0, _utils.tableToArray)(textHTML);
> +          pastedData = parseHtmlFragment(textHTML);
> +          /**
> +           */
>          } else {
>            pastedData = event.clipboardData.getData('text/plain');
>          }
> @@ -63725,3 +63732,131 @@ exports.default = _default;
>  /******/ ])["default"];
>  });
>  //# sourceMappingURL=handsontable.js.map
> +
> +/**
> + */
> +/**
> + * Helper to verify if DOM element is an HTMLTable element.
> + *
> + * @param {Element} element Node element to verify if it's an HTMLTable.
> + */
> +function isHTMLTable(element) {
> +  return (element && element.nodeName || '').toLowerCase() === 'table';
> +}
> +
> +/**
> + * Converts HTMLTable or string into array.
> + *
> + * @param {Element|String} element Node element or string, which should contain `<table>...</table>`.
> + */
> +function tableToArray(element) {
> +  var result = [];
> +  var checkElement = element;
> +
> +  if (typeof checkElement === 'string') {
> +    //remove new lines
> +    checkElement = checkElement.replace(/\n/g, '');
> +
> +    var tempElem = document.createElement('div');
> +    tempElem.innerHTML = checkElement;
> +    var children = tempElem.children;
> +    var childrenLen = children.length;
> +
> +    console.log('---children---');
> +    console.log(children);
> +
> +    for (var childIndex = 0; childIndex < childrenLen; childIndex += 1) {
> +      var childVal = children[childIndex];
> +      if (childVal && isHTMLTable(childVal)) {
> +        var rows = childVal.rows;
> +        var rowsLen = rows && rows.length;
> +        var tempArray = [];
> +
> +        for (var row = 0; row < rowsLen; row += 1) {
> +            var cells = rows[row].cells;
> +            var cellsLen = cells.length;
> +            var newRow = [];
> +
> +            for (var column = 0; column < cellsLen; column += 1) {
> +              var cell = cells[column];
> +              cell.innerHTML = cell.innerHTML.trim().replace(/<br(.|)>(\n?)/, '\n');
> +              var cellText = cell.innerText;
> +
> +              newRow.push(cellText);
> +            }
> +
> +            tempArray.push(newRow);
> +        }
> +
> +        result.push.apply(result, tempArray);
> +      }
> +    }
> +  }
> +
> +  return result;
> +}
> +
> +
> +//Parses/extracts fragments from textHtml and converts to array
> +function parseHtmlFragment(textHTML) {
> +  var fragment = textHTML;
> +  //if start and end fragment tags found, get only the fragment for more cleaner data.
> +  if (textHTML.includes('<!--StartFragment-->') && textHTML.includes('<!--EndFragment-->')) {
> +    fragment = textHTML.split('<!--StartFragment-->')[1].split('<!--EndFragment-->')[0];
> +  }
> +
> +  //remove meta tags
> +  fragment = fragment.replace(/<meta[^>]+>/g, '');
> +  //remove div tags
> +  fragment = fragment.replace(/<div[^>]+>/g, '');
> +  fragment = fragment.replace(/<\/div>/g, '');
> +  fragment = fragment.replace(/<DIV[^>]+>/g, '');
> +  fragment = fragment.replace(/<\/DIV>/g, '');
> +
> +  //fix for copying issue from linux/libre office
> +  fragment = fragment.replace(/<!DOCTYPE[^>]+>/g, '');
> +  fragment = fragment.replace(/<html>/g, '');
> +  fragment = fragment.replace(/<\/html>/g, '');
> +  fragment = fragment.replace(/<title>/g, '');
> +  fragment = fragment.replace(/<\/title>/g, '');
> +  fragment = fragment.replace(/<head>/g, '');
> +  fragment = fragment.replace(/<\/head>/g, '');
> +  fragment = fragment.replace(/<style[^<]+</g, '');
> +  fragment = fragment.replace(/\/style>/g, '');
> +  fragment = fragment.replace(/<body>/g, '');
> +  fragment = fragment.replace(/<\/body>/g, '');
> +
> +  fragment = fragment.trim();
> +  console.log('---fragment1---');
> +  console.log(fragment);
> +
> +
> +  //define temporary storage of parsed data
> +  var parsedDataArray = [];
> +
> +  //IE single row and multiple row paste handling
> +  //reformat fragment, add table and tbody tag
> +  if (fragment.substring(1, 3).toUpperCase() == "TR" ||
> +      fragment.substring(1, 3).toUpperCase() == "TD") {
> +      fragment = '<table><tbody>' + fragment + '</tbody></table>';
> +  }
> +
> +  //IE and Non IE single cell paste handling
> +  //reformat fragment, add table,tbody, and td tag
> +  if (fragment.substring(1, 3).toUpperCase() != "TR" &&
> +      fragment.substring(1, 3).toUpperCase() != "TD" &&
> +      fragment.substring(1, 6).toUpperCase() != "TABLE" &&
> +      fragment.substring(1, 4).toUpperCase() != "COL") {
> +      fragment = '<table><tbody><td>' + fragment + '<td></tbody></table>';
> +  }
> +
> +  console.log('---fragment2---');
> +  console.log(fragment);
> +  return tableToArray(fragment);
> +}
> +/**
> + */

Hopefully this can help. Thank you very much!

Hello, I would just like to follow-up this. Appreciate your help. Thanks.

Hi @noel.antoniooo18,
It seems you use your custom build of Handsontable@6.2.2. We do not support clients customisation. You can reach out to our sales team to ask about consulting services.

As you use the very outdated code, I can suggest upgrading Handsontable to the latest version. Since v6.2.2, we updated the CopyPaste plugin multiple times. However, since v7.0.0, we merged handsontable and handsontable-pro into one product. You can read more about currently available kinds of licenses in our documentation: https://handsontable.com/docs/license-key/.

I’m closing this issue due to the custom build of Handsontable. The library does not cause the problem.