Combine data from multiple Excel tables into a single table
This sample combines data from multiple Excel tables into a single table that includes all the rows. It assumes that all tables being used have the same structure.
There are two variations of this script:
- The first script combines all tables in the Excel file.
- The second script selectively gets tables within a set of worksheets.
Setup: Sample Excel file
This workbook contains the data, objects, and formatting expected by the script.
Sample code: Combine data from multiple Excel tables into a single table
Add the following script to the sample workbook and try the sample yourself!
function main(workbook: ExcelScript.Workbook) {
// Delete the "Combined" worksheet, if it's present.
workbook.getWorksheet('Combined')?.delete();
// Create a new worksheet named "Combined" for the combined table.
const newSheet = workbook.addWorksheet('Combined');
// Get the header values for the first table in the workbook.
// This also saves the table list before we add the new, combined table.
const tables = workbook.getTables();
const headerValues = tables[0].getHeaderRowRange().getTexts();
console.log(headerValues);
// Copy the headers on a new worksheet to an equal-sized range.
const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length-1, headerValues[0].length-1);
targetRange.setValues(headerValues);
// Add the data from each table in the workbook to the new table.
const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
for (let table of tables) {
let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
let rowCount = table.getRowCount();
// If the table is not empty, add its rows to the combined table.
if (rowCount > 0) {
combinedTable.addRows(-1, dataValues);
}
}
}
Sample code: Combine data from multiple Excel tables in select worksheets into a single table
Download the sample file tables-select-copy.xlsx and use it with the following script to try it out yourself!
function main(workbook: ExcelScript.Workbook) {
// Set the worksheet names to get tables from.
const sheetNames = ['Sheet1', 'Sheet2', 'Sheet3'];
// Delete the "Combined" worksheet, if it's present.
workbook.getWorksheet('Combined')?.delete();
// Create a new worksheet named "Combined" for the combined table.
const newSheet = workbook.addWorksheet('Combined');
// Create a new table with the same headers as the other tables.
const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()[0].getHeaderRowRange().getTexts();
const targetRange = newSheet.getRange('A1').getResizedRange(headerValues.length-1, headerValues[0].length-1);
targetRange.setValues(headerValues);
const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
// Go through each listed worksheet and get their tables.
sheetNames.forEach((sheet) => {
const tables = workbook.getWorksheet(sheet).getTables();
for (let table of tables) {
// Get the rows from the tables.
let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
let rowCount = table.getRowCount();
// If there's data in the table, add it to the combined table.
if (rowCount > 0) {
combinedTable.addRows(-1, dataValues);
}
}
});
}
Training video: Combine data from multiple Excel tables into a single table
GitHub에서 Microsoft와 공동 작업
이 콘텐츠의 원본은 GitHub에서 찾을 수 있으며, 여기서 문제와 끌어오기 요청을 만들고 검토할 수도 있습니다. 자세한 내용은 참여자 가이드를 참조하세요.
Office Scripts