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

     

  • In reply to Raja Venkat:

    Hello Raja,

    Ok I'll take a look! Thanks once again

     

     

  • In reply to Raquel:

    Hi again! Maybe I din't explain myself clear enough(sorry about that.......still new at this Confused)!

    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. Hmm

    I hope that I expressed myself better.

    Thanks in advance for all of your help!!

     

    Regards,

    Raquel

  • In reply to 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 Big Smile)  . 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 beWink)...

    (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.

     

     

Related