Hello,
I am trying to create a table containing 82 columns and 10000 rows (11028 to be exact) in a sheet.
I tried to add the lines one by one and then to do a Sync but the table is not created.
I thought it was a lot of data at one time so I tried adding lines in packets of 1000. This time, I get the table but only the first 1000 rows are filled.
In the log, I see all the packets go by.
In the code below :
- "values" is the json formatted data i try to add
- "tableName" speaks for itself.
The steps before 5 are inscriptions of some data in other sheets but these works.
function CreateODataQuerySheetStep5_List(values, tableName) {
let n = values.length;
if (n > 0) {
CreateODataQuerySheetStep5_ListTableHeader(values, tableName);
} else {
Excel.run(function (context) {
let headers = [["No data"]];
let sheet = context.workbook.worksheets.getItem(newQueryName);
let table = sheet.tables.add("A1", true);
table.name = tableName;
table.getHeaderRowRange().values = headers;
sheet.activate();
window.location.href = "Home.html";
return context.sync();
}).catch(function (error) {
logging(sessionStorage.getItem("Token"), error);
});
}
}
function CreateODataQuerySheetStep5_ListTableHeader(values, tableName)
{
console.log("Creating header");
Excel.run(function (context) {
console.log("Get Sheet");
let sheet = context.workbook.worksheets.getItem(newQueryName);
console.log("Define table horizontal length");
let line = values[0];
let keys = Object.keys(line);
let address = "A1:" + ColumnToLetter(keys.length) + "1";
console.log("Computing header columns");
let headers = [];
for (let j = 0; j < keys.length; j++) {
let key = keys[j];
headers.push(key);
}
console.log("Adding table");
let table = sheet.tables.add(address, true);
table.name = tableName;
let temp = [];
temp.push(headers);
console.log("Putting header");
table.getHeaderRowRange().values = temp;
return context.sync().then(function () {
console.log("Header created")
CreateODataQuerySheetStep5_List1000(values, 0, tableName, headers);
}).catch(function (error) {
logging(sessionStorage.getItem("Token"), error);
});
}).catch(function (error) {
logging(sessionStorage.getItem("Token"), error);
});
}
function CreateODataQuerySheetStep5_List1000(values, start, tableName, headers)
{
console.log("Adding 1000 rows");
Excel.run(function (context) {
console.log("Get Sheet");
let sheet = context.workbook.worksheets.getItem(newQueryName);
console.log("Get Table");
let table = sheet.tables.getItem(tableName);
console.log("Computing max between 1000 and remaining lines");
let x = values.length - start;
let n = Math.min(1000, x);
console.log("Adding rows from " + start.toString() + " to " + (start + n).toString());
for (let i = start; i < n; i++) {
let line = values[i];
let keys = Object.keys(line);
let row = [];
for (let jj = 0; jj < headers.length; jj++) {
let prop = headers[jj];
let value = line[prop];
if ($.type(value).toLowerCase() === 'string') {
value = "'" + value;
}
row.push(value);
}
table.rows.add(null, [row]);
}
console.log("Rows added");
let y = start + 1000;
if (y < values.length)
{
console.log("Rows are remaining");
return context.sync().then(function () {
console.log("Recursive Call");
CreateODataQuerySheetStep5_List1000(values, y, tableName, headers);
}).catch(function (error) {
logging(sessionStorage.getItem("Token"), error);
});
}
else
{
//if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
// sheet.getUsedRange().format.autofitColumns();
// sheet.getUsedRange().format.autofitRows();
//}
console.log("Sheet activation");
sheet.activate();
return context.sync().then(function () {
console.log("return to home");
window.location.href = "Home.html";
});
}
}).catch(function (error) {
logging(sessionStorage.getItem("Token"), error);
});
}