Build Excel in Oracle using xlsx_builder

I mentioned in a recent post that I was looking to automate the production of some Excel workbooks from an Oracle Database. I think I finally hit upon a solution that might get approved for use. The answer might well be the xlsx builder package. 

There are some useful comments here:

https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/

The package is here: 

https://technology.amis.nl/wp-content/uploads/2021/03/as_xlsx20.txt

I was keen to give the package a go so compiled it on my home laptop. The only extra config that was required was setting up a file directory, a quick Google sorted that along with knowing I needed to call this in caps. 

Once compiled it was simply a case of calling the package. There is no real documentation that I could find however producing basic extracts was easy. 

	begin
	as_xlsx.clear_workbook;
	as_xlsx.new_sheet( 'query 1' );
	as_xlsx.query2sheet( 'select * from cast_dim', p_sheet => 1 );
	as_xlsx.new_sheet( 'query 2' );
	as_xlsx.query2sheet( 'select * from director_dim', p_sheet => 2 ) ;
	as_xlsx.save('TEST_FILES', 'my2.xlsx' );
	end;

view raw ExcelOracle.sql hosted with ❤ by GitHub

This produced a nice spreadsheet as shown below. 

The output was as expected, 2 tabs, with the right data and all the data. Time to give it some stress testing and to see what else it can do in terms of data volume and formatting etc.