Dans cet article, vous apprendrez comment créer une petite application en ligne avec Invantive Cloud afin de télécharger en masse des données provenant d’une ou plusieurs administrations Exact Online sous forme de fichier Excel.
Excel et téléchargement Exact Online
Avec différents produits d’Invantive, il est possible de créer des fichiers Excel contenant des données issues (par exemple) d’Exact Online :
- Invantive Control for Excel : voir par exemple De meest populaire Exact Online Excel add-in formules
- Get My Report : Get My Exact Online Report
- Invantive SQL et Invantive Script : voir par exemple Export all Outstanding Items (AP and AR) from Exact Online Companies
Aucun de ces outils n’impose de limite réelle au nombre de lignes pouvant être traitées, hormis les limites propres à Excel. Et les utilisateurs professionnels apprécient la structure claire en colonnes et lignes. De plus, les produits Invantive créent également une plage nommée (« named range ») afin que les données puissent facilement être placées dans un tableau croisé dynamique.
Cela comportait toutefois certaines limitations. Les utilisateurs de Mac devaient par exemple se limiter à Get My Report ou à l’Invantive Data Hub, plus technique. Et les utilisateurs de Windows ne pouvaient récupérer rapidement une analyse sur une tablette que via Get My Report.
Export en ligne d’Exact Online avec Invantive Cloud
Avec vos propres applications sur Invantive Cloud, il est désormais possible de convertir rapidement et de manière fiable de grandes quantités de données issues d’Exact Online ou d’autres plateformes en fichiers Excel pratiques.
Voici par exemple à quoi ressemble le résultat final :
Dans cet exemple, nous abordons :
- l’enregistrement d’une base de données Exact Online et d’une application,
- la création d’un fichier Excel dans une application,
- l’ajout de feuilles de calcul avec un nom personnalisé,
- le remplissage de cellules spécifiques avec une valeur telle que « Dump of Exact Online… »,
- le remplissage de feuilles de calcul avec une requête SQL.
Vous cherchez un exemple pour un fichier CSV, ou un export au format JSON ou XML, ou encore une solution avec Power Pivot ou Power Query ? Les articles suivants vous aideront à démarrer :
- JSON genereren vanuit Invantive UniversalSQL
- Laad Exact Online in Qlik Sense en Tableau
- Ophalen in CSV van RIVM Covid-19 Gegevens
- Export all Outstanding Items (AP and AR) from Exact Online Companies
- Exporteer Exact Online Stukslijsten naar Excel
- Binnenhalen artikelvoorraden in Excel Power Query
- Verbind Power Query met Exact Online
Préparatifs
Les préparatifs suivants sont nécessaires :
- Un identifiant de connexion sur Invantive Cloud.
- Un identifiant de connexion sur Exact Online avec une ou plusieurs administrations.
Définir une base de données Exact Online
La première étape consiste à définir une base de données Exact Online. Cela peut se faire très simplement en suivant ce guide étape par étape :
Cette base de données Exact Online peut être utilisée pour Power BI, Power Query ou Azure Data Factory, mais tout aussi facilement pour, par exemple, Access OData ou comme source pour une application Invantive Cloud.
Définir une application Cloud
L’étape suivante consiste à définir une application sur Invantive Cloud. Suivez les étapes suivantes :
- Sélectionnez « Applications » dans le menu :
- Choisissez « Nouveau ».
- Remplissez les champs comme suit :
- Choisissez « Modules ».
- Choisissez « Nouveau ».
- Remplissez les champs comme suit :
Créer et remplir le fichier Excel
Ce module contiendra le code permettant de créer le fichier Excel.
Le code complet se trouve à la fin de cet article.
Vous voulez avancer rapidement ?
- Collez alors le code dans le module, dans le champ « Code source ».
- Choisissez « My First App » dans le fil d’Ariane en haut de la page.
- Choisissez « Exécuter ».
- Choisissez votre base de données Exact Online.
- Choisissez « Exécuter » :
- Choisissez le module :
- Le fichier Excel est téléchargé.
Pour les téléchargements volumineux dépassant 3 minutes, il est nécessaire de lancer le module via Invantive App Online. Avec Invantive App Online, il est également possible d’exécuter le module avec validation d’adresse IP et nom d’utilisateur/mot de passe, ou de placer le module derrière un CDN.
Mais que fait ce code ? Le code est écrit dans le langage Invantive PSQL ; il s’agit de l’extension procédurale d’Invantive SQL.
Declare
Le bloc declare définit trois variables :
declare
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
Parmi ces variables, deux ont un type de données utilisable uniquement en PSQL :
excel_workbookexcel_worksheet
Un excel_workbook contient un classeur Excel complet. Chaque classeur se compose d’une ou plusieurs feuilles de calcul représentées par excel_worksheet.
Le type de données blob est utilisé pour stocker le fichier Excel (binaire) assemblé.
Classeur Excel et page de garde
La première étape consiste à définir un classeur Excel. Cela se fait via des appels à ce qu’on appelle un « package » : un regroupement de fonctions. Vous pouvez en apprendre davantage à ce sujet sur A short overview of Invantive PSQL packages.
--
-- Create an Excel workbook.
--
l_workbook := excel.new();
Le classeur est encore vide. Une feuille de calcul intitulée « Introduction » y est ajoutée en appelant la fonction add_worksheet du package excel :
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Introduction'
);
Ensuite, deux cellules sont remplies avec quelques informations via la fonction set_cell_contents :
excel.set_cell_contents(l_worksheet, 'A1', 'Dump of Exact Online data using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A2', 'Created ' || to_char(sysdate));
Remplir une feuille de calcul Excel avec une requête
Bien entendu, les données d’une requête SQL sur la base de données Exact Online peuvent être placées une à une dans une feuille de calcul Excel avec set_cell_contents, à l’intérieur d’une boucle for r in (query) loop...end loop.
Mais il est plus rapide et plus simple d’utiliser la fonction fill_using_query, qui remplit et met en forme en une seule fois une feuille de calcul avec les résultats d’une requête Invantive SQL.
Il existe différentes façons de récupérer les données d’une administration Exact Online dans Excel. Le premier exemple utilise une table temporaire, remplie par une requête :
--
-- Add a worksheet for Exact Online articles.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Exact Online Articles'
);
--
-- Create a list of articles consisting of code, description and EAN.
--
create or replace table itm@inmemorystorage
as
select itm.code
, itm.description
, itm.barcode
from ItemsIncremental@eol itm
order
by itm.code
;
--
-- Place the items in the Excel worksheet.
--
excel.fill_using_query(l_worksheet, 'select * from itm@inmemorystorage');
Cette approche a l’avantage que la création et le remplissage de la table temporaire peuvent être isolés dans un module distinct ou une fonction PSQL séparée. Il est également possible de transmettre directement une requête Exact Online complète à fill_using_query, comme ici avec les comptes du grand livre :
--
-- Add a worksheet for Exact Online General Ledger Accounts.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Exact Online GL Accounts'
);
--
-- Place the General Ledger accounts in the Excel worksheet.
--
-- Instead of using a temporary table, this approach uses a direct query to fill
-- the Excel worksheet.
--
excel.fill_using_query
( l_worksheet
, 'select gat.code, gat.description'
|| ' from GLAccountsIncremental@eol gat'
|| ' order by gat.code'
);
Conversion en fichier XLSX
La variable l_workbook contient désormais trois feuilles de calcul. Le code suivant les convertit en un fichier XLSX pour Excel grâce à un appel à export_to_xlsx. Le contenu binaire est ensuite renvoyé au navigateur via le package cloud_http :
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="items.xlsx"');
end;
Code PSQL complet de l’exportateur Exact Online vers Excel
declare
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
begin
--
-- Create an Excel workbook.
--
l_workbook := excel.new();
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Introduction'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Dump of Exact Online data using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A2', 'Created ' || to_char(sysdate));
--
-- Add a worksheet for Exact Online articles.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Exact Online Articles'
);
--
-- Create a list of articles consisting of code, description and EAN.
--
create or replace table itm@inmemorystorage
as
select itm.code
, itm.description
, itm.barcode
from ItemsIncremental@eol itm
order
by itm.code
;
--
-- Place the items in the Excel worksheet.
--
excel.fill_using_query(l_worksheet, 'select * from itm@inmemorystorage');
--
-- Add a worksheet for Exact Online General Ledger Accounts.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Exact Online GL Accounts'
);
--
-- Place the General Ledger accounts in the Excel worksheet.
--
-- Instead of using a temporary table, this approach uses a direct query to fill
-- the Excel worksheet.
--
excel.fill_using_query
( l_worksheet
, 'select gat.code, gat.description'
|| ' from GLAccountsIncremental@eol gat'
|| ' order by gat.code'
);
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="items.xlsx"');
end;





