![Create an Excel-file with PL/SQL 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Create an Excel-file with PL/SQL 1")
For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.
begin as_xlsx.query2sheet( 'select * from dual' ); as_xlsx.save( 'MY_DIR', 'my.xlsx' ); end;The main purpose for this package is getting data from the database into an Excel file, so I deliberate did not include some Excel functionality, such as formulas, into the package. Excel itself is a far better tool for such things.
Anton
The source code for the package: as_xlsx
P.S. I have added the possibility to add Comments and MergedCells to the Excel-file
P.S.2 And bold/italic fonts
P.S.3 Fixed issue with timezones with a regionname
P.S.4 Fixed issue with XML-escaping from text
P.S.5 Fixed NLS-issue with column width
P.S.6 Added p_rgb to get_font
P.S.7 Fixed bug in add_string
P.S.8 Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase)
Added list_validation = drop-down
P.S.9 Added freeze_pane
P.S.10 Fixed another bug in add_string
P.S.11 version 20, but important change: changed to date1904=false
I’m running the package from a Linux server with a remote Oracle DB RAC where the package is installed. I replaced the “MY_DIR” with “/stage” which the correct path related to root (/). I get the error below. How does this package handle linux directories? What should I be using?
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at “HPM.AS_XLSX”, line 140
ORA-06512: at “HPM.AS_XLSX”, line 1837
ORA-06512: at line 3
Anton,
I know you released this a while ago into the public and I have REALLY gotten a GREAT DEAL of usage from it for my users.
I need to ask a question about performance. When I try using this package with a query returning 20000 or so rows, the routine seems to get bogged down and takes forever to return a file. Is there anything I can do to improve performance?
@Tony Miller. Performance is an issue. The package needs to store everything in memory before it can create an Excel-file. If your database doesn’t have that much memory (XE-database) it can be a problem. If have tried to improve the performance, but haven’t found a way yet.
Very strange issue here.. Ran code days ago that was dragging VERYYY slowly, ran the same code today after re-compiling your package and the code I am using with it and I am getting SIGNIFICANT improvement of performance..
We are running on an 11g r2 Enterprise instance (VM on a Linux server).. I do appreciate what you have done with this package..
Might have been a server issue with memory getting hogged by other processes and now its free..
Either way, thanks for the help!!
We have had the same performance issues, we solved it by changing the following;
In function finish, declare a local var
lc_rows tp_rows;
See changes below
..
‘;
add1xml( t_excel, ‘xl/theme/theme1.xml’, t_xxx );
for s in 1 .. workbook.sheets.count()
loop
t_col_min := 16384;
t_col_max := 1;
/* vvvvvv CHANGED vvvvvvv */
— t_row_ind := workbook.sheets( s ).rows.first();
lc_rows := workbook.sheets( s ).rows;
/* while t_row_ind is not null
loop
t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
t_row_ind := lc_rows.first();
loop
exit when t_row_ind is null;
t_col_min := least( t_col_min, lc_rows( t_row_ind ).first () );
t_col_max := greatest( t_col_max, lc_rows( t_row_ind ).last() );
t_row_ind := lc_rows.next( t_row_ind );
end loop;
/* ^^^^^^ CHANGED ^^^^^^^ */
..
..
..
/* vvvvvv CHANGED vvvvvvv */
/*
t_xxx := t_xxx || ”;
t_row_ind := workbook.sheets( s ).rows.first();
t_tmp := null;
while t_row_ind is not null
loop
t_tmp := t_tmp || ”;
t_len := length( t_tmp );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
while t_col_ind is not null
loop
t_cell := ”
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”;
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || ”;
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
/* New */
t_xxx := t_xxx || ”;
t_row_ind := lc_rows.first();
t_tmp := null;
while t_row_ind is not null
loop
t_tmp := t_tmp || ”;
t_len := length( t_tmp );
t_col_ind := lc_rows( t_row_ind ).first();
while t_col_ind is not null
loop
t_cell := ”
|| to_char( lc_rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”;
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := lc_rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || ”;
t_row_ind := lc_rows.next( t_row_ind );
end loop;
/* ^^^^^^ CHANGED ^^^^^^^ */
Another change was made in procedure clear_workbook;
procedure clear_workbook
is
t_row_ind pls_integer;
t_clear_rows tp_rows;
begin
for s in 1 .. workbook.sheets.count()
loop
workbook.sheets( s ).rows := t_clear_rows;
/*
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null
loop
workbook.sheets( s ).rows( t_row_ind ).delete();
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
workbook.sheets( s ).rows.delete();
..
Hope this will help.
Anton, thanks for creating this. Is this code released under a specific license?
Hi Anton, I just discovered your as_xlsx.
First of all, thank you VERY much for posting it for us. It really looks like the miracle I’ve been looking for…
I have one quick question, I hope you ‘ll answer:
I intend to use as_xlsx to produce excel report sheets from sql queries. A typical query will produce a result like this:
area_id area_value area_factor
————– ————— ————–
123456789 1234.5 62.5
234567890 987.8 0
345678901 12345 35
456789012 23456 .32 <–(this is 0.32)
… etc
Trying to avoid pestering you with details, my question is:
Is there a way, while using query2sheet, to declare an entire column's format as, say, number with 1 decimal?
If not, can I invoke cell formatting (always, inside a query2sheet call…)?
Thank you in advance for your time and patience.
Regards
Greg
Hi Anton,
Thanks a lot. This Plugin seem to be very useful, but I need to export data from a TABLE TYPE (and not with select, as you wrote). how can I implement this?
TYPE Elad_Tbl_T IS TABLE OF Elad_Rec_T index by binary_integer;
l_my_tbl_inst Elad_Tbl_T;
In my procedure, I enter all the business logic, in that table type (l_my_tbl_inst).
instead of:
as_xlsx.query2sheet( ‘select * from dual’ );
I want to write, something like that:
as_xlsx.query2sheet( ‘l_my_tbl_inst’ );
Thanks in advanced,
Elad
Hi Anton,
I have been using your xlsx package quite a lot lately and also did some customization.
Another thing I successfully used your package for is creating an APEX IR Downloader with full coloring and so on.
If you are interested in the things I did just contact me on twitter.
A huge thank your for providing this package.
Moritz
Very useful, Anton! Thanks for sharing.
How i could use query2sheet to write data into an existing excel file.
Thanks
Hi Anton,
Great to have this package in my ammo.
But i am tired of getting below error.
ORA-04030: out of process memory when trying to allocate 65572 bytes (PLS non-lib hp,PL/SQL STACK)
ORA-06512: at “SYS.UTL_SYS_COMPRESS”, line 17
ORA-06512: at “SYS.UTL_SYS_COMPRESS”, line 202
ORA-06512: at “SYS.UTL_COMPRESS”, line 59
ORA-06512: at “IRMAEXT.AS_XLSX”, line 271
ORA-06512: at “IRMAEXT.AS_XLSX”, line 1280
ORA-06512: at “IRMAEXT.AS_XLSX”, line 1342
ORA-06512: at “IRMAEXT.AS_XLSX”, line 2504
ORA-06512: at line 14
I am trying to generate excel with 900000 Records. Memory consumption for oracle exe goes to 2,000,000 K just before getting this error.
My oracle version is 10.2.0.3.0 Oracle Database 10g Enterprise Edition.
Looking forward for some help. Tried out most of google suggestions but nither of them worked. Guess the creator himself will show some light on This.
– Ajit
Hi Anton,
great package. Thanks for sharing. I’m finding a problem between oracle 10 and 11.2. In the first case i create a report with 150000 rows in 132 seconds; in the second case i create the same report, but only 5000 rows, in 900 seconds. can you help me about some different parameter to increase in 11.2?
This package has been very helpful to me. In one of the reply below you had said “Build in a limit to restrict the package to generate max 5000 rows so people don’t allocate to much memory?” Could you please point where this limit is set in the package? I am running into an odd situation where it only creates 200 rows in the Excel file whereas the table has 514 rows. My table has 4 clob type columns. If the table has clob type column, do I need to covert the columns into string or varchar2 type before calling the Query2Sheet?
i hope someone can help me about the date system too, im using windows, i have an extracted excel and after copying its cell in date format into another excel file, the date becomes different. this is excel’s issue “1904 / 1900″, is there a way that we set the package to just be in 1900? your help is appreciated. Thanks!
@Ramkumar. This package uses the Excel 1904 date format. There’s no setting to change that.
Hi Anton,
Great Package…
I wanted to have different font-size and Font-Color on the title – Few Merged Cells…
I am able to do it it in XML …
Just wanted to know if I could do somekind of HTML Format implementation… Similar to the following…
which works in XML –> Excel…
as_xlsx.cell( 1, 1, ‘Title – SubTitle‘);
Currently it treats them as text.. in you procedure…
as_xlsx.mergecells( 1, 1, 20, 1 )
Title‘||
‘– SubTitle‘)
You could try
as_xlsx.cell( 1, 2, ‘Title- SubTitle’, p_fontId => as_xlsx.get_font( ‘calibri’, 2, 15, p_bold =>true ) );
Hi Anton,
thanks for the Quick Response..
What Ment was –>
Tiltle using ‘calibri’ 15, p_bold =>true
SubTitle using ‘calibari’ 15, bold=>false
both in the same cell
I tried in 2 different cells (works) but then merging them gets ris of the second cell
Wonderful Package, and I love it!
Thanks A lot in Advance!
This is really good. Is there an alternative for doing the xls files?
@Boydus. Not that I know of
I ran below code, and it generated the file successfully.
But I could not open the file, when I tried it throws an error message “Excel found unreable content in ‘my.xlsx'”
begin
as_xlsx.new_sheet( ‘query 1’ );
as_xlsx.query2sheet( ‘select 1 r from dual’, p_sheet => 1 );
as_xlsx.new_sheet( ‘query 2’ );
as_xlsx.query2sheet( ‘select 2 r from dual’, true, ‘ETF_DATA_OUT_DIR’, ‘my.xlsx’, 2 );
end;
That code works for me. And it will probably work for you too if you try it in a new session.
If you start your code with as_xlsx.clear_workbook;
you make sure that you start with a “clean” excel file.
Anton,
Great work. Thank you for posting your tool. Your as_pdf3 tool uses dbms_lob.freetemporary when you do a save. Is there any reason to avoid freeing the blob when doing a save in as_xlsx?
Thanks,
Ted
No reason, I thought that as soon the used blob went out of scope (i.e. after the call to finish) it is freed automatically.
What kind of help do you expect? On my 11 XE database the package produces a 73220 rows, with 10 numerical columns, Excel file without a problem in 10 seconds. If I change it to use 2 string columns of size 100 it still works OK. So my guess is that you are using a lot of very large strings on a 10 (XE) database. And it looks like the package isn’t suitable for that. What should I do? Keep guessing and change the package and hope for better results? Build in a limit to restrict the package to generate max 5000 rows so people don’t allocate to much memory?
Anton,
I would say people need to understand you provided a FREE package to be used and they take their OWN risks in using it.. Shesh, you went out of your way to provide the package and we are using it to produce Office 2003 files with some special formatting..
We are VERY Thankful for what you provided, and what Denes provided (a package to grab the current select for an Interactive report with bind variables dealt with properly… Yes, it has some issues with returning extra columns with the select due to the way interactive reports re-write the query you build for a report.
But we appreciate someone else contributed this to the community with NOT strings attached (beyond a thank you or a possible contribution to keeping their site up and running..)
Hi Anton,
While exporting 73220 rows, i am getting below error message.
“ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmucpcon: tds)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmucalm coll)”.
I think , you package causes excessive allocations of process memory space.
Please help me.
Your package is great , Really it helps me lot.
I have problem , if am writing more than 10k rows and it is taking for ever. is there any limit to write, please help me
Thanks
Hello,
Thank you for this source code. it works great and is close to what we are looking for in our system. However, we support clients with previous versions of Excel (mainly 97-03 and above) but not all clients have the compatibility pack installed (some are unable to). The comments are not easy to read through since there is no scrollbar on them but I know it was asked. Is there a way to adapt this to create an .xls file instead of xlsx?
Maybe i am doing something wrong. When I try to name the file .xls and open it i am prompted that the file type and extension are different. can anyone confirm that you can save .xls files and open them normally without modifying the code.
Thanks,
Bill
You can’t create xls files with this package.
That is what I was afraid of. Thank you for the quick reply and again for the code.
Hi Anton,
first let me thank you for this great package.
Please note that I had an issue getting ORA-6502. I got this error when appliying “Auto Filter” in procedure “finish”. I debugged the procedure and found out an issue with concatination variable t_xxx.
When replacing this code using dbms_lob.writeappend resolves the issue.
I propose to replace all concatinations with pipe by dbms_lob.writeappend for t_xxx.
— ############################### CHANGED CODE #############
for a in 1 .. workbook.sheets( s ).autofilters.count()
loop
t_tmp:= ”;
t_len := length( t_tmp );
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
end loop;
— ############################### CHANGED CODE #############
— ############################### ORIGINAL CODE #############
for a in 1 .. workbook.sheets( s ).autofilters.count()
loop
t_xxx := t_xxx || ”;
end loop;
— ############################### ORIGINAL CODE #############
kind regards
Ralph
Hello Anton,
Thank you for the update. The package was renamed to match or naming standards. This is only for testing. How can I change the font on the entire sheet and how can I specify the column widths? Can each column have its own column width?
Newbie.
Hello Anton,
I’ve been using your pl/sql package to create an excel file. It works great !!!
I have two questions. How do I rename the sheets (ie. Sheet1) and how can I create a excel document with multiple sheets within the same excel document? Below is what I’m trying to attempt. When I run the following query, the output file is created but when I try to open the file with excel I get multiple errors.
DECLARE
v_dir VARCHAR2(30) := Course_dir;
v_file VARCHAR2(30) := ‘my_course.xls’;
begin
pwg_create_xlsx.clear_workbook;
pwg_create_xlsx.new_sheet(‘Course 24967’);
pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24967’ );
pwg_create_xlsx.new_sheet(‘Course 24293’);
pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24293’ );
pwg_create_xlsx.save(v_dir, v_file);
end;
@Newbie. Any reason why you renamed the package?
Use the parameter p_sheet, for instance
pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24967’, p_sheet => 2 );
Works even better w/ autoflush 😉
utl_file.put_raw( file => t_fh
, buffer => dbms_lob.substr( p_blob
, t_len
, i * t_len + 1
)
, autoflush => true
);
I must say I never had any problems without the autoflush = true. But you are right, it’s better. And if you have trouble writing out a file, it might even help to add max_linesize => 32767 to the utl_file.fopen statement.
Anton
Is their a whitepaper on how to implement this from both Apex, where I am just opening a .xlsx file and from a stored Proc where I am emailing the xlsx file and passing in the SQL statement on both instances?
Chuck
No
So is it meant to only save to the network?
I have been working with Anton to have a modified version of this build a xlsx file and allow the user to download/save it locally..
My business requirement is to transition from sending .xls files (we are currently using xml Spreadsheet by Matzberger) to .xlsx files.
Would love to use this, as it seems to be widely regarded, but not sure how to use.
No, I don’t have a whitepaper. But using the function finish you will get a blob. You can store that blob in a table, use it as an attachment for a email, throw it away, what ever you want.
Anton is right, once the procedure runs and builds the Finish blob item, you can do what you want with it. In my case I am downloading it via a wpg_docload.download_file call to the user for either a save to disk or open right there option…
Thank you,
Tony Miller
LuvMuffin Software
I am trying to use this package with a slight twist.. Since you have an existing plugin that will allow download of a report via Excel 2007 format file, but it can only handle reports up to 26 columns, I was wanting to use this library and just have it determine the active sql select from an interactive report (code already added) and run the query like it does now into a return BLOB and then download t he BLOB via wpg_docload.download_file..
However, the last step of downloading via wpg_docload.download_file is not producing an error, but also not producing a download to the users browser..
Any suggestions?
I’m using this package very successfully and now have a requirement to replicate an excel spreadsheet that I’ve been presented with. To replicate the format exactly I’m required to change the colour of some of the cell borders. Can anyone tell me if this is possible? I’ve not immediately seen how to do it but thought maybe I’m missing something?
Thanks
Adam
I found my problem. I formatted the cell for Text using as_xlsx.get_numFmt(‘@’). What I didn’t know was that formulas entered into fields formatted as Text are just that…TEXT. By changing to get_numFmt(‘General’) I was able to get the results I wanted and allow my users to enter formulas after downloading the XLSX file.
nice work, i hope someone can help me about the date system too, im using windows, i have an extracted excel and after copying its cell in date format into another excel file, the date becomes different. this is excel’s issue “1904 / 1900”, is there a way that we set the package to just be in 1900? your help is appreciated. Thanks!
Nice job.
/*
SUM(B1:C1)
10
*/
Anton – this is is a wonderful package. Thank you very much for sharing this with everyone.
I have run into an issue that I wonder if anyone has resolved. After downloading the Excel file, I am unable to create formulas in cells that have text data. When I enter the formula such as =C2, the result is a text entry of =C2 rather than the value of C2.
Thank you,
Mike Chambers
Mike,
You can try something like this in your code:
Worked for me in XE 10g(not tested for complex formulas or different return type).
I hope is useful.
—————————————————————————
/*
–Simple test calls:
as_xlsx.cell(1,1,”,p_type => ‘f’, p_formula => ‘B1+C1’ );
or
as_xlsx.cell(1,1,”,p_type => ‘f’, p_formula => ‘SUM(B1:C1)’ );
*/
—————————————————————————
while t_col_ind is not null
loop
/*Start 14-sep-2013*/
IF workbook.sheets(s).rows(t_row_ind)(t_col_ind).type = ‘f’ THEN
/*
SUM(B1:C1)
10
*/
declare
l_type varchar2(30):=’t=”array”‘;
begin
t_cell := ”
|| ”
|| workbook.sheets(s).rows(t_row_ind)(t_col_ind).formula
|| ”
/* || ”
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”*/
|| ”;
end;
else
/* End 14-sep-2013 */
t_cell := ”
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”;
END IF; /* 14-sep-2013 */
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
—————————————————————————
instead of :
—————————————————————————
–changes to function finish: loop through cells. Modified t_cell := statment to include tag for forumla
while t_col_ind is not null
loop
t_cell := ”;
if workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).type = ‘f’ then
t_cell := t_cell||”
|| workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).formula
|| ”;
end if;
t_cell := t_cell||”
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”;
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || ”;
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
—————————————————————————
Nice job Anton.
Best regards,
euelvis
Robyn,
I have added formulas to my cells. Here are the changes I’ve applied:
Forgive me if there is a better way to post my changes. This is my first time posting code changes.
Mike Chambers
———————————–
—- package changes start
———————————–
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value number
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’ — if = ‘f’ then add p_formula to cell
, p_formula varchar2 default null — cell formula
);
—
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value varchar2
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’ — added M.CHAMBERS: if = ‘f’ then add p_formula to cell
, p_formula varchar2 default null –added M.CHAMBERS: cell formula
);
—
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value date
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’ — if = ‘f’ then add p_formula to cell
, p_formula varchar2 default null — cell formula
);
—
———————————–
—- package changes end
———————————–
———————————–
—- package body changes start
———————————–
–M.CHAMBERS: modified to_cell to add formula functionality
type tp_cell is record
( value number
, style varchar2(50)
, type varchar2(1) default ‘v’ –added M.CHAMBERS
, formula varchar2(1000) default null –added M.CHAMBERS
);
–M.CHAMBERS: modified to remove case sensitive issue for date format
function get_numFmt( p_format varchar2 := null )
return pls_integer
is
t_cnt pls_integer;
t_numFmtId pls_integer;
t_format varchar2(100) default lower(p_format); –added M.CHAMBERS
begin
if t_format is null –M.CHAMBERS: changed from p_format
then
return 0;
end if;
t_cnt := workbook.numFmts.count();
for i in 1 .. t_cnt
loop
if workbook.numFmts( i ).formatCode = t_format –M.CHAMBERS: changed from p_format
then
t_numFmtId := workbook.numFmts( i ).numFmtId;
exit;
end if;
end loop;
if t_numFmtId is null
then
t_numFmtId := case when t_cnt = 0 then 164 else workbook.numFmts( t_cnt ).numFmtId + 1 end;
t_cnt := t_cnt + 1;
workbook.numFmts( t_cnt ).numFmtId := t_numFmtId;
workbook.numFmts( t_cnt ).formatCode := t_format; –M.CHAMBERS: changed from p_format
workbook.numFmtIndexes( t_numFmtId ) := t_cnt;
end if;
return t_numFmtId;
end;
–M.CHAMBERS: modified to add formula functionality
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value number
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’ –added M.CHAMBERS
, p_formula varchar2 default null –added M.CHAMBERS
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value ;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := null;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
end;
–M.CHAMBERS: modified to add formula functionality
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value varchar2
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’ –added M.CHAMBERS
, p_formula varchar2 default null –added M.CHAMBERS
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
t_alignment tp_alignment := p_alignment;
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := add_string( p_value );
workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
if t_alignment.wrapText is null and instr( p_value, chr(13) ) > 0
then
t_alignment.wrapText := true;
end if;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := ‘t=”s” ‘ || get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, t_alignment );
end;
—
–M.CHAMBERS: modified to add formula functionality
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value date
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
, p_type varchar2 default ‘v’
, p_formula varchar2 default null
)
is
t_numFmtId pls_integer := p_numFmtId;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value – to_date(’01-01-1904′,’DD-MM-YYYY’);
workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
if t_numFmtId is null
and not ( workbook.sheets( t_sheet ).col_fmts.exists( p_col )
and workbook.sheets( t_sheet ).col_fmts( p_col ).numFmtId is not null
)
and not ( workbook.sheets( t_sheet ).row_fmts.exists( p_row )
and workbook.sheets( t_sheet ).row_fmts( p_row ).numFmtId is not null
)
then
t_numFmtId := get_numFmt( ‘dd/mm/yyyy’ );
end if;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, t_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
end;
—
–changes to function finish: loop through cells. Modified t_cell := statment to include tag for forumla
while t_col_ind is not null
loop
t_cell := ”;
if workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).type = ‘f’ then
t_cell := t_cell||”
|| workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).formula
|| ”;
end if;
t_cell := t_cell||”
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
|| ”;
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || ”;
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
Hello Anton.
Thank you! It works well.
Hi Robyn
The code I have put into my Post is just an additional procedure to the original package…
did you download and compile the package from this site first ?
once done you can add my procedure to the newly created package.
Regards,
Gilles
Hello,
I have the need to add excel formulas to the spreadsheet so that the user can see record counts in the spreadsheet following filtering applied by user post spreadsheet creation.
Has anyone experimented with adding excel formulas? perhaps have a plsql function already created to add this?
Thanks, Robyn
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;
Hi ,
When i tried to install the package it seems that something is missed (package, library..) .For example : “get_font invalid identifier ” or “new_sheet invalid identifier”…
Can you help in order to compile this very usefull package?
Many thanks
@Btissam The source consists pf one package specification and one packakage body. If you run them unaltered they should compile OK
Hello,
Is it possible to generate xls file (Office 97-2003) ?
Regards,
Stefan Pastrilov
I know.
We needed the overload function to get the index of a sheet so we can use that index in the other functions like cell.
Hello,
Great package and functionality but i think i found a possible bug in the query2sheet function of the package.
In that function you have the t_sheet pls_integer variable.
you use that variable before a value is assigned to it.
Because of this you can’t specify what sheet you want to use for the results.
I think its better you put the following code at the beginning of your function
beginÂ
if p_sheet is null then
t_sheet := new_sheet;
else
t_sheet := nvl(p_sheet, workbook.sheets.count());
end if;
As you can see i also needed to overlead the new_sheet so that it returns the index of the new sheet.
Â
Moving the following line in the procedure a couple of lines up will do, no need for overloading new_sheet
t_sheet := nvl( p_sheet, workbook.sheets.count() );
So change it to:
if p_sheet is null
then
new_sheet;
end if;
t_sheet := nvl( p_sheet, workbook.sheets.count() );
Anyhow this is a very good starting point and helps me a lot!! 🙂
@dieterdaniel
This package does produces the “native” xlsX Excel-format. But at the moment it can’t produce formulas, graphs or macro’s , just “data”
Understandable, it’s not the “native” Excel format that PL/SQL is working with.
I keep playing around with it. As I’m just starting out more examples would be aprecciated if other people already developed more “fancy” code to generate XLS sheets. Especially putting formulas or stuff like graphs into the results.
This package is really helpful!! Thanks for sharing it!
One question: would it be possible to open an existing XLS to edit data in there?
@dieterdaniel
No, that’s not possible.
Finally a litle more time to experiment.
I have modified it to the following:
<workbookPr date1904=”false” dateCompatibility=”true” defaultThemeVersion=”124226″/>
Dates seem to be displaying correctly in Excel/win and Excel/Mac.
Dates are displaying as their decimal value in openOffice, but I can live with that, at least for now.
Got it!
Had a look at the openOffice preferences. Seems that openOffice allows three seperate start dates as the basis for calculating the decimal date.
– 12/30/1899
– 01/01/1900 (StarCalc 1.0)
– 01/01/1904
The last of these options was checked.
I noticed that the as_xlsx package used the following formular for date cell contents:
(p_value – to_date(’01-01-1900′,’DD-MM-YYYY’)
Changing the openOffice preference to 01/01/1900 yielded immediate results, the dates which previously were years off are now correct.
Googling “xlsx date format 1900 1904” yields a lot of results, the main difference seems to be between mac/win. I’m on a Mac which may be why the default was 1904.
I wonder if there is a way of specifying the date initalisation point in the actual xlsx file.
@TonyReed
In the package I use: <workbookPr date1904=”true” defaultThemeVersion=”124226″/>
Seems that way.
Will have to double check the date in office.
Actually 4 years + 1 day off.
Very Strange.
There seems to be a nls issue as well, possibly related.
modified the query in the above procedure to:
select sysdate, to_char(sysdate,”DD/MM/YYYY HH24:Mi”) to_charSysdate from dual
Result is:
SYSDATEÂ Â Â Â Â Â Â Â Â Â Â Â TO_CHARSYSDATE
40881.6090856482Â Â Â 06/12/2011 14:37
converted decimal to date:
SYSDATEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â TO_CHARSYSDATE
12/05/2015 14:37:05Â Â Â 06/12/2011 14:37
Somethings definitely rotten in the state of Norway.
Â
@TonyReed
I don’t think it’s a NLS problem. Your to_charsysdate is a String and not a date.
The problem is in my opinion that OpenOffice doesn’t recognise the used Excel dateformating.
And did you notice that your converted decimal is one day off?
Hi Anton,
Looks like this might be a specific problem with openOffice. I just tested with a copy of office 2010 – works nicely.
In openOffice the following procedure returns:
SYSDATE
40881.4262384259
Formatting the decimal as a date turns it into:
12/05/2015 10:13:47
Â
procedure test_xlsx is
v_xlsx blob;
v_mime_type varchar2(255):=’application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’;
begin
as_xlsx.query2sheet(p_query=> ‘select sysdate from dual’);
v_xlsx:=as_xlsx.finish;
owa_util.mime_header(CCONTENT_TYPE=>v_mime_type, BCLOSE_HEADER=>false, CCHARSET=>’UTF-8′);
htp.p(‘Expires: ‘||to_char(sysdate, ‘Dy, DD Mon YYYY HH24:Mi:ss’)||’ GMT’);
htp.p(‘Cache-Control: no-cache’);
htp.p( ‘Content-length: ‘ || length(v_xlsx) );
htp.p( ‘Content-Disposition: filename=”‘ || ‘test-‘||to_char(sysdate,’YYYYMMDDHH24miss’)||’.xlsx’ || ‘”‘ );
owa_util.HTTP_HEADER_CLOSE;
wpg_docload.download_file( v_xlsx );
end;
The procedure can be run at:
https://production.presstogo.com/mars/xxtest.test_xlsx
Any ideas what the issue is?
Thnx Anton, Now I successfully compiled the package
Hi Anton & co,
I’m having trouble with dates.
The files return a cryptic decimal value for all date fields.
e.g. 40734.7118171296
When manually formatting the cell to a date this is displayed as:
07/11/2015 17:05:01
Database parameters are:
NLS_DATE_FORMATÂ Â Â Â Â Â Â Â Â Â DD-MON-RR
NLS_DATE_LANGUAGEÂ Â Â Â Â Â Â Â AMERICAN
NLS_CHARACTERSETÂ Â Â Â Â Â Â Â Â AL32UTF8
hope you can point me in the right direction.
@TonyReed
How do you generate cryptic values? Do you use date parameters?
Hi, I’m back 🙂
Some time ago I realized that 11.1 has problems with “utl_compress.lz_compress”, in fact, does not compress at all, therefore large sheets can be really big. This is replacement:
create or replace and compile java source named zip_blob as
import java.io.OutputStream;
import java.util.zip.GZIPOutputStream;
import java.util.zip.Deflater;
import java.sql.Blob;
public class zip_blob
{
public zip_blob()
{
}
public static int zip_blob_compressed_out(java.sql.Blob in_blob,java.sql.Blob[] out_blob, float compression)
{
final int comp = (int)compression;
try{
//zipoutputstream from out_blob
Blob zip_out = out_blob[0];
OutputStream os = zip_out.setBinaryStream(1);
GZIPOutputStream zos = new GZIPOutputStream(os)Â Â Â {
{
def.setLevel(comp);
}
};
int chunksize = 32768;
//add data to zip
long len = in_blob.length();
long offset = 1;
byte[] buffer;
while (offset < len) {
buffer = in_blob.getBytes(offset, chunksize);
if (buffer == null)
break;
zos.write(buffer, 0, buffer.length);
offset += buffer.length;
}
zos.close();
out_blob[0] = zip_out;Â Â Â Â Â Â Â
}catch (Exception e) {
e.printStackTrace(System.out);
return 1;
}
return 0;
}Â
}
CREATE OR REPLACE PACKAGE zip_blob AS
FUNCTION zip_blob_compress(in_blob IN BLOB, out_blob in out nocopy BLOB, comp number)Â RETURN number
AS LANGUAGE JAVA
NAME ‘zip_blob.zip_blob_compressed_out(java.sql.Blob, java.sql.Blob[], float) return java.lang.int’;
END zip_blob;
Commenting out:
t_blob := utl_compress.lz_compress( p_content);
and adding:
t_zip_res integer;
….
t_zip_res := zip_blob.zip_blob_compress(p_content,t_blob,9);
plus
do the trick …
Cheers 🙂
Hi ,
I got following error  while compiling the package.
Compilation errors for PACKAGE BODY NANO.AS_XLSX
Â
Error: PLS-00201: identifier ‘UTL_FILE’ must be declared
Line: 95
Text: T_FH Â UTL_FILE.FILE_TYPE;
Â
Error: PL/SQL: Item ignored
Line: 95
Text: T_FH Â UTL_FILE.FILE_TYPE;
Â
Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line: 98
Text: T_FH := UTL_FILE.FOPEN(P_DIRECTORY, P_FILENAME, ‘wb’);
Â
Error: PL/SQL: Statement ignored
Line: 98
Text: T_FH := UTL_FILE.FOPEN(P_DIRECTORY, P_FILENAME, ‘wb’);
Â
Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line: 100
Text: UTL_FILE.PUT_RAW(T_FH, DBMS_LOB.SUBSTR(P_BLOB, T_LEN, I * T_LEN + 1));
Â
Error: PL/SQL: Statement ignored
Line: 100
Text: UTL_FILE.PUT_RAW(T_FH, DBMS_LOB.SUBSTR(P_BLOB, T_LEN, I * T_LEN + 1));
Â
Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line: 102
Text: UTL_FILE.FCLOSE(T_FH);
Â
Error: PL/SQL: Statement ignored
Line: 102
Text: UTL_FILE.FCLOSE(T_FH);
Â
Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
Line: 299
Text: T_IND := GET_FONT(‘Calibri’);
Â
Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
Line: 302
Text: T_IND := GET_FILL(‘none’);
Â
Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
Line: 303
Text: T_IND := GET_FILL(‘gray125’);
Â
Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
Line: 306
Text: T_IND := GET_BORDER(”, ”, ”, ”);
Â
Error: Hint: Variable ‘TS’ is declared but never used in ‘FINISH’
Line: 833
Text: TS Â Â Â Â TIMESTAMP := SYSTIMESTAMP;
Can you please tell me how to fix it?
Thnx
@Amit. It looks like you are missing the execute privilege on package sys.utl_file
Hi…Package is so useful…can you let us know to download the generated .xlsx file using the mime_header.
Anton,
I meant that when I use Your code as_xlsx.cell( 2, 4, 12345, p_numFmtId => as_xlsx.get_numFmt( ‘# ##0′) );
and excel is produced, in cell there’s formating #\ ##0 used not # ##0. Package changes ‘ ‘ into ‘\ ‘.
Regards,
Peter
Â
@Peter
Strange, it doesn’t do that for me. The package puts the format you enter unchanged in the generated xml.
Thank You Anton,
but it’s not working properly for large number (for instance 2 000 000). In excel there’s formatting #\ ##0 not # ##0 :/
Regards,
Peter
@Peter.
This pacakge puts whatever format you use in the Excel. If you think that #\ ##0 is a valid Excel number format, use that. If you need something else, use something else.
Anton,
how format number columns in xlxs output for excel # ##0 ?
Regards,
Peter
Â
Use as_xlsx.cell( 2, 4, 12345, p_numFmtId => as_xlsx.get_numFmt( ‘# ##0’) );
Thanks a lot 🙂
@Peter
The funcion finish returns a blob containing the “excel-file”. This blob can be sent as an attachment from a mail
Anton,
What if i’d like to send it by mail without saving?
Which object should be sent?
Regards,
Peter
Anton, Further to Morten’s “refcursor2sheet” request.
Could you refactor query2sheet into a separate “query2sheet” and “cursor2sheet”. The former would do the open cursor/parse then pass the opened cursor to the latter (which would do the describe columns onwards).
In 11g we can covert the refcursor to a DBMS_SQL cursor and call “cursor2sheet” directly.
I’ve put those changes in on:
http://www.sydoracle.com/Codespace/as_xlsx9.txt
Â
I’v tried that solution but it relay mess up with shared strings mapping in workbook when you have null columns between not null. Additionally it generates a lot of redundant cell data with same shared string that is mapped to null. Generally I think better way is to stop generation of null cells at all, since you got cell reference like ‘A1’ etc in sheet.xml exel can handle this and all xlsx is smaller 🙂
damn tags 🙂
when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
then
dbms_sql.column_value( t_c, c, v_tab );
for i in 0 .. t_r – 1
loop
if v_tab( i + v_tab.first() ) is not null then
cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet );
end if;
end loop;
v_tab.delete;
else
null;
end case;
added: if v_tab( i + v_tab.first() ) is not null then … end if;
Some troubles with NULL values in query result, it seems like string is the same type as NULL (96) so cannot create shared strings table my quick fix for that is between –> if v_tab( i + v_tab.first() ) is not null then t_sheet );
–> end if; <–
end loop;
v_tab.delete;
else
null;
end case;
@jareeq
You found a bug.
Changing a line in procedure add_string solves it too
workbook.strings( nvl( p_string, ” ) ) := t_cnt;
It looks that the empty string ” isn’t the same as null all the time. At least on my 11XE database.
@hUGO
You can use the p_theme parameter of get_font. But that relates to the standard themes of excel, and so indirect to font-color.
But I’ve added a rgb parameter to the procedure. That would make it a little bit easier to change font-color.
Hi Anton!
great work!!
it is possible to give color to the font?
thanks!
At the end, I took a different approach. I loaded the Apache POI jars into the database and wrote a simple java procedure that takes your query as the input and returns the generated xlsx in a blob.
Thanks, I tried it, but to no avail. 🙁 I will look into it, if I find a way I am going to let you know.
I tried your package but it was slow (2 mins 30 seconds for an excel with column A-Q and 2900 rows). Is it just me or is this normal? This is a package I have been looking for for ages, Â but with this speed I cannot use it. Â Thank you for your effort.
@prunoki See some comments back for an easy way to get it faster
hola
Felicitaciones por tu blog.
Como puedo cambiar el nombre a las hojas de un libro de excel
Gracias
I prefer comments in Dutch, but German or English are OK to, but Spanish no 🙂
Do you mean procedure new_sheet( p_sheetname varchar2 := null );???
begin
as_xlsx.new_sheet( ‘query 1’ );
as_xlsx.query2sheet( ‘select 1 r from dual’, p_sheet => 1 );
as_xlsx.new_sheet( ‘query 2’ );
as_xlsx.query2sheet( ‘select 2 r from dual’, true, ‘MY_DIR’, ‘my.xlsx’, 2 );
end;
Hi Anton,
It’s really very nice thing. you just save a lot of time for us. But one thing I have found that while I’m trying to create a large excel file , suppose it has 4000 rows 470 coulms (i.e. A-RB) it takes 12 minutes to create the file. Could the time be reduced?
Please help.
Thanks you once more for your great endeavour.
@Partha I think it is possible to get that time down to 3-4 minutes. But I don’t think you will get it fast with that many columns.
Try changing “t_tmp clob;” to “t_tmp varchar2(32767);”
Hi Anton,
really great work!
Correct me if I’m wrong, but after taking a quick look at your code it seems you put every string as new string in the SharedStrings.xml. A while ago just out of curiosity I took a closer look at the Office Open XML file format. I remember that one of the reasons MS gave for writing strings to a different file was that in spreadsheets “highly repetitive strings are very common”. So it should be fairly easy to greatly reduce the file size of the resulting xlsx file by only adding new strings to your tp_strings and just reference it for duplicates.
Don’t forget to differentiate between count and uniqueCount in your sharedStrings.xml 🙂
Cheers
@Dirk. You are right about the way I handle strings. And you are right about the other things too 🙂 . I will look into it (some day)
Excelent work with this library Anton!
There’s only one thing that confuses me – is it possible that your zip implementation creates files six times larger then those created with Excel 2007? After opening & saving a file created with your library its size decreased from 12MB to 2MB.
@Tomaž
I don’t think it has anything to do with the zip implementation. I place the (formatting) tags around every cell in the sheet. And I suppose Excel does it a little bit smarter, formating a complete column or row in one time.
Thanks, Anton. Â Great tool…I’ve been doing this the hard way with ult_file and || chr(13) forever.
Â
Cheers.
Â
-abe
Hi Anton,
great! we tried to implement it by our self. then in stumbled across you package. great!
but very soon we’ve got  an issue:
2 out of 21 Columns do not appear when opening the xlsx file in excel. they are marked as hidden. to show them youhave to  expand them manually. both colums are filled with date values and are empty (null) in the first couple of rows.
using excel i saved the xlsx as xml, without any changes and found this section “<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”24″ ss:ExpandedRowCount=”1563″ x:FullColumns=”1″
x:FullRows=”1″ ss:DefaultRowHeight=”15″>
<Column ss:Index=”14″ ss:Hidden=”1″ ss:AutoFitWidth=”0″/>
<Column ss:Index=”21″ ss:Hidden=”1″ ss:AutoFitWidth=”0″/>”
in the very beginning of the xml file.
any idea?
grtz
Peter
Dear Anton
Thanks for your help. It worked after I changed the schema val as non.
It took me a lot of time tracing this problem before asking you. Thanks again, you did me a great help!! \^Q^/
I also tried other fonts like Arial, Times New Roman but still cannot work….
My Oracle is installed in the remote server(unix without excel). After generating excel file, I will get the excel file to my PC. I have no idea whether those fonts were in the server or not but can make suer my pc can display those fonts well?
Do you mean I should install those fonts in my server? Is that in the Oracle packages?Â
 Â
@Stevenquave
The fonts should be on your client PC. If have tried some different fonts, some did work and some didn’t. After changing one line, 893, in the package body all fonts were working for me.
Try changing the line <scheme val="minor"/>
to
<scheme val="none"/>
@Stevenquave
Strange, that code works for me. Do you have the Calibri -font installed on your computer?
Your sample code is quite useful ..  Before that, I used .Net to make excel file, but it took much of my time waiting for generating files through connecting database and file formatting.Â
Tried to set the font name as the code below, but the font name cannot display well and always show as the default font setting of my pc.
Do you have any suggestions about that? Look forward for your reply and thanks in advance.
cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet,p_borderId => as_xlsx.get_border( p_bolder, p_bolder, p_bolder, p_bolder),p_fontId=>as_xlsx.get_font(‘Calibri’,p_fontsize=>8));
Stevenquave
Thank you for updating. It works now.
Hi Anton,
very useful package. Thanks for sharing.
I try to put “&” in a column but it shows “&”. Is there any tricks to do this in your package.
Â
Thanks
David
@dpigetyphie Oops, you found a bug. Doing XML-escaping twice isn’t double so good. I fixed it, see as_xlsx
@Morton: You should be able to solve your SYS_REFCURSOR problem with this function:
DBMS_SQL.TO_CURSOR_NUMBER
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sql.htm#CHDJDGDG
As fare as I know its only available in 11g
Hi Anton,
really great package. I only found one little problem when using time zones. If you execute this example:
begin
EXECUTE IMMEDIATE ‘ALTER SESSION SET TIME_ZONE =”Australia/Sydney”’;
as_xlsx.query2sheet( ‘select rownum, x.*
, case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
, case when mod( rownum, 2 ) = 1 then ”demo ” || rownum end demo2 from dual x connect by rownum <= 5’ );
as_xlsx.save( ‘MY_DIR’, ‘my.xlsx’ );
end;
You get an invalid file. I suggest to use this code instead:
<dcterms:created xsi:type=”dcterms:W3CDTF”>’ || to_char( current_timestamp, ‘yyyy-mm-dd”T”hh24:mi:ssTZH:TZM’ ) || ‘</dcterms:created>
<dcterms:modified xsi:type=”dcterms:W3CDTF”>’ || to_char( current_timestamp, ‘yyyy-mm-dd”T”hh24:mi:ssTZH:TZM’ ) || ‘</dcterms:modified>
@Björn: Thanks for the feedback and the solution! I will change it, see new version as_xlsx
Thank you for your support. Very nice work!
I will wait for new releases 🙂
@Vasya I have added bold and italic fonts. I had already parameters for that, but I didn’t used those parameters. But now it functions OK. I have no plans for adding row_height or PageSetup Orientation. But who knows, maybe if I have some spare time and I get bored ….
Anton!
Thank you very much for mergecells!!!
But not work bold font.
And desirable features, if possible:
– set_row_height
– set PageSetup Orientation and FitToPagesWide.
With these possibilities it would be amazing package!!! 🙂
We still use Excel 2003 with a xlsx-converter. That seems to mess up the autofilter without the definedNames tag. When I use Excel 2010 it works fine. Sorry!
Thank you for this post, Anton. You are saving me the trouble to figure out the new excel xml structure on my own. I did it once with the former 2003 spreadsheetml…
While playing around with it I came across two problems:
– our database language is set to German, thus decimals are represented by ‘,’ instead of ‘.’ That messes up alle non-integer number cells and date cells. When I set the nls_language to american it works.
– the autofilter function is missing a <definedNames> tag in the workbook.xml file. so the autofilter will be rendered but doesn’t work. I added the following code between the </sheets> and the <calcPr> tags
bFirst := true;
for s in 1 .. workbook.sheets.count() LOOP
if workbook.sheets( s ).autofilters.count() > 0 THEN
if bFirst then
t_xxx := t_xxx || ‘
<definedNames>’;
end if;
t_col_min := 1;
t_col_max := 1;
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null loop
t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
for a in 1 .. workbook.sheets( s ).autofilters.count() loop
t_xxx := t_xxx || ‘
<definedName name=”_xlnm._FilterDatabase” localSheetId=”‘ || (s – 1) || ‘” hidden=”1″>Sheet’ || s || ‘!$’ ||
fk_alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_start, t_col_min ) ) || ‘$’ ||
nvl( workbook.sheets( s ).autofilters( a ).row_start, workbook.sheets( s ).rows.first() ) || ‘:$’ ||
fk_alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_end, t_col_max ) ) || ‘$’ ||
nvl( workbook.sheets( s ).autofilters( a ).row_end, workbook.sheets( s ).rows.last() ) ||
‘</definedName>’;
end loop;
if bFirst then
t_xxx := t_xxx || ‘
</definedNames>’;
bFirst := false;
end if;
end if;
end loop;
@Vasya: You can’t merge cells, like I said in the blog I build this package for unloading data, not to create fancy spreadsheets.
@Achim: You are right about the NLS-settings, I will fix that some day. But Autofilters work for me without definedNames, at least with Excel 2010. I will test it with Excel 2007.
Anton, how can I merge cells?
@Pradeep. Images is also one of the things I deliberately left out of this package. It takes quite some coding to add this functionality.
Thanks Anton for the post. I already did some minor version of this and there I was having a problem to include a picture/.jpeg to my excel file.
Can you please let me know if you know any solution for that.
@Marc, I have added the possiblity to set aligments for a data cell, this also include the possibilty to add the wrapText option. And it also automaticly sets the wrapText option when a data cell contains a chr(13). as_xlsx
Thanks Anton.
Is there a way I can specify a data cell (or column of cells) that contain carriage-returns  – chr(13) – to have the Wrap Text option in the xlsx set to True?
@Morten. In this case it’s a bit different, to read a (unknown) refcursor every column is turned into a string, even numbers and dates. And its (very) difficult to detect a number or date and show these in the right way in the Excel file. But feel free to do it yourself, and I’m happy to include it in the official version 🙂
Very nice and very useful, Anton! Thanks for sharing.
I have a small enhancement request: Can you add a “refcursor2sheet” procedure? It should work along the same lines as “refcursor2pdf” from the example to your PDF package. I could add it myself, but I’m sure others would find this useful as well, so it’s perhaps better if you add it to the “official” version of your package.
– Morten