oracle - Printing Columnwise in Excel using ORA_EXCEL package -
i using ora_excel package generate excel below.
columna row1 row2 row3
using below procedure,
declare doc_id pls_integer:=0; sheet_id pls_integer:=0; row_id pls_integer:=0; begin doc_id := ora_excel.new_document; sheet_id := ora_excel.add_sheet('sheet 1',doc_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row1', doc_id,sheet_id, row_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row2',doc_id,sheet_id, row_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row3', doc_id,sheet_id, row_id); ora_excel.save_to_file('ftp_folder','excel.xlsx',doc_id); end;
and generating excel specific folder.
now wants add record columnb
after printing rows of columna
below.
columna columnb row1 row1b row2 row2b row3 row3b
i newbie pl/sql.
could me resolve issue.
is there anyway navigate , update specific row?
try this:
declare doc_id pls_integer:=0; sheet_id pls_integer:=0; row_id pls_integer:=0; begin doc_id := ora_excel.new_document; sheet_id := ora_excel.add_sheet('sheet 1',doc_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row1', doc_id,sheet_id, row_id); ora_excel.set_cell_value('b','row1b', doc_id,sheet_id, row_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row2',doc_id,sheet_id, row_id); ora_excel.set_cell_value('b','row2b', doc_id,sheet_id, row_id); row_id := ora_excel.add_row; ora_excel.set_cell_value('a','row3', doc_id,sheet_id, row_id); ora_excel.set_cell_value('b','row3b', doc_id,sheet_id, row_id); ora_excel.save_to_file('ftp_folder','excel.xlsx',doc_id); end;
check link: http://www.oraexcel.com/documentation/ora_excel.set_cell_value
if columna , columnb populated @ different times , different sources, before write excel data file, code might work:
declare doc_id pls_integer:=0; sheet_id pls_integer:=0; row_id pls_integer:=0; v_idx pls_integer; type t_rowids_t table of pls_integer indexed pls_integer; vt_rowids t_rowids_t; begin doc_id := ora_excel.new_document; sheet_id := ora_excel.add_sheet('sheet 1',doc_id); --select first table row_id := ora_excel.add_row; vt_rowids(row_id) := row_id; ora_excel.set_cell_value('a','row1', doc_id,sheet_id, row_id); row_id := ora_excel.add_row; vt_rowids(row_id) := row_id; ora_excel.set_cell_value('a','row2',doc_id,sheet_id, row_id); row_id := ora_excel.add_row; vt_rowids(row_id) := row_id; ora_excel.set_cell_value('a','row3', doc_id,sheet_id, row_id); --select table v_idx := vt_rowids.first; while v_idx not null loop ora_excel.set_cell_value('b','row1b', doc_id,sheet_id, vt_rowids(v_idx)); ora_excel.set_cell_value('b','row2b', doc_id,sheet_id, vt_rowids(v_idx)); ora_excel.set_cell_value('b','row3b', doc_id,sheet_id, vt_rowids(v_idx)); v_idx := vt_rowids.next(v_idx); end loop; ora_excel.save_to_file('ftp_folder','excel.xlsx',doc_id); end;
Comments
Post a Comment