how to avoid break/titles per every 14 rows. Is there a way to adapt this to create an .xls file instead of xlsx? Thanks so much, Oracle technology is changing and we How do I rename the sheets (ie. "calculated" value must be provided with the formula. Thank you for this source code. Here are other notes on creating spreadsheets with Oracle qualifications. of Excels ability to read from an HTML table. Tried out most of google suggestions but nither of them worked.

We still use Excel 2003 with a xlsx-converter. Scripts You cant create xls files with this package. 40734.7118171296. If you execute this example: begin EXECUTE IMMEDIATE ALTER SESSION SET TIME_ZONE =Australia/Sydney; as_xlsx.query2sheet( select rownum, x. Thank you for posting your tool. Use the parameter p_sheet, for instance pwg_create_xlsx.query2sheet(select * from courses where course_numb = 24967, p_sheet => 2 ); 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. My table has 4 clob type columns. 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). Guess the creator himself will show some light on This. servicesApplication

@Btissam The source consists pf one package specification and one packakage body. 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 throughconnecting 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. Hi , I got following error while compiling the package. After logging in you can close it and return to this page. 9-10, and Microsoft Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with the OReilly learning platform. 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; . Thank you for this post, Anton. When replacing this code using dbms_lob.writeappend resolves the issue. The first example demonstrates the basic structure of a PL/SQL program that uses the XLSX library. First of all, thank you VERY much for posting it for us. so the autofilter will be rendered but doesnt work. * , 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. grtz Peter, Dear Anton Thanks for your help. It worked after I changed the schema val as non. How to import the exported data in another PC? I replaced the MY_DIR with /stage which the correct path related to root (/). Do you have the Calibri -font installed on your computer? If youre extracting data for someone to load into Microsoft DBA performance tuning consulting professionals. But column headings are appearing after every 15 rows in the excel sheet, i want the headings to appear once only, what can i do to get rid of this problem? The Oracle of contains HTML markup. Googling xlsx date format 1900 1904 yields a lot of results, the main difference seems to be between mac/win. With these possibilities it would be amazing package!!! Excel-DB. Anyone Is there any reason to avoid freeing the blob when doing a save in as_xlsx? Of course there are limits to the power of csv spreadsheets Ive been using your pl/sql package to create an excel file. Hi Anton, thanks a lot for this very usefull package. It took me a lot of time tracing this problem before asking you.Thanks again, you did me a great help! Seems that way. And I suppose Excel does it a little bit smarter, formating a complete column or row in one time. 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. ! \^Q^/, I also tried other fonts like Arial, Times New Romanbut still cannot work. Hi Anton, Looks like this might be a specific problem with openOffice. You dont need to how can I implement this? I need to ask a question about performance. Check out the Apex 4.0 preview at Carl Backstrom's blog! I added the following code between the and the 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 || ; 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 || ; end loop; if bFirst then t_xxx := t_xxx || ; bFirst := false; end if; end if; end loop; @Vasya: You cant merge cells, like I said in the blog I build this package for unloading data, not to create fancy spreadsheets. UNIXOracle Excel has also the ability to display controls. What should I be using? and most Oracle professionals will use an inexpensive tool (Excel-DB) to create When I use Excel 2010 it works fine. Im running the package from a Linux server with a remote Oracle DB RAC where the package is installed.

Could the time be reduced? @hUGO You can use the p_theme parameter of get_font. Theres no setting to change that. Your package is great , Really it helps me lot.

how to create multiple tabs in one single file using spool command. Just

This package is really helpful!! The files return a cryptic decimal value for all date fields. Database Support @Morten. As Im just starting out more examples would be aprecciated if other people already developed more fancy code to generate XLS sheets. The Oracle of

It helped me complete my work project. The challenge in short was: Our invoicing system produces files that contain one or [], Juggling with SQL Types in SQL and PL/SQL fading borders between Collection and Table, Xenogenetics for PL/SQL: Infusing with Java Best Practices and Design Patterns (presentation at OOW 2010), Oracle OpenWorld 2015: Finally a realVision and Mission. Thanks for sharing it! Thanks for sharing. Updated for PeopleTools 8.55, Signup below to download COMPLETE LIST OF PEOPLESOFT TABLES. And did you notice that your converted decimal is one day off? Never Again Google It ! Remote DBA Services your help is appreciated. I tried the above solution to send output of a query from sql*plus to an excel file, it works fine.

Thank you for your effort. hope you can point me in the right direction. uuencode emp.xls| If have tried some different fonts, some did work and some didnt. Im on a Mac which may be why the default was 1904. turn converts the HTML table into a spreadsheet. Currently, XLSX writer only supports check boxes: Excel allows to freeze parts of the sheet. 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; Its really very nice thing. I am almost a bit proud on how easy this 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); 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. 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. I get the error below. Images is also one of the things I deliberately left out of this package. -abe, Hi Anton, great! we tried to implement it by our self. Cell styles allow to format the content of a cell, for example some text should be displayes with. Can anyone tell me if this is possible? This Oracle comma-delimited flat file, which can be read my MS-Excel as a .csv file: 3 - shell script to create Oracle data: Note: Tips 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 ) ; unsupported type If Not bGotType Then raise eTypeNotDefined ; End If ; 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); 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. @Ramkumar. 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. Thanks Anton for the post. 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. Answer: Oracle has many ways to output spreadsheet from Oracle. from Oracle. I know. If you find an error 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; 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 you need something else, use something else.

@Amit. Can you help in order to compile this very usefull package? The following example It should work along the same lines as refcursor2pdf from the example to your PDF package. Maybe i am doing something wrong. You could try as_xlsx.cell( 1, 2, Title- SubTitle, p_fontId => as_xlsx.get_font( calibri, 2, 15, p_bold =>true ) ); 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.

