How to create dynamically headers in excel using javascript Api

Question

In excel we create a table we have to pass a static address for adding columns to the table. I want to add columns dynamically, not bypassing the excel address. Is this possible in excel?

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/1/2017", "The Phone Company", "Communications", "$120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
        ["1/11/2017", "Bellows College", "Education", "$350"],
        ["1/15/2017", "Trey Research", "Other", "$135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    await context.sync();
});

Shivharakh, do you want to 1. add a new table and then add data to it, 2. format existing data in a worksheet as a table or 3. add a column to an existing table? — Jakob Nielsen-MSFT, Nov 04 '22 at 14:36
I want to add new table then with dynamic headers, add data to it. @JakobNielsen-MSFT — Shiv Yadav, Nov 05 '22 at 14:19

score 1 · Answer 1 · answered Nov 06 '22 at 18:11

The following sample code shows how to create a table add rows to it in a more dynamic way compared the code sample that you provided in your question.

  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    const header = ["Date", "Merchant", "Category", "Amount"];

    const rows = [
      ["1/1/2017", "The Phone Company", "Communications", "$120"],
      ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
      ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
      ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
      ["1/11/2017", "Bellows College", "Education", "$350"],
      ["1/15/2017", "Trey Research", "Other", "$135"],
      ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]

    // Add header to the grid
    const headerRange = sheet.getRange("A1").getResizedRange(0, header.length - 1);
    headerRange.values = [header];

    // Add table
    const expensesTable = sheet.tables.add(headerRange, true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    // Add rows to the table
    expensesTable.rows.add(null /*add at the end*/, rows);

    // Autofit columns and rows
    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    await context.sync();
  });

Glad it is working for you Shivharakh! Consider marking the question as answered or upvote the answer. — Jakob Nielsen-MSFT, Nov 07 '22 at 07:58

How to create dynamically headers in excel using javascript Api

1 Answers1