Export to Excel with multiple sheet without using Excel Buffer

Name DataType Subtype Length
Window Dialog
ExcelRowCTR Integer
XlApp Automation Unknown Automation Server.Application
XlBook Automation Unknown Automation Server.Workbook
Xlsheet Automation Unknown Automation Server.Worksheet
XlRange Automation Unknown Automation Server.Range


Name ConstValue
Text001 Processing  @1@@@@@@@@


Name
MakeExcelDataHeader
MakeExcelDataFooter
MakeExcelDataGrandFooter
CreateBook
CreateExcelbook
GiveUserControl

AddData
-----------------------------------------------------------------------------------------------------------------------
Window.OPEN(Text001);
Window.UPDATE(1,ROUND(LineCount / NoOfRecord * 10000,1));
Window.close;
--------------------------------------------------------------------------------------------------------------------------
CreateBook()
ExcelRowCTR:=0;

CREATE(XlApp, FALSE, TRUE);
XlBook := XlApp.Workbooks.Add();
Xlsheet:= XlApp.ActiveSheet;
-------------------------------------------------------------------------------------------------------------------------

CreateExcelbook(SheetName : Text[30])
{
Xlsheet.Range('B1','P'+FORMAT(ExcelRowCTR)).Columns.ColumnWidth :=6;  //4Jun2013
Xlsheet.Range('B1','B'+FORMAT(ExcelRowCTR)).Columns.ColumnWidth :=7;  //5Jun2013
Xlsheet.Range('Q1','Q'+FORMAT(ExcelRowCTR)).Columns.ColumnWidth :=7;  //5Jun2013
Xlsheet.Range('A1','Q'+FORMAT(ExcelRowCTR)).Columns.WrapText := TRUE;
Xlsheet.Range('A1','Q'+FORMAT(ExcelRowCTR)).VerticalAlignment:= -4160;  //VerticalAlignment Center


Xlsheet.PageSetup.Orientation:= 2; //Landscape    //4Jun2013
//Xlsheet.PageSetup.PaperSize:=2;

//Xlsheet.PageSetup.CenterHeader := 'created from: ' +USERID + ' ' + FORMAT(TODAY);
Xlsheet.PageSetup.PrintTitleRows := '$1:$13';
}

//Xlsheet.PageSetup.PrintGridlines := TRUE;

//Xlsheet.Range('A2','P5').BorderAround(1);                                               //Border Row
//Xlsheet.Range('I'+FORMAT(ExcelRowCTR),'O'+FORMAT(ExcelRowCTR)).BorderAround(1,2,-4105); //Border Line


XlApp.Visible:=TRUE;
Xlsheet.Name := SheetName;
XlApp.Visible:=FALSE;

GiveUserControl;
------------------------------------------------------------------------------------------------------------------------
GiveUserControl()
XlApp.Visible(TRUE);
XlApp.UserControl(TRUE);
CLEAR(XlApp);
------------------------------------------------------------------------------------------------------------------------
AddData(StartRange : Text[30];EndRange : Text[30];Value : Text[1024];IsBold : Boolean;IsItalics : Boolean;IsUnderline : Boolean;IsSize : Decimal)

Xlsheet.Range(StartRange).Value := Value;
XlRange:=Xlsheet.Range(StartRange,EndRange);
XlRange.Merge;
Xlsheet.Range(StartRange,EndRange).Font.Name:='Calibri';
Xlsheet.Range(StartRange,EndRange).Font.Bold := IsBold;
Xlsheet.Range(StartRange,EndRange).Font.Italic :=IsItalics;
Xlsheet.Range('A'+FORMAT(ExcelRowCTR),'Q'+FORMAT(ExcelRowCTR)).Font.Size(IsSize);
----------------------------------------
------------------------------------------------------------------------------------------------------------------------
Multiple Sheet
------------------------------------------------------------------------------------------------------------------------
CreateBook()
CREATE(XlApp, FALSE, TRUE);
//XlBook := XlApp.Workbooks.Add();
//Xlsheet:= XlApp.ActiveSheet;

XlApp.Workbooks.Add(1);
XlBook := XlApp.ActiveWorkbook;

Xlsheet := XlBook.Worksheets.Add;
Xlsheet.Name := 'Non_Tariff';
Xlsheet := XlBook.Worksheets.Add;
Xlsheet.Name := 'Tariff';

XlApp.DisplayAlerts(FALSE);
Xlsheet := XlBook.Worksheets.Item('Sheet1');
Xlsheet.Delete;
-------------------------------------------


//GenericSalesRptTariff - OnPreDataItem()
Xlsheet := XlBook.Worksheets.Item(1);

-------------------------------------------
-------------Online---------------------
-------------------------------------------
OnRun()
// OPEN THE EXCEL APPLICATION