With this package its very easy to create an Excel 2007 file with only a few lines of PL/SQL code. Regards, Peter, Anton, Further to Mortens refcursor2sheet request. perhaps have a plsql function already created to add this? Had a look at the openOffice preferences. The following example shows the first values of the fibonacci series: This example creates 100 columns. Excel itself is a far better tool for such things. spreadsheet. 911RAC You are saving me the trouble to figure out the new excel xml structure on my own. It works great !!! Thanks for sharing. adjust column widths. Dont forget todifferentiate between count and uniqueCount in your sharedStrings.xml Cheers, @Dirk. 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. It is used to test if more than 26 columns (names A through Z in Excel) are possible. . Anton is right, once the procedure runs and builds the Finish blob item, you can do what you want with it. I have run into an issue that I wonder if anyone has resolved.

But at the moment it cant produce formulas, graphs or macros , just data. When I set the nls_language to american it works. After changing one line, 893, in the package body all fonts were working for me. sqlplus -M "HTML ON" -s scott/tiger| I have two questions. or have a suggestion for improving our content, we would appreciate your Doing XML-escaping twice isnt double so good.

How i could use query2sheet to write data into an existing excel file. Support, SQL TuningSecurityOracle Example 9-10 this is excels issue 1904 / 1900, is there a way that we set the package to just be in 1900? All rights reserved by I debugged the procedure and found out an issue with concatination variable t_xxx. @Pradeep. What are the ways to output a Excel spreadsheet from Oracle? In one of the reply below you had said Build in a limit to restrict the package to generate max 5000 rows so people dont allocate to much memory? Could you please point where this limit is set in the package? The login page will open in a new tab. Is this code released under a specific license? Excel, you can apply an interesting technique whereby you take advantage 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. Doyou mean I should install those fonts in my server?Is that in the Oracle packages? , @Stevenquave The fonts should be on your client PC. Please log in again. I think , you package causes excessive allocations of process memory space. Great toolIve been doing this the hard way with ult_file and || chr(13) forever.

This is really good. it works great and is close to what we are looking for in our system.

to show them youhave to expand them manually. TrainingOracle This is my first time posting code changes. TuningEmergency I have added formulas to my cells. t_zip_res := zip_blob.zip_blob_compress(p_content,t_blob,9); plus do the trick . great work!! 1 - Excel creation tools both colums are filled with date values and are empty (null) in the first couple of rows. Another thing I successfully used your package for is creating an APEX IR Downloader with full coloring and so on. I will change it, see new version as_xlsx, Thank you for your support. is the registered trademark of Oracle Corporation. Thank you for the quick reply and again for the code. But one thing I have found that while Im trying to create a large excel file , suppose it has 4000 rows 470 coulms (i.e. I want to write SQL that will put the If not, can I invoke cell formatting (always, inside a query2sheet call)? And it will probably work for you too if you try it in a new session. But Ive added a rgb parameter to the procedure. A huge thank your for providing this package. .xls spreadsheets from Oracle queries.

But that relates tothe standard themes of excel, and so indirect to font-color.

If I change it to use 2 string columns of size 100 it still works OK. ############################### 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 #############. And if you have trouble writing out a file, it might even help to add max_linesize => 32767 to the utl_file.fopen statement. I could add it myself, but Im sure others would find this useful as well, so its perhaps better if you add it to the official version of your package. 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; 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 ? My Oracle is installed in the remote server(unix without excel).

This blob can be sent as an attachment from a mail, Anton, What if id like to send it by mail without saving? 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). 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. The problem is in my opinion that OpenOffice doesnt recognise the used Excel dateformating. Oracle has many ways to output spreadsheet Changing the openOffice preference to 01/01/1900 yielded immediate results, the dates which previously were years off are now correct. But Autofilters work for me without definedNames, at least with Excel 2010. Oracle). @Toma I dont think it has anything to do with the zip implementation. Im finding a problem between oracle 10 and 11.2. If the table has clob type column, do I need to covert the columns into string or varchar2 type before calling the Query2Sheet? This time he had another interesting challenge not all that weird to be honest. Spool the output to a file with the extension .xls. then in stumbled across you package. While exporting 73220 rows, i am getting below error message. I will wait for new releases .

XLSX Writer is a PL/SQL library that allows to create Excels in the .xlsx document format. any idea?

I got this error when appliying Auto Filter in procedure finish. PricesHelp How can I change the font on the entire sheet and how can I specify the column widths? So my guess is that you are using a lot of very large strings on a 10 (XE) database. This is only for testing. /* 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 */.

Hello Anton, Thank you for the update. Oracle Thanks Anton. After downloading the Excel file, I am unable to create formulas in cells that have text data. Get full access to Oracle SQL*Plus: The Definitive Guide, 2nd Edition and 60K+ other titles, with free 10-day trial of O'Reilly. UpgradesSQL Oracle Morten. @Stevenquave Strange, that code works for me. 12/30/1899 01/01/1900 (StarCalc 1.0) 01/01/1904 The last of these options was checked. 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, Thnx Anton, Now I successfully compiled the package, Hi Anton & co, Im having trouble with dates. In 11g we can covert the refcursor to a DBMS_SQL cursor and call cursor2sheet directly. If have tried to improve the performance, but havent found a way yet. 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.. Im using this package very successfully and now have a requirement to replicate an excel spreadsheet that Ive been presented with. Very strange issue here.. This launches Excel because of the Understandable, its not the native Excel format that PL/SQL is working with. Ion

- 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; .