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.readExcel = function(req, res) {
var data;
try {
var workbook = XLS.readFile(req.files.file.path);
data = XLS.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
} catch (e) {
res.json({
err: ERR.IMPORT_FAILURE,
msg: '导入失败',
detail: e.message
});
return null;
}
if (!data.length) {
res.json({
err: ERR.IMPORT_FAILURE,
msg: '没有数据要导入'
});
return null;
}
return data;
};
function csv (sheet, next) {
var data = X.utils.make_csv(sheet, {FS: ',', RS: "\n"});
data = data.split("\n");
var name = sheet.name.replace(/ +/g, '-');
var mediate = es.readArray(data);
mediate.name = name;
next(null, mediate);
}
reporter.beforeRenderListeners.add(definition.name, this, function(req, res) {
if (req.files && req.files.file) {
var file = req.files.file;
if (path.extname(file.path) === ".xls") {
//TODO async!!!!
var xls = XLS.readFile(file.path);
var data = XLS.utils.sheet_to_row_object_array(xls.Sheets.Sheet1);
req.data = { rows: [] };
data.forEach(function(r) {
var item = {};
for (var key in r) {
if (r.hasOwnProperty(key))
item[key] = r[key];
}
req.data.rows.push(item);
});
}
}
});
grunt.registerMultiTask('dataImport', 'Import the data from xls', function() {
var path = require('path');
var fs = require('fs');
var xls = require('xlsjs');
var enums = require('../enums');
grunt.log.write('\n Importing data ...\n');
fs.mkdir('tmp');
// helper function
function getJsonValue(key, value, indentation) {
var safeValue = value.replace('\'', '\\\'');
if(!indentation) indentation = '';
if(!isNaN(value)) {
return indentation+'\''+key+'\': '+safeValue+',';
}
return indentation+'\''+key+'\': \''+safeValue+'\',';
};
// Open items
var dataRoot = 'assets/data/';
var destFile = 'src/data/items.js';
var sourceFiles = {
'buildings.xls': [['category', 'gearBuilding'], ['gearType', 'building']],
'components.xls': [['category', 'component']],
'gear - chest.xls': [['category', 'gearChest'], ['gearType', 'chest']],
'gear - feet.xls': [['category', 'gearFeet'], ['gearType', 'feet']],
'gear - head.xls': [['category', 'gearHead'], ['gearType', 'head']],
'gear - legs.xls': [['category', 'gearLegs'], ['gearType', 'legs']],
'gear - weapons.xls': [['category', 'gearMainHand'], ['gearType', 'mainHand']],
'gear - picks.xls': [['category', 'gearMainHand'], ['gearType', 'miningGear']],
'gems.xls': [['category', 'gem']],
'materials.xls': [['category', 'rawMaterial']],
'potions.xls': [['category', 'usable']],
'scavenge.xls': [['category', 'scavenge']]
};
var importData = [];
importData.push('// ------------------------------------------------------------------------');
importData.push('// AUTO-GENERATED, DO NOT EDIT MANUALLY');
importData.push('// Generated @ '+new Date().toString());
importData.push('// ------------------------------------------------------------------------');
importData.push('');
importData.push('Items = {');
for(var sourceFile in sourceFiles) {
var staticData = sourceFiles[sourceFile];
var workbook = xls.readFile(dataRoot + sourceFile);
var sheetNames = workbook.SheetNames;
var sheet = xls.utils.sheet_to_row_object_array(workbook.Sheets[sheetNames[0]]);
grunt.log.write('\nImporting: '+sourceFile+'\n');
importData.push('\t// -------------------------------------------');
importData.push('\t// '+sourceFile);
importData.push('\t// -------------------------------------------');
for(var r = 0; r < sheet.length; r++) {
grunt.log.write('.');
var row = sheet[r];
var craftCost = [];
var currentCraftCostEntry = undefined;
// Todo: data checks, crafting etc and proper formatting, this is just a test!
importData.push('\t\''+row['id']+'\': {');
for(var i = 0; i < staticData.length; i++) {
importData.push(getJsonValue(staticData[i][0], staticData[i][1], '\t\t'));
}
for(column in row) {
var value = row[column];
column = column.toLowerCase();
// Skip underscore columns and some other special ones
if(column[0] == '_') {
continue;
}
if(column.indexOf('craft') == 0) {
// Todo: Crafting cost
if(column[column.length - 1] == '#') {
if(!value || value == '') {
// No crafting info set
continue;
}
if(!currentCraftCostEntry) {
grunt.log.write('\n Invalid crafting data in XLS: '+sourceFile+' -> '+row['id']+'\n');
continue;
}
currentCraftCostEntry.push(value);
craftCost.push(currentCraftCostEntry);
currentCraftCostEntry = undefined;
} else {
currentCraftCostEntry = [value];
}
continue;
}
if(column == 'type') {
// Todo: use the enums value
continue;
}
importData.push(getJsonValue(column, row[column], '\t\t'));
}
if(craftCost.length > 0) {
importData.push('\t\t\'craftCost\': {');
for(var n = 0; n < craftCost.length; n++) {
importData.push(getJsonValue(craftCost[n][0], craftCost[n][1], '\t\t\t'));
}
importData.push('\t\t},');
}
importData.push('\t},');
}
importData.push('');
}
importData.push('}');
var fd = fs.openSync(destFile, 'w');
fs.write(fd, importData.join('\n'));
fs.close(fd);
});
CV.prototype.csv = function(ws) {
return csv_file = xlsjs.utils.make_csv(ws)
}
function importData(sourceFiles, destFile, sectionKey) {
var importData = [];
writeHeader(importData);
importData.push((sectionKey || 'Items') + ' = {');
for(var sourceFile in sourceFiles) {
var staticData = sourceFiles[sourceFile];
var workbook = xls.readFile(dataRoot + sourceFile);
var sheetNames = workbook.SheetNames;
var sheet = xls.utils.sheet_to_row_object_array(workbook.Sheets[sheetNames[0]]);
grunt.log.write('\nImporting: '+sourceFile+'\n');
writeSectionHeader(importData, sourceFile);
for(var r = 0; r < sheet.length; r++) {
grunt.log.write('.');
var row = sheet[r];
var craftCost = [];
var currentCraftCostEntry = undefined;
if(!validateBasics(row)) {
throw new Error("Basic validation failed for row " + r + " in " + sourceFile);
}
// Write the entry head
importData.push('\t\''+row['id']+'\': {');
// Write out the static data
for(var key in staticData) {
importData.push(getJsonValue(key, staticData[key], '\t\t'));
}
// Check the extra columns
for(column in row) {
var value = row[column];
var columnName = column;
column = column.toLowerCase();
// Skip underscore columns and some other special ones
if(column[0] === '_' || row[columnName] === undefined) {
continue;
}
if(column.indexOf('craft') === 0) {
// Todo: Crafting cost
if(column[column.length - 1] === '#') {
if(!value || value === '') {
// No crafting info set
continue;
}
if(!currentCraftCostEntry) {
throw new Error("Invalid data, row " + r + " in " + sourceFile);
}
currentCraftCostEntry.push(value);
craftCost.push(currentCraftCostEntry);
currentCraftCostEntry = undefined;
} else {
currentCraftCostEntry = [value];
}
continue;
}
// just write it out, no special treatment
importData.push(getJsonValue(columnName, row[columnName], '\t\t'));
}
if(craftCost.length > 0) {
importData.push('\t\t\'craftCost\': {');
for(var n = 0; n < craftCost.length; n++) {
importData.push(getJsonValue(craftCost[n][0], craftCost[n][1], '\t\t\t'));
}
importData.push('\t\t},');
}
importData.push('\t},');
}
importData.push('');
}
importData.push('}');
var fd = fs.openSync(destFile, 'w');
fs.write(fd, importData.join('\n'));
fs.close(fd);
};
var cheerio = require('cheerio');
//var PkbRows = require('../../.lib/pkbrows.js');
var filePath = path.join(__dirname, '../pkb', 'kbplus_pkg_1382.xls');
var xls = XLS.readFile(filePath);
var PkbRows = require('../../.lib/pkbrows.js');
var pkb = new PkbRows('rsc');
pkb.setKbartName();
var json;
var match;
for (var i = 0, j = xls.SheetNames.length; i < j; i++) {
var sheet = xls.Sheets[xls.SheetNames[i]];
json = XLS.utils.sheet_to_row_object_array(sheet);
if (json.length > 0) { break; }
}
if (json.length === 0) {
console.error('No data found in the Excel file');
process.exit(1);
}
var url = "http://pubs.rsc.org/en/journals/getatozresult?key=title&value=current";
request.get(url, function (err, res, body) {
var $ = cheerio.load(body);
json.forEach(function (row) {
var kbartRow = pkb.initRow({});
$('a.jLink').each(function () {
function handleXlsSheet(name, data) {
if (data['!mergeCells']) {
// Copy data in merges into all the merged cells so we can easily find it when looking for column headings
for (var mergeCell = 0; mergeCell < data['!mergeCells'].length; mergeCell++) {
var mergeData = data['!mergeCells'][mergeCell]
, src = data[XLS.utils.encode_cell(mergeData.s)]
, destRef;
for (var mCol = mergeData.s.c; mCol <= mergeData.e.c; mCol++) {
for (var mRow = mergeData.s.r; mRow <= mergeData.e.r; mRow++) {
destRef = XLS.utils.encode_cell({c:mCol, r:mRow});
data[destRef] = src;
}
}
}
}
var rowCategories = [];
// Look for columns of numbers in the data
for (var col=data['!range'].s.c; col <= data['!range'].e.c; col++) {
var numberCount = 0,
numberCol = false,
columnHeading = undefined;
for (var row=data['!range'].s.r; row <= data['!range'].e.r; row++) {
var cellRef = String.fromCharCode(col+65)+(row+1)
, cell = data[cellRef];
if (cell) {
if (cell.t === 'n') {
if (!numberCol) {
numberCount++;
if (numberCount >= config.NUMBERS_REQ_FOR_TAKE) {
// Get what looks like a column heading
var colHeadCells = [];
for (var backRow = row - config.NUMBERS_REQ_FOR_TAKE; backRow >= 0; backRow --) {
var backRowRef = String.fromCharCode(col+65)+(backRow+1)
, textCell = data[backRowRef];
if (textCell && textCell.t === 's') {
colHeadCells.unshift(textCell.v);
} else {
columnHeading = colHeadCells.join(' - ');
break;
}
}
if (colHeadCells.length > 0) {
numberCol = true;
// We have something that looks like data - loop back and get the bits we skipped
// while we were making our minds up
for (var numberRow = row - config.NUMBERS_REQ_FOR_TAKE + 1; numberRow <= row; numberRow++ ) {
var numberCellRef = String.fromCharCode(col+65)+(numberRow+1);
console.log(numberCellRef, columnHeading, rowCategories[numberRow] || handleRowCategory(data, col, numberRow, rowCategories) , data[numberCellRef].v)
}
}
}
} else {
var triple = {heading:columnHeading, category: rowCategories[row]|| handleRowCategory(data, col, row, rowCategories) , value: data[cellRef].v};
console.log(cellRef, triple)
}
} else {
numberCount = 0;
}
} else {
numberCount = 0;
}
}
}
}
xls.SheetNames.forEach(function (sheetName) {
console.log("Reading " + sheetName);
var csvdata = XLS.utils.make_csv(xls.Sheets[sheetName]);
writeCleanCsvOfSheet(csvdata, sheetName, csvwriter);
});