staticsfrm.~pas

这个是个简单的关于出票申请的处理

    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_START_UPDATE, oAgentList.Count + 8);
    Screen.Cursor := crHourGlass;
    try
      //建立OLE对象
      xlApp := CreateOleObject('Excel.Application');
      xlBook := xlApp.Workbooks.Add;
      xlSheet := xlBook.Worksheets['sheet1'];
      xlApp.Visible := false;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 1);

      //定义Excel格式
      xlSheet.Cells.Font.Name := '宋体';
      xlSheet.Cells.Font.Size := 12;
      xlSheet.Cells.VerticalAlignment := 2;
      //xlSheet.Cells.RowHeight := 17.25;
      xlSheet.Range['C:D'].HorizontalAlignment := xlCenter;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 2);


      //输出标题内容
      xlSheet.Range['A1'].Value := '票号领取统计表';
      xlSheet.Range['A1:H1'].HorizontalAlignment := 7;
      xlSheet.Range['1:1'].Font.Size := 13;
      xlSheet.Range['1:1'].Font.Bold := true;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 3);

      if AStartDay = AEndDay then
      begin
        xlSheet.Range['G2'].Value := '日期: ' + DatetoStr(AStartDay);
        xlSheet.Range['G2:H2'].HorizontalAlignment := 7;
      end
      else
      begin
        xlSheet.Range['F2'].Value := '日期:从' + DateToStr(AStartDay) + '到' + DateToStr(AEndDay);
        xlSheet.Range['F2:H2'].HorizontalAlignment := 7;
      end;
      xlSheet.Range['A3'].Value := '代理人姓名' ;
      xlSheet.Range['A3:B3'].HorizontalAlignment := 7;
      xlSheet.Range['C3'].Value := '代理人帐号' ;
      xlSheet.Range['C3:D3'].HorizontalAlignment := 7;
      xlSheet.Range['E3'].Value := '领取总票数' ;
      xlSheet.Range['E3:F3'].HorizontalAlignment := 7;
      xlSheet.Range['G3'].Value := '所占百分比' ;
      xlSheet.Range['G3:H3'].HorizontalAlignment := 7;
      iNextRow := 4;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 4);

      //查询结果, 导到EXCEL
      for I := 0 to oAgentList.Count-1 do
      begin
        sSqlStr := 'SELECT USERNAME FROM SITE_USER WHERE ID = ' + oAgentList.Strings[I];
        if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
          sUserName := DMSystem.Qry_Temp.FieldByName('USERNAME').AsString;

        xlSheet.Range[Format('A%d', [iNextRow])].Value := sUserName;
        xlSheet.Range[Format('A%d:B%0:d', [iNextRow])].HorizontalAlignment := 7;

        sSqlStr := 'SELECT SUM(TICKETCOUNT) AS ALLTOTALCOUNT FROM TICKET_NUMBER_HISTORY WHERE AGENTID <> ' +
                   IntToStr(COMPANYAGENTID) + sDateSqlStr;

        if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
          AllAgentTotalCount := DMSystem.Qry_Temp.FieldByName('ALLTOTALCOUNT').AsInteger;


        sSqlStr := 'SELECT SUM(TICKETCOUNT) AS AGENTTOTALCOUNT FROM TICKET_NUMBER_HISTORY ' +
                   'WHERE AGENTID = ' + oAgentList.Strings[I] + sDateSqlStr;
        if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
        with DMSystem.Qry_Temp do
        begin
          xlSheet.Range[Format('C%d', [iNextRow])].Value := oAgentList.Strings[I];
          xlSheet.Range[Format('C%d:D%0:d', [iNextRow])].HorizontalAlignment := 7;

          AgentTotalCount := FieldByName('AGENTTOTALCOUNT').AsInteger;
          xlSheet.Range[Format('E%d', [iNextRow])].Value := AgentTotalCount;
          xlSheet.Range[Format('E%d:F%0:d', [iNextRow])].HorizontalAlignment := 7;

          if AllAgentTotalCount <> 0 then fPercentage := AgentTotalCount/AllAgentTotalCount * 100
          else fPercentage := 0;
          xlSheet.Range[Format('G%d', [iNextRow])].Value := Format('%0.2f', [fPercentage]) + '%';
          xlSheet.Range[Format('G%d:H%0:d', [iNextRow])].HorizontalAlignment := 7;
          Inc(iNextRow);
        end;
        SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 5 + I);
        Application.ProcessMessages;
      end;

      //合计
      xlSheet.Range[Format('A%d', [iNextRow])].Value := '  ';
      xlSheet.Range[Format('A%d:B%0:d', [iNextRow])].HorizontalAlignment := 7;
      xlSheet.Range[Format('C%d', [iNextRow])].Value := '人数:' + IntToStr(oAgentList.Count) + '(人)';
      xlSheet.Range[Format('C%d:D%0:d', [iNextRow])].HorizontalAlignment := 7;
      xlSheet.Range[Format('E%d', [iNextRow])].Value := '合计:' + IntToStr(AllAgentTotalCount) + '(票)';
      xlSheet.Range[Format('E%d:F%0:d', [iNextRow])].HorizontalAlignment := 7;
      xlSheet.Range[Format('G%d', [iNextRow])].Value := '  ';
      xlSheet.Range[Format('G%d:H%0:d', [iNextRow])].HorizontalAlignment := 7;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 6 + oAgentList.Count);

      //设置边框
      xlSheet.Range[Format('A3:H%0:d', [iNextRow])].Borders.LineStyle := xlContinuous;
      xlSheet.Cells.EntireColumn.AutoFit;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 7 + oAgentList.Count);

      //设置页脚
      xlSheet.PageSetup.LeftFooter := '制表：' + DMSystem.WorkName;
      xlSheet.PageSetup.CenterFooter := '审核：';
      xlSheet.PageSetup.RightFooter := '第 &P 页，共 &N 页';
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 8 + oAgentList.Count);
    except
      if not VarIsNull(xlApp) then
      begin
        xlApp.Quit;
        xlApp.Disconnect;
        xlApp := Unassigned;
        xlApp := NULL;
      end;
      Exit;
    end;
  finally
    oAgentList.Free;
    Screen.Cursor := crDefault;
    xlSheet := Unassigned;
    xlBook := Unassigned;
    if not VarIsNull(xlApp) then
    begin
      xlApp.Visible := true;
      xlApp := Unassigned;
    end;
    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_FINISHED_UPDATE, 0);
  end;
