workbook.SheetNames.every(function(sheetName) {
sheet = workbook.Sheets[sheetName];
if(!sheet || !sheet["!ref"])
return res.send('sheet error');
range = xlsx.utils.decode_range(sheet["!ref"]);
var startRow = range.s.r;
var startCol = range.s.c;
var endCol = range.e.c;
var counter = config.offset;
if(maxrow + startRow > 1000000){
console.log('empty sheet');
return false;
}
var sheetRange = {
startRow: startRow,
startCol: startCol,
endCol: endCol
}
var keyJSON = xlsxParser.getHeader(sheetRange, config.offset, sheet);
sheetRange.startRow = sheetRange.startRow + 1;
insertingData(sheetRange, config.offset, keyJSON, sheet, counter, maxrow, function(){
return res.send('insertion completed');
});
return true;
});
module.exports = function (inputExcelFile, modelInfoSheetName, modelMetadataSheetName, modelsConfigFile) {
let workbook = XLSX.readFile(inputExcelFile)
let modelInfo = XLSX.utils.sheet_to_json(workbook.Sheets[modelInfoSheetName])
let modelMetadata = XLSX.utils.sheet_to_json(workbook.Sheets[modelMetadataSheetName])
modelInfo = _.chain(modelInfo)
.sortBy(value => value['Name'])
.reject(value => value.Ignore)
.groupBy(value => value['Name'])
.mapValues((value, key) => {
let metadata = _.merge({}, _.find(modelMetadata, value => value.Model === key))
return {
dataSource: metadata.DataSource ? metadata.DataSource : 'transient',
public: _.lowerCase(metadata.Public) === 'true'
}
})
.merge({
'_meta': {
'sources': ['./models'],
'mixins': ['./mixins']
}
})
.value()
// write to model-config file
jsonfile.writeFileSync(modelsConfigFile, modelInfo)
}
exportFile() {
/* convert AOA back to worksheet */
const ws = XLSX.utils.aoa_to_sheet(this.state.data);
/* build new workbook */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "SheetJS");
/* write file */
const wbout = XLSX.write(wb, {type:'binary', bookType:"xlsx"});
const file = DDP + "sheetjsw.xlsx";
writeFile(file, output(wbout), 'ascii').then((res) =>{
Alert.alert("exportFile success", "Exported to " + file);
}).catch((err) => { Alert.alert("exportFile Error", "Error " + err.message); });
};
function sheet_from_array_of_arrays(data) {
var ws = {};
var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0}};
for(var R = 0; R !== data.length; ++R) {
for(var C = 0; C !== data[R].length; ++C) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v === null) continue;
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
/**
* 格式化表格数据
* @param data
* @param merges 如果设置了merges，则使用 merges，否则根据数据自动生成 TODO 自动生成待实现
* @returns {{}}
*/
function parseCellData(data, merges, cols) {
var ws = {};
var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
for (var R = 0; R !== data.length; ++R) {
for (var C = 0; C !== data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = {v: data[R][C]};
if (cell.v === null) continue;
var cellRef = xlsx.utils.encode_cell({c: C, r: R});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = xlsx.SSF._table[14];
cell.v = dateNum(cell.v);
}
else cell.t = 's';
ws[cellRef] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = xlsx.utils.encode_range(range);
ws['!merges'] = merges;
//TODO: 设置列宽待实现
//if (cols) {
// ws['!cols'] = cols;
//}
return ws;
}
function sheetFromArrayOfArrays(data, opts) {
var ws = {};
var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(var R = 0; R != data.length; ++R) {
for(var C = 0; C != data[R].length; ++C) {
if(range.s.r > R) { range.s.r = R; }
if(range.s.c > C) { range.s.c = C; }
if(range.e.r < R) { range.e.r = R; }
if(range.e.c < C) { range.e.c = C; }
var cell = {v: data[R][C] };
if(cell.v == null) { continue; }
var cell_ref = xlsx.utils.encode_cell({c:C,r:R});
if(typeof cell.v === 'number') { cell.t = 'n'; }
else if(typeof cell.v === 'boolean') { cell.t = 'b'; }
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = xlsx.SSF._table[14];
cell.v = datenum(cell.v);
}
else {
cell.t = 's';
}
ws[cell_ref] = cell;
}
}
if(range.s.c < 10000000) {
ws['!ref'] = xlsx.utils.encode_range(range);
}
return ws;
}
//returns a
function getTeamPayload(excelBook, teamNumber){
var ws = excelBook.Sheets["" + teamNumber];
var range = xlsx.utils.decode_range(ws["!ref"]);
var payload = [];
var keySet = genKeySet(matchSchema);
for(var row = 2; row <= range.e.r; row++){
var currentPos = 0;
for(var col = 0; col <= range.e.c; col++){
found = false;
var cellRef = xlsx.utils.encode_cell({r: row, c: col});
var cell = ws[cellRef];
//console.log(keySet[currentPos]);
if(typeof cell === 'undefined'){
setValue(matchSchema, keySet[currentPos], 'undefined');
currentPos++;
} else if(typeof cell !== 'undefined' && cell.t === 'n'){
//console.log(keySet[currentPos] + ' : ' + cell.v);
setValue(matchSchema, keySet[currentPos], cell.v);
currentPos++;
} else if(typeof cell !== 'undefined' && cell.t === 's'){
//console.log(keySet[currentPos] + ' : ' + cell.v);
setValue(matchSchema, keySet[currentPos], true);
currentPos++;
}
}
//console.log(matchSchema);
payload.push(JSON.parse(JSON.stringify(matchSchema)));
setDefault(matchSchema);
}
//console.log(payload)
return payload;
}
function sheet_from_array_of_json(data) {
var ws = {};
var range = {s: {c:0, r:0}, e: {c:0, r:0 }};
for(var R = 0; R != data.length; ++R) {
if(range.e.r < R) range.e.r = R;
for(var C = 0; C != data[R].length; ++C) {
if(range.e.c < C) range.e.c = C;
/* create cell object: .v is the actual data */
var cell = { v: data[R][C] };
if(cell.v == null) continue;
/* create the correct cell reference */
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
/* determine the cell type */
if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else cell.t = 's';
/* add to structure */
ws[cell_ref] = cell;
}
}
ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
_toSheet(data, opts) {
var worksheet = {};
var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(var R = 0; R != data.length; ++R) {
for(var C = 0; C != data[R].length; ++C) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
if(jQuery.isNumeric(cell.v)) cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = XLSX.SSF._table[14];
cell.v = this._datenum(cell.v);
}
else cell.t = 's';
worksheet[cell_ref] = cell;
}
}
if(range.s.c < 10000000) worksheet['!ref'] = XLSX.utils.encode_range(range);
return worksheet;
}
let getCell = function (idMap, idx) {
let result = {'err': -1, 'sheet': '', 'cell': {}}
if (idx.charAt(0) === '#') {
let _addr = idx.split(/\((-?\d*),(-?\d*)\)/)
let _idx = idMap[_addr[0].slice(1)]
let _tmp = _idx.split('.')
let cell = XLSX.utils.decode_cell(_tmp[1])
if (_addr[1]) { cell.c += parseInt(_addr[1]) }
if (_addr[2]) { cell.r += parseInt(_addr[2]) }
result = {
'err': 0,
'sheet': _tmp[0],
'cell': cell
}
}
else {
let _tmp = idx.split('.')
result = {
'err': 0,
'sheet': _tmp[0],
'cell': XLSX.utils.decode_cell(_tmp[1])
}
}
return result
}
const makeExcelFile = (data, sheetName = 'Sheet 1') => {
let workbook = { Sheets: {}, Props: {}, SSF: {}, SheetNames: [] }
let workbookSheet = {}
let range = { s: {c:0, r:0}, e: {c:0, r:0} }
for (let r = 0; r != data.length; ++r) {
if (range.e.r < r) range.e.r = r
for (let c = 0; c != data[r].length; ++c) {
if (range.e.c < c) range.e.c = c
const v = data[r][c]
const isnum = typeof v === 'number'
const isbool = typeof v === 'boolean'
const t = isnum ? 'n' : (isbool ? 'b' : 's')
const cell = { v, t }
if (cell.v == null) continue
const cell_ref = XLSX.utils.encode_cell({ c, r })
workbookSheet[cell_ref] = cell
}
}
workbookSheet['!ref'] = XLSX.utils.encode_range(range)
workbook.SheetNames.push(sheetName)
workbook.Sheets[sheetName] = workbookSheet
const type = 'base64'
return XLSX.write(workbook, { type })
}
function readExcel(excel) {
var workbook = xlsx.readFile('./' + excel[0].path);
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = 'A1';
/* Get worksheet */
var worksheet = workbook.Sheets[first_sheet_name];
/* Find desired cell */
var desired_cell = worksheet[address_of_cell];
/* Get the value */
var desired_value = desired_cell.v;
console.log(desired_value);
console.log(xlsx.utils.sheet_to_json(worksheet));
var json = xlsx.utils.sheet_to_json(worksheet);
var p;
for (p in json) {
console.log(json[p]);
console.log(json[p].product);
console.log(json[p].price);
}
};
async function book_append_mongo(wb, coll, name) {
const aoo = await coll.find({}).toArray();
aoo.forEach((x) => delete x._id);
const ws = XLSX.utils.json_to_sheet(aoo);
XLSX.utils.book_append_sheet(wb, ws, name);
return ws;
}
function prepforsexql(ws, sname, db) {
/* Get sheet range */
if(!ws || !ws['!ref']) return;
var range = XLSX.utils.decode_range(ws['!ref']);
if(!range || !range.s || !range.e || range.s > range.e) return;
var R = range.s.r, C = range.s.c;
/* Generate headers */
var names = new Array(range.e.c-range.s.c+1);
for(C = range.s.c; C<= range.e.c; ++C){
var addr = XLSX.utils.encode_cell({c:C,r:R});
names[C-range.s.c] = ws[addr] ? ws[addr].v : XLSX.utils.encode_col(C);
}
/* De-duplicate headers */
for(var i = 0; i < names.length; ++i) if(names.indexOf(names[i]) < i)
for(var j = 0; j < names.length; ++j) {
var _name = names[i] + "_" + (j+1);
if(names.indexOf(_name) > -1) continue;
names[i] = _name;
}
/* Guess column types */
var types = new Array(range.e.c-range.s.c+1);
for(C = range.s.c; C<= range.e.c; ++C) {
var seen = {}, _type = "";
for(R = range.s.r+1; R<= range.e.r; ++R) seen[(ws[XLSX.utils.encode_cell({c:C,r:R})]||{t:"z"}).t] = true;
if(seen.s || seen.str) _type = "TEXT";
else if(seen.n + seen.b + seen.d + seen.e > 1) _type = "TEXT";
else switch(true) {
case seen.b:
case seen.n: _type = "REAL"; break;
case seen.e: _type = "TEXT"; break;
case seen.d: _type = "TEXT"; break;
}
types[C-range.s.c] = _type || "TEXT";
}
/* Create table */
log_and_exec(db, "DROP TABLE IF EXISTS `" + sname + "`;" );
log_and_exec(db, "CREATE TABLE `" + sname + "` (" + names.map(function(n, i) { return "`" + n + "` " + (types[i]||"TEXT"); }).join(", ") + ");" );
/* Insert data */
for(R = range.s.r+1; R<= range.e.r; ++R) {
var fields = [], values = [];
for(var C = range.s.c; C<= range.e.c; ++C) {
var cell = ws[XLSX.utils.encode_cell({c:C,r:R})];
if(!cell) continue;
fields.push("`" + names[C-range.s.c] + "`");
var val = cell.v;
switch(types[C-range.s.c]) {
case 'REAL': if(cell.t == 'b' || typeof val == 'boolean' ) val = +val; break;
default: val = '"' + val.toString().replace(/"/g, '""') + '"';
}
values.push(val);
}
if(fields.length > 0) log_and_exec(db, "INSERT INTO `" +sname+ "` (" + fields.join(", ") + ") VALUES (" + values.join(",") + ");");
}
}
//credits: https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js (a bit modified)
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cellData = data[R][C];
var cell;
var cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (_.str.startsWith(cellData, "=")) {
//TODO: update xlsx lib when updated with this feature:
//now formula only works since I edited the current module code (node_modules/xlsx/xlsx.js) andd added this feature:
// https://github.com/christocracy/js-xlsx/commit/45f9e0198c10086f03dac000c09f24fe18bbd5d8
//details: https://github.com/SheetJS/js-xlsx/pull/103
cell = {
//f: _.str.strRight(cellData, '='),
f: cellData,
t: 'n'
};
} else {
cell = {
v: cellData
};
if (cell.v === null) continue;
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
}
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
const getHeaderRow = sheet => {
const headers = []
const range = xlsx.utils.decode_range(sheet['!ref'])
let C
const R = range.s.r
for (C = range.s.c; C <= range.e.c; ++C) {
var cell = sheet[xlsx.utils.encode_cell({ c: C, r: R })]
var hdr = 'UNKNOWN ' + C
if (cell && cell.t) hdr = xlsx.utils.format_cell(cell)
headers.push(hdr)
}
return headers
}
module.exports = (opt, columns, moduleCallback) => {
//file locations
let dt = new Date()
let dir = dt.getFullYear() + '-' + ('0' + (Number(dt.getMonth()) + 1).toString()).slice(-2) + '-' + ('0' + dt.getDate()).slice(-2)
const table = opt.source + '.' + opt.table
const mkdirp = require('mkdirp')
const outputFile = `${opt.cfg.dirs.output}xlsx/${dir}/${table}.xlsx`
const XLSX = require('xlsx')
XLSX.cellDates = true
//read tsv file
let wb = XLSX.readFile(opt.opfile.filename, {
FS: '\t',
cellDates: true
})
let worksheet = wb.Sheets['Sheet1']
//get header row
let headers = []
let range = XLSX.utils.decode_range(worksheet['!ref'])
let C
let R = range.s.r /* start in the first row */
//loop through first row
for (C = range.s.c; C <= range.e.c; ++C) {
let v = XLSX.utils.encode_cell({
c: C,
r: R
})
let cell = worksheet[v]
let hdr = 'UNKNOWN ' + C
if (cell && cell.t) {
worksheet[v].v = worksheet[v].v.replace(/_IND|_DEC/, '')
hdr = XLSX.utils.format_cell(cell)
}
headers.push(hdr)
}
//mkdirp and write file
mkdirp(opt.cfg.dirs.output + 'xlsx/' + dir, (e) => {
if (e) return moduleCallback(e)
XLSX.writeFile(wb, outputFile)
moduleCallback(null, range.e.r, headers)
})
}
/**
* 获取 sheet 中指定 range 的 行列
*/
function getArrayOfArrayByRange(sheet, rawRange) {
rawRange = rawRange || sheet['!ref'];
var arrOfArr = [];
var range = XLSX.utils.decode_range(rawRange);
for (var row = range.s.r; row <= range.e.r; row += 1) {
var rowArr = []
for (var col = range.s.c; col <= range.e.c; col += 1) {
var addr = XLSX.utils.encode_cell({ c: col, r: row});
var value = sheet[addr] && sheet[addr].v; // sheet[addr] may be undefined
rowArr.push(value);
}
arrOfArr.push(rowArr);
}
return arrOfArr;
}
function _json_to_exel(jsonData, header) {
//return XLSX.utils.json_to_sheet(jsonData, {header: header});
let ws = XLSX.utils.json_to_sheet(jsonData, {
header: header
})
let wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "data");
const wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'buffer'
};
let buffer = XLSX.write(wb, wopts);
return buffer;
}
exports.parse2json = function(xlsx, sheet_name, schemaNeedHeadLine) {
var content = XLSX.utils.sheet_to_row_object_array(xlsx.Sheets[sheet_name]);
var ret = [];
var headLine = [];
var sheet = xlsx.Sheets[sheet_name];
var range = XLSX.utils.decode_range(sheet["!ref"]);
for (var i = range.s.c; i <= range.e.c; i++) {
var box_name = XLSX.utils.encode_cell({
c: i,
r: 0
});
if (sheet[box_name]) {
headLine.push(sheet[box_name].v);
}
}
for (var i = 0; i < content.length; i++) {
var tmpObj = {};
for (var j = 0; j < schemaNeedHeadLine.length; j++) {
if (schemaNeedHeadLine[j].type == 'int') {
if (typeof content[i][schemaNeedHeadLine[j].name] == 'number') {
tmpObj[schemaNeedHeadLine[j].name] = Math.round(content[i][schemaNeedHeadLine[j].name]);
}
}
else if (schemaNeedHeadLine[j].type == 'array') {
tmpObj[schemaNeedHeadLine[j].name] = [];
for (var k = 0; k < headLine.length; k++) {
if (headLine[k].indexOf(schemaNeedHeadLine[j].name) > -1) {
if (content[i][headLine[k]]) {
tmpObj[schemaNeedHeadLine[j].name].push(content[i][headLine[k]]);
}
else {
tmpObj[schemaNeedHeadLine[j].name].push(null);
}
}
}
}
else {
if (content[i][schemaNeedHeadLine[j].name]) {
tmpObj[schemaNeedHeadLine[j].name] = content[i][schemaNeedHeadLine[j].name];
}
}
}
ret.push(tmpObj);
}
return ret;
};
P = P.then(async ([client, pres, fmts]) => {
const wb = XLSX.utils.book_new();
await SheetJSMongo.book_append_mongo(wb, pres, "pres");
await SheetJSMongo.book_append_mongo(wb, fmts, "fmts");
XLSX.writeFile(wb, "mongocrud.xlsx");
return [client, pres, fmts];
});
sheet_name_list.forEach(function(sheetName) {
console.log("reading row...");
var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
if(roa.length > 0){
result[sheetName] = roa;
}
});
_.each(workbook.SheetNames, function(sheetName) {
var rObjArr = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName],{raw:true});
rObjArr = util.removeEmptyStrings(rObjArr);
if(rObjArr.length > 0){
result[sheetName] = rObjArr;
}
});
result.format.forEach(function(cell){
var cell_ref = xlsx.utils.encode_cell({c:cell.col, r:cell.row});
if(!ws[cell_ref])
ws[cell_ref]={v:''};
if(!ws[cell_ref].s)
ws[cell_ref].s={};
if(cell.key === 'bold')
if(ws[cell_ref].s.font)
ws[cell_ref].s.font.bold = true;
else
ws[cell_ref].s.font={bold: true};
if(cell.key === 'italic')
if(ws[cell_ref].s.font)
ws[cell_ref].s.font.italic = true;
else
ws[cell_ref].s.font={italic: true};
if(cell.key === 'borders')
{
ws[cell_ref].s.border={};
if (cell.value && cell.value.top && cell.value.top.width)
ws[cell_ref].s.border.top={style: 'thin'};
if(cell.value && cell.value.left && cell.value.left.width)
ws[cell_ref].s.border.left={style: 'thin'};
if(cell.value && cell.value.bottom && cell.value.bottom.width)
ws[cell_ref].s.border.bottom={style: 'thin'};
if(cell.value && cell.value.right && cell.value.right.width)
ws[cell_ref].s.border.right={style: 'thin'};
}
if(cell.key === 'color')
{
ws[cell_ref].s.fill={};
ws[cell_ref].s.fill.fgColor=cell.value;
}
});
this.parse = function(worksheet,range){
for(var R = range.s.r;R<=range.e.r;R++){
var address = XLSX.utils.encode_cell({c:range.s.c, r:R});
var cell = worksheet[address];
this.headers.push(cell.w.trim());
}
for(var C = range.s.c+1; C <= range.e.c; C++){
var record = {};
for(var R = range.s.r;R<=range.e.r;R++){
var address = XLSX.utils.encode_cell({c:C, r:R});
var cell = worksheet[address];
record[this.headers[R-range.s.r]] = cell.w.trim();
}
this.records.push(record);
}
}
dataSet.forEach(dataSetItem => {
var columns = dataSetItem.columns;
var xSteps = typeof(dataSetItem.xSteps) === 'number' ? dataSetItem.xSteps : 0;
var ySteps = typeof(dataSetItem.ySteps) === 'number' ? dataSetItem.ySteps : 0;
var data = dataSetItem.data;
if (dataSet === undefined || dataSet.length === 0) {
return;
}
rowCount += ySteps;
if (columns.length >= 0) {
columns.forEach((col, index) => {
var cellRef = XLSX.utils.encode_cell({c: xSteps + index, r: rowCount});
fixRange(range, 0, 0, rowCount, xSteps, ySteps);
getHeaderCell(col, cellRef, ws);
});
rowCount += 1;
}
for (var R = 0; R != data.length; ++R, rowCount++) {
for (var C = 0; C != data[R].length; ++C) {
var cellRef = XLSX.utils.encode_cell({c: C + xSteps, r: rowCount});
fixRange(range, R, C, rowCount, xSteps, ySteps);
getCell(data[R][C], cellRef, ws);
}
}
});
form.parse(req, function(err, fields, files) {
if (err) {
res.send("There was an error in processing your request.")
}
var brand = require('./maps/' + fields.brand + '.js')
var parts = []
switch (files.loadsheets[0].originalFilename.slice(-4).toUpperCase()) {
case "XLSX":
var loadsheet = XLSX.readFile(files.loadsheets[0].path)
parts = XLSX.utils.sheet_to_row_object_array(loadsheet.Sheets[brand.tab])
brand.transform(parts, res)
break
case ".XLS":
var loadsheet = XLS.readFile(files.loadsheets[0].path)
parts = XLS.utils.sheet_to_row_object_array(loadsheet.Sheets[brand.tab])
brand.transform(parts, res)
break
case ".TXT":
case ".CSV":
csv()
.from.path(files.loadsheets[0].path, {columns: true})
.to.array(function(data) {
brand.transform(data, res)
})
break
default:
res.send("Unknown file format.")
}
})
exports.validateExcel = function(type, excelPath, strict){
if(strict == undefined)
strict = true;
type = type || 'series';
var expectedColumns = excelTemplate[type];
var workbook = XLSX.readFile(excelPath);
var first_sheet_name = workbook.SheetNames[0];
var rows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[first_sheet_name]);
var match = false;
var columns = Object.keys(rows[0]);
if(strict){
if(expectedColumns.length == columns.length){
expectedColumns.forEach(function(column,index){
if((index === 0 || (index > 0 && match)) && column == columns[index]) {
match = true;
} else {
match = false;
}
});
}
} else {
match = expectedColumns.length == _.intersection(columns, expectedColumns).length;
}
return match;
}
req.file('dataFile').upload('./.tmp/export.xlsx', function onUploadComplete(err, uploadedFiles) {
if(err) return res.serverError(err);
var workbook = xlsx.readFile('./.tmp/export.xlsx');
var sheet = workbook.Sheets['Sheet1'];
data = xlsx.utils.sheet_to_row_object_array(sheet);
importdata = _(data).map(function(researcher) {
return {
lastname: researcher['Achternaam'],
position: researcher['Medewerkerssubgroep'],
researchid: researcher['Pers.nr.'],
wbselements: _(data).where({'Pers.nr.': researcher['Pers.nr.']}).pluck('WBS-element').value()
}
}).uniq('researchid');
_(importdata).each(function(researcher) {
Researcher.findOne({researchid: researcher['researchid']}).exec(function(err, user) {
if(user === undefined) {
Researcher.create(researcher).exec(function(err, created) {
if(err) {
console.log(err);
}
if(created) {
console.log("Created a user");
}
});
}
else {
console.log("User already found, not creating");
}
});
});
});
/**
* Find the cells of title.
*
* @method findTitleCells
*
* @param {Object} worksheet
* @param {Object} range
* @param {Object} titleCell
* @param {Object} ignoreCells
*
* @return {Array} cells The cells of title.
*/
function findTitleCells(worksheet, range, titleCell, ignoreCells) {
var cells = [];
var ir = titleCell.r;
for (var ic = range.s.c; ic <= range.e.c; ++ic) {
var cell_address = {
c: ic,
r: ir
};
z = XLSX.utils.encode_cell(cell_address);
if (z == titleCell.z) {
continue;
}
var cell = factoryCell(cell_address, z, worksheet[z]);
// TODO: check the value is valid or not (only alphabet)
// check the cell is need to ignore or not
if (isNeedIgnore(cell, ignoreCells)) {
continue;
}
if (titleCell && cell.v && (cell.r == titleCell.r)) {
cells[ic] = cell;
}
}
return cells;
}