Skip to content

Instantly share code, notes, and snippets.

@sincerefly

sincerefly/index.html

Last active April 7, 2020 05:50
Show Gist options
  • Save sincerefly/26144840d04f6d1c725be6c394866fd1 to your computer and use it in GitHub Desktop.
Save sincerefly/26144840d04f6d1c725be6c394866fd1 to your computer and use it in GitHub Desktop.
Download ZIP
Electron文件拖拽与打开文件管理器选择文件路径，导出SQLite3数据库中的数据到Excel 运行效果与说明：https://blog.yasking.org/a/electron-drag-open-file-demo.html
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>数据导出工具 V1.0.2 </title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div class="date-select">
<div style="padding-top: 5px;">
<span>起始时间:</span>
<input id="datetime_start" type="datetime-local" value=""/>
<span>结束时间:</span>
<input id="datetime_end" type="datetime-local" value=""/>
</div>
</div>
<div id="holder" class="dropify-wrapper">
<div id="message" class="dropify-message">
点击或将文件拖拽到此区域处理
</div>
</div>
<script src="process.js"></script>
<script>
var today_date = new Date().Format("yyyy-MM-ddT00:00:01");
var now_date = new Date().Format("yyyy-MM-ddThh:mm:ss");
console.log(today_date)
console.log(now_date)
document.getElementById('datetime_start').value = today_date;
document.getElementById('datetime_end').value = now_date;
</script>
<script>
// You can also require other files to run in this process
require('./renderer.js')
// 拖拽文件到窗口事件
const holder = document.getElementById('holder')
holder.ondragover = () => {
return false;
}
holder.ondragleave = holder.ondragend = () => {
return false;
}
holder.ondrop = (e) => {
e.preventDefault()
for (let f of e.dataTransfer.files) {
console.log('File(s) you dragged here: ', f.path)
document.getElementById("message").innerHTML = "已选择文件：" + f.path + "<br><br>" + "正在导出数据请稍候...";
var filepath = f.path;
start_process(filepath);
}
return false;
}
// 点击鼠标打开文件管理器
function showOpenDialogHandler() {
var options = {
defaultPath: 'D:\\service-data',
filters: [
{ name: 'Database', extensions: ['db'] }
],
properties: ['openFile']
}
dialog.showOpenDialog(options, (fileNames) => {
// fileNames is an array that contains all the selected
if(fileNames === undefined){
console.log("No file selected");
return;
} else {
console.log(fileNames[0]);
document.getElementById("message").innerHTML = "已选择文件：" + fileNames[0] + "<br><br>" + "正在导出数据请稍候...";
var filepath = fileNames[0];
}
start_process(filepath);
});
}
const {dialog} = require('electron').remote;
const fileManagerBtn = document.getElementById('holder')
fileManagerBtn.addEventListener('click', showOpenDialogHandler)
</script>
</body>
</html>
Raw
package.json
{
"name": "export-tool",
"version": "1.0.2",
"description": "数据导出工具",
"main": "main.js",
"scripts": {
"start": "electron .",
"install-sqlite3": "npm install sqlite3 --build-from-source --runtime=electron --target=1.8.2 --dist-url=https://atom.io/download/electron",
"build": "electron-builder --win --ia32",
"release": "build"
},
"author": "dongdong",
"license": "CC0-1.0",
"build": {
"productName": "数据导出工具",
"compression": "normal",
"electronVersion": "1.8.2",
"directories": {
"output": "./elect_builder_output"
},
"win": {
"target": ["portable"],
"icon": "ico.ico",
"legalTrademarks": "HyLink"
}
},
"devDependencies": {
"electron": "1.8.2",
"electron-builder": "20.2.1",
"electron-rebuild": "1.7.3"
},
"dependencies": {
"node-xlsx": "0.11.2"
}
}
Raw
process.js
// select all data from sqlite3 db file
/**
*对Date的扩展，将 Date 转化为指定格式的String
*月(M)、日(d)、小时(h)、分(m)、秒(s)、季度(q) 可以用 1-2 个占位符，
*年(y)可以用 1-4 个占位符，毫秒(S)只能用 1 个占位符(是 1-3 位的数字)
*例子：
*(new Date()).Format("yyyy-MM-dd hh:mm:ss.S") ==> 2006-07-02 08:09:04.423
*(new Date()).Format("yyyy-M-d h:m:s.S") ==> 2006-7-2 8:9:4.18
*/
Date.prototype.Format = function (fmt) { // author: meizz
var o = {
"M+": this.getMonth() + 1, // 月份
"d+": this.getDate(), // 日
"h+": this.getHours(), // 小时
"m+": this.getMinutes(), // 分
"s+": this.getSeconds(), // 秒
"q+": Math.floor((this.getMonth() + 3) / 3), // 季度
"S": this.getMilliseconds() // 毫秒
};
if (/(y+)/.test(fmt))
fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
function prepend(arr, item) {
return [item].concat(arr);
}
function load_data_from_sqlite(db_filepath) {
var data_all = [];
var data_linkong = [];
var start_at = document.getElementById('datetime_start').value.replace('T', ' ')
var end_at = document.getElementById('datetime_end').value.replace('T', ' ')
console.log(start_at)
console.log(end_at)
var get_result = function(callback){
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(db_filepath);
db.serialize(function() {
query_string = "SELECT * FROM collectDataTable where createdAt >= datetime('" + start_at +"') and createdAt < datetime('" + end_at + "')"
console.log(query_string)
db.all(query_string, function(err, res) {
callback(res);
});
});
db.close();
}
get_result(function(data){
var length = data.length;
for (var i=0; i < length; i++) {
row = data[i]
var name = JSON.parse(row['data'])['idcardInfo']['name'];
var idcard = JSON.parse(row['data'])['idcardInfo']['idcard'];
var create_at = row['createdAt'].split('.')[0];
var location_name = JSON.parse(row['data'])['locationInfo']['locationName'];
var state = JSON.parse(row['data'])['idcardCompareInfo']['result']['tags'][0]['property'];
console.log(name+" "+idcard+" "+create_at+" "+location_name);
data_all.push([name, idcard, create_at, location_name])
if (state == "111001") {
data_linkong.push([name, idcard, create_at, location_name, '临控人员'])
}
}
console.log(data_all, data_linkong)
save_to_xlsx(data_all, data_linkong)
})
}
function save_to_xlsx(data_all, data_linkong) {
let xlsx = require('node-xlsx'),
fs = require('fs');
// const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
var da = prepend(data_all, ["姓名", "年龄", "采集时间", "性别"])
var dl = prepend(data_linkong, ["姓名", "年龄", "采集时间", "性别"] "人员属性"])
var buffer = xlsx.build([{name: "所有人员", data: da}, {name: "临时表", data: dl}]);
// var now_time = new Date().Format("yyyy-MM-dd hh-mm-ss");
var start_at = document.getElementById('datetime_start').value.replace('T', ' ').split(':').join('-')
var end_at = document.getElementById('datetime_end').value.replace('T', ' ').split(':').join('-')
fs.writeFileSync('C:/Users/Administrator/Desktop/导出数据-['+start_at+']-['+end_at+'].xlsx', buffer);
setTimeout(function(){
document.getElementById("message").innerHTML = "数据导出成功"
}, 1000);
}
function start_process(filepath) {
load_data_from_sqlite(filepath)
}
Raw
style.css
.dropify-wrapper {
/* height: 200px; */
height: 190px;
max-width: 100%;
font-size: 14px;
padding: 20px 10px;
color: #777;
background-color: #FFF;
text-align: center;
border: 2px dashed #E5E5E5;
}
.dropify-message {
position: relative;
top: 42%;
}
.date-select {
padding-bottom: 10px;
}
span {
font-size: 14px;
padding: 5px 10px;
color: #777;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment