dll.dpr

用delphi操作Excel

library DLL;

{ Important note about DLL memory management: ShareMem must be the
  first unit in your library's USES clause AND your project's (select
  Project-View Source) USES clause if your DLL exports any procedures or
  functions that pass strings as parameters or function results. This
  applies to all strings passed to and from your DLL--even those that
  are nested in records and classes. ShareMem is the interface unit to
  the BORLNDMM.DLL shared memory manager, which must be deployed along
  with your DLL. To avoid using BORLNDMM.DLL, pass string information
  using PChar or ShortString parameters. }

uses
  SysUtils,
  Classes,
  windows,
  DB,
  ADODB,
  ComObj,
  Forms,
  Dialogs,
  Comctrls,
  Variants,
  u_public in 'u_public.pas';

{$R *.res}

function XlsComToRow(OpnDlg: TOpenDialog; ProgrssBar: TProgressBar):Boolean;stdcall;
const
    BeginRow = 9; BeginCol = 7;
var
    ExlApp: Variant;
    ExlFileName: string;
    iRow, iCol, iiRow, i: Integer;
begin
    OpnDlg.Filter := 'Excel(*.xls) ? *.xls';
    if OpnDlg.Execute then
        ExlFileName := OpnDlg.FileName;
    try
        ExlApp := CreateOleObject('Excel.Application');
        try
            ExlApp.WorkBooks.Open(ExlFileName);
            ExlApp.WorkBooks[1].WorkSheets.Add;
            ExlApp.WorkBooks[1].WorkSheets[1].Cells[1, 1].Value := '货号';
            ExlApp.WorkBooks[1].WorkSheets[1].Cells[1, 2].Value := '颜色编码';
            ExlApp.WorkBooks[1].WorkSheets[1].Cells[1, 3].Value := '内长';
            ExlApp.WorkBooks[1].WorkSheets[1].Cells[1, 4].Value := '尺码';
            ExlApp.WorkBooks[1].WorkSheets[1].Cells[1, 5].Value := '数量';
            try
                iiRow := 2;
                ProgrssBar.Position := 0;
                for iRow := BeginRow to ExlApp.WorkSheets[2].UsedRange.Rows.Count - 1 do
                begin
                  for iCol := BeginCol to ExlApp.WorkSheets[2].UsedRange.Columns.Count - 1 do
                  begin
                    if trim(ExlApp.WorkSheets[2].Cells[iRow, iCol].value) <> '' then
                    begin
                      ExlApp.WorkSheets[1].Cells[iiRow, 1].value := trim(ExlApp.WorkSheets[2].Cells[iRow, 3].value);  //货号
                      ExlApp.WorkSheets[1].Cells[iiRow, 2].value := trim(ExlApp.WorkSheets[2].Cells[iRow, 4].value);  //颜色编号
                      ExlApp.WorkSheets[1].Cells[iiRow, 3].value := trim(ExlApp.WorkSheets[2].Cells[iRow, 5].value);  //内长
                      for i:=3 to 7 do
                      begin
                        if trim(ExlApp.WorkSheets[2].Cells[i, 6].value) = trim(ExlApp.WorkSheets[2].Cells[iRow, 6].value) then
                          ExlApp.WorkSheets[1].Cells[iiRow, 4].value := trim(ExlApp.WorkSheets[2].Cells[i, iCol].value);  //尺码
                      end;
                      ExlApp.WorkSheets[1].Cells[iiRow, 5].value := trim(ExlApp.WorkSheets[2].Cells[iRow, iCol].value);  //数量
                      inc(iiRow);
                    end;
                  end;
                  ProgrssBar.Position := ProgrssBar.Position + 1;
                end;
                Application.MessageBox('数据转换成功！', '提示', mb_ok+mb_iconinformation);
            except
                Application.MessageBox('数据转换失败！', '提示', mb_ok+mb_iconinformation);
            end;
            ExlApp.WorkBooks[1].Save;
            ExlApp.WorkBooks[1].Close;
            ExlApp.Quit;
            ExlApp := Unassigned;
        except
            ShowMessage('不能正确操作Excel文件');
            ExlApp.WorkBooks.Close;
            ExlApp.Quit;
            ExlApp := Unassigned;
        end;
    except
        Application.MessageBox('Excel没有安装！', '提示', mb_ok+mb_iconinformation);
    end;
    Result := true;
end;

Exports
  XlsComToRow name 'XlsComToRow';

begin
end.