.then(() => {
let js = "module.exports = function() {\nconst workbook = {};\n";
workbook.eachSheet(sheet => {
js += 'workbook.' + sheet.name + " = {};\n";
sheet.eachRow({includeEmpty: true}, (row, rowNumber) => {
row.eachCell({includeEmpty: true}, cell => {
js += 'workbook.' + sheet.name + '.' + cell.address + ' = () => ';
if(cell.value && cell.value.formula) {
js += cell.value.formula.replace(/([A-Z][0-9]+)/g, 'workbook.' + sheet.name + '.$1()');
} else {
js += cell && cell.toCsvString() || 'null';
}
js += ";\n";
for(const name of cell.names) {
js += 'workbook.' + name + ' = ' + 'workbook.' + sheet.name + '.' + cell.address + ";\n";
}
});
});
});
js += "return workbook;\n}\n";
return js;
})
/**
* Write Avails as an Excel spreadsheet to a stream.
*
* @method writeAvailsToXLSXStream
* @param {Avail[]} avails - Avails
* @param {stream.Writable} stream - Writable stream, e.g. process.stdout
* @return {Promise|Avail[]|Error} Promise that stream will finish
*/
function writeAvailsToXLSXStream(avails, stream) {
// create workbook and sheets
var workbook = new Excel.Workbook();
var tvSheet = workbook.addWorksheet('TV');
var movieSheet = workbook.addWorksheet('Movies');
// Add headers to each sheet
tvSheet.addRow(superUpperCamelCaseArray(AVAILS_HEADERS_TV))
movieSheet.addRow(superUpperCamelCaseArray(AVAILS_HEADERS_MOVIE))
// place each avail into appropriate sheet
avails.forEach(function (avail) {
switch (avail.work_type) {
case 'Season':
case 'Episode':
tvSheet.addRow(Avails.toArray(avail));
break;
case 'Movie':
movieSheet.addRow(Avails.toArray(avail));
break;
default:
throw new Error('Unsupported WorkType: ' + avail.work_type);
}
});
return workbook.xlsx.write(stream).then(function () {
return avails;
});
}
return new Promise((resolve, reject) => {
var workbook = new Excel.Workbook();
workbook.creator = 'Progic.se';
workbook.lastModifiedBy = 'Progic.se';
workbook.created = new Date();
workbook.modified = new Date();
var worksheet = workbook.addWorksheet('Resultat');
worksheet.columns = [
{ header: 'startnummer', key: 'start_nbr', width: 12 },
{ header: 'förnamn', key: 'firstname', width: 20 },
{ header: 'efternamn', key: 'lastname', width: 30 },
{ header: 'sluttid', key: 'endTime', width: 12 },
{ header: 'netto', key: 'netTime', width: 12 },
{ header: 'kön', key: 'gender', width: 8 }
];
if (results.length != 0) {
if (results.male) {
results.male.forEach((p) => {
worksheet.addRow({start_nbr: p.startNbr, firstname: p.firstname, lastname: p.lastname, endTime: p.endTime, netTime: p.netTime, gender: p.gender});
})
}
if (results.female) {
results.female.forEach((p) => {
worksheet.addRow({start_nbr: p.startNbr, firstname: p.firstname, lastname: p.lastname, endTime: p.endTime, netTime: p.netTime, gender: p.gender});
})
}
}
workbook.xlsx.writeFile(filename)
.then(function() {
console.log('done')
resolve(fs.createReadStream(filename))
})
})
.exec(function(err, doc) {
var workbook = new Excel.Workbook();
var sheet = workbook.addWorksheet('sheet1');
sheet.columns = columns;
for (var i = 0; i < doc.length; i++) {
sheet.addRow({
id: i,
year: doc[i].year,
month: doc[i].month,
name: doc[i].name,
mobile: doc[i].mobile,
amount: doc[i].amount,
tax: doc[i].tax,
cash: doc[i].cash,
status: doc[i].status,
});
}
done(null, workbook);
});
.then(function () {
var worksheet = workbook.getWorksheet('Лист1');
var row = worksheet.getRow(worksheet.lastRow.number-3);
console.log(loadedFile);
console.log(row.values);
var controlCompany=worksheet.getCell('A6').value.split(':').splice(1, 1).join();
var finalizedRow=row.values;
finalizedRow.push(controlCompany);
var house = new House(year, month, finalizedRow);
dbinputer(house)
});
.then(function() {
// use workbook
var worksheet = workbook.getWorksheet('Artist');
var artCol = worksheet.getCell('A2').value;
console.log(artCol);
//console.log(artCol.worksheet._rows[0]._worksheet);
});
Q.all(cargas).then(function(information){
const workbook = new Excel.Workbook(),
datos = {
cartas: information[0],
objetivos: information[1],
indicadores: information[2],
jerarquias: information[3]
};
workbook.creator = (typeof creator === 'undefined') ? 'Me' : creator;
workbook.lastModifiedBy = (typeof creator === 'undefined') ? 'Me' : creator;
workbook.created = new Date();
workbook.modified = new Date();
workbook.addWorksheet('Indicadores');
const worksheet = workbook.getWorksheet('Indicadores');
fulfillSheet(worksheet, datos);
workbook.xlsx.writeFile(filename).then(function(){ defer.resolve(workbook); }, defer.reject);
}, defer.reject);
.exec(function(err, doc) {
if(err) return done(err);
var workbook = new Excel.Workbook();
var sheet = workbook.addWorksheet('sheet1');
sheet.columns = columns;
for (var i = 0; i < doc.length; i++) {
sheet.addRow({
month: doc[i].month,
city: doc[i].city,
userCode: doc[i].userCode,
mobile: doc[i].mobile,
mainProductCode: doc[i].mainProductCode,
mainProductName: doc[i].mainProductName,
originProductCode: doc[i].originProductCode,
originproductName: doc[i].originproductName,
originTime: doc[i].originTime,
vandorCode: doc[i].vandorCode,
vandorName: doc[i].vandorName,
vandorChannelCode: doc[i].vandorChannelCode,
vandorChannelName: doc[i].vandorChannelName,
paymentChannelCode: doc[i].paymentChannelCode,
paymentChannelName: doc[i].paymentChannelName,
policyName: doc[i].policyName,
createDate: doc[i].createDate,
bonusType: doc[i].bonusType,
bonusNet: doc[i].bonusNet,
bonusTax: doc[i].bonusTax,
bonusTotal: doc[i].bonusTotal,
productCode: doc[i].productCode,
productName: doc[i].productName,
sellerName: doc[i].sellerName,
sellerMobile: doc[i].sellerMobile,
lastupdatetime: utils.dateFormat(doc[i].lastupdatetime, 'yyyy-MM-dd:hh:mm:ss'),
});
}
done(null, workbook);
});
'use strict';
var request = require('co-request');
var cheerio = require('cheerio');
var co = require('co');
var Excel = require('exceljs');
var async = require('async');
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('ASICS');
var _ = require('lodash');
var page_no = 0;
var link = 0;
var link1 = 0;
var pages_processed = 0;
var sleep = require('sleep');
const BaseUrl = "http://uniball.in/shop/index.php?id_lang=1&id_category=12&controller=category&n=56";
const MainUrl = "";
worksheet.columns = [
{
header: "URL",
key: "url",
width: 30
},
{
header: "Title",
key: "title",
width: 20
},
{
header: "Images",
key: "imageUrls",
width: 30
ROWS: [
[1, 'Jose Damian', '<*****@*****.**>', 'Pucallpa'],
[2, 'Gustabo Artica', '<*****@*****.**>', 'Huancayo'],
[3, 'Diana Hun', '<*****@*****.**>', 'Lima'],
[4, 'Juan Azabache', '<*****@*****.**>', 'Lima'],
[5, 'Ely Ramos Yañe', '<*****@*****.**>', 'Lima'],
[6, 'Jose Florindez', '<*****@*****.**>', 'Lima'],
[7, 'Hector Billi Huby Bautista', '<*****@*****.**>', 'Lima'],
[8, 'Luigi Limaylla', '<*****@*****.**>', 'Lima'],
[9, 'Daniel Infante Capristano', '<*****@*****.**>', 'Lima'],
[10, 'Alejandro Nunez', '<*****@*****.**>', 'Lima'],
]
}
// creamos documento
var workbook = new Excel.Workbook();
// creamos hoja
var worksheet = workbook.addWorksheet('My Sheet', 'FFC0000');
// insertamos las cabeceras
worksheet.columns = conf.COLS;
// insertamos los registros
worksheet.addRows(conf.ROWS);
// seteando estilos a las cabeceras y registros
worksheet.eachRow(function(row, rowNumber) {
if ( rowNumber === 1 ) {
row.font = style.ROW_HEADER.font;
row.fill = style.ROW_HEADER.fill;
'use strict';
var request = require('request-promise');
var cheerio = require('cheerio');
var co = require('co');
var async = require('async');
var sleep = require('sleep');
var _ = require('lodash');
var Excel = require('exceljs');
var pages = 0;
var productPages = 0;
var urlencode = require('urlencode');
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('CASIO WATCHES');
var baseUrl = 'http://www.casioindiashop.com/Handler/ProductShowcaseHandler.ashx';
// 'http://www.hpshopping.in/Ink_and_Toner/Ink_Cartridges';
// var mainUrl = 'http://www.hpshopping.in';
worksheet.columns = [
{
header: "URL",
key: "url",
width: 30
},
{
header: "Title",
key: "title",
width: 20
}, {
header: "Brand",
key: "brand",
width: 20
router.get('/write/get', function(req, res) {
if(req.query.xlsx){
function writeToXLSX(data) {
var ws_name = "test",wb,ws,wbout;
wb = new Workbook(), ws = sheet_from_array_of_json(data);
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
//XLSX.writeFile(wb, __dirname+"/../public/xlsx_test.xlsx",{bookType:'xlsx', bookSST:true, type: 'binary'});
wbout = XLSX.write(wb, {
type: 'base64'
});
res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//application/vnd.ms-excel
res.setHeader("Content-disposition", "attachment;filename=test.xlsx");
res.send(new Buffer(wbout, 'base64'));
}
function Workbook() {
if(!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
//没用
function stringToArrayBuffer(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
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;
}
var rows = [
["a","b","c"],
[11,22,33],
[44,55,66]
];
writeToXLSX(rows);
}else{
var workbook = new Excel.Workbook();
var sheet = workbook.addWorksheet('My Sheet');
var stream = new Buffer('');
var rows = [
["a","b","c"],
[11,22,33],
[44,55,66]
];
sheet.addRows(rows);
/*
workbook.xlsx.writeFile(__dirname+"/../public/开发进度表1.xlsx").then(function(data) {
console.log(data);
});
*/
res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//application/vnd.ms-excel
res.setHeader("Content-disposition", "attachment;filename=report.xlsx");
workbook.xlsx.write(res).then(function(data){
res.end();
});
}
});
'use strict';
var request = require('request');
var cheerio = require('cheerio');
var co = require('co');
var Excel = require('exceljs');
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('LUXOR PRODUCTS');
var _ = require('lodash');
var BaseUrl = "http://www.luxor.in/sitemap.xml";
var MainUrl = "http://www.luxor.in";
worksheet.columns = [
{ header: "URL", key: "url", width: 30 },
{ header: "Title", key: "title", width: 20 },
{ header: "Features", key: "features", width: 30 },
{ header: "Images", key: "imageUrls", width: 30 },
{ header: "MRP", key: "price", width: 15 },
{ header: "Description", key: "description", width: 30 },
{ header: "Brand", key: "Actors", width: 15 },
{ header: "Model Number", key: "Directors", width: 15 },
{ header: "Colour", key: "Language", width: 15 },
{ header: "Grip Type", key: "Subtitles", width: 15 },
{ header: "Material", key: "Region", width: 15 },
{ header: "Size", key: "numberOfDiscs", width: 15 },
{ header: "Manufacturer Part Number", key: "Studio", width: 15 },
{ header: "Item Height", key: "releaseDate", width: 15 },
{ header: "Item Length", key: "Format", width: 15 },
{ header: "Ink Colour", key: "Rated", width: 15 },
{ header: "Item Width", key: "Rated1", width: 15 },
{ header: "Item Weight", key: "runTime", width: 15 },
{ header: "Sheet Size", key: "sheetSize", width: 15 },
.then(function() {
var routes = [] ;
wb.eachSheet(function(worksheet, sheetId) {
//var routeId = worksheet.
console.log(worksheet.name);
var routeId = parseInt(_s.words(worksheet.name)[0]) ;
if(isNaN(routeId)
/*|| (
routeId!=83
//&&
//routeId!=83
//&& routeId!=70
)*/
){
return;
}
var meta = { lastCellNum: 1};
var route = {
st: '',
en: '',
routeId: routeId,
fleetId: fleetId,
stages: [],
trips: [
[],
[]
]
};
worksheet.eachRow(function(row, rowNumber) {
if(rowNumber==nameRowNum || rowNumber==stationRowNum || rowNumber==onDistRowNum || rowNumber==reDistRowNum){
}
else if(rowNumber==onStopIDRowNum){ //Onward Stop ID
row.eachCell(function(cell, colNumber) {
//console.log("Cell " + colNumber + " = " + cell.value);
meta[colNumber] = { onwardStopId : cell.value };
meta.lastCellNum = colNumber;
});
}
else if(rowNumber==reStopIDRowNum){ //Return Stop ID
row.eachCell(function(cell, colNumber) {
//console.log("Cell " + colNumber + " = " + cell.value);
meta[colNumber].returnStopId = cell.value ;
});
}
else if(_s.isBlank(row.getCell(1).value) ){ //Its a new trip
//console.log(row.getCell(3).value);
var timeCellVal = row.getCell(3).value ;
//console.log(timeCellVal);
if(_s.isBlank(timeCellVal) || timeCellVal.formula != undefined && (_s.isBlank(timeCellVal.result) || timeCellVal.result==undefined) || (timeCellVal.sharedFormula != undefined && timeCellVal.result == undefined)) { //Check if start time is given
//console.log("Empty row");
return;
}
else {
console.log("Row %j", rowNumber);
var dir = (row.getCell(2).value == "Onward") ? 0 : 1;
//TODO remove hardcoded 3
var trip = {stops: {}, fleetId: 3, tripId: -1, direction: dir, serviceId: 1}; //TODO set service ID based on chosen service
for(i=3; i<= meta.lastCellNum; i++){
var time;
//console.log("Cell value " + JSON.stringify(row.getCell(i).value));
if( row.getCell(i) && row.getCell(i).value && (row.getCell(i).value.formula != undefined || row.getCell(i).value.sharedFormula != undefined) ) {
console.log("Formula with result %j", row.getCell(i).value.result);
if(moment(row.getCell(i).value.result, 'hh:mm:ss a').isValid())
time = moment(row.getCell(i).value.result, 'hh:mm:ss a').format('HH:mm');
else
time = moment(row.getCell(i).value.result, 'hh:mm:ss A').format('HH:mm');
}
else {
//console.log("No formula with value %j and text %j and type %j and cell %j" , row.getCell(i).value, row.getCell(i).text, row.getCell(i).type, row.getCell(i));
if(row.getCell(i).type == 2 && row.getCell(i).value <= 1.0 ){ //Numeric
var secs = row.getCell(i).value * 86400;
time = moment().startOf('day').add(secs, 'second').format('HH:mm');
}
else {
if(moment(row.getCell(i).value).isValid())
time = moment(row.getCell(i).value).add( -moment(row.getCell(i).value).utcOffset(), 'minute').format('HH:mm') ;
else
time = moment(row.getCell(i).value, 'hh:mm:ss A').add( -moment(row.getCell(i).value).utcOffset(), 'minute').format('HH:mm') ;
}
//console.log(row.getCell(i).value);
//console.log(moment(row.getCell(i).value).utcOffset());
//It thinks 06:00:00 am is a UTC value and gives back 11:30 am
}
//console.log(time);
var stopId = dir==0 ? meta[i].onwardStopId : meta[i].returnStopId ;
if(trip.stops['' + meta[i].onwardStopId] || trip.stops['' + meta[i].returnStopId])
stopId = '' + stopId + '_2' ;
if(time != "Invalid date") //Handle express trips that have no time for some stops
trip.stops[ '' + stopId ] = time ;
}
//console.log(trip);
route.trips[dir].push(trip);
}
}
});
//console.log(moment("6:00:00 am").format());
//console.log(JSON.stringify(route));
routes.push(route);
});
//console.log(routes);
cb(routes);
});
'use strict';
var request = require('co-request');
var cheerio = require('cheerio');
var co = require('co');
var Excel = require('exceljs');
var async = require('async');
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('FUNSKOOL1');
var _ = require('lodash');
var page_no = 0;
var link = 0;
var link1 = 0;
var pages_processed = 0;
var sleep = require('sleep');
const BaseUrl = "http://funskoolindia.com/products/search?brandId=44&filter=true&page=";
const MainUrl = "http://www.funskoolindia.com";
worksheet.columns = [
{
header: "URL",
key: "url",
width: 30
},
{
header: "Title",
key: "title",
width: 20
},
{
header: "Images",
key: "imageUrls",
width: 30
.then(function() {
//** 所有的sheets
var sheets = {};
workbook.eachSheet(function(sheet, sheetId) {
sheets[sheetId] = sheet;
});
//** 所有的数据
var sets = [];
for(var i in sheets){
sheets[i].eachRow(function(row, rowNumber) {
// console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
var set = {
month: String(row.values[1]) || '',
city: String(row.values[2]) || '',
userCode: String(row.values[3]) || '',
mobile: String(row.values[4]) || '',
mainProductCode: String(row.values[5]) || '',
mainProductName: String(row.values[6]) || '',
originProductCode: String(row.values[7]) || '',
originproductName: String(row.values[8]) || '',
originTime: String(row.values[9]) || '',
vandorCode: String(row.values[10]) || '',
vandorName: String(row.values[11]) || '',
vandorChannelCode: String(row.values[12]) || '',
vandorChannelName: String(row.values[13]) || '',
paymentChannelCode: String(row.values[14]) || '',
paymentChannelName: String(row.values[15]) || '',
policyName: String(row.values[16]) || '',
createDate: String(row.values[17]) || '',
bonusType: String(row.values[18]) || '',
bonusNet: String(row.values[19]) || '',
bonusTax: String(row.values[20]) || '',
bonusTotal: String(row.values[21]) || '',
productCode: String(row.values[22]) || '',
productName: String(row.values[23]) || '',
};
//** 过滤标题行
if (!(set.month == '账期' || set.city == '城市')) {
sets.push(set);
}
});
}
//** 记录无法更新的数据集合
var wrongSets = [];
async.eachSeries(sets, function(record, cb) {
//** 跳过标题行
if(record.month == '账期') return cb();
//** 开始日期
var createDate = (record.createDate || '').match(/\d+/g) || [];
var days = createDate[0] || 0;
var month = (createDate[1] || 1) - 1;
var year = (createDate[2] || 100) < 100 ? (2000 + parseInt(createDate[2])) : parseInt(createDate[2]);
//** 缩小时间范围
var prevDay = new Date((new Date(year,month,days)).getTime() - 1000*60*60*24); //** 前一天
var nextDay = new Date((new Date(year,month,days)).getTime() + 1000*60*60*24); //** 后一天
//** 对账编码
var packageCodeRegex = new RegExp(regexp.escape('e' + record.productCode), 'i');
//** 关联到订单
Order.findOne({
'customer.mobile': record.mobile,
'status': '成功',
'goods.packageCode': {
$regex: packageCodeRegex
},
'lastupdatetime': {
'$gt': prevDay,
'$lt': nextDay,
}
}, function(err,order){
if(err) console.log(err);
order = order || {};
record.sellerName = order.createBy && order.createBy.username || '';
record.sellerMobile = order.createBy && order.createBy.mobile || '';
//** 找到并更新对账单中的推荐人信息
FinanceBonusUnicom.findOneAndUpdate({
month: record.month,
userCode: record.userCode,
createDate: record.createDate,
}, {
$set: record
}, {
'upsert': true,
'new': true,
}, function(err) {
if (err) wrongSets.push(set);
cb();
});
});
}, function(err) {
if (err) return done(err);
done(null, {
wrongSets: wrongSets
});
});
});
.then(function() {
var micros = stopwatch.microseconds;
console.log('Loaded', filename);
console.log('Time taken:', micros);
var ws = wb.getWorksheet("blort");
assert(ws, "Expected to find a worksheet called blort");
var column1 = ws.getColumn(1);
assert(column1 && (column1.width == 25), "Expected column width of col 1 to be 25, was " + column1.width);
var column9 = ws.getColumn(9);
assert(column9 && column9.hidden, "Expected column 9 to be hidden: \n" + JSON.stringify(column9.defn, null, ' '));
var row16 = ws.getRow(16);
assert(row16 && (row16.hidden), "Expected row 16 to be hidden");
assert(ws.getCell("A2").value == 7, "Expected A2 == 7");
assert(ws.getCell("B2").value == "Hello, World!", 'Expected B2 == "Hello, World!", was "' + ws.getCell("B2").value + '"');
assertFont(ws.getCell("B2").font, fonts.comicSansUdB16, "B2");
assertEqual('B2', 'border', ws.getCell("B2").border, borders.thin);
assert(Math.abs(ws.getCell("C2").value + 5.55) < 0.000001, "Expected C2 == -5.55, was" + ws.getCell("C2").value);
assert(ws.getCell("C2").numFmt == '"£"#,##0.00;[Red]\-"£"#,##0.00', 'Expected C2 numFmt to be "£"#,##0.00;[Red]\-"£"#,##0.00, was ' + ws.getCell("C2").numFmt);
assertFont(ws.getCell("C2").font, fonts.arialBlackUI14, "C2");
assert(ws.getCell("D2").value instanceof Date, "expected D2 to be a Date, was " + ws.getCell("D2").value);
assertEqual('D2', 'border', ws.getCell("D2").border, borders.doubleRed);
assert(ws.getCell("C5").value.formula, "Expected C5 to be a formula, was " + JSON.stringify(ws.getCell("C5").value));
assertEqual('C6', 'border', ws.getCell("C6").border, borders.thickRainbow);
assert(ws.getCell("A9").numFmt == "# ?/?", 'Expected A9 numFmt to be "# ?/?", was ' + ws.getCell("A9").numFmt);
assert(ws.getCell("B9").numFmt == "h:mm:ss", 'Expected B9 numFmt to be "h:mm:ss", was ' + ws.getCell("B9").numFmt);
assert(ws.getCell("C9").numFmt == "0.00%", 'Expected C9 numFmt to be "0.00%", was ' + ws.getCell("C9").numFmt);
assert(ws.getCell("D9").numFmt == "[Green]#,##0 ;[Red](#,##0)", 'Expected D9 numFmt to be "[Green]#,##0 ;[Red](#,##0)", was ' + ws.getCell("D9").numFmt);
assert(ws.getCell("E9").numFmt == "#0.000", 'Expected E9 numFmt to be "#0.000", was ' + ws.getCell("E9").numFmt);
assert(ws.getCell("F9").numFmt == "# ?/?%", 'Expected F9 numFmt to be "# ?/?%", was ' + ws.getCell("F9").numFmt);
assert(ws.getCell("A10").value == "<", 'Expected A10 to be "<", was "' + ws.getCell("A10").value + '"');
assert(ws.getCell("B10").value == ">", 'Expected A10 to be ">", was "' + ws.getCell("B10").value + '"');
assert(ws.getCell("C10").value == "<a>", 'Expected A10 to be "<a>", was "' + ws.getCell("C10").value + '"');
assert(ws.getCell("D10").value == "><", 'Expected A10 to be "><", was "' + ws.getCell("D10").value + '"');
assert(ws.getRow(11).height == 40, 'Expected Row 11 to be height 40, was ' + ws.getRow(11).height);
_.each(alignments, function(alignment, index) {
var rowNumber = 11;
var colNumber = index + 1;
var cell = ws.getCell(rowNumber, colNumber);
assert(cell.value == alignment.text, 'Expected Cell[' + rowNumber + ',' + colNumber + '] to be ' + alignment.text + ', was ' + cell.value);
assert(_.isEqual(cell.alignment,alignment.alignment), 'Expected Cell[' + rowNumber + ',' + colNumber + '] alignment to be ' + JSON.stringify(alignment.alignment) + ', was ' + JSON.stringify(cell.alignment));
});
var row12 = ws.getRow(12);
assert(row12.height == 40, 'Expected Row 12 to be height 40, was ' + row12.height);
assert(_.isEqual(row12.getCell(1).fill, fills.blueWhiteHGrad), 'Expected [12,1] fill to be ' + JSON.stringify(fills.blueWhiteHGrad) + ', was ' + JSON.stringify(row12.getCell(1).fill));
assert(_.isEqual(row12.getCell(2).fill, fills.redDarkVertical), 'Expected [12,2] fill to be ' + JSON.stringify(fills.redDarkVertical) + ', was ' + JSON.stringify(row12.getCell(2).fill));
assert(_.isEqual(row12.getCell(3).fill, fills.redGreenDarkTrellis), 'Expected [12,3] fill to be ' + JSON.stringify(fills.redGreenDarkTrellis) + ', was ' + JSON.stringify(row12.getCell(3).fill));
assert(_.isEqual(row12.getCell(4).fill, fills.rgbPathGrad), 'Expected [12,4] fill to be ' + JSON.stringify(fills.rgbPathGrad) + ', was ' + JSON.stringify(row12.getCell(4).fill));
assertFont(ws.getRow(13).font, fonts.arialBlackUI14, "Row 13");
assertFont(ws.getCell("H12").font, fonts.comicSansUdB16, "H12");
assertFont(ws.getCell("G13").font, fonts.arialBlackUI14, "G13");
assertFont(ws.getCell("H13").font, fonts.arialBlackUI14, "H13");
assertFont(ws.getCell("I13").font, fonts.arialBlackUI14, "I13");
assertFont(ws.getCell("H14").font, fonts.comicSansUdB16, "H14");
assert(ws.getCell("H12").value == "Foo", 'Expected H12 to be "Foo", was "' + ws.getCell("H12").value + '"');
assert(ws.getCell("G13").value == "Foo", 'Expected G13 to be "Foo", was "' + ws.getCell("G13").value + '"');
assert(ws.getCell("H13").value == "Bar", 'Expected H13 to be "Bar", was "' + ws.getCell("H13").value + '"');
assert(ws.getCell("I13").value == "Baz", 'Expected I13 to be "Baz", was "' + ws.getCell("I13").value + '"');
assert(ws.getCell("H14").value == "Baz", 'Expected H14 to be "Baz", was "' + ws.getCell("H14").value + '"');
assert(passed, "Something went wrong", "All tests passed!");
});
schema.statics.toExcelTemplate = function(done){
var workbook = new Excel.Workbook();
var sheet = workbook.addWorksheet('sheet1');
sheet.columns = columns;
done(null, workbook);
};
const Excel = require('exceljs');
const wb = new Excel.Workbook();
const sheet = wb.addWorksheet('シート1');
sheet.columns = [
{header: 'ID', key: 'id'},
{header: 'NAME', key: 'name'}
];
sheet.addRow({id: 1, name: 'アイテム1'});
sheet.addRow({id: 2, name: 'アイテム2'});
sheet.addRow([3, "aaa"]);
sheet.addRow([4, "データ1"]);
wb.xlsx.writeFile('sample.xlsx')
.then( () => console.log('created') );