Download Custom Excel by Select Query on button click ( without IR/IG Report )

- January 18, 2021

Note 1:- Pre-rendering only advisable if you want to generate excel from Interactive Grid Page other wise below process you can directly call from submit Page --> process

Note 2:- If calling from submit Page (button) --> then make sure Reload on Submit should be "Always" on page property

1) Create Blank Page --> Pre-Rendering --> Before Header --> Process --> Download Excel

declare
l_highlights apex_data_export.t_highlights;
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
l_print_config apex_data_export.t_print_config;
begin
-- apex_data_export.add_highlight(
-- p_highlights => l_highlights,
-- p_id => 1,
-- p_value_column => 'HIGHLIGHT1',
-- p_display_column => null,
-- p_text_color => '#FFFF',
-- p_background_color => '#F5B642');
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select rownum as "SR.NO",
INCITMTYP as "ITEM.TYPE",
prtpfx as "PRTPFX",
item_code as "PART CODE",
item_name as "DESCRIPTION",
unit as "UNIT",
null as "BATCH.NO",
null as "Prod.Date",
null as "Exp.Date",
txt6 as "COUNTRY OF ORIGIN",
null as "QTY",
null as "RATE",
null as "VAT%"
from item_mast');

-- l_print_config := apex_data_export.get_print_config(
-- p_body_font_color => '#4B4540',
-- p_page_header => 'Header - ????',
-- p_page_header_font_color => '#4B4540',
-- p_page_header_font_size => 14,
-- p_page_header_font_weight => apex_data_export.c_font_weight_bold,
-- p_page_footer => 'Footer - ????',
-- p_page_footer_font_color => '#4B4540',
-- p_page_footer_font_size => 14,
-- p_page_footer_font_weight => apex_data_export.c_font_weight_bold,
-- p_border_width => 1,
-- p_border_color => '#4B4540');
l_export := apex_data_export.export (
p_context => l_context,
p_format => 'XLSX',
p_print_config => l_print_config,
p_highlights => l_highlights );
apex_exec.close( l_context );
apex_data_export.download( p_export => l_export );
exception
when others then
apex_exec.close( l_context );
raise;
end;