Hi Tom,

Thanks for being with us!

I have a excel file with a template, what my requirement is to copy that template from that excel file to new excel file and also append some data into the same excel file with template.
when I tried to write data into excel file after copying the template, template gets overide.
I want to write the data into cells after that template.
Please go through the code and thanks in advance.

create or replace PROCEDURE PRO_EXCEL_FILE_SAPGEN
AS
 APPID PLS_INTEGER; 
 ConvID  PLS_INTEGER;
 v_filehandle_input UTL_FILE.FILE_TYPE; 
 A NUMBER(5):=25; 
  report VARCHAR2(256);
 Excel_Loc Varchar2(150);
 
 v_file exception;
 pragma exception_init(v_file,-106556);
 
 MyFormatNum PLS_INTEGER;
  zippdfbat   utl_file.file_type;
  v_date varchar2(20);
  FILENAME   utl_file.file_type;
   v_newline VARCHAR2(32767);
   V_filehandle_output   utl_file.file_type;
begin

v_date := to_char(sysdate,'ddmmyy');

 zippdfbat    := UTL_FILE.fopen('ACQOUTYES','ZipPDF.bat','w');
                                 
  UTL_FILE.put_line(zippdfbat,'@echo off');
  UTL_FILE.put_line(zippdfbat,'cd\');
  UTL_FILE.put_line(zippdfbat,'E:');
  UTL_FILE.put_line(zippdfbat,'cd OTP');
  UTL_FILE.put_line(zippdfbat,'cd ACQUIRING');
  UTL_FILE.put_line(zippdfbat,'cd YES');
  UTL_FILE.put_line(zippdfbat,'copy E:\OTP\ACQUIRING\YES\SAPGAN.xls  E:\OTP\ACQUIRING\YES\sapgen_settle'||v_date||'.xls');
  UTL_FILE.put_line(zippdfbat,'pause');
  UTL_FILE.fclose(zippdfbat);
  
  DBMS_SCHEDULER.run_job ('excel_job', TRUE);
 
  report    := 'sapgen_settle'||v_date||'.xls';
  

 as_xlsx.new_sheet;
 as_xlsx.clear_workbook;
 as_xlsx.query2sheet( 'SELECT * FROM DAILY_SETTLE_ENTRIES where length(PROFIT_CENTER) = 6' );
 as_xlsx.save( 'ACQOUTYES', report );  

exception when others then
dbms_output.put_line(sqlerrm);
END PRO_EXCEL_FILE_SAPGEN;

as_xlsx.clear_workbook;