Delphi exl Report

 

Function TForm1.GenExcel: boolean;
Var
  I: Integer;
  sheet: variant;
  sTmpRegion, sReportName: String;
  xlsRange: variant;

  DN, PN, Customer_PN, Bill_To, Cust_PO,
    Re_Cfm_dateRe_Cfm_date,
    DN_line, DN_Qty, Ship_way, Pack_way: String;
Begin
  Result := GetPackDetail;
  If Result = False Then
    Begin
      exit;
    End;
  Try
    Try
      sTmpRegion := FormatDateTime('yyyy' + '' + 'MM' + '' + 'dd' + '' + 'hh' + '' + 'mm' + '' + 'ss', now);
      sReportName := 'D:\myexl\' + 'exl ' + sTmpRegion + '.xls';
      sheet := CreateOleObject('Excel.Sheet');
      xlsRange := sheet.Application.Range['A2:j2'];
      xlsRange.HorizontalAlignment := xlCenter;
      xlsRange.Interior.ColorIndex := 33;
      xlsRange := sheet.Application.Range['A2:C65536'];
      xlsRange.NumberFormat := '@';
      i := 2;
      //Write title
      sheet.Application.cells[i, 1] := 'DN';
      sheet.Application.cells[i, 2] := 'P/N.';
      sheet.Application.cells[i, 3] := 'Customer P/N';
      sheet.Application.cells[i, 4] := 'Bill-To.';
      sheet.Application.cells[i, 5] := 'Cust.PO.';
      sheet.Application.cells[i, 6] := 'Re-Cfm date.';
      sheet.Application.cells[i, 7] := ' DN line#';
      sheet.Application.cells[i, 8] := 'DN Qty';
      sheet.Application.cells[i, 9] := 'TShip way';
      sheet.Application.cells[i, 10] := 'Pack way';

      GetPackDetail;
      //While data
      While Not cds_Tmp.Eof Do
        Begin
          i:=i+1;
          DN := cds_Tmp.FieldByName('DN').AsString;
          PN := cds_Tmp.FieldByName('PN').AsString;
          Customer_PN := cds_Tmp.FieldByName('Customer_PN').AsString;
          Bill_To := cds_Tmp.FieldByName('Bill_To').AsString;
          Cust_PO := cds_Tmp.FieldByName('Cust_PO').AsString;
          Re_Cfm_dateRe_Cfm_date := cds_Tmp.FieldByName('Re_Cfm_dateRe_Cfm_date').AsString;
          DN_line := cds_Tmp.FieldByName('DN_line').AsString;
          DN_Qty := cds_Tmp.FieldByName('DN_Qty').AsString;
          Ship_way := cds_Tmp.FieldByName('Ship_way').AsString;
          Pack_way := cds_Tmp.FieldByName('Pack_way').AsString;
         //Write Excel
          sheet.Application.cells[i, 1] := DN;
          sheet.Application.cells[i, 2] := PN;
          sheet.Application.cells[i, 3] := Customer_PN;
          sheet.Application.cells[i, 4] := Customer_PN;
          sheet.Application.cells[i, 5] := Cust_PO;
          sheet.Application.cells[i, 6] := Re_Cfm_dateRe_Cfm_date;
          sheet.Application.cells[i, 7] := DN_line;
          sheet.Application.cells[i, 8] := DN_Qty;
          sheet.Application.cells[i, 9] := Ship_way;
          sheet.Application.cells[i, 10] := Pack_way;
          cds_Tmp.Next;
        End;
      // add grid
      xlsRange := sheet.Application.Range['A2:j' + IntToStr(i)];
      xlsRange.Font.Name := 'Arial';
      xlsRange.Font.Size := 10;
      xlsRange.Borders.LineStyle := 1;
      sheet.Application.Columns.EntireColumn.AutoFit;
    Except
      On e: Exception Do
        Begin
          ShowMessage('Error occurs!');
          exit;
        End;
    End;
  Finally
    sheet.SaveAs(sReportName);
    ShowMessage('Process Complete!');
  End;

End;

Function TForm1.GetPackDetail: Boolean;
Begin
  ssql :=
    '0SELECT' +
    '  C021.NCMP, C021.PINO AS DN,C022.ITEM AS PN,c081.cprt AS Customer_PN,' +
    '  C021.cuno AS Bill_To,C021.refa AS Cust_PO,TO_CHAR(c021.invd, ''YYYY/MM/DD'')AS Re_Cfm_dateRe_Cfm_date, ' +
    '  C022.PONO AS DN_line,C022.DQUA AS DN_Qty,C021.CRTE AS Ship_way,C021.MPAK AS Pack_way ' +
    'FROM ' +
    '  CMPPS021 C021, CMPPS022 C022,cmpps081 c081 ' +
    'WHERE ' +
    '  c021.ncmp=c022.ncmp' +
    '  AND c021.pino=c022.pino' +
    '  AND c021.ncmp=c081.ncmp' +
    '  AND c021.orno=c081.orno' +
    '  AND c022.pono=c081.pono' +
    '  AND C021.pgi_status=''Y'''+
    '  AND TO_CHAR (c021.invd, ''YYYY/MM/DD'')BETWEEN '+ QuotedStr(FormatDateTime('yyyy-mm-dd', DateFrom.Date)) + '' +
                                               '  AND '+ QuotedStr(FormatDateTime('yyyy-mm-dd', DateTo.Date));
   // '  AND C021.PINO =' + QuotedStr(Edit1.text) + '';
  result := CDS_Open(cds_Tmp, sSQL)
End;