Met Invantive UniversalSQL kunnen Excel-files omgezet worden via een select op een exceltable, zoals bijvoorbeeld beschreven in Combining Excel-based sales estimate with Exact Online actuals.
De andere richting - namelijk het omzetten van resultaten uit een query in Excel - is ook mogelijk vanaf versie 22.0.16. De werking is vergelijkbaar met het omzetten naar XML en JSON met, respectievelijk, for xml en for json (voorbeeld). Het lijkt ook op het omzetten naar CSV zoals beschreven in CSV genereren vanuit Invantive Cloud en UniversalSQL.
De for excel clause
Het omzetten van de resultaten van een select SQL query naar het Excel xlsx-formaat gebeurt door een for excel clause toe te voegen aan het einde van de query. Deze for excel clause zorgt er voor dat de query maar één kolom teruggeeft met in de kolom Excel de waarde die de volledige resultaten van het select-statement bevat. Er komt altijd precies één rij terug met daarin het binaire xlsx-bestand.
Via extra mogelijkheden op de for excel clause kan het uitvoerformaat gepreciseerd worden:
Het meestgebruikte (en standaard) Excel-uitvoerformaat is een werkboek met in het eerste werkblad een koprij en daarna de data met per rij één record.
Excel uitvoer van webpagina’s op app-online
De Excel xlsx-file die als uitvoer uit een query met for excel clause komt is eenvoudig terug te geven via een applicatiemodule op Invantive Cloud. Dat kan voor grootschalig gebruik ook via app-online. Vergeet in dat geval niet om het Content Type van de gegenereerde pagina in te stellen op application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. Op deze wijze kunnen eenvoudig rapporten gemaakt worden die met enkele of meerdere queries Excel teruggeven voor verdere verwerking in andere pakketten of EDI.
Excel bestand wegschrijven
Het wegschrijven van het Excel-bestand vanuit Invantive Query Tool en andere on-premises producten gaat met Invantive Script. Een voorbeeld:
select Excel as blobcontents
, 'my-first-excel.xlsx' filename
from ( select lnd_code
, lnd_omschrijving
from bubs_landen_v@ora
for excel
)
local export documents in blobcontents to "c:\temp\" filename column filename
Geheugenbeslag for xlsx
De for excel clause geeft één rij terug met één kolom. De omvang kan niet groter worden dan 2 GB. Bovendien gelden de reguliere eisen voor een Excel werkboek zoals maximaal één miljoen rijen.
Select excel kolom
De for excel achter de query verandert het aantal kolommen die uit de query terugkomen. Alle kolommen van de query verdwijnen en worden vervangen door de kolom excel die de uitkomst van een groepfunctie op de opgevraagde waarden is. Echter, dit gebeurt pas na het selecteren van de kolommen met select * of select COLUMNS.
Alhoewel het logisch is dat de for excel gebeurt na het selecteren van de op te nemen kolommen, kan het verwarrend zijn. Een query zoals:
select excel excel_new
from dual@datadictionary
for json auto
is niet mogelijk omdat de excel kolom al de oorspronkelijke kolommen vervangt na de selectie. Als de kolomnaam absoluut ingesteld moet worden, gebruik dan een constructie zoals:
select excel excel_new
from ( select *
from dual@datadictionary
for excel
)