Excel download uit Exact Online in eigen formaat met een cloud app

Wiki (nl)
, , , ,
1

In dit artikel leer je hoe je met Invantive Cloud een kleine online applicatie kunt maken om massaal gegevens uit een of meerdere Exact Online administraties te downloaden als een Excel-bestand.

Excel en Exact Online download

Met verschillende producten van Invantive is het mogelijk om Excel bestanden te maken met gegevens uit (bijvoorbeeld) Exact Online:

Al deze tools kennen geen feitelijke limiet aan het aantal regels dat opgenomen kan worden, buiten de Excel-limieten. En zakelijke gebruikers zijn blij met de strakke structuur met kolommen en regels. Bovendien maken de Invantive producten ook een benoemd bereik (“named range”) aan zodat de gegevens gemakkelijk in een draaitabel gezet kunnen worden.

Toch zaten er wat beperkingen aan vast. Mac-gebruikers moesten zich bijvoorbeeld beperken tot Get My Report of het technische Invantive Data Hub. En Windows-gebruikers kunnen alleen via Get My Report even snel op een tablet een analyse ophalen.

Online Export van Exact Online met Invantive Cloud

Met eigen apps op Invantive Cloud is het nu ook mogelijk om vlot en betrouwbaar grote hoeveelheden cijfers uit Exact Online of andere platformen om te zetten naar handige Excel-bestanden.

Het eindresultaat ziet er bijvoorbeeld zo uit:

Voorbeeld Exact Online dump naar Excel

In dit voorbeeld behandelen we:

  • het registreren van een Exact Online database en een app,
  • het aanmaken van een Excel-bestand in een app,
  • het toevoegen van werkbladen met zelfgekozen naam,
  • het vullen van specifieke cellen met een waarde zoals “Dump of Exact Online…”,
  • het vullen van werkbladen met een SQL query.

Zoek je een voorbeeld voor een CSV-bestand, of export in JSON of XML-formaat, of een oplossing met Power Pivot of Power Query? De volgende artikelen helpen je op weg:

Voorbereidingen

De volgende voorbereidingen zijn nodig:

  • Een aanmeldcode op Invantive Cloud.
  • Een aanmeldcode op Exact Online met een of meerdere administraties.

Exact Online Database Definiëren

De eerste stap is het definiëren van een Exact Online database. Dat kan heel eenvoudig door dit stappenplan te volgen:

Deze Exact Online database kun je gebruiken voor Power BI, Power Query of Azure Data Factory, maar net zo gemakkelijk voor bijvoorbeeld Access OData of als bron voor een Invantive Cloud applicatie.

Cloud Applicatie Definiëren

De volgende stap is het definiëren van een app op Invantive Cloud. Voer de volgende stappen uit:

  • Selecteer in het menu ‘Applicaties’:
    Cloud applicaties
  • Kies ‘Nieuw’.
  • Vul de velden als volgt in:
    My First App op Invantive Cloud
  • Kies ‘Modules’.
  • Kies ‘Nieuw’.
  • Vul de velden als volgt in:
    Download module

Excel-bestand maken en Vullen

Deze module zal de code bevatten om het Excel-bestand aan te maken.

De volledige code staat aan het einde van dit artikel.

Wil je snel verder?

  • Plak dan de code in de module bij het veld “Broncode”.
  • Kies “My First App” in het kruimelspoor bovenaan.
  • Kies “Uitvoeren”.
  • Kies je Exact Online database.
  • Kies “Uitvoeren”:
    Applicatie draaien
  • Kies de module:
    Module draaien
  • Het Excel-bestand wordt gedownload.

Voor grote downloads langer dan 3 minuten is het nodig om de module te starten via Invantive App Online. Met Invantive App Online is het ook mogelijk om met IP-adresvalidatie en gebruikersnaam/wachtwoord de module uit te voeren, of de module achter een CDN te plaatsen.

Maar wat doet de code? De code is in de taal Invantive PSQL; dat is de procedurele uitbreiding op Invantive SQL.

Declare

Het blok met declare definieert een drietal variabelen:

declare
  l_workbook  excel_workbook;
  l_worksheet excel_worksheet;
  l_binary    blob;

Van deze variabelen hebben twee variabelen een datatype dat alleen in PSQL gebruikt kan worden:

  • excel_workbook
  • excel_worksheet

Een excel_workbook bevat een volledig Excel-werkboek. Elk werkboek bestaat uit een of meerdere werkbladen die gerepresenteerd worden door excel_worksheet.

Het datatype blob wordt gebruikt om het samengestelde (binaire) Excel-bestand in op te slaan.

Excel-werkboek en Voorblad

De eerste stap is het definiëren van een Excel-werkboek. Dat gebeurt door aanroepen naar een zogenaamde “package”: een bundeling van functies. Meer hierover is te lezen in A short overview of Invantive PSQL packages.

  --
  -- Create an Excel workbook.
  --
  l_workbook := excel.new();

Het werkboek is nog leeg. Er wordt een werkblad aan toegevoegd met de titel “Introduction” door de functie add_worksheet in de package excel aan te roepen:

  --
  -- Add a worksheet with a title.
  --
  l_worksheet := excel.add_worksheet
  ( l_workbook
  , 'Introduction'
  );

Vervolgens worden twee cellen gevuld met wat informatie via de functie 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));

Excel-werkblad met Query Vullen

Uiteraard kunnen de gegevens van een SQL-query op de Exact Online database 1-voor-1 met set_cell_contents binnen een for r in (query) loop...end loop in een Excel-werkblad gezet worden.

Maar sneller en gemakkelijker is het gebruik van de functie fill_using_query die in 1x een werkblad vult en opmaakt met de resultaten van een Invantive SQL query.

Er zijn verschillende manieren om de gegevens uit een Exact Online administratie in Excel te krijgen. Het eerste voorbeeld werkt met een tijdelijke tabel, die gevuld wordt met een query:

  --
  -- 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');

Deze aanpak heeft als voordeel dat het maken en vullen van de tijdelijke tabel geïsoleerd kan worden in een aparte module of PSQL functie. Het is ook mogelijk om een volledige Exact Online query direct mee te geven aan fill_using_query zoals hier met grootboekrekeningen:

  --
  -- 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'
  );

Omzetten naar XLSX-Bestand

In de variabele l_workbook zitten nu drie werkbladen. De volgende code zet die om in een XLSX-bestand voor Excel door een aanroep naar export_to_xlsx. Vervolgens wordt de binaire inhoud teruggegeven aan de browser via de cloud_http-package:

  --
  -- 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;

Volledige PSQL Code Exact Online Exporter naar 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;