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