Vorhandene Excelliste bearbeiten

[Lill Jens]

Delphi-Quellcode:

procedure TFMain.Excellfuellen(Sender: TObject);
var Workbook, Worksheet, Filename, Excel: OleVariant;
    OpenDialog:TOpenDialog;
begin
  OpenDialog:=TOpenDialog.Create(Self);
  try
    OpenDialog.InitialDir:=ExtractFilePath(Application.ExeName);
    OpenDialog.Filter:='xls|*.xls';
    if OpenDialog.Execute then begin
      try
        Excel := CreateOleObject('Excel.Application');
        Excel.Visible := true;   { für die testphase sinnvoll }

        Filename:=OpenDialog.FileName;
        Workbook:=Excel.Workbooks.Open(filename,
                                       emptyParam, emptyParam, emptyParam,
                                       emptyParam, emptyParam, emptyParam,
                                       emptyParam, emptyParam, emptyParam,
                                       emptyParam, emptyParam, emptyParam);
        // Excel.Workbooks.Add;
        Excel.ActiveWorkBook.Saved := True;   // ... verhindert unliebsame Dialoge
        //Workbook.Worksheet.UsedRange[LCID].Rows.Count;


      except
        ShowMessage('Excel konnte nicht gestartet werden !');
      end;
    end
  finally
    OpenDialog.free;
  end;
end;

Steig noch nicht so ganz durch hab mein excel sonst immer anders angesprochen:

Delphi-Quellcode:

procedure TFMain.DBGridToExcel(DBGrid:TDBGrid; StartSpalte, StartZeile:integer);
type TSpalten = array[1..256] of string;
// Funktion für das exportieren der Datensätze in Excel
  function CreateSpalten:TSpalten;
  var i, j, x:integer;
      abbruch:boolean;
  begin
    x:=1;
    abbruch:=false;
    j:=0;
    while (j <= 26) and not abbruch do begin
      i:=1;
      while (i <= 26) and not abbruch do begin
        if j = 0 then
          Result[x]:=chr(i+64)
        else
          Result[x]:=chr(j+64)+chr(i+64);
        inc(i);
        inc(x);
        abbruch:=(x > 256);
      end;
      inc(j);
    end;
  end;

var Excel:TExcelApplication;
    i, Zeile, lcid:integer;
    Workbook:_Workbook;
    Sheet, Zelle, Inhalt:Variant;
    Spalten:TSpalten;
begin
  if     assigned(DBGrid)
     and assigned(DBGrid.DataSource)
     and assigned(DBGrid.DataSource.DataSet) then
  begin
    if DBGrid.DataSource.DataSet.Active then begin
      Excel:=TExcelApplication.Create(nil);
      try
        lcid:=GetUserDefaultLCID;
        Excel.Connect;
        Excel.Visible[lcid]:=true;
        Excel.UserControl:=true;

        Workbook:=Excel.Workbooks.Add(EmptyParam, lcid);

        Spalten:=CreateSpalten;
        Zeile:=StartZeile;
        for i:=43 to DBGrid.FieldCount do begin
          Inhalt:=DBGrid.Fields[i-1].DisplayName;
          Zelle:=Excel.Cells.Range[
            Spalten[i+StartSpalte-1]+inttostr(Zeile),
            Spalten[i+StartSpalte-1]+inttostr(Zeile)
          ];
          Zelle.Value:=Inhalt;
          Zelle.Font.Bold:=true;
        end;

        DBGrid.DataSource.DataSet.First;
        while not DBGrid.DataSource.DataSet.Eof do begin
          inc(Zeile);
          for i:=1 to DBGrid.FieldCount do begin
            Inhalt:=DBGrid.DataSource.DataSet.FieldByName(
              DBGrid.Fields[i-1].FieldName
            ).AsString;
            Zelle:=Excel.Cells.Range[
              Spalten[i+StartSpalte-1]+inttostr(Zeile),
              Spalten[i+StartSpalte-1]+inttostr(Zeile)
            ];
            Zelle.Value:=Inhalt;
          end;
          DBGrid.DataSource.DataSet.Next;
        end;

        Sheet:=Workbook.ActiveSheet;
        Sheet.Columns[
          Spalten[StartSpalte]+':'+Spalten[StartSpalte+DBGrid.FieldCount]
        ].EntireColumn.AutoFit;

      finally
        Excel.Disconnect;
        Excel.free;
      end;
    end;
  end;
end;