forked from sheetjs/docs.sheetjs.com
89 lines
2.9 KiB
JavaScript
89 lines
2.9 KiB
JavaScript
|
const Knex = require('knex');
|
||
|
|
const XLSX = require("xlsx");
|
||
|
|
|
||
|
|
/* read file and get first worksheet */
|
||
|
|
const oldwb = XLSX.readFile("pres.numbers");
|
||
|
|
const oldws = oldwb.Sheets[oldwb.SheetNames[0]];
|
||
|
|
|
||
|
|
/* create table and load data given an array of objects and a Knex connection */
|
||
|
|
async function aoo_to_knex_table(knex, aoo, table_name) {
|
||
|
|
/* define types that can be converted (e.g. boolean can be stored in float) */
|
||
|
|
const T_FLOAT = ["float", "boolean"];
|
||
|
|
const T_BOOL = ["boolean"];
|
||
|
|
|
||
|
|
/* types is a map from column headers to Knex schema column type */
|
||
|
|
const types = {};
|
||
|
|
|
||
|
|
/* names is an ordered list of the column header names */
|
||
|
|
const names = [];
|
||
|
|
|
||
|
|
/* loop across each row object */
|
||
|
|
aoo.forEach(row =>
|
||
|
|
/* Object.entries returns a row of [key, value] pairs */
|
||
|
|
Object.entries(row).forEach(([k,v]) => {
|
||
|
|
|
||
|
|
/* If this is first occurrence, mark unknown and append header to names */
|
||
|
|
if(!types[k]) { types[k] = ""; names.push(k); }
|
||
|
|
|
||
|
|
/* skip null and undefined values */
|
||
|
|
if(v == null) return;
|
||
|
|
|
||
|
|
/* check and resolve type */
|
||
|
|
switch(typeof v) {
|
||
|
|
/* change type if it is empty or can be stored in a float */
|
||
|
|
case "number": if(!types[k] || T_FLOAT.includes(types[k])) types[k] = "float"; break;
|
||
|
|
/* change type if it is empty or can be stored in a boolean */
|
||
|
|
case "boolean": if(!types[k] || T_BOOL.includes(types[k])) types[k] = "boolean"; break;
|
||
|
|
/* no other type can hold strings */
|
||
|
|
case "string": types[k] = "text"; break;
|
||
|
|
default: types[k] = "text"; break;
|
||
|
|
}
|
||
|
|
})
|
||
|
|
);
|
||
|
|
|
||
|
|
/* Delete table if it exists in the DB */
|
||
|
|
await knex.schema.dropTableIfExists(table_name);
|
||
|
|
|
||
|
|
/* use column type info to create table */
|
||
|
|
await knex.schema.createTable(table_name, (table) => {
|
||
|
|
names.forEach(h => {
|
||
|
|
/* call schema function e.g. table.text("Name"); table.float("Index"); */
|
||
|
|
table[types[h] || "text"](h);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
/* insert each row */
|
||
|
|
await knex.insert(aoo).into(table_name);
|
||
|
|
return knex;
|
||
|
|
}
|
||
|
|
|
||
|
|
(async() => {
|
||
|
|
/* open connection to SheetJSKnex.db */
|
||
|
|
let knex = Knex({ client: 'better-sqlite3', connection: { filename: "SheetJSKnex.db" }, useNullAsDefault: true });
|
||
|
|
|
||
|
|
try {
|
||
|
|
/* generate array of objects from worksheet */
|
||
|
|
const aoo = XLSX.utils.sheet_to_json(oldws);
|
||
|
|
|
||
|
|
/* create table and load data */
|
||
|
|
await aoo_to_knex_table(knex, aoo, "Test_Table");
|
||
|
|
} finally {
|
||
|
|
/* disconnect */
|
||
|
|
knex.destroy();
|
||
|
|
}
|
||
|
|
|
||
|
|
/* reconnect to SheetJSKnex.db */
|
||
|
|
knex = Knex({ client: 'better-sqlite3', connection: { filename: "SheetJSKnex.db" }, useNullAsDefault: true });
|
||
|
|
|
||
|
|
try {
|
||
|
|
/* get data from db */
|
||
|
|
const aoo = await knex.select("*").from("Test_Table");
|
||
|
|
|
||
|
|
/* export to file */
|
||
|
|
const newws = XLSX.utils.json_to_sheet(aoo);
|
||
|
|
const newwb = XLSX.utils.book_new();
|
||
|
|
XLSX.utils.book_append_sheet(newwb, newws, "Export");
|
||
|
|
XLSX.writeFile(newwb, "SheetJSKnex.xlsx");
|
||
|
|
} finally { knex.destroy(); }
|
||
|
|
})();
|