Using Spreadsheets as Data Sources Leave feedback
On this page
A worksheet of a spreadsheet document can be loaded as a DocumentTable and used as a data source. For spreadsheets, the engine detects column types from cell values, so numeric columns can be used in arithmetic operations and aggregates directly.
- Load a worksheet as a
DocumentTable, extracting column names from the first row - Assemble a document using the table as a data source
The first worksheet contains the Client, Manager and Contract Price columns.
The template contains a two-column table that groups contracts by manager and sums contract prices. The first cell of the data row contains:
<<foreach [group in contracts
.groupBy(c => c.Manager)
.orderBy(g => g.key)]>><<[group.key]>>
The second cell contains:
<<[group.sum(
c => c.Contract_Price)]>><</foreach>>
const groupdocs = require('@groupdocs/groupdocs.assembly');
// Extract column names from the first row.
const options = new groupdocs.DocumentTableOptions();
options.setFirstRowContainsColumnNames(true);
// Use data of the first worksheet.
const table = new groupdocs.DocumentTable('Contracts Data.xlsx', 0, options);
// Check column count, names, and types.
const columns = table.getColumns();
console.log(columns.getCount()); // 3
for (let i = 0; i < columns.getCount(); i++) {
const column = columns.get(i);
console.log(column.getName(), column.getType().getName());
}
// Client java.lang.String
// Manager java.lang.String
// Contract_Price double
// NOTE: A space is replaced with an underscore, because spaces are not allowed in column names.
// NOTE: The type of the last column is double, because all its cells contain numeric values.
const assembler = new groupdocs.DocumentAssembler();
assembler.assembleDocument('Using Spreadsheet as Table of Data.docx',
'Using Spreadsheet as Table of Data_report.docx',
new groupdocs.DataSourceInfo(table, 'contracts'));
process.exit(0);
If column names are not extracted from the first row, the columns are named A, B, C, and so on, the header row is treated as a data row, and all columns get the java.lang.String type.
Was this page helpful?
Any additional feedback you'd like to share with us?
Please tell us how we can improve this page.
Thank you for your feedback!
We value your opinion. Your feedback will help us improve our documentation.
