This series is a collection of small tools made by myself in order to solve the problems of development or operation during my one and a half years of working in OPPO. It is specially recorded in the form of articles, on the one hand as my knowledge reserve, on the other hand, I hope to give some inspiration to students who meet the same problems.
Operating distress
Buried data platform has hundreds of fields, but the operation only want to pick a dozen of them and make excel report. However, this system was developed by other teams using PHP. The background of the buried data system does not support filtering out specified fields, and the data needs to be manually copied to the local Excel table when taking data. It sometimes takes half a day to sort out an active data.
From the analysis of the problem, we come to the following conclusions: Searching for specified data and making Excel forms are important reasons for operation time consuming. How to improve them?
- Filter out the specified data table to improve search efficiency.
- Export tables to Excel in a convenient format, reducing manual copy and paste operations.
Knowing the direction to go, the next step is to design how to solve the problem.
Write a script
Our first version of the scheme is very simple, write a section of JS script, by manipulating dom, simulate manual selection click button, select the input specified field.
let targetStr = 'angular vue react';
let ul = dropdownMenu;
let children = ul.children;
let targetArr = targetStr.split(""); // Generate the target array
for (let i = 0; i < children.length; i++) {
let li = children[i].children[0];
let label = li.innerHTML;
let input = li.children[0];
for (let j = 0; j < targetArr.length; j++) {
if (label.indexOf(targetArr[j]) > -1) { input.click(); }}}Copy the codeGenerate an array of selected fields, iterate over all of the checkboxes, iterate over the array of selected fields, trigger the click event, and complete the effect of selecting all buttons. Here is the logic for generating excel tables.
function tableToExcel(fileName, excelContent) {
var uri = "data:application/vnd.ms-excel; base64,";
var fileName = fileName || "excelexport";
var template =
'<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 name="renderer" content="webkit"><! --[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:Display Gridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><! [endif]--></head><body><table>{table}</table></body></html>';
var ctx = { worksheet: "Worksheet".table: excelContent };
var a = document.createElement("a");
a.hreflang = "zh";
a.type = "application/vnd.ms-excel";
let blob = new Blob([format(template, ctx)]);
a.href = URL.createObjectURL(blob);
a.target = "_blank";
a.download = fileName + ".xls";
a.tableBorder = 1;
document.body.appendChild(a);
a.click();
// Click download to complete and delete the created A tag
setTimeout(function () {
document.body.removeChild(a);
}, 200);
}
document.getElementById('targetTable').addEventListener("dblclick".function (e) {
tableToExcel("Form", e.target.innerHTML);
});
Copy the codeThe logic here is roughly as follows: bind a double click event to the specified table and double click to download the Excel table. The logic that triggers the download is simple:
- Concatenate a specific string to generate a
Blodobject - To create a
aThe label,hrefThe parameter is oneBlodThe url generated by the.
Finally, we paste the two pieces of code into Google’s Console and press Enter to run the first version.
Let’s think out of the box. Can we implement a function that takes an array of a particular format and generates an Excel spreadsheet? All it really does is reassemble a table tag.
Optimize the experience
In fact, it is not difficult to find that as a small tool, it is very convenient for technical personnel to use, but for non-technical personnel such as operation, there will be some problems.
- The process is cumbersome. You need to open the console and then run the code.
- You need to manually modify the code. The process of filtering strings requires modifications in the code
targetStrA string. - Repeat. Each time you filter, you need to run through the code.
- Learning costs are high. Each operation learns how to run the code inside the console
In fact, for the above operation flow, we use a script to insert an input field in the page. In this way, operations only need to run the code once.
However, is there a way to avoid operating students to run code, interface operation? It’s easy to write a Google plugin!
Google plug-in
The Google plugin inserts a JAVASCRIPT script into the current page, and also adds a popover at the top that can be implemented with a new HTML.
- Implement an input via a popover to let the operator enter the field that needs to be selected.
- Insert a script in the web page, add a double click download event to all the tables, and receive popup input values to complete the filtering logic.
Because it involves part of the business code, in this only provides part of the logic, only for everyone’s reference, adapted from the project Chrome-plugin-demo, on how to write Google teaching is very comprehensive.
// manifest.json
{
"manifest_version": 2."name": Buried Data Platform Plug-in."version": "1.0"."description": "This plug-in is only for buried data platforms"."author": "ting"."icons": {
"48": "icon.png"."128": "icon.png"
},
"browser_action": {
"default_icon": "icon.png"."default_popup": "popup.html" // Specify the popover page name.
},
"content_scripts": [{"matches": ["*://baidu.com/*"].// This plugin can only run under Baidu domain names
"js": ["content-script.js"] // Set insert}]"permissions": ["tabs"."storage"].// Apply permissions: tabs and storage (for popup and JS injection communication)
}
Copy the codeAbove is the configuration of our popover and JS injection file, this file will tell Google Browser parsing logic path.
<! -- popup.html -->
<! DOCTYPEhtml>
<html>
<head>
<meta charset="utf-8" />
<title>popup</title>
<style>
p {
margin: 0;
padding: 10px 0;
}
</style>
</head>
<body style="width:500px; min-height:100px;">
<textarea name="" id="textarea" cols="60" rows="10"></textarea>
<p>* The fields to be filtered are used<b>The blank space</b>For example, pv-login PV-channel</p>
<button id="filter-button">filter</button>
<script type="text/javascript" src="popup.js"></script>
</body>
</html>
Copy the codeThe popover has three main elements: a Taxtarea input box, a confirmation button, and a text prompt.
// popup.js
// Pass the text in the textarea to content-script.js (the js file injected into the window)
function sendMessage(value) {
chrome.tabs.query(
{
active: true.currentWindow: true,},(tabs) = > {
let message = {
data: value,
};
chrome.tabs.sendMessage(tabs[0].id, message); }); }// Data will be destroyed when closing the popover, so use storage to cache the current input.
chrome.storage.local.get(["ltExportExcelVal"].function (result) {
document.getElementById("textarea").value = result.ltExportExcelVal || "";
});
// Click filter button
document.getElementById("filter-button").addEventListener("click".function () {
let value = document.getElementById("textarea").value;
sendMessage(value);
// Caches the last validation input
chrome.storage.local.set({ ltExportExcelVal: value }, function () {
console.log("Value is set to " + value);
});
});
Copy the codeThere are two main things you do in popup.js: pass the input and cache the last input.
// Because content-script.js is inserted, it is possible that the table has not been loaded.
// Wait for the page to load, then add an event to each table.
(function (win) {
"use strict";
var listeners = [];
var doc = win.document;
var MutationObserver = win.MutationObserver || win.WebKitMutationObserver;
var observer;
function check() {
for (var i = 0; i < listeners.length; i++) {
var listener = listeners[i];
var elements = doc.querySelectorAll(listener.selector);
for (var j = 0; j < elements.length; j++) {
var element = elements[j];
if(! element.ready) { element.ready =true; listener.fn.call(element, element); }}}}function ready(selector, fn) {
listeners.push({
selector: selector,
fn: fn,
});
if(! observer) { observer =new MutationObserver(check);
observer.observe(doc.documentElement, {
childList: true.subtree: true}); } check(); } win.ready = ready; }) (this);
// Add double click events to all tables
function tableToExcel(fileName, excelContent) { / /... }
// Add a double click event to all tables.
ready("table".function (element) {
element.addEventListener("dblclick".function () {
tableToExcel("Form", element.innerHTML);
});
document.getElementById('targetTable').addEventListener
});
// Get the value passed in the popover, filter out the specified field
chrome.runtime.onMessage.addListener((request) = > {
let targetStr = request.data;
/ /... The simulated user clicks to filter out the specified table field.
});
Copy the codeThrough this Google plugin, I got a whole bunch of snacks that the operation gave me.
Some thoughts and summaries
In fact, the development principle and logic of this small tool are simple, but it can help the operation or help other students to reduce a lot of ineffective workload. In fact, as front-end development, our skills should not only be used in business, if we can help colleagues and family around to solve problems, we can also realize their own value.
In addition, the first time to understand the original HTML can directly export Excel files, XLSX file principle is XML format files, can also be directly decompression, the same HTML can also export HTML, TXT and other formats of the file, using this principle we can achieve more interesting and practical functions!