Oracle -> Write excel file from PL/SQL -> Tutorial 6
 

Tutorials

Tutorial

Headers and autofilter

Deutsche Version

In 'First Steps' we saw that in many cases headers will be defined by default and also an autofilter will be set. There are different possibilities to define your own headers.

Titles

One or two rows

Headers with one or two rows that use standard layout (colour and font) can be defined easily in an array.

    TYPE t_rec_caption  IS RECORD(
         title              VARCHAR2(100)
        ,topTitle           VARCHAR2(100)
        ,span               PLS_INTEGER
        ,comment            VARCHAR2(1000)
        ,repeatTitle        BOOLEAN
        );
    TYPE t_tab_caption IS TABLE OF t_rec_caption
        INDEX BY BINARY_INTEGER;

For every column you can define a title and a comment.

Optionaly you can define another row above (topTitle). These "Top Titles" can span more than one column (span). Hint: has at least one column a topTitle then all others need at least a space as topTitleif they are not included in a "spanned" topTitle.

repeatTitle in the first t_tab_caption element decides, whether the titles will be repeated on every printed page. Dont set this attribute if you want to use print area and/or repeat title in t_rec_printSetup.

Everything handmade

Siehe Complex Example

Autofilter

If you have defined your headers with t_tab_caption (as shown above), then you can simply set p_autofilter = TRUE (Default) in closeWorksheet and you will get a filter for every column where you defined a header

But you can also define exactly where you want a filter:

    TYPE t_rec_autofilter IS RECORD (
         row         PLS_INTEGER
        ,colFrom     PLS_INTEGER
        ,colTo       PLS_INTEGER
       );

This is quite selfexplanatory: the filter will be set in row row from column colFrom to column colTo. This will only work when p_autolilter is not TRUE (Default!) in closeWorksheet.