end;

procedure TFormStatics.ExportStoreTicketReportToExcel(AStartDay,
  AendDay: TDate);
var
  xlApp, xlBook, xlSheet, xlQuery: Variant;
  sSqlStr, sDateSqlStr: String;
  i, iNextRow, TicketCount ,TotalCount, nRecordCount, nAgentId: Integer;
  fPercentage: Double;
begin
  nRecordCount := GetStoreTicketRecordCount(AStartDay, AEndDay);
  if nRecordCount = 0 then
  begin
    ShowMessage('无记录，不做统计！');
    Exit;
  end;

  try
    sDateSqlStr := ' OPERATEDATE >=' + #39 + DateToStr(AStartDay) + #39 + ' AND ' +
                   'OPERATEDATE <=' + #39 + DateToStr(AEndDay) + #39;
    TicketCount := 0;
    TotalCount := 0;

    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_START_UPDATE, nRecordCount + 8);
    Screen.Cursor := crHourGlass;
    try
      //建立OLE对象
      xlApp := CreateOleObject('Excel.Application');
      xlBook := xlApp.Workbooks.Add;
      xlSheet := xlBook.Worksheets['sheet1'];
      xlApp.Visible := false;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 1);

      //定义Excel格式
      xlSheet.Cells.Font.Name := '宋体';
      xlSheet.Cells.Font.Size := 12;
      xlSheet.Cells.VerticalAlignment := 2;
      //xlSheet.Cells.RowHeight := 17.25;
      xlSheet.Range['B:C'].HorizontalAlignment := xlCenter;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 2);


      //输出标题内容
      xlSheet.Range['A1'].Value := '票号入库统计表';
      xlSheet.Range['A1:D1'].HorizontalAlignment := 7;
      xlSheet.Range['1:1'].Font.Size := 13;
      xlSheet.Range['1:1'].Font.Bold := true;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 3);

      if AStartDay = AEndDay then
      begin
        xlSheet.Range['C2'].Value := '日期: ' + DatetoStr(AStartDay);
        xlSheet.Range['C2:D2'].HorizontalAlignment := 7;
      end
      else
      begin
        xlSheet.Range['C2'].Value := '日期:从' + DateToStr(AStartDay) + '到' + DateToStr(AEndDay);
        xlSheet.Range['C2:D2'].HorizontalAlignment := 7;
      end;
      xlSheet.Range['A3'].Value := '起始票号' ;
      xlSheet.Range['B3'].Value := '入库数量' ;
      xlSheet.Range['C3'].Value := '入库时间' ;
      xlSheet.Range['D3'].Value := '所占百分比' ;
      iNextRow := 4;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 4);

      //查询结果, 导到EXCEL
      sSqlStr := 'SELECT SUM(TICKETCOUNT) AS ALLTOTALCOUNT FROM TICKET_NUMBER_HISTORY WHERE AGENTID= ' +
                 InttoStr(COMPANYAGENTID) + ' AND ' + sDateSqlStr;
      if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
        TotalCount := DMSystem.Qry_Temp.FieldByName('ALLTOTALCOUNT').AsInteger;


      sSqlStr := 'SELECT STARTPRINTCODE, TICKETCOUNT, OPERATEDATE FROM TICKET_NUMBER_HISTORY WHERE AGENTID= ' +
                 InttoStr(COMPANYAGENTID) + ' AND ' + sDateSqlStr;
      if DMSystem.SQL_Querys(DMSystem.Qry_Temp, sSqlStr) then
      with DMSystem.Qry_Temp do
      try
        First;
        while not Eof do
        begin
          TicketCount := FieldByName('TICKETCOUNT').AsInteger;
          xlSheet.Range[Format('A%d', [iNextRow])].Value := FieldByName('STARTPRINTCODE').AsString;
          xlSheet.Range[Format('B%d', [iNextRow])].Value := IntToStr(TicketCount);
          xlSheet.Range[Format('C%d', [iNextRow])].Value := FieldByName('OPERATEDATE').AsString;

          if TotalCount <> 0 then fPercentage := TicketCount/TotalCount * 100
          else fPercentage := 0;
          xlSheet.Range[Format('D%d', [iNextRow])].Value := Format('%0.2f', [fPercentage]) + '%';


          Inc(iNextRow);
          SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 5 + I);
          Next;
          Application.ProcessMessages;
        end;
      finally
      end;

      //合计
      xlSheet.Range[Format('B%d', [iNextRow])].Value := '合计:' + IntToStr(TotalCount) + '(票)';
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 6 + nRecordCount);

      //设置边框
      xlSheet.Range[Format('A3:D%0:d', [iNextRow])].Borders.LineStyle := xlContinuous;
      xlSheet.Cells.EntireColumn.AutoFit;
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 7 + nRecordCount);

      //设置页脚
      xlSheet.PageSetup.LeftFooter := '制表：' + DMSystem.WorkName;
      xlSheet.PageSetup.CenterFooter := '审核：';
      xlSheet.PageSetup.RightFooter := '第 &P 页，共 &N 页';
      SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_PLAYING_UPDATE, 8 + nRecordCount);
    except
      if not VarIsNull(xlApp) then
      begin
        xlApp.Quit;
        xlApp.Disconnect;
        xlApp := Unassigned;
        xlApp := NULL;
      end;
      Exit;
    end;
  finally
    Screen.Cursor := crDefault;
    xlSheet := Unassigned;
    xlBook := Unassigned;
    if not VarIsNull(xlApp) then
    begin
      xlApp.Visible := true;
      xlApp := Unassigned;
    end;
    SendMessage(Application.MainForm.Handle, WM_STATUS_MSG, DEF_FINISHED_UPDATE, 0);
  end;
end;

procedure TFormStatics.WMSETCURSTATICS(var message: TMessage);
const
  cTag = 2001;
var
  nIndex: Integer;
begin
  nIndex := message.WParam - cTag;
  SetCurStatics(tvStatics.TopItem.Item[nIndex]);
end;

procedure TFormStatics.SetCurStatics(Node: TTreeNode);
const
  cApp = 1;
  cApplyIndex = 0;
  cLQTicketIndex = 1;
  cStoreTicketIndex = 2;
begin
  if Node.Level = 1 then
  begin
    case Node.Index of
      cApplyIndex: FCurReportType := rtApply;
      cLQTicketIndex: FCurReportType := rtLQTicket;
      cStoreTicketIndex: FCurReportType := rtStoreTicket;
    end;
    pnlTitle.Caption := Node.Text;
    btnStatics.Enabled := True;
  end;
end;

end.