CREATE(XlApp, FALSE, TRUE);
// ALLOW IT TO BE VISIBLE
XlApp.Visible(TRUE);
// CREATE A WORKBOOK WITH ONLY ONE SHEET (WORKBOOK CANNOT BE CREATED EMPTY)
XlApp.Workbooks.Add(1);
// MAKE THE WORKBOOK ACTIVE
xlBook := XlApp.ActiveWorkbook;
// CREATE THE WORKSHEETS (4 OF THEM) WITHIN THE WORKBOOK, AND NAME EACH ONE ON IT'S WORKSHEET TAB
xlSheetCP := xlBook.Worksheets.Add;
xlSheetCP.Name := 'wholesale';
xlSheetIC := xlBook.Worksheets.Add;
xlSheetIC.Name := 'Obtain';
xlSheetCE := xlBook.Worksheets.Add;
xlSheetCE.Name := 'Non-tariff';
xlSheetMC := xlBook.Worksheets.Add;
xlSheetMC.Name := 'Tariff';
// Remove the 'original' sheet named 'Sheet1' - keeping only MY sheets
XlApp.DisplayAlerts(FALSE);
xlSheet := xlBook.Worksheets.Item('Sheet1');
xlSheet.Delete;
// CALL FUNCTION TO SETUP SHEETS
//SheetSetup;
SheetSetup_1;
SheetSetup_2;
-------------------------------------------
SheetSetup_1()
// SET THE COUNTER TO BEGIN AT THE FIRST SHEET, AND END AT LAST
//i := 1;
//x := (xlBook.Worksheets.Count);
// LOOP THROUGH EACH SHEET - OUTPUTTTING COLUMN HEADINGS & FORMATTING
//REPEAT
xlSheet := xlBook.Worksheets.Item(1);
xlSheet.Range('A3').Value := 'Item';
xlSheet.Range('B3').Value := 'Robosol';
xlSheet.Range('C3').Value := 'Amount';
xlSheet.Range('C:C').Columns.NumberFormat := '#,##0.00';
//...AND SO ON AND SO ON
//i := i + 1;
//UNTIL i > x;
-------------------------------------------
SheetSetup_2()
// SET THE COUNTER TO BEGIN AT THE FIRST SHEET, AND END AT LAST
//i := 1;
//x := (xlBook.Worksheets.Count);
// LOOP THROUGH EACH SHEET - OUTPUTTTING COLUMN HEADINGS & FORMATTING
//REPEAT
xlSheet := xlBook.Worksheets.Item(1);
xlSheet.Range('A3').Value := 'Item';
xlSheet.Range('B3').Value := 'Description';
xlSheet.Range('C3').Value := 'vaibhav';
xlSheet.Range('C:C').Columns.NumberFormat := '#,##0.00';
//...AND SO ON AND SO ON
//i := i + 1;
//UNTIL i > x;
-------------------------------------------------
Xlsheet.Range('A'+FORMAT(ExcelRowCTR),'J'+FORMAT(ExcelRowCTR)).Columns.AutoFit;

//PageBreak ++
             XlRange := Xlsheet.Range('A'+FORMAT(ExcelRowCTR+1),'Z'+FORMAT(ExcelRowCTR+1));
             XlRange.PageBreak := -4135;
 //PageBreak --
=========================================================
------------------------------------------------------------------------------------------------------------------------

GetNextAlphabet(AlphabetNo : Integer) NextAlphabet : Text[10]
CLEAR(ArrayAlphabet);
ArrayAlphabet[1]:='A';
ArrayAlphabet[2]:='B';
ArrayAlphabet[3]:='C';
ArrayAlphabet[4]:='D';
ArrayAlphabet[5]:='E';
ArrayAlphabet[6]:='F';
ArrayAlphabet[7]:='G';
ArrayAlphabet[8]:='H';
ArrayAlphabet[9]:='I';
ArrayAlphabet[10]:='J';
ArrayAlphabet[11]:='K';
ArrayAlphabet[12]:='L';
ArrayAlphabet[13]:='M';
ArrayAlphabet[14]:='N';
ArrayAlphabet[15]:='O';
ArrayAlphabet[16]:='P';
ArrayAlphabet[17]:='Q';
ArrayAlphabet[18]:='R';
ArrayAlphabet[19]:='S';
ArrayAlphabet[20]:='T';
ArrayAlphabet[21]:='U';
ArrayAlphabet[22]:='V';
ArrayAlphabet[23]:='W';
ArrayAlphabet[24]:='X';
ArrayAlphabet[25]:='Y';
ArrayAlphabet[26]:='Z';

EXIT(ArrayAlphabet[AlphabetNo]);
=================================================================
-----------------------------------------------------------------------------------------------------------------------
//Add column on run time

ExtendedHeader(ColumnNo : Integer)
FOR i:=1 TO Ctr DO
BEGIN
     Xlsheet.Range(GetNextAlphabet(ColumnNo+i)+FORMAT(ExcelRowCTR)).Value :=FORMAT(ArrayCharges[i]);
     Xlsheet.Range(GetNextAlphabet(ColumnNo+i)+FORMAT(ExcelRowCTR),GetNextAlphabet(ColumnNo+i)+FORMAT(ExcelRowCTR)).Font.Bold := TRUE;
END;
===============================================================================

Xlsheet.Range('J'+FORMAT(ExcelRowCTR),'J'+FORMAT(ExcelRowCTR)).HorizontalAlignment :=-4152;

LEFT: sheet.range(...).HorizontalAlignment := -4131
Center: sheet.range(...).HorizontalAlignment := -4108
Right: sheet.range(...).HorizontalAlignment := -4152

------------------------------------------------------------------------
Xlsheet.Shapes.AddPicture('D:\Customer Reports\Companypic.jpg',1,1,345,50,200,50);