While using packeage as_xlsx the row format not applying

Question

I'm using Mr. Scheffer's as_xlsx package as_xlsx

Evrything is fine, but if i want to make my first row (my header) bold, it's not working, and i dont know why.. i dont get any error.

BEGIN
declare sDatum varchar2(10); sDatumFile varchar2(8); sSelect varchar2(2000); 
BEGIN
sDatum:=to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'DD.MM.YYYY');
sDatumFile:=to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'DDMMYYYY');
select sql_command into sSelect from TABLE_SQL where id=9;
--reset format
as_xlsx.clear_workbook;
--query2sheet
as_xlsx.query2sheet(sSelect); 
--format width & bold
for i in 1 .. 13
loop
as_xlsx.set_column_width( p_col => i, p_width => 10);
end loop;
--this procedure is not working properly
as_xlsx.set_row(p_row => 1, p_fontID => as_xlsx.get_font('Calibri', p_fontsize => 11, p_bold => true), p_alignment => as_xlsx.get_alignment(p_vertical => 'center', p_wraptext => true));
--is working again
as_xlsx.set_column_width( p_col => 2, p_width => 50);
as_xlsx.set_column_width( p_col => 3, p_width => 35);
--save
as_xlsx.save('d:\oracle\exp', 'analysis '||sDatumFile||'.xlsx' ); 
--send
pck_comm.mail_ora_priloha('xy@somedns.com','xy@somedns.com','analysis - to '||sDatum,'analysis to: '||sDatum,'analysis '||sDatumFile||'.xlsx','DIR_EXPORTY');
--delete
utl_file.fremove('d:\oracle\exp', 'analysis '||sDatumFile||'.xlsx');
END;

If i tried to add to as_xlsx.query2sheet(sSelect); paramater p_sheet=>1 then evrything is bold, and i get 10+ sheets. In theese sheets column headers are separetly in first row. Data is in 1. sheet only.

Any suggestion ?

ps: i made an array, with my column names and added the as_xlsx.cell procedure into loop:

for j in 1 .. array.count loop
as_xlsx.cell(p_col =>j, p_row => 1, p_value => array(j), p_fontid =>as_xlsx.get_font('Calibri', p_bold => true),p_alignment =>as_xlsx.get_alignment(p_vertical => 'center', p_wraptext => true) );

In this case, it's working. But still i dont know, why set_row is not working.

Answer 1

It looks like there is a problem with use of as_xlsx procedures and functions. It is not the same if you set row before or after query2sheet. Tested your code with my data and got the same issue. But when I created my own sheet and reordered the steps it worked ok.

Declare   
    sSelect varchar2(2000) := 'Select * From MY_TABLE'; 
Begin
    --  1st reset format
        as_xlsx.clear_workbook;

    --  2nd create the sheet
        as_xlsx.new_sheet('TestSheet');

    --  3rd set row 1 bold for your sheet
        as_xlsx.set_row(  p_row => 1, p_fontID => as_xlsx.get_font('Calibri', p_bold => true), p_sheet => 1 );

    --  4th populate sheet with your data
        as_xlsx.query2sheet(p_sql => sSelect, p_sheet => 1); 

    --  5th set column widths
        as_xlsx.set_column_width( p_col => 1, p_width => 20);
        as_xlsx.set_column_width( p_col => 2, p_width => 10);
        as_xlsx.set_column_width( p_col => 3, p_width => 10);
        as_xlsx.set_column_width( p_col => 4, p_width => 10);
        as_xlsx.set_column_width( p_col => 5, p_width => 100);
        as_xlsx.set_column_width( p_col => 6, p_width => 100);
        as_xlsx.set_column_width( p_col => 7, p_width => 10);

    --  6th save
        as_xlsx.save('YourDirectoryObject', 'YourFileName.xlsx' ); 
End;

Result: