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

Preface
1. Paste Events and Clipboard
2. The content format in the clipboard
3. How to parse HTML string

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)

Detailed explanation of how to use the canvas operation plugin fabric.js

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

Table of contents

Preface

1. Paste Events and Clipboard

2. The content format in the clipboard

3. How to parse HTML string

Detailed explanation of how to use the canvas operation plugin fabric.js

Example of creating a virtual host based on Apache port

A brief discussion on the efficiency of MySQL subquery union and in

Vue plugin error: Vue.js is detected on this page. Problem solved

How to install Nginx in CentOS7 and configure automatic startup

Modularity in Node.js, npm package manager explained

Example of using Docker to build an ELK log system

10 reasons why Linux is becoming more and more popular

MySQL 5.7.17 winx64 free installation version configuration method graphic tutorial

SQL insert into statement writing method explanation

Recommend

Example method to view the IP address connected to MySQL

Summary of MySQL common SQL statements including complex SQL queries

Sample code for achieving three-dimensional picture placement effect with pure CSS

The problem of Chinese garbled characters appearing when connecting to MySQL database in Idea

Example of creating circular scrolling progress bar animation using CSS3

Summary of methods to clear cache in Linux system

Solve the compatibility issue between MySQL 8.0 driver and Alibaba Druid version

Docker binding fixed IP/cross-host container mutual access operation

Solve the problem of Navicat importing database data structure sql reporting error datetime(0)

How to build a complete samba server in Linux (centos version)

Introduction to the use of data custom attributes in HTML and plug-in applications

WeChat applet to save albums and pictures to albums

NodeJS realizes image text segmentation

Ubuntu installs scrcpy to complete mobile phone screen projection and control (another way to use QQ WeChat in Ubuntu)

5 Ways to Send Emails in Linux Command Line (Recommended)