u_public.~pas

用delphi操作Excel

unit u_public;

interface

uses windows, SysUtils, Classes, DB, ADODB, ComObj, Forms, Dialogs, Comctrls,
      Variants;

implementation

function XlsComToRow(OpnDlg: TOpenDialog; ProgrssBar: TProgressBar):Boolean;
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.Name;
    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;

end.