How to use JS to parse the excel content in the clipboard

How to use JS to parse the excel content in the clipboard

Preface

This time I recorded an idea I had last night: copy the Excel content to the clipboard and convert it into the JSON format you want. The core is to convert the Excel content to JSON. This part mainly depends on how the Excel format and JSON are mapped to the business, so I won’t expand on it. However, through practice, I gained some knowledge points about the clipboard.

Note: Because it is only for my own gadget implementation, I don't consider compatibility, and practice it under Chrome

The whole steps are:

  • From an excel file with content, select the content and press ctrl+c to copy it to the clipboard (my content happens to be the entire table, so ctrl+A can select the content)
  • Paste to the web page, js listens to the paste event, and obtains the copied excel content (including format) from the clipboard object
  • Parse the content into your own format [Extended Supplement]

There are three main points here:

1. Paste Events and Clipboard

document.addEventListener('paste', event => {
    //clipboardData object in event console.log(event.clipboardData)
})

When the paste event is triggered, you can get the clipboardData from the event

However, window.clipboardData is also used in it. I tried it in chrome and codepen, but couldn't get the content.

2. The content format in the clipboard

When the code in the previous part is printed to the console, there will be a puzzlement. A DataTransfer object is printed out in the console, but in fact, when this object is expanded in the console, the attributes either have no value or are empty arrays, which is very confusing.

It wasn't until I looked into the console property content that I found it.

In this object, getData is its common method, which is used to obtain data content. It needs to accept a DOMString parameter.

The commonly used method is to paste plain text, which can be obtained by calling getData('text').

But what I want is the Excel format. At first I didn’t know what Excel format was, but when I copied from Excel and pasted it back into Excel, the format was still retained, so I thought the clipboard should still retain the format of the original content, so I gave it a try.

By traversing and printing out the types attribute of the DataTransfer object, you can know

document.addEventListener('paste', event => {
      event.clipboardData.types.map(type=>{console.log(type)})
})

Type has three values: text/plain, text/html, Files

So I used type 'text/html' and tried getData, and sure enough, I got the formatted content, which is actually a string of HTML code as follows

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<style>
...
<table>
...

Through observation, we know that the desired content is the table in the HTML code. Then it will be easy to convert the format. Just parse the HTML string and use the selector to get the cell content.

3. How to parse HTML string

This really took me a lot of time, and later I found DOMParser, which originally supports parsing HTML strings>>

Convert a string to DOM using (new DOMParser()).parseFromString

const html = event.clipboardData.getData('text/html');
const $doc = new DOMParser().parseFromString(html,'text/html');
// Load all rows const $trs = Array.from($doc.querySelectorAll('table tr'));

Then you can querySelectorAll happily.

The above is the details of how to use JS to parse the Excel content in the clipboard. For more information about JS parsing the Excel content in the clipboard, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • JS cleverly obtains clipboard data and pastes Excel data
  • Two common methods of copying content to the clipboard in JavaScript
  • Code Detailed Explanation of JS Clipboard Operations
  • JS implements the function of copying content to the clipboard
  • js implements various methods of copying to the clipboard (sharing)
  • js copy content to clipboard code, js copy code simple example
  • JS implements the method of obtaining clipboard contents
  • JS implements the function of copying content to the clipboard and is compatible with all browsers (recommended)
  • How to copy or cut content to the clipboard using JavaScript

<<:  Detailed tutorial for installing mysql5.7.21 under Windows

>>:  Understanding Nginx Current Limitation in One Article (Simple Implementation)

Recommend

A brief discussion on the implementation principle of Webpack4 plugins

Table of contents Preface know Practice makes per...

How to deploy Solidity smart contracts using ethers.js

If you have developed DApps on Ethereum, you may ...

Solution to primary key conflict when innodb_index_stats reports an error when importing backup data

Fault description percona5.6, mysqldump full back...

CentOS7.5 installation of MySQL8.0.19 tutorial detailed instructions

1. Introduction This article does not have screen...

MySQL query specifies that the field is not a number and comma sql

Core SQL statements MySQL query statement that do...

A comprehensive understanding of Vue.js functional components

Table of contents Preface React Functional Compon...

A brief discussion on the use of Web Storage API

Table of contents 1. Browser local storage techno...

Summary of Vue3 combined with TypeScript project development practice

Table of contents Overview 1. Compositon API 1. W...

Summary of various methods for Vue to achieve dynamic styles

Table of contents 1. Ternary operator judgment 2....

JS implements request dispatcher

Table of contents Abstraction and reuse Serial Se...

HTML form tag tutorial (5): text field tag

<br />This tag is used to create a multi-lin...

Detailed explanation of the points that need to be paid attention to in HTML standards that comply with W3C standards

XML/HTML CodeCopy content to clipboard <!DOCTY...

Win10 64-bit MySQL8.0 download and installation tutorial diagram

How do I download MySQL from the official website...

Detailed explanation of nginx-naxsi whitelist rules

Whitelist rule syntax: BasicRule wl:ID [negative]...

Implementing the preview function of multiple image uploads based on HTML

I recently wrote a script for uploading multiple ...