Delphi对Excel的操作

    Delphi将数据存到Excel中。这里是将数据追加到excel文件的末尾。

//保存数据到文件最后
//path：文件所在路径
//v：待保存的数据
//sheetindex
function SaveToExcel(path:string;v:Variant;sheetindex:Integer=1):Boolean;
var
i:Integer;
index:Integer;
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
stmp:string;
sr:TSearchRec;
itmp:Integer;
begin
  Result := True;
  try
    MsExcel := CreateOleObject('Excel.Application');
    try
      //判断是否存在该文件，不存在就创建
      if(FindFirst(path,faanyfile,sr)=0) then
      begin
        MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      end
      else
      begin
        MsExcelWorkBook := CreateOleObject('Excel.Sheet');
        MsExcel.DisplayAlerts:=false;
        MsExcelWorkBook.SaveAS(path);
        MsExcel.Quit;
        MsExcel := Unassigned;

        MsExcel := CreateOleObject('Excel.Application');
        MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      end;
      try
        MsExcelWorkSheet := MsExcel.Worksheets.Item[sheetindex];
        index := MsExcelWorkSheet.Range['A1'].CurrentRegion.rows.count;

        i := 1;
        while i <= VarArrayHighBound(v, 2) do
        begin
          stmp := v[1,i];
          MsExcelWorkSheet.Cells.item[index+1,i] := v[1,i];
          inc(i);
        end;
        MsExcel.DisplayAlerts:=false;
        MsExcel.ActiveWorkBook.Save;
      finally
        MsExcel.WorkBooks.Close;
      end;
    finally
      MsExcel.Quit;
      MsExcel := Unassigned;
    end;
  except
  begin
    showmessage('写入文件失败！');
  end;
  end;
end;

删除一行数据：先找到对应数据对应的行号：

//删除某一行的数据
//path：文件所在路径
//SData:查找的数据
//findCOl：在哪一列查找数据
function DeleteExcelRow(path:string;SData:Variant;findCol:Integer):Boolean;
var
i:Integer;
roucount:Integer;
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
v:Variant;
begin
  Result := false;
  try
    MsExcel := CreateOleObject('Excel.Application');
    try
      MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      try
        MsExcelWorkSheet := MsExcel.Worksheets.Item[1];
        roucount := MsExcelWorkSheet.Range['A1'].CurrentRegion.rows.count;
        v := varArrayCreate([1, 1000, 1, 7], varVariant);
        v := MsExcelWorkSheet.Range['A1:G1000'].Value;
        for i := 1 to roucount do
        begin
          if v[i,findCol] = SData then
          begin
            MsExcel.ActiveSheet.Rows[i].Delete;
            break;
          end;
        end;
        MsExcel.DisplayAlerts:=false;
        MsExcel.ActiveWorkBook.Save;
      finally
        MsExcel.WorkBooks.Close;
      end;
    finally
      MsExcel.Quit;
      MsExcel := Unassigned;
    end;
  except
    begin
      showmessage('打开文件失败！');  
    end;
  end;
  Result := True;
end;

获取excel表名，存于variant变量数组V中。

//获取所有表名
//path参数是excel文件路径
//v是out的，输出型变量，获取到的表名存于v
procedure GetExcelSheetName(path:string;out v:Variant);
var
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
i:Integer;
begin
  v := varArrayCreate([1, 50, 0, 1], varVariant);
  try
    MsExcel := CreateOleObject('Excel.Application');
    try
      MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      try
        for i := 1 to MsExcel.Sheets.Count do
        begin
          v[i,0] := MsExcel.Sheets[i].Name;
        end;
      finally
        MsExcel.WorkBooks.Close;
      end;
    finally
      MsExcel.Quit;
      MsExcel := Unassigned;
    end;
  except
    begin
      showmessage('打开文件失败!');
    end;
  end;
end;

读取Excel内容：

//读取excel文件，得到数据v，最大行数rowcount
//path：文件所在路径
//v：文件内数据
//rowcount:文件最大行数
//sheetindex:读取的文件sheet序号
function ReadFromExcel(path:string;out v:Variant;out rowcount:Integer;sheetindex:Integer=1):Boolean;
var
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
begin
  Result := false;
  try
    MsExcel := CreateOleObject('Excel.Application');
    try
      MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      try
        MsExcelWorkSheet := MsExcel.Worksheets.Item[sheetindex];
        rowcount := MsExcelWorkSheet.Range['A1'].CurrentRegion.rows.count;
        v := varArrayCreate([1, 1000, 1, 7], varVariant);
        v := MsExcelWorkSheet.Range['A1:G1000'].Value;
      finally
        MsExcel.WorkBooks.Close;
      end;
    finally
      MsExcel.Quit;
      MsExcel := Unassigned;
    end;
  except
    begin
      showmessage('打开文件失败！');
    end;
  end;
  Result := True;
end;

删除一个表单：

//删除某个表单
//path:excel文件路径
//RecipeName：表单名
function DeleteRecipe(path:string;RecipeName:string):Boolean;
var
i:Integer;
index:Integer;
MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
begin
  Result:=False;
  try
    MsExcel := CreateOleObject('Excel.Application');
    try
      MsExcelWorkBook := MsExcel.Workbooks.Open (path);
      try
        //先判断当前是否已存在该配方
        for i := 1 to MsExcel.Sheets.Count do
        begin
          if (RecipeName = MsExcel.Sheets[i].Name) then
          begin
            MsExcel.DisplayAlerts:=false;
            MsExcel.Sheets[i].delete;
            Result := True;
            break;
          end;
        end;

        MsExcel.DisplayAlerts:=false;
        MsExcel.ActiveWorkBook.Save;

    finally
        MsExcel.WorkBooks.Close;
      end;
    finally
      MsExcel.Quit;
      MsExcel := Unassigned;
    end;
  except
    showmessage('删除失败！');
  end;
end;

delphi中的数组的行和列有些容易弄混。。。。