How to export every report in Excel

a.mouttou

Hello,

A little thing that i make for exporting every report in a excel file.

1°) Modify the Excel buffer table by adding these functions

//Function for opening workbook
OpenBookSimple(FileName : Text[250])
IF FileName = '' THEN
ERROR(Text001);

IF NOT EXISTS(FileName) THEN
ERROR(Text003,FileName);

IF NOT CREATE(XlApplication,TRUE) THEN
ERROR(Text000);

XlApplication.Workbooks._Open(FileName);
XlWorkBook := XlApplication.ActiveWorkbook;

SelectSheetSimple(SheetName : Text[30]) : Boolean
IF XlWorkBook.Name='' THEN ERROR(Text023);

i := 1;
EndOfLoop := XlWorkBook.Worksheets.Count;
WHILE i <= EndOfLoop DO BEGIN
XlWorkSheet := XlWorkBook.Worksheets.Item(i);
IF SheetName = XlWorkSheet.Name THEN i := EndOfLoop;
i := i + 1;
END;

IF SheetName = XlWorkSheet.Name THEN
EXIT(TRUE)
ELSE
EXIT(FALSE);

//Function creating a sheet
CreateSheetSimple(SheetName : Text[30])
IF SelectSheetSimple(SheetName) THEN
ERROR(Text024);
XlWorkSheet:=XlWorkBook.Worksheets.Add;
XlWorkSheet.Name:=PADSTR(SheetName,30);

//Write the sheet in excel
WriteSheetSimple(SheetName : Text[30])
Window.OPEN(
Text005 +
'@\');
Window.UPDATE(1,0);

XlEdgeBottom := 9;
XlContinuous := 1;
XlLineStyleNone := -4142;
XlLandscape := 2;
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNTAPPROX;
RecNo := 0;

IF SheetName <> '' THEN
BEGIN
IF NOT SelectSheetSimple(SheetName) THEN ERROR(Text024);
END;
IF FIND('-') THEN
REPEAT
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF NumberFormat <> '' THEN
XlWorkSheet.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
IF Formula = '' THEN
XlWorkSheet.Range(xlColID + xlRowID).Value := "Cell Value as Text"
ELSE
XlWorkSheet.Range(xlColID + xlRowID).Formula := GetFormula;
IF Comment <> '' THEN
XlWorkSheet.Range(xlColID + xlRowID).AddComment := Comment;
IF Bold THEN
XlWorkSheet.Range(xlColID + xlRowID).Font.Bold := Bold;
IF Italic THEN
XlWorkSheet.Range(xlColID + xlRowID).Font.Italic := Italic;
XlWorkSheet.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
IF Underline THEN
XlWorkSheet.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
UNTIL NEXT = 0;
XlWorkSheet.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;

Window.CLOSE;

//Showing excel application and giving the control to the user
ShowApp()
XlApplication.Visible(TRUE);
XlApplication.UserControl(TRUE);


//Use for runing excel macro
RunMacro(MacroName : Text[150])
XlApplication.Run(MacroName);


2°) Declare these variables
Name DataType Subtype Length
CheminExcel Text 150
ExportXL Boolean
XL Record Excel Buffer
NbrLignes Integer


And this function in C/AL Globals

//This function is use to write data in excel buffer
EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean)
XL.INIT;
XL.VALIDATE("Row No.",RowNo);
XL.VALIDATE("Column No.",ColumnNo);
XL."Cell Value as Text" := CellValue;
XL.Formula := '';
XL.Bold := Bold;
XL.Italic := Italic;
XL.Underline := UnderLine;
XL.INSERT;

3°) on the request form
Add two field
Export to Excel with sourcexpr ExportXL
and
Path of the Excel File sourceexpr = CheminXL

And add this code to the openform trriger

OnOpenForm()
IF CheminExcel ='' THEN CheminExcel:= 'c:\reporting\modele\ExcelFile.xls'

//you can make an excel with all data you want

4°) And add this code to the report
Report - OnPreReport()

IF ExportXL THEN
BEGIN
//Open the workbook populate by the request form
XL.OpenBookSimple(CheminExcel);

//Delete all data from excel buffer
XL.DELETEALL;
XL.SelectSheetSimple('Customers');
NbrLignes:=2;
END;

Report - OnPostReport()

IF ExportXL THEN
BEGIN
IF XL.COUNT > 0 THEN XL.WriteSheetSimple('');
XL.RunMacro('MajCarte');
XL.GiveUserControl;
END;

And on the section paste this code

//to write all the data you want in the section
IF ExportXL THEN
BEGIN
//EnterCell(Row,Col,Data,Bold,Italic,Underline);
EnterCell(NbrLignes,1,FORMAT("No."),FALSE,FALSE,FALSE);
EnterCell(NbrLignes,2,FORMAT(Name),FALSE,FALSE,FALSE);
NbrLignes:=+1;
END;


Ok if you questions

Marije_Brummel

What is wrong with using the Excel buffer?

Alex_Chow

I think his intention was so that every report can be exported to Excel by adding a simple line calling the Excel Buffer functions.

However, I still don't see how this can work... :-k

a.mouttou

To BlackTiger on each report i write the part of code in the section code, so i can write it after on the excelfile.

If you want i can give you, the excel buffer and an exemple report.

Regards.

Alex_Chow

Yes, but you would still need to map the fields displayed on the report to your Excel buffer.

In another words, it's not automated in which you can just teach the end user to copy and paste some code for it to work.

You still need a junior developer to map the fields to the buffer.