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

CSS fixes the container level (div...) tag in one position (on the far right of the page)

The code looks like this: .process{ border:1px so...

How to Run a Command at a Specific Time in Linux

The other day I was using rsync to transfer a lar...

How to view the docker run startup parameter command (recommended)

Use runlike to view the docker run startup parame...

Summary of the differences between Mysql primary key and unique key

What is a primary key? A primary key is a column ...

LayUI+Shiro implements a dynamic menu and remembers the example of menu expansion

Table of contents 1. Maven Dependency 2. Menu rel...

In-depth explanation of slots and filters in Vue

Table of contents Slots What are slots? Slot Cont...

Implement a simple data response system

Table of contents 1. Dep 2. Understand obverser 3...

MySQL 8.0.12 installation configuration method and password change

This article records the installation and configu...

Let's take a look at some powerful operators in JavaScript

Table of contents Preface 1. Null coalescing oper...

How to implement scheduled automatic backup of MySQL under CentOS7

The happiest thing that happens in a production e...

How to modify the password of MySQL 5.1 and remotely log in to the MySQL database

Create a mysql user and authorize: Format: grant ...

Implementation of installing Docker in win10 environment

1. Enter the Docker official website First, go to...

How to implement rounded corners with CSS3 using JS

I found an example when I was looking for a way t...

Detailed explanation of HTML onfocus gain focus and onblur lose focus events

HTML onfocus Event Attributes Definition and Usag...

Why MySQL database avoids NULL as much as possible

Many tables in MySQL contain columns that can be ...