Excel with multiple sheet

Comments

• Options

  mohana_cse06 Member Posts: 5,503

  2011-06-02

  Check Codeunit Export Analysis View how it is creating

  
  -Mohana
  http://mohana-dynamicsnav.blogspot.in/
  https://www.facebook.com/MohanaDynamicsNav

  0
• Options

  manisharma31 Member Posts: 285

  2011-06-03

  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;

  Regards,
  Manish

  0