create more than one excel sheet based on a template

Hi everyone!

I’m designing a report that selects an Excel sheet in an Excel book (this sheet is going to be used as a template) and fills in a series of data from a table in Navision. For each different register date I have to create a new sheet in another Excel book but based on the initial template.

I know table 370 Excel Buffer has a CreateSheet function and SaveWorkbook but I don´t know how to continue.

Thanks in advance for your help!

Regards,

Raquel

Hi Raquel,

Follow this Post

Hello Raja,

Ok I’ll take a look! Thanks once again

Hi again! Maybe I din’t explain myself clear enough(sorry about that…still new at this [8-)])!

I have an Excel workbook which has one sheet. This sheet is configured in a particular way, it acts like a template (see figure). I have to process certain data in a group of records and transfer those values to the Excel template.

For each Entry date I must add a new Sheet but that new sheet must exactly the same style as the initial template Excel sheet (Sheet 1 in example).What I do first in the OnPreReport function is create an Excel book. Production is a table I developed and rExcelBuffer is type ExcelBuffer but is a temporary table. v_iteration is to determine whether it’s the first record being processed. The code is the following:

Production".SETRANGE(Production.“Entry date”,“Starting date”,“Ending date”);

IF Production.FINDSET() THEN BEGIN

CLEAR(rExcelBuffer);

rExcelBuffer.DELETEALL;

rExcelBuffer.CreateBook;

v_iteration := FALSE;

END

ELSE

ERROR('No entry with that date);

Then on the OnAfterGetRecord() function of the DataItem, I write the following code, where rExcelBuffer2 is also type Excel Buffer.

IF NOT v_iteration THEN BEGIN //only one record

rExcelBuffer.INIT;

rExcelBuffer.VALIDATE(rExcelBuffer.“Row No.”,3);

rExcelBuffer.VALIDATE(rExcelBuffer.“Column No.”,44);

rExcelBuffer.“Cell Value as Text”:=FORMAT(WORKDATE);

rExcelBuffer.INSERT;

END

ELSE BEGIN

CLEAR(rExcelBuffer);

rExcelBuffer.DELETEALL;

rExcelBuffer.INIT;

rExcelBuffer.VALIDATE(rExcelBuffer.“Row No.”,3);

rExcelBuffer.VALIDATE(rExcelBuffer.“Column No.”,44);

rExcelBuffer.“Cell Value as Text”:=FORMAT(WORKDATE);

rExcelBuffer.INSERT;

END;

IF NOT v_iteration THEN BEGIN //2º iteration and so on

CLEAR(rExcelBuffer2);

rExcelBuffer2.DELETEALL;

v_Num := 1;

Sheet := ‘Sheet1’;

rExcelBuffer2.OpenBook(FileName,Sheet); //This is the template sheet I was talking about

IF rExcelBuffer.FINDSET THEN REPEAT

BEGIN

rExcelBuffer2.TRANSFERFIELDS(rExcelBuffer);

rExcelBuffer2.INSERT;

END;

UNTIL rExcelBuffer.NEXT = 0;

rExcelBuffer2.CreateSheet(Sheet,’’,COMPANYNAME,USERID);

v_iteration := TRUE;

END

ELSE BEGIN

rExcelBuffer2.AddSheet; //AddSheet is a new function I added to the Excel Buffer table

Sheet := Text000;

v_Num +=1;

Sheet := ‘Sheet1’;

rExcelBuffer2.OpenBook(FileName,Sheet);

IF rExcelBuffer.FINDSET THEN REPEAT

BEGIN

rExcelBuffer2.TRANSFERFIELDS(rExcelBuffer);

rExcelBuffer2.INSERT;

END;

UNTIL rExcelBuffer.NEXT = 0;

Sheet := STRSUBSTNO(Sheet, v_Num);

rExcelBuffer2.CreateSheet(Sheet,’’,COMPANYNAME,USERID);

END;

As I mentioned above, AddSheet() is a function I added in the Excel Buffer table to be able to add a new sheet to the Excel workbook. The code is the following:

XlWrkBk := XlApp.ActiveWorkbook;

XlWrkSht := XlWrkBk.Worksheets.Add;

And finally on the OnPostDataItem() I save the workbook tsave all the changes.

rExcelBuffer2.SaveWorkBook(SavePath);

Right now, the first sheet is generated correctly and the data is set where it is supposed to go in the template. With this code I also add new sheets to the workbook but they appear blank and don’t inherit the template’s style. [^o)]

I hope that I expressed myself better.

Thanks in advance for all of your help!!

Regards,

Raquel

Hi Raquel,

I think your resultant report ( i mean excel) is getting all the dates in one sheet. You said(each Entry date I must add a new Sheet)

if above my analysis is Right( i thought [:D]) . i Haven’t seen any Logic that checks for Dates…

sry If am wrong…

What i may have done is Make some Function such that to PrintHeader , PrintFooter instead of Using Template and i use them in OnafterGet as yourequirement needs it You can use ur template wherever i used Function (may be[;)])…

(in OnafterGet)

if date = ‘’ then begin

Date := starting date; PrintHEader; End;

if postingdate = date then

Print records;

else begin

printfooter;

Addnewsheet; printheader; date:= posting date;

printrecords;

at last OnPost printfooter…

Let me know if it Helped or Not.