I made some progress on the excel add-in I'd been tasked with to develop. Up until this time, I'd been so absorbed in learning the main code base at at my new company that I had not time to spend with this. Actually, its a totally diffirent beast being a JavaScript centric project. I'd been assigned countless bugs and tasks for the main code base so all my time was spent doing that.
During the last week of my illness, I basically was bed-ridden but I could code, so I did and I made some really great progress. I took a lot of my ideas from my Angular4 investment project and incorporated them into the plugin. i also started getting my head around the excel api. This code uses Promises which I' learned about a while back so i finally could use that. Promises lead to code that looks a like this(notice the multiple returns which return new Promises):
static SyncTable<T>(entities: T[], tableName: string, exclComplexTypes: boolean = true): OfficeExtension.IPromise<any> {
// Get the named table.
// Get the number of rows, if its diffirenct find difdirent rows from the bottom, create those entries
let changes: TableChange<T>[] = [];
//
return Excel.run(context => {
const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
var table = currentWorksheet.tables.getItemOrNullObject(tableName);
var bodyRange;
return context.sync().then(tableResult => {
// now should or should not have table?
if (table.isNullObject) {
// empty table - normal entityToGrid
console.log('Original row count is ' + entities.length);
ExcelUtils.EntitiesToGrid(entities, tableName, exclComplexTypes);
} else {
// Existing table, get table and check the number of extra rows
bodyRange = table.getDataBodyRange().load(['values', 'rowCount']); // cant use yet
return context.sync().then(dataBodyResult => {
console.log('RowCount is : ' + bodyRange.rowCount);
if (bodyRange.rowCount > entities.length) {
// get the added rows
var diffCount = bodyRange.rowCount - entities.length;
for (var i = 0; i < diffCount; i++) {
var itemIndex = entities.length + i;
var result = bodyRange.values[itemIndex];
var obj: T = ReflectionUtils.toObject<T>(result, ReflectionUtils.getEntityProperties(entities[0], exclComplexTypes)[0].sort());
var change: TableChange<T> = { change: 'a', value: <T>obj };
changes.push(change);
console.log('new row: ' + JSON.stringify(result));
}
//return new Promise<TableChange<T>[]>((resolve, reject) => { resolve(changes); });
return context.sync();
} else if (bodyRange.rowCount > entities.length) {
// note which rows were removed and get them from the original entities
}
//return new Promise<TableChange<T>[]>((resolve, reject) => { resolve(changes); });
return context.sync();
});
}
})
}).then(value => {
return new OfficeExtension.Promise((resolve, reject) => resolve(changes));
})
.catch(error => {
console.log(`Error: \({error}`);
if (error instanceof OfficeExtension.Error) {
console.log(`Debug info: \){JSON.stringify(error.debugInfo)}`);
}
});
}
}That code is turns a list JSON objects(entities) to an excel table and then if you edit that table and add extra rows, it detects the new rows and returns again those changes as a promise. I then act on those changes by 'subscribing' to that promise then creating them on the server. The returned promise contains the list of changed rows... its like madness.
sync() {
ExcelUtils.SyncTable<Trade>(this.trades, 'Trades', false).then((values) => {
var syncResults = <TableChange<Trade>[]>values;
if (syncResults && syncResults.length) {
syncResults.forEach(tableChange => {
var thinNewTrade: Trade = <Trade>{};
var thinTradeRow = tableChange.value;
for (let property in thinTradeRow) {
(<any>thinNewTrade)[property] = (<any>thinTradeRow)[property];
}
this.zone.run(() => {
this.readyTradeForUpsert(thinNewTrade);
});
});
} else {
this.message = 'No changes detected';
}
});
}
Its all very rough but its got a lot of return context.sync().then...return context.sync()..then...etc. This is the chain of promises that I'm talking about that you need to do when working with promises.
Here is the code that will turn any JSON object into a table in excel:
static EntitiesToGrid<T>(entities: T[], tableName: string, exclComplexTypes: boolean = true): void {
Excel.run(context => {
const cols = ReflectionUtils.getEntityProperties<T>(entities[0], exclComplexTypes);
cols[0].sort(); // sort columns alphabeticallly
const rows = ReflectionUtils.getEntitiesPropertyValues<T>(entities, cols);
// Turn camel case into display case ie. myNameIsEarl becomes My Name Is Earl
cols[0] = cols[0].map(item => { return StringUtils1.Displayify(item); });
const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
// Work out the range we need based on the number of columns we have
const range = 'A1:' + StringUtils1.xlsColumnDef[cols[0].length - 1] + '1';
const expensesTable = currentWorksheet.tables.add(range, true /*hasHeaders*/);
expensesTable.name = tableName;
expensesTable.getHeaderRowRange().values = cols;
expensesTable.rows.add(null /*add at the end*/, rows);
expensesTable.getRange().format.autofitColumns();
expensesTable.getRange().format.autofitRows();
return context.sync();
})
.catch(error => {
console.log(`Error: \({error}`);
if (error instanceof OfficeExtension.Error) {
console.log(`Debug info: \){JSON.stringify(error.debugInfo)}`);
}
});
}Most of this code can be found here.