读取excel文件，生成excel文件!
当前为
// ==UserScript==
// @name 读取excel文件
// @namespace http://tampermonkey.net/
// @version 0.2
// @description 读取excel文件，生成excel文件!
// @author BigHan
// @match https://www.tianyancha.com/*
// @icon https://www.google.com/s2/favicons?sz=64&domain=greasyforks.org
// @grant GM_xmlhttpRequest
// @connect *
// @license BH
// ==/UserScript==
(function() {
'use strict';
function import_js(src) {
let script = document.createElement('script');
script.src = src;
document.head.appendChild(script);
}
import_js('https://unpkg.com/xlsx/dist/xlsx.full.min.js');
var getUrl = 'https://www.tianyancha.com/search?key=';
var input = document.createElement('input');
input.type = 'file';
input.id = 'excel';
input.style.width="80px";
input.style.hight="60px";
input.style.position="fixed";
input.style.top="80%";
input.style.right="5px";
input.style.align="center";
input.style.borderRadius = '15px';
input.style.background="rgb(222 225 205)";
// 读取本地excel文件，读取Excel文件对象
function readWorkbookFromLocalFile(file, callback) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {type: 'binary'});
if(callback) callback(workbook);
};
reader.readAsBinaryString(file);
}
// 转成cvs 要注意Execel表格内容不能包含英文的,不然解析出来的数据格式会有问题
async function readWorkbook(workbook) {
var sheetNames = workbook.SheetNames; // 工作表名称集合
let nowTime = timestampToTime();
console.log(nowTime);
sheetNames.forEach((sheet)=>{
var worksheet = workbook.Sheets[sheet];
var jsonData = XLSX.utils.sheet_to_json(worksheet);
for (var i = 0; i < jsonData.length; i++) {
if(i%500 == 0){
console.log('当前循环次数 ： '+i);
}
var row = jsonData[i];
var args = row['qymc'];
if (typeof(args) == "undefined"){
args = row['id'];
}
var request = new XMLHttpRequest();
request.open('GET', getUrl+args, false);
// overrideMimeType() 用来强制解析 response 为 XML
request.overrideMimeType('text/xml');
request.send(null);
if (request.status === 200) {
var result = request.responseXML;
if(result != null){
var list = result.getElementsByClassName('index_search-item-center__Q2ai5');
if(list.length > 0){
var nameSource = list[0].getElementsByClassName('index_name__qEdWi');
var dateSource = list[0].getElementsByClassName('index_info-col__UVcZb');
var name = nameSource[0].textContent;
var date = dateSource[2].textContent;
row['nqymc'] = name;
row['date'] = date;
}else{
row['nqymc'] = '';
row['date'] = '';
}
}
}
}
createExcel(jsonData);
})
}
function promise_fetch(req_url){
return new Promise(function(resolve, reject){
console.log(req_url);
GM_xmlhttpRequest({
method: "GET",
url: req_url,
//headers: { "Content-Type": "application/x-www-form-urlencoded" },
//responseType: "json",
onload: function(r){
if(r.readyState == 4){
// index_list-wrap___axcs
var result = r.responseXML;
var list = result.getElementsByClassName('index_search-item-center__Q2ai5');
var name = list[0].getElementsByClassName('index_name__qEdWi');
var date = list[0].getElementsByClassName('index_info-col__UVcZb');
var name1 = name[0].textContent;
var buildDate = date[2].textContent;
console.log(name1+" : "+buildDate);
//resolve(r.responseXML);
}
}
});
});
}
//生成Excel文件
function createExcel(data){
let nowTime = timestampToTime()
// 导出的表格名称
const filename =nowTime+'结果.xlsx'
// Excel第一个sheet的名称
const ws_name = 'Sheet1'
const wb = XLSX.utils.book_new()
const ws = XLSX.utils.json_to_sheet(data);
XLSX.utils.book_append_sheet(wb, ws, ws_name) // 将数据添加到工作薄
XLSX.writeFile(wb, filename) // 导出Excel
}
function timestampToTime() {
let d = new Date();
let y = d.getFullYear();
let m = d.getMonth()+1;
let d1 = d.getDate();
let h = d.getHours();
let s = d.getMinutes();
return ""+y + m + d1 + h + s;
}
setTimeout(() => {
document.body.appendChild(input);
document.querySelector('#excel').onchange = function (e) {
let file = e.target.files[0];
readWorkbookFromLocalFile(file, function(workbook) {
readWorkbook(workbook);
});
}
}, 1000);
})();