printselectunit.pas

公交行业的管理系统

unit PrintSelectUnit;

interface

uses 
  Windows, Messages, SysUtils, Classes, Graphics, Forms, Dialogs, Controls, StdCtrls, 
  Buttons,ComObj, DB, ADODB, ComCtrls;

type

  TPrintFieldSelectDlg = class(TForm)
    SrcList: TListBox;
    DstList: TListBox;
    SrcLabel: TLabel;
    DstLabel: TLabel;
    IncludeBtn: TSpeedButton;
    IncAllBtn: TSpeedButton;
    ExcludeBtn: TSpeedButton;
    ExAllBtn: TSpeedButton;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    Edit2: TEdit;
    Label3: TLabel;
    DateTimePicker1: TDateTimePicker;
    Label4: TLabel;
    SrcListView: TListView;
    procedure IncludeBtnClick(Sender: TObject);
    procedure ExcludeBtnClick(Sender: TObject);
    procedure IncAllBtnClick(Sender: TObject);
    procedure ExcAllBtnClick(Sender: TObject);
  private
    ExcelApp,ExcelWorkSheet:variant;
  public
    { Public declarations }
    procedure MoveSelected(List: TCustomListBox; Items: TStrings);
    procedure SetItem(List: TListBox; Index: Integer);
    function GetFirstSelection(List: TCustomListBox): Integer;
    procedure SetButtons;
    procedure InitPrnSrc(curQuery:TADOQuery);
    procedure InitPrnSrc2(curQuery:TADOQuery;nextQuery:TADOTable);
    procedure PrnSrc(curQuery:TADOQuery;XLSName:string);
    procedure ToExcel(curQuery:TADOQuery;XLSName:string);
  end;

var
  PrintFieldSelectDlg: TPrintFieldSelectDlg;

implementation
uses BusTradeDataModuleUnit, PrintInfoUnit, PrintProc;
{$R *.dfm}
procedure TPrintFieldSelectDlg.ToExcel(curQuery:TADOQuery;XLSName:string);
var
i,j,k:integer;
ExcelApp,ExcelWorkSheet:variant;
Range:variant;
begin
      ExcelApp:=CreateOleObject('excel.application');
      ExcelApp.Workbooks.Open(ExtractFilePath(Application.ExeName)+XLSName);
      ExcelWorkSheet:=ExcelApp.workbooks[1].worksheets[1];
      ExcelWorkSheet.activate;
      ExcelApp.Visible:=false;
      if srclist.Visible=true then
      begin

      for i:=0 to PrintFieldSelectDlg.DstList.Count-1 do
      begin
           ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
           ExcelWorkSheet.Columns[i+1].Font.Size := 12;
           ExcelWorkSheet.Columns[i+1].HorizontalAlignment := 4;
           ExcelWorkSheet.Columns[i+1].AutoFit;
           Range:=ExcelWorkSheet.Cells[1,i+1];
           Range.Borders[1].LineStyle := 7;
           Range.Borders[2].LineStyle := 7;
           Range.Borders[3].LineStyle := 7;
           Range.Borders[4].LineStyle := 7;
           Range.Characters.Font.Bold := True;
      end;
      curQuery.First;
      j:=2;
      while not curQuery.Eof  do
      begin
           for i:=0 to DstList.Count-1 do
           begin
                  ExcelWorkSheet.cells(j,i+1):=curQuery.FieldByName(PrintFieldSelectDlg.DstList.Items[i]).AsString;
                  Range:=ExcelWorkSheet.Cells[j,i+1];
                  Range.Borders[1].LineStyle := 7;
                  Range.Borders[2].LineStyle := 7;
                  Range.Borders[3].LineStyle := 7;
                  Range.Borders[4].LineStyle := 7;
           end;
           j:=j+1;
           curQuery.Next;
      end;

       for i:=0 to PrintFieldSelectDlg.DstList.Count-1 do
       ExcelWorkSheet.Columns[i+1].AutoFit;

      //////////////////////////////新添代码/////////////////////////////
      end
      else
      begin
     // for i:=0 to PrintFieldSelectDlg.SrcListView.Items.Count-1 do
        for i:=0 to DstList.Count-1 do
        begin
           for k:=0 to SrcListView.Items.Count-1 do
           begin
              if srclistview.Items.Item[k].Caption=DstList.Items[i] then
              begin
                   if trim(srclistview.Items.Item[k].SubItems.Text)='***' then
                   begin
                       ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
                   end
                   else
                   begin
                      ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[k].SubItems.Text);
                   end;
                  // ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[k].SubItems.Text);
              end;
           end;
             {  begin
                   ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
               end
               else
               begin
                  ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[i].SubItems.Text);
               end; }
           ExcelWorkSheet.Columns[i+1].Font.Size := 12;
           ExcelWorkSheet.Columns[i+1].HorizontalAlignment := 4;
           ExcelWorkSheet.Columns[i+1].AutoFit;
           Range:=ExcelWorkSheet.Cells[1,i+1];
           Range.Borders[1].LineStyle := 7;
           Range.Borders[2].LineStyle := 7;
           Range.Borders[3].LineStyle := 7;
           Range.Borders[4].LineStyle := 7;
           Range.Characters.Font.Bold := True;
      end;
      curQuery.First;
      j:=2;
      while not curQuery.Eof  do
      begin
           for i:=0 to DstList.Count-1 do
           begin
                  ExcelWorkSheet.cells(j,i+1):=converterpercent(curQuery.FieldByName(PrintFieldSelectDlg.DstList.Items[i]).AsString);
              //    ExcelWorkSheet.cells(j,i+1):=curQuery.FieldByName(PrintFieldSelectDlg.SrcListView.Items.Item[i].SubItems).AsString;
                  Range:=ExcelWorkSheet.Cells[j,i+1];
                  Range.Borders[1].LineStyle := 7;
                  Range.Borders[2].LineStyle := 7;
                  Range.Borders[3].LineStyle := 7;
                  Range.Borders[4].LineStyle := 7;
           end;
           j:=j+1;
           curQuery.Next;
      end;

       for i:=0 to PrintFieldSelectDlg.DstList.Count-1 do
       ExcelWorkSheet.Columns[i+1].AutoFit;
      //////////////////////////////新添代码end//////////////////////////
      end;
      ExcelApp.Visible:=true;
      ExcelWorkSheet.PageSetup.LeftFooter :='统计负责人：'+Edit1.Text;
      ExcelWorkSheet.PageSetup.CenterFooter :='统计人员：'+Edit2.Text ;
      ExcelWorkSheet.PageSetup.RightFooter :='填表日期：'+DateToStr(DateTimePicker1.Date);
      ExcelApp.activeworkbook.saved:=true;

end;
procedure TPrintFieldSelectDlg.PrnSrc(curQuery:TADOQuery;XLSName:string);
var
i,j,k:integer;
ExcelApp,ExcelWorkSheet:variant;
Range:variant;
begin
      ExcelApp:=CreateOleObject('excel.application');
      ExcelApp.Workbooks.Open(ExtractFilePath(Application.ExeName)+XLSName);
      ExcelWorkSheet:=ExcelApp.workbooks[1].worksheets[1];
      ExcelWorkSheet.activate;
      ExcelApp.Visible:=false;
      if srclist.Visible=false then
      begin
      //////////////////////////////新添代码/////////////////////////////
     // for i:=0 to PrintFieldSelectDlg.SrcListView.Items.Count-1 do
        for i:=0 to DstList.Count-1 do
        begin
           for k:=0 to SrcListView.Items.Count-1 do
           begin
              if srclistview.Items.Item[k].Caption=DstList.Items[i] then
              begin
                   if trim(srclistview.Items.Item[k].SubItems.Text)='***' then
                   begin
                       ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
                   end
                   else
                   begin
                      ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[k].SubItems.Text);
                   end;
                  // ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[k].SubItems.Text);
              end;
           end;
             {  begin
                   ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
               end
               else
               begin
                  ExcelWorkSheet.cells(1,i+1):=trim(srclistview.Items.Item[i].SubItems.Text);
               end; }
           ExcelWorkSheet.Columns[i+1].Font.Size := 12;
           ExcelWorkSheet.Columns[i+1].HorizontalAlignment := 4;
           ExcelWorkSheet.Columns[i+1].AutoFit;
           Range:=ExcelWorkSheet.Cells[1,i+1];
           Range.Borders[1].LineStyle := 7;
           Range.Borders[2].LineStyle := 7;
           Range.Borders[3].LineStyle := 7;
           Range.Borders[4].LineStyle := 7;
           Range.Characters.Font.Bold := True;
      end;
      curQuery.First;
      j:=2;
      while not curQuery.Eof  do
      begin
           for i:=0 to DstList.Count-1 do
           begin
                  ExcelWorkSheet.cells(j,i+1):=converterpercent(curQuery.FieldByName(PrintFieldSelectDlg.DstList.Items[i]).AsString);
              //    ExcelWorkSheet.cells(j,i+1):=curQuery.FieldByName(PrintFieldSelectDlg.SrcListView.Items.Item[i].SubItems).AsString;
                  Range:=ExcelWorkSheet.Cells[j,i+1];
                  Range.Borders[1].LineStyle := 7;
                  Range.Borders[2].LineStyle := 7;
                  Range.Borders[3].LineStyle := 7;
                  Range.Borders[4].LineStyle := 7;
           end;
           j:=j+1;
           curQuery.Next;
      end;

       for i:=0 to PrintFieldSelectDlg.DstList.Count-1 do
       ExcelWorkSheet.Columns[i+1].AutoFit;
      //////////////////////////////新添代码end//////////////////////////
      end
      else
      begin

      for i:=0 to PrintFieldSelectDlg.DstList.Count-1 do
      begin
           ExcelWorkSheet.cells(1,i+1):=DstList.Items[i];
           ExcelWorkSheet.Columns[i+1].Font.Size := 12;
           ExcelWorkSheet.Columns[i+1].HorizontalAlignment := 4;
           ExcelWorkSheet.Columns[i+1].AutoFit;
           Range:=ExcelWorkSheet.Cells[1,i+1];
           Range.Borders[1].LineStyle := 7;
           Range.Borders[2].LineStyle := 7;
           Range.Borders[3].LineStyle := 7;
           Range.Borders[4].LineStyle := 7;
           Range.Characters.Font.Bold := True;
      end;
      curQuery.First;
      j:=2;
      while not curQuery.Eof  do