convertigo/c8oprj-lib-excel

This is the Microsoft Excel connector for Convertigo platform. Install this library to enable writing and reading from Microsoft Excel sheets on Office365 Cloud or local XSLX files for your Convertigo applications

lib_MicrosoftExcel

This is the Microsoft Excel connector for Convertigo platform. Install this library to enable writing and reading from Microsoft Excel sheets on Office365 Cloud or local XSLX files for your Convertigo applications.

Usage for Office 365

The Office 365 sequences will only work if you performed an OAuth Authentication to AzureAD first.

Configuring your Microsoft Azure AD OAuth

This connector uses the OAuth authentication protocol to exchange data with Microsoft Excel Sheets hosted on Microsoft OneDrive. You must configure OAuth in your Azure portal:

• Connect to https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
• Click on "new registration"
• Configure Name and Account type
• Add the http://localhost:18080/convertigo/projects/lib_OAuth/getToken.html redirect URI with the "Single-page application (SPA)" type. (Useful for Studio testing operations)
• Add the https://c8ocloud.convertigo.net/convertigo/projects/lib_OAuth/getToken.html with the "Single-page application (SPA)" type. (useful for production operations)
• In Authentication menu, check Access tokens (used for implicit flows) and ID tokens (used for implicit and hybrid flows)

Copy and save the Client ID and if necessary the Tenant ID as you will need these in the next steps.

Configuring Convertigo Symbols

lib_MicrosoftExcel needs some symbols to be configured. You configure them through the Web Console: https://<your site>.convertigo.net/admin, hit the symbols button to get to the symbol configuration page.

Symbol	value
lib_oauth.azuread.clientid	The client ID value you copied in the previous step
lib_oauth.azuread.tenantid	the tenenant id value you copied in the previous step. This is optional and should be used only if you want to restrict to a specific Azure tenant.

Usage for local Excel files

To upload an Excel file and output an XML or JSON structure use the sequence SheetGetRangeFile

To export data to an Excel output file use the sequence ExportDataToSheet

Table Of Content

For more technical informations : documentation

• Installation
• Sequences
  • checkAccessToken
  • ExportDataToSheet
  • loginAzureAdWithAccessToken
  • SheetGetRange
  • SheetGetRangeFile

Installation

1. In your Convertigo Studio click on to import a project in the treeview

2. In the import wizard

   

   paste the text below into the Project remote URL field:

   Usage	Click the copy button at the end of the line
   To contribute	lib_MicrosoftExcel=https://github.com/convertigo/c8oprj-lib-excel.git:branch=8.2.X
   To simply use	lib_MicrosoftExcel=https://github.com/convertigo/c8oprj-lib-excel/archive/8.2.X.zip

3. Click the Finish button. This will automatically import the lib_MicrosoftExcel project

Sequences

checkAccessToken

Checks is a valid access token is held by the current users' session for AzureAD

This as to be called by client apps to decide whenever or not they have to display an OAuth login screen

ExportDataToSheet

Use to export data to various file format (txt, csv, xlsx, ods...). Uses the SheetJS CE framework.

Output

name	comment
file	local_path -> Server local file path. url_path -> Server file path url.
success	true/false
error	Error message.

variables

name	comment
book_type	Type of workbook to export to. Default is XLSX.
data	Data source. Can be a Convertigo Complex variable or a JSON String. It must be an array of objects.
file_name	Name of the output file with extension.
sheet_name	Name of the XLSX Sheet if applicable.

loginAzureAdWithAccessToken

Perform the OAuth flow for AzureAD

If the token is valid, it will be stored in the user's session to be used when calling Microsoft APIs.

Also if the token is valid, setAuthenticatedUser step is executed to flag this session as authenticated.

variables

name	comment
access_token	Access Token

SheetGetRange

Read a Range of values from an Office 365 Sheet.

variables

name	comment
itemPath	The path of the file to import.
name	The sheet name in a workbook (If none provided, the first sheet in a workbook)
range	The range of cells to retrieve for ex: as A1:D10 (If not provided, all non empty cells in a sheet)

SheetGetRangeFile

Imports an xlsx file ( from "itemPath") and outputs a JSON object of the "name" Sheet by the range of "range". Uses the SheetJS CE framework.

variables

name	comment
itemPath	The local path of the file to import.
name	The sheet name in the workbook (If not provided, the first sheet in a workbook). Can not be empty string.
range	The range of cells to retrieve for ex: "A1:D10" (If not provided or empty string, all non empty cells in a sheet)