Create an Excel-file with PL/SQL

Hi Anton,
thanks a lot for this very usefull package.
I took time to add a personnal “Cursor2Sheet” procedure which works also on Oracle 10 (using XMLType in between

I Post it here incase it is of any use to anybody :

/************************************/
procedure cursor2sheet
( p_sql in sys_refcursor
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
, p_footer boolean := true
)
is

ctx dbms_xmlgen.ctxHandle;
tmpXml XMLType ;

cursor cData is
Select
t2.Column_Value.GetRootElement() ColName
, ExtractValue(t2.Column_Value, ‘node()’) Value
From Table(XMLSequence(tmpXml)) t
, Table(XMLSequence(Extract(t.Column_Value,’/ROWSET/ROW/node()’))) t2
Order by RowNum ;

tsColHeaders SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
tsValues SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
t_sheet pls_integer := 1;
t_cur_row pls_integer := 1;
colId pls_integer := 1;
nColNumber pls_integer;
n pls_integer;

aTmpVal SYS.AnyData ;
nNumVal Number ;
tTSVal TimeStamp ;
dDateVal Date ;
sVarcharVal Varchar2(4000) ;
bGotType boolean ;
eTypeConvert Exception ;
eTypeDateFormat Exception ;
eTypeNonNumeric Exception ;
eTypeNotDefined Exception ;
pragma exception_init(eTypeConvert,-6502);
pragma exception_init(eTypeDateFormat,-1830);
pragma exception_init(eTypeNonNumeric,-1858);

begin

— XML Creation from the sys_refcursor
ctx := dbms_xmlgen.newContext(p_sql);
— this is important in order to get all the column headers, even if all data are null
dbms_xmlgen.SetNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
dbms_xmlgen.getXMLType(ctx, TmpXml);

if p_sheet is null
then
new_sheet;
end if;

— Load Columns and Values into Arrays
Open cData ;
Fetch cData bulk collect into tsColHeaders, tsValues ;
Close cData ;

— get distinct headers
tsColHeaders := set(tsColHeaders) ;
— get number of headers (of columns)
nColNumber := tsColHeaders.count ;

— Create column headers if wanted
If p_column_headers
Then

— set headers into sheet
For i in tsColHeaders.first .. tsColHeaders.last
Loop
cell(i, t_cur_row, tsColHeaders(i), p_fontId => get_font(‘Calibri’, p_bold => true), p_sheet => t_sheet);
End Loop ;
t_cur_row := 2;

End if;

t_sheet := nvl(p_sheet, workbook.sheets.count());

— fill cells
For i in tsValues.first .. tsValues.last
Loop
— check if we must reset col to 1 and go to next line
If i > nColNumber
and mod(i ,nColNumber) = 1
Then
— reset colId to 1 and go to next line
colId := 1 ;
t_cur_row := t_cur_row + 1;
End If ;

— find the good type and insert into Cell
— initialize “checker”
bGotType := false ;

— Number ?
If Not bGotType
Then
Begin
aTmpVal := Sys.AnyData.ConvertNumber(tsValues(i)) ;
bGotType := true ;
n := aTmpVal.GetNumber(nNumVal) ;
— load data into cell
cell(colId, t_cur_row, nNumVal, p_sheet => t_sheet ) ;

— if conversion fails
Exception
When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
Then
bGotType := false ;

End ;
End If ;

— TimeStamp ?
If Not bGotType
Then
Begin
aTmpVal := Sys.AnyData.ConvertTimestamp(tsValues(i)) ;
bGotType := true ;
n := aTmpVal.GetTimeStamp(tTSVal) ;
— load data into cell
cell(colId, t_cur_row, to_date(tTSVal), p_sheet => t_sheet ) ;

— if conversion fails
Exception
When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
Then
bGotType := false ;

End ;
End If ;

— Date ?
If Not bGotType
Then
Begin
aTmpVal := Sys.AnyData.ConvertDate(tsValues(i)) ;
bGotType := true ;
n := aTmpVal.GetDate(dDateVal) ;
— load data into cell
cell(colId, t_cur_row, dDateVal, p_sheet => t_sheet ) ;

— if conversion fails
Exception
When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
Then
bGotType := false ;

End ;
End If ;

— Varchar2 ?
If Not bGotType
Then
Begin
aTmpVal := Sys.AnyData.ConvertVarchar2(tsValues(i)) ;
bGotType := true ;
n := aTmpVal.GetVarchar2(sVarcharVal) ;
— load data into cell
cell(colId, t_cur_row, sVarcharVal, p_sheet => t_sheet ) ;

— if conversion fails
Exception
When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
Then
bGotType := false ;

End ;
End If ;

— unsupported type
If Not bGotType
Then
raise eTypeNotDefined ;
End If ;

— go to next col
colId := colId + 1 ;

End Loop ;

If p_footer
Then
— set footer
cell(1, t_cur_row+2 , ‘Generated ‘||sysdate||’ by ‘||user, p_sheet => t_sheet ) ;
End If ;

if ( p_directory is not null and p_filename is not null )
then
save( p_directory, p_filename );
end if;

exception
when eTypeNotDefined
then
raise_application_error(-20999,’one data has an unsupported type’, false);
raise;
when others
then
raise_application_error(-20999,’Export to XLSX failed’, true);

end;