docs.sheetjs.com/docz/static/knex/SheetJSKnexTest.js

89 lines
2.9 KiB
JavaScript
Raw Permalink Normal View History

2023-09-24 03:59:48 +00:00
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(); }
})();