Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL

My EXPORTER package + Java code has functions called WriteExcelSpreadsheet that can be used to create and export any SQL SELECT statement to disk as an Excel Spreadsheet using Oracle database’s DBMS_JOB or DBMS_SCHEDULER.

Because Excel Spreadsheets have limited number of rows (65535) and columns (255) per sheet (the maximum size of data in a cell is 32767 characters), control of what I call the overflow of a result-set’s contents across Sheets can be controlled by the _stratification parameters:

If number of rows in result-set is greater than 65535 and _stratification is N (NONE) or V (VERTICAL) then the result-set processing will only output 65535 rows in the first sheet. No more sheets will be created and no more data output.

If _stratification is H (HORIZONTAL) a new sheet is created for the next 65535 rows etc.

If the result-set contains greater than 255 columns and _stratification is set to V (VERTICAL) then the first 255 columns will be in the first sheet, the next 255 in the second sheet etc up to the maximum number of rows that can be output in a SELECT statement.

If greater than 255 columns exist and _stratification is H or N then only the first 255 columns will be output in the first sheet: if greater than 65535 rows also exists then overflow is controlled by _stratification = H or N.

You will note that when exporting a SQL SELECT statement with an SDO_GEOMETRY object that you can specify the export text format for all the SDO_GEOMETRY objects in the result set. This format can be Well Known Text (WKT) or Geography Markup Language (GML) version 2 (version 3 can only be made available for Oracle databases supporting JVM 1.5 or above).

Finally, a number of default date and time formats are made available for the formatting of date, datetime (concatenation of parameters p_dateFormat and p_timeFormat) and time:

   /* 2. Date/Time formats
   */
   c_DATEFORMAT                CONSTANT varchar2(20) := 'yyyyMMdd';
   c_DATEFORMAT1               CONSTANT varchar2(20) := 'M/d/yy';
   c_DATEFORMAT2               CONSTANT varchar2(20) := 'd-MMM-yy';
   c_DATEFORMAT3               CONSTANT varchar2(20) := 'd-MMM';
   c_DATEFORMAT4               CONSTANT varchar2(20) := 'MMM-yy';
   c_TIMEFORMAT                CONSTANT varchar2(20) := 'h:mm a';
   c_TIMEFORMAT1               CONSTANT varchar2(20) := 'h:mm:ss a';
   c_TIMEFORMAT2               CONSTANT varchar2(20) := 'H:mm';
   c_TIMEFORMAT3               CONSTANT varchar2(20) := 'H:mm:ss';
   c_TIMEFORMAT4               CONSTANT varchar2(20) := 'mm:ss';
   c_TIMEFORMAT5               CONSTANT varchar2(20) := 'H:mm:ss';
   c_TIMEFORMAT6               CONSTANT varchar2(20) := 'H:mm:ss';

You are not limited to these default values. Here is an example of an export.

 Prompt Exporting Excel Spreadsheet
 BEGIN
   codesys.exporter.writeExcelSpreadsheet(
      p_sql             => 'SELECT ID,LONGITUDE,LATITUDE,SPECIES,STATION_NUMBER as STATION,LENGTH,WEIGHT,SEX,MATURITY,WATER_CHECK as H2O,OBSERVATION_DATE as obsn_date,GEOM FROM GUTDATA',
      p_outputDirectory => 'C:\temp\',
     p_fileName        => 'gutdata',
     p_sheetName       => 'GutData',
     p_stratification  => 'N',
     p_geomFormat      => codesys.EXPORTER.c_WKT_FORMAT,
     p_DateFormat      => codesys.EXPORTER.c_DATEFORMAT,
     p_TimeFormat      => codesys.EXPORTER.c_TIMEFORMAT,
     p_precision       => 7);
 End;
 /

With the export file looking like….

I hope this helps someone.