Excel-nedlasting fra Exact Online i eget format med en skyapp

I denne artikkelen lærer du hvordan du med Invantive Cloud kan lage en liten nettbasert applikasjon for å laste ned store mengder data fra en eller flere Exact Online-administrasjoner som en Excel-fil.

Excel og Exact Online-nedlasting

Med ulike produkter fra Invantive er det mulig å lage Excel-filer med data fra (for eksempel) Exact Online:

Ingen av disse verktøyene har noen reell begrensning på antall rader som kan inkluderes, utover Excels egne grenser. Og forretningsbrukere setter pris på den stramme strukturen med kolonner og rader. I tillegg oppretter Invantive-produktene også et navngitt område (“named range”) slik at dataene enkelt kan settes inn i en pivottabell.

Likevel var det noen begrensninger. Mac-brukere måtte for eksempel nøye seg med Get My Report eller det tekniske Invantive Data Hub. Og Windows-brukere kan bare raskt hente ut en analyse på et nettbrett via Get My Report.

Nettbasert eksport av Exact Online med Invantive Cloud

Med egne apper på Invantive Cloud er det nå også mulig å raskt og pålitelig konvertere store mengder tall fra Exact Online eller andre plattformer til praktiske Excel-filer.

Sluttresultatet kan for eksempel se slik ut:

I dette eksempelet går vi gjennom:

  • registrering av en Exact Online-database og en app,
  • opprettelse av en Excel-fil i en app,
  • tillegg av arbeidsark med selvvalgt navn,
  • fylling av spesifikke celler med en verdi som “Dump of Exact Online…”,
  • fylling av arbeidsark med en SQL-spørring.

Leter du etter et eksempel for en CSV-fil, eller eksport i JSON- eller XML-format, eller en løsning med Power Pivot eller Power Query? Følgende artikler hjelper deg videre:

Forberedelser

Følgende forberedelser er nødvendige:

  • En påloggingskode til Invantive Cloud.
  • En påloggingskode til Exact Online med en eller flere administrasjoner.

Definere Exact Online-database

Første steg er å definere en Exact Online-database. Det kan gjøres enkelt ved å følge denne trinnvise veiledningen:

Denne Exact Online-databasen kan du bruke til Power BI, Power Query eller Azure Data Factory, men like gjerne for eksempel for Access OData eller som kilde for en Invantive Cloud-applikasjon.

Definere Cloud-applikasjon

Neste steg er å definere en app på Invantive Cloud. Utfør følgende trinn:

Opprette og fylle Excel-fil

Denne modulen skal inneholde koden for å opprette Excel-filen.

Den fullstendige koden finner du nederst i denne artikkelen.

Vil du komme i gang raskt?

For store nedlastinger som tar lengre enn 3 minutter, er det nødvendig å starte modulen via Invantive App Online. Med Invantive App Online er det også mulig å kjøre modulen med IP-adressevalidering og brukernavn/passord, eller å plassere modulen bak et CDN.

Men hva gjør koden egentlig? Koden er skrevet i språket Invantive PSQL; dette er den prosedurale utvidelsen av Invantive SQL.

Declare

Blokken med declare definerer tre variabler:

declare
  l_workbook  excel_workbook;
  l_worksheet excel_worksheet;
  l_binary    blob;

Av disse variablene har to variabler en datatype som bare kan brukes i PSQL:

  • excel_workbook
  • excel_worksheet

En excel_workbook inneholder en komplett Excel-arbeidsbok. Hver arbeidsbok består av ett eller flere arbeidsark som representeres av excel_worksheet.

Datatypen blob brukes til å lagre den sammensatte (binære) Excel-filen.

Excel-arbeidsbok og forside

Første steg er å definere en Excel-arbeidsbok. Dette gjøres ved kall til en såkalt “package”: en samling funksjoner. Mer om dette kan du lese i A short overview of Invantive PSQL packages.

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

Arbeidsboken er fortsatt tom. Det legges til et arbeidsark med tittelen “Introduction” ved å kalle funksjonen add_worksheet i pakken excel:

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

Deretter fylles to celler med litt informasjon via funksjonen 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));

Fylle Excel-arbeidsark med spørring

Selvfølgelig kan dataene fra en SQL-spørring mot Exact Online-databasen settes inn én for én med set_cell_contents innenfor en for r in (query) loop...end loop i et Excel-arbeidsark.

Men det er raskere og enklere å bruke funksjonen fill_using_query, som i ett steg fyller og formaterer et arbeidsark med resultatene fra en Invantive SQL-spørring.

Det finnes flere måter å hente data fra en Exact Online-administrasjon til Excel på. Det første eksempelet bruker en midlertidig tabell, som fylles med en spørring:

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

Denne tilnærmingen har fordelen av at opprettelsen og fyllingen av den midlertidige tabellen kan isoleres i en egen modul eller PSQL-funksjon. Det er også mulig å sende en fullstendig Exact Online-spørring direkte til fill_using_query, som her med hovedbokskontoer:

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

Konvertere til XLSX-fil

Variabelen l_workbook inneholder nå tre arbeidsark. Følgende kode konverterer disse til en XLSX-fil for Excel gjennom et kall til export_to_xlsx. Deretter returneres det binære innholdet til nettleseren via cloud_http-pakken:

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

Fullstendig PSQL-kode for Exact Online-eksport til 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;