Excel with multiple sheet

aamoll

I want to print data in multiple sheet in one workbook.

mohana_cse06

Check Codeunit Export Analysis View how it is creating

manisharma31

This Might Help

<Control1000000000> - OnPush() // Code Written on a Button.
Win.OPEN(Text001+
Text002);
CLEAR(xlApp);
CREATE(xlApp,TRUE,TRUE);
xlApp.Workbooks.Add(1);
xlBook := xlApp.ActiveWorkbook;
CreateSheetName;
xlApp.DisplayAlerts(FALSE);
xlSheet := xlBook.Worksheets.Item('Sheet1');
xlSheet.Delete;
SheetSetup;
xlApp.Visible(TRUE);
CLEAR(xlBook);
CLEAR(xlApp);
Win.CLOSE;

SheetSetup()// Function to Create Multiple Work Sheet in Excel Starts >>
i := 1;
x := (xlBook.Worksheets.Count);
REPEAT
Win.UPDATE(1,i);
Win.UPDATE(2,ROUND(i/x*10000,1));
xlSheet := xlBook.Worksheets.Item(i);
intHeadingNo:=1;

DocTypeLen:=STRLEN(FORMAT(DocType));
Name:=DELSTR(xlSheet.Name,1,DocTypeLen+3);

CreateWorkSheetHeading;
intRowNo:=intHeadingNo;
CreateWorkSheetData;
i := i + 1;
UNTIL i > x;

CreateSheetName()
DataFnd:=FALSE;
IF TranType=TranType::Purchase THEN BEGIN
PurchHeader.RESET;
PurchHeader.SETRANGE(PurchHeader."Document Type",DocType);
PurchHeader.ASCENDING(FALSE);
IF PurchHeader.FINDFIRST THEN BEGIN
DataFnd:=TRUE;
REPEAT
xlSheetCP := xlBook.Worksheets.Add;
xlSheetCP.Name :=FORMAT(DocType)+' - '+PurchHeader."No.";
UNTIL PurchHeader.NEXT=0;
END;
END ELSE BEGIN
SaleHeader.RESET;
SaleHeader.SETRANGE(SaleHeader."Document Type",DocType);
SaleHeader.ASCENDING(FALSE);
IF SaleHeader.FINDFIRST THEN BEGIN
DataFnd:=TRUE;
REPEAT
xlSheetCP := xlBook.Worksheets.Add;
xlSheetCP.Name :=FORMAT(DocType)+' - '+SaleHeader."No.";
UNTIL SaleHeader.NEXT=0;
END;
END;
IF NOT DataFnd THEN BEGIN
xlBook.Close;
CLEAR(xlBook);
CLEAR(xlApp);
ERROR('No Data to create');
END;

CreateWorkSheetHeading()
IF TranType=TranType::Purchase THEN
xlSheet.Range('A'+FORMAT(intHeadingNo)).Value := 'Vendor Code :'
ELSE
xlSheet.Range('A'+FORMAT(intHeadingNo)).Value := 'Customer Code :';

xlSheet.Range('B'+FORMAT(intHeadingNo)).Value := GetVendorData(0);
xlSheet.Range('A'+FORMAT(intHeadingNo)).Font.Bold:=TRUE;
intHeadingNo+=1;

IF TranType=TranType::Purchase THEN
xlSheet.Range('A'+FORMAT(intHeadingNo)).Value := 'Vendor Name :'
ELSE
xlSheet.Range('A'+FORMAT(intHeadingNo)).Value := 'Customer Name :';

xlSheet.Range('B'+FORMAT(intHeadingNo)).Value := GetVendorData(1);
xlSheet.Range('A'+FORMAT(intHeadingNo)).Font.Bold:=TRUE;

intHeadingNo+=1;
xlSheet.Range('A'+FORMAT(intHeadingNo)).Value := 'Item Code';
xlSheet.Range('B'+FORMAT(intHeadingNo)).Value := 'Description';
xlSheet.Range('C'+FORMAT(intHeadingNo)).Value := 'Location';
xlSheet.Range('D'+FORMAT(intHeadingNo)).Value := 'Quantity';
xlSheet.Range('E'+FORMAT(intHeadingNo)).Value := 'Unit of Measure';
xlSheet.Range('F'+FORMAT(intHeadingNo)).Value := 'Unit Price';
xlSheet.Range('G'+FORMAT(intHeadingNo)).Value := 'Total Amount';
xlSheet.Range('A'+FORMAT(intHeadingNo),'G'+FORMAT(intHeadingNo)).Font.Bold:=TRUE;
xlSheet.Range('D'+FORMAT(intHeadingNo),'G'+FORMAT(intHeadingNo)).Columns.NumberFormat := '#,##0.00';

CreateWorkSheetData()
intRowNo+=1;
GrossTotal:=0;
IF TranType=TranType::Purchase THEN BEGIN
PurchLine.RESET;
PurchLine.SETRANGE(PurchLine."Document Type",DocType);
PurchLine.SETRANGE(PurchLine."Document No.",Name);
IF PurchLine.FINDFIRST THEN REPEAT
xlSheet.Range('A'+FORMAT(intRowNo)).Value := PurchLine."No.";
xlSheet.Range('B'+FORMAT(intRowNo)).Value := PurchLine.Description;
xlSheet.Range('C'+FORMAT(intRowNo)).Value := PurchLine."Location Code";
xlSheet.Range('D'+FORMAT(intRowNo)).Value := PurchLine.Quantity;
xlSheet.Range('E'+FORMAT(intRowNo)).Value := PurchLine."Unit of Measure";
xlSheet.Range('F'+FORMAT(intRowNo)).Value := PurchLine."Direct Unit Cost";
xlSheet.Range('G'+FORMAT(intRowNo)).Value := PurchLine."Line Amount";
xlSheet.Range('D'+FORMAT(intRowNo),'G'+FORMAT(intRowNo)).Columns.NumberFormat := '#,##0.00';
intRowNo+=1;
GrossTotal+=PurchLine."Line Amount";
UNTIL PurchLine.NEXT=0;
END ELSE BEGIN
SaleLine.RESET;
SaleLine.SETRANGE(SaleLine."Document Type",DocType);
SaleLine.SETRANGE(SaleLine."Document No.",Name);
IF SaleLine.FINDFIRST THEN REPEAT
xlSheet.Range('A'+FORMAT(intRowNo)).Value := SaleLine."No.";
xlSheet.Range('B'+FORMAT(intRowNo)).Value := SaleLine.Description;
xlSheet.Range('C'+FORMAT(intRowNo)).Value := SaleLine."Location Code";
xlSheet.Range('D'+FORMAT(intRowNo)).Value := SaleLine.Quantity;
xlSheet.Range('E'+FORMAT(intRowNo)).Value := SaleLine."Unit of Measure";
xlSheet.Range('F'+FORMAT(intRowNo)).Value := SaleLine."Unit Price";
xlSheet.Range('G'+FORMAT(intRowNo)).Value := SaleLine."Line Amount";
xlSheet.Range('D'+FORMAT(intRowNo),'G'+FORMAT(intRowNo)).Columns.NumberFormat := '#,##0.00';
intRowNo+=1;
GrossTotal+=SaleLine."Line Amount";
UNTIL SaleLine.NEXT=0;
END;
xlSheet.Range('A'+FORMAT(intRowNo)).Value := 'Gross Total';
xlSheet.Range('A'+FORMAT(intRowNo),'G'+FORMAT(intRowNo)).Font.Bold:=TRUE;
xlSheet.Range('G'+FORMAT(intRowNo)).Value := GrossTotal;
xlSheet.Range('G'+FORMAT(intRowNo)).Columns.NumberFormat := '#,##0.00';

GetVendorData(WhatData : Integer) : Text[30]
IF TranType=TranType::Purchase THEN BEGIN
PurchHeader.RESET;
PurchHeader.SETRANGE(PurchHeader."Document Type",DocType);
PurchHeader.SETRANGE(PurchHeader."No.",Name);
IF PurchHeader.FINDFIRST THEN
CASE WhatData OF
0: EXIT(PurchHeader."Buy-from Vendor No.");

1: EXIT(PurchHeader."Pay-to Name");
END;
END ELSE BEGIN
SaleHeader.RESET;
SaleHeader.SETRANGE(SaleHeader."Document Type",DocType);
SaleHeader.SETRANGE(SaleHeader."No.",Name);
IF SaleHeader.FINDFIRST THEN
CASE WhatData OF
0: EXIT(SaleHeader."Sell-to Customer No.");

1: EXIT(SaleHeader."Bill-to Name");
END;
END;