@microsoft/connected-workbooks
TypeScript icon, indicating that this package has built-in type declarations

3.0.0 • Public • Published

Connected Workbooks

Build Status License

A pure JS library, Microsoft backed, that provides xlsx workbook generation capabilities, allowing for:

  1. Fundemental "Export to Excel" capabilities for tabular data (landing in a table in Excel).
  2. Advanced capabilities of "Export a Power Query connected workbook":
    • Can refresh your data on open and/or on demand.
    • Allows for initial data population.
    • Supports more advanced scenarios where you provide branded/custom workbooks, and load your data into PivotTables or PivotCharts.

Connected Workbooks allows you to avoid "data dumps" in CSV form, providing a richer experience with Tables and/or connected Queries for when your business application supports it.

Learn about Power Query here

Where is this library used? here are some examples:

Kusto LogAnalytics Datamart VivaSales
Azure Data Explorer Log Analytics Datamart Viva Sales

How do I use it? here are some examples:

1. Export a table directly from an Html page:

import workbookManager from '@microsoft/connected-workbooks';

const blob = await workbookManager.generateTableWorkbookFromHtml(document.querySelector('table') as HTMLTableElement);    
workbookManager.downloadWorkbook(blob, "MyTable.xlsx");

2. Export a table from raw data:

import workbookManager from '@microsoft/connected-workbooks';

const grid = {
  config: { promoteHeaders:true, adjustColumnNames:true }
  data: [
      ["Product", "Price", "InStock", "Category", "Date"],
      ["Widget A", 19.99, true, "Electronics", "10/26/2024"],
      ["Gizmo B", 9.99, true, "Accessories", "10/26/2024"],
      ["Bubala", 14.99, false, "Accessories", "10/22/2023"],
      ["Thingamajig C", 50, false, "Tools", "5/12/2023"],
      ["Doohickey D", 50.01, true, "Home", "8/12/2023"]
  ]
};
const blob = await workbookManager.generateTableWorkbookFromGrid(grid);    
workbookManager.downloadWorkbook(blob, "MyTable.xlsx");

image

3. Control Document Properties:

const blob = await workbookManager.generateTableWorkbookFromHtml(
  document.querySelector('table') as HTMLTableElement, {
    docProps: { 
      createdBy: 'John Doe',
      lastModifiedBy: 'Jane Doe',
      description: 'This is a sample table'
    }
  }
);
    
workbookManager.downloadWorkbook(blob, "MyTable.xlsx");

image

4. Export a Power Query connected workbook:

import workbookManager from '@microsoft/connected-workbooks';

const blob = await workbookManager.generateSingleQueryWorkbook({
  queryMashup: 'let \
                    Source = {1..10} \
                in \
                    Source',
  refreshOnOpen: true
});

workbookManager.downloadWorkbook(blob, "MyConnectedWorkbook.xlsx");

image

(after refreshing on open)

Advanced Usage - bring your own template:

You can use the library with your own workbook as a template!

const blob = await workbookManager.generateSingleQueryWorkbook(
  { queryMashup: query, refreshOnOpen: true },
  undefined /* optional Grid */,
  templateFile);
workbookManager.downloadWorkbook(blob, "MyBrandedWorkbook.xlsx");

image

Template requirements:

Have a single query named Query1 loaded to a Query Table, Pivot Table, or a Pivot Chart.

Recommendation - have your product template baked and tested in your own product code, instead of your user providing it.

For user templates - a common way to get the template workbook with React via user interaction:

const [templateFile, setTemplateFile] = useState<File | null>(null);
...
<input type="file" id="file" accept=".xlsx" style={{ display: "none" }} onChange={(e) => {
  if (e?.target?.files?.item(0) == null) return;
  setTemplateFile(e!.target!.files!.item(0));
}}/>

API

The library exposes a workbookManager, which generates a workbook via several APIs:

1. Generate a Power Query connected workbook

 async function `generateSingleQueryWorkbook`: `Promise<Blob>`
Parameter Type Required Description
query QueryInfo required Power Query mashup
grid Grid optional Initial grid data
fileConfigs FileConfigs optional Custom file configurations

2. Generate a table workbook from a Html page

async function `generateTableWorkbookFromHtml`: `Promise<Blob>`
Parameter Type Required Description
htmlTable HTMLTableElement required Initial data loaded to workbook
fileConfigs FileConfigs optional Custom file configurations

3. Generate a table workbook with raw data

async function `generateTableWorkbookFromGrid`: `Promise<Blob>`
Parameter Type Required Description
grid Grid required Initial data loaded to workbook
fileConfigs FileConfigs optional Custom file configurations

Types

QueryInfo

Parameter Type Required Description
queryMashup string required Mashup string
refreshOnOpen boolean required Should workbook data refresh upon open
queryName string optional Query name, defaults to "Query1"

Grid

Parameter Type Required Description
data (string | number | boolean)[][] required Grid data
config GridConfig optional customizations to Grid handling (see GridConfig)

GridConfig

Parameter Type Required Description
promoteHeaders boolean optional Should first row of gridData be used as the header, defaults to false - generating "Column1", "Column2"...
adjustColumnNames boolean optional Should column names be adjusted to be valid Excel names (Fix duplicates for example), defaults to true

FileConfigs

Parameter Type Required Description
templateFile File optional Custom Excel workbook
docProps DocProps optional Custom workbook properties

DocProps

Parameter Type Required
title string optional
subject string optional
keywords string optional
createdBy string optional
description string optional
lastModifiedBy string optional
category string optional
revision number optional

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

Package Sidebar

Install

npm i @microsoft/connected-workbooks

Weekly Downloads

20

Version

3.0.0

License

MIT

Unpacked Size

277 kB

Total Files

51

Last publish

Collaborators

  • ron.microsoft
  • microsoft1es