Home » Developer & Programmer » Reports & Discoverer » Rpt2Xls Cell Merge and Center (windows xp orant 6i)
| Rpt2Xls Cell Merge and Center [message #458219] |
Fri, 28 May 2010 00:24  |
|
|
Dear All Friends
i m using rpt2xls producer and successfully runing very good and easy i ask you some qustion for the export how can posible
in query cell merge and center in report transfar to excel time
please some guidnes.
if posible then please mantion to be a query..
sory for language ...
exmple
how can i add here merge cell
RPT2XLS.put_cell(10,:SHIPPING_MARKS, FontSize => 10 ,FontColor => 1, FontStyle => RPT2XLS.BOLD);---+RPT2XLS.UNDERLINE);
please urgent reply..
thanks
[EDITED by LF, after topics have being merged and (partially) duplicate message deleted]
[Updated on: Fri, 28 May 2010 04:34] by Moderator Report message to a moderator |
|
|
|
| Re: Rpt2Xls Cell Merge and Center [message #494385 is a reply to message #458219] |
Mon, 14 February 2011 02:39  |
nagu_bhat
Messages: 10
Registered: May 2009
|
Junior Member |
|
|
Hi
You have to Change the rpt2xls package first to incorporate some changes to do that.
Package Spec
-------------
package rpt2xls is
-- font style constants
bold constant binary_integer := 1;
italic constant binary_integer := 2;
underline constant binary_integer := 4;
-- horizontal alignment constants
subtype xlhalign is binary_integer;
center constant xlhalign := -4108;
centeracrossselection constant xlhalign := 7;
distributed constant xlhalign := -4117;
fill constant xlhalign := 5;
general constant xlhalign := 1;
justify constant xlhalign := -4130;
left constant xlhalign := -4131;
right constant xlhalign := -4152;
procedure put_cell (colno_from in binary_integer,
cellvalue in varchar2,
colno_to in binary_integer default null,
fontname in varchar2 default null,
fontsize in binary_integer default null,
fontstyle in binary_integer default null,
fontcolor in binary_integer default null,
bgrcolor in binary_integer default null,
format in varchar2 default null,
align in xlhalign default null,
new_sheet in varchar2 default 'N',
rename_sheet in varchar2 default null);
procedure new_line;
procedure prev_line;
procedure run (p_file_name in varchar2);
procedure release_memory;
end;
Package Body
------------
package body rpt2xls is
type excelcell is record (rowno binary_integer, colno_from binary_integer, colno_to binary_integer,
val varchar2(2000), fontname varchar2(20), fontsize binary_integer,
fontstyle binary_integer, fontcolor binary_integer, bgrcolor binary_integer,
format varchar2(60), align xlhalign, new_sheet varchar2(1),
rename_sheet varchar2(100));
type excelcells is table of excelcell;
cell excelcells := excelcells();
currentrow binary_integer := 1;
procedure new_line is
begin
currentrow := currentrow + 1;
end;
procedure prev_line is
begin
currentrow := currentrow - 1;
end;
procedure put_cell (colno_from in binary_integer,
cellvalue in varchar2,
colno_to in binary_integer default null,
fontname in varchar2 default null,
fontsize in binary_integer default null,
fontstyle in binary_integer default null,
fontcolor in binary_integer default null,
bgrcolor in binary_integer default null,
format in varchar2 default null,
align in xlhalign default null,
new_sheet in varchar2 default 'N',
rename_sheet in varchar2 default null) is
begin
cell.extend;
cell(cell.last).rowno := currentrow;
cell(cell.last).colno_from := colno_from;
cell(cell.last).val := cellvalue;
cell(cell.last).colno_to := colno_to;
cell(cell.last).fontname := fontname;
cell(cell.last).fontsize := fontsize;
cell(cell.last).fontstyle := fontstyle;
cell(cell.last).fontcolor := fontcolor;
cell(cell.last).bgrcolor := bgrcolor;
cell(cell.last).format := format;
cell(cell.last).align := align;
cell(cell.last).new_sheet := new_sheet;
cell(cell.last).rename_sheet := rename_sheet;
end;
procedure run (p_file_name varchar2) is
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
workcell ole2.obj_type;
workfont ole2.obj_type;
workinterior ole2.obj_type;
workrange ole2.obj_type;
workcolumn ole2.obj_type;
arglist ole2.list_type;
worksheet_idx number;
worksheet_cnt number;
v_desc varchar2(100);
v_row_count number;
v_from_col_char varchar2(1);
v_to_col_char varchar2(1);
v_from_val number;
v_to_val number;
procedure release_safely (io_ole2obj in out ole2.obj_type) is
begin
if io_ole2obj is not null then
ole2.release_obj (io_ole2obj);
io_ole2obj := Null;
end if;
end;
procedure autofit is
begin
arglist := ole2.create_arglist;
ole2.add_arg (arglist, 'A:IV');
workcolumn := ole2.get_obj_property (worksheet, 'Columns', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (workcolumn, 'AutoFit');
release_safely (workcolumn);
end;
procedure add_newsheet is
begin
autofit;
worksheet_idx := worksheet_idx + 1;
release_safely (worksheet);
arglist := ole2.create_arglist;
ole2.add_arg (arglist, worksheet_idx);
worksheet := ole2.get_obj_property (worksheets, 'Item', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (worksheet, 'Select');
-- Initialize Row Count
v_row_count := 0;
end;
procedure save_file is
begin
if p_file_name is not null then
arglist := ole2.create_arglist;
ole2.add_arg (arglist, p_file_name);
ole2.invoke (workbook, 'SaveAs', arglist);
ole2.destroy_arglist (arglist);
end if;
end;
begin
application := ole2.create_obj('Excel.Application');
ole2.set_property (application, 'DisplayAlerts', False);
if p_file_name is null then
ole2.set_property (application, 'Visible', True);
else
ole2.set_property (application, 'Visible', False);
end if;
workbooks := ole2.get_obj_property (application, 'Workbooks');
workbook := ole2.invoke_obj (workbooks, 'Add');
worksheets := ole2.get_obj_property (workbook, 'Worksheets');
-- Create Extra 17 Excel Sheets
for i in 1..17
loop
release_safely (worksheet);
worksheet := ole2.invoke_obj (worksheets, 'Add');
end loop;
-- Rename all 20 Excel Sheets
for i in 1..20
loop
release_safely (worksheet);
arglist := ole2.create_arglist;
ole2.add_arg (arglist, i);
worksheet := ole2.get_obj_property (worksheets, 'Item', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (worksheet, 'Select');
ole2.set_property (worksheet,'Name', 'Page'||to_char(i,'FM99'));
end loop;
-- Go to First Shet
release_safely (worksheet);
arglist := ole2.create_arglist;
ole2.add_arg (arglist, 1);
worksheet := ole2.get_obj_property (worksheets, 'Item', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (worksheet, 'Select');
worksheet_idx := ole2.get_num_property(worksheet, 'Index');
worksheet_cnt := ole2.get_num_property(worksheets, 'Count');
if p_file_name is not null then
arglist := ole2.create_arglist;
ole2.add_arg (arglist, p_file_name);
ole2.invoke (workbook, 'FileSaveAs', arglist);
ole2.destroy_arglist (arglist);
end if;
v_row_count := 1;
for i in cell.first .. cell.last
loop
if cell(i).new_sheet = 'Y' then
add_newsheet;
end if;
if cell(i).rename_sheet is not null then
ole2.set_property (worksheet, 'Name', cell(i).rename_sheet);
end if;
if cell(i).val is not null then
arglist := ole2.create_arglist;
ole2.add_arg (arglist, v_row_count);
ole2.add_arg (arglist, cell(i).colno_from);
workcell := ole2.get_obj_property (worksheet, 'Cells', arglist);
workfont := ole2.get_obj_property (workcell, 'Font');
workinterior := ole2.get_obj_property (workcell, 'Interior');
ole2.destroy_arglist (arglist);
ole2.set_property (workcell, 'Value', cell(i).val);
ole2.set_property (workcell, 'NumberFormat', cell(i).format);
if cell(i).align is not null then
ole2.set_property (workcell, 'HorizontalAlignment', cell(i).align);
end if;
if cell(i).fontname is not null then
ole2.set_property (workfont, 'Name', cell(i).fontname);
end if;
if cell(i).fontsize is not null then
ole2.set_property (workfont, 'Size', cell(i).fontsize);
end if;
if mod(cell(i).fontstyle, 2) = 1 then
ole2.set_property (workfont, 'Bold', 1);
end if;
if mod(cell(i).fontstyle, 4) > 2 then
ole2.set_property (workfont, 'Italic', 1);
end if;
if mod(cell(i).fontstyle,  > 4 then
ole2.set_property (workfont, 'Underline', 2);
end if;
if cell(i).fontcolor is not null then
ole2.set_property (workfont, 'ColorIndex', cell(i).fontcolor);
end if;
if cell(i).bgrcolor is not null then
ole2.set_property (workinterior, 'ColorIndex', cell(i).bgrcolor);
end if;
release_safely (workinterior);
release_safely (workfont);
release_safely (workcell);
if cell(i).colno_to is not null then
arglist := ole2.create_arglist;
if trunc(cell(i).colno_from / 26) > 0 and cell(i).colno_from > 26 then
if (trunc(cell(i).colno_from / 26) * 26) = cell(i).colno_from then
v_from_col_char := chr(64 + (cell(i).colno_from / 26) - 1);
else
v_from_col_char := chr(64 + trunc(cell(i).colno_from / 26));
end if;
else
v_from_col_char := Null;
end if;
if trunc(cell(i).colno_to / 26) > 0 and cell(i).colno_to > 26 then
if (trunc(cell(i).colno_to / 26) * 26) = cell(i).colno_to then
v_to_col_char := chr(64 + (cell(i).colno_to / 26) - 1);
else
v_to_col_char := chr(64 + trunc(cell(i).colno_to / 26));
end if;
else
v_to_col_char := Null;
end if;
if mod(cell(i).colno_from,26) = 0 then
v_from_val := cell(i).colno_from - ((trunc(cell(i).colno_from / 26) - 1) *26);
else
v_from_val := mod(cell(i).colno_from,26);
end if;
if mod(cell(i).colno_to,26) = 0 then
v_to_val := cell(i).colno_to - ((trunc(cell(i).colno_to / 26) - 1) *26);
else
v_to_val := mod(cell(i).colno_to,26);
end if;
if v_from_col_char is null and v_to_col_char is null then
v_desc := v_from_col_char || chr(64 + cell(i).colno_from) || v_row_count || ':' || v_to_col_char || chr(64 + cell(i).colno_to) || v_row_count;
elsif v_from_col_char is null and v_to_col_char is not null then
v_desc := v_from_col_char || chr(64 + cell(i).colno_from) || v_row_count || ':' || v_to_col_char || chr(64 + v_to_val) || mod(v_row_count,26);
elsif v_from_col_char is not null and v_to_col_char is not null then
v_desc := v_from_col_char || chr(64 + v_from_val) || mod(v_row_count,26) || ':' || v_to_col_char || chr(64 + v_to_val) || mod(v_row_count,26);
end if;
ole2.add_arg (arglist, v_desc);
workrange := ole2.get_obj_property (worksheet, 'Range', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (workrange, 'Merge');
release_safely (workrange);
end if;
end if;
if i >= 5000 and mod(i, 5000) = 0 then
save_file;
end if;
if i <= cell.last-1 and cell(i).rowno <> cell(i+1).rowno then
v_row_count := v_row_count + (cell(i+1).rowno - cell(i).rowno);
if v_row_count > 65000 then
-- Autofit Excel Sheet
autofit;
-- Go to Next Sheet
exit when (worksheet_idx + 1) > worksheet_cnt;
add_newsheet;
end if;
end if;
end loop;
-- Autofit Excel Sheet
autofit;
-- Delete Blank Excel Extra Sheets
for i in worksheet_idx+1 .. worksheet_cnt
loop
release_safely (worksheet);
arglist := ole2.create_arglist;
ole2.add_arg (arglist, worksheet_idx+1);
worksheet := ole2.get_obj_property (worksheets, 'Item', arglist);
ole2.destroy_arglist (arglist);
ole2.invoke (worksheet, 'Delete');
end loop;
-- If filename is not null saving and quitting excel
if p_file_name is not null then
save_file;
ole2.invoke (application, 'Quit');
end if;
release_safely (worksheet);
release_safely (worksheets);
release_safely (workbook);
release_safely (workbooks);
release_safely (application);
exception
when others then
ole2.release_obj (application);
end;
procedure release_memory is
begin
cell := excelcells();
sys.dbms_session.free_unused_user_memory;
end;
end;
now pass your string as
RPT2XLS.put_cell(10,:SHIPPING_MARKS, colno_to => 12, FontSize => 10 ,FontColor => 1, FontStyle => RPT2XLS.UNDERLINE, , align => rpt2xls.centeracrossselection);
Regards
Nagaraj
|
|
|
|
| Re: Rpt2Xls Cell Merge and Center [message #494386 is a reply to message #494385] |
Mon, 14 February 2011 02:45 |
nagu_bhat
Messages: 10
Registered: May 2009
|
Junior Member |
|
|
HI
Small Change while passing the command
RPT2XLS.put_cell(10,:SHIPPING_MARKS, colno_to => 12, FontSize => 10 ,FontColor => 1, FontStyle => RPT2XLS.BOLD + RPT2XLS.UNDERLINE, , align => rpt2xls.centeracrossselection);
Regards
Nagaraj
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 19:06:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
