Excelのセルを読み込む方法について教えて下さい。

パラッゾ 2008-02-07 06:07:33 No: 29691

ServersのExcelを使ってExcelファイル内のセルを読み込む方法を教えて下さい。DelphiのバージョンはDelphi6Proです。宜しくお願いします。

KHE00221 2008-02-07 19:38:53 No: 29692

Serversなんて使ったこと無いけど調べてみたよｗ

こんな感じかな？

BDS2006 + XP + XP Office

StringGrid の Cols 、 Rows , ColWidths RowHeights もエクセル側に合わせます

function ExcelToStringGrid(FileName:String;StringGrid:TStringGrid):Boolean;
var
    Excel: Variant;
    WorkBook: Variant;
    WorkSheet: Variant;
    Col,Row : Integer;
begin
    Result := False;
    Excel := CreateOleObject('Excel.Application');
    try
      WorkBook := Excel.Workbooks.Add;
      try
        WorkBook :=Excel.WorkBooks.Open(FileName := FileName , readOnly := False);
        WorkSheet := WorkBook.WorkSheets[1];

StringGrid.ColCount := WorkSheet.UsedRange.Columns.Count+1;
StringGrid.RowCount := WorkSheet.UsedRange.Rows.Count;

        for Col := 1 to WorkSheet.UsedRange.Columns.Count do
        begin
          StringGrid.ColWidths[Col]  := WorkSheet.UsedRange.Columns[Col].Width;
          for Row := 1 to WorkSheet.UsedRange.Rows.Count do
          begin
            StringGrid.Cells[Col,Row] := WorkSheet.Cells[Row,Col].Value;
         end;
        end;

        for Row := 1 to WorkSheet.UsedRange.Rows.Count-1 do
        begin
          StringGrid.RowHeights[Row]  := WorkSheet.UsedRange.Rows[Row].Height;
        end;

Result := True;

      finally
        WorkBook.close;
        WorkBook:=unAssigned;
        WorkSheet:=unAssigned;
      end;

    finally
      Excel.Quit;
      Excel:=unAssigned;
    end;

end;

KHE00221 2008-02-07 19:51:58 No: 29693

Servers使ってないねｗ

パラッゾ 2008-02-07 20:10:11 No: 29694

KHE00221さん有難う御座います、参考にしてみます。

KHE00221 2008-02-07 21:21:06 No: 29695

修正

StringGrid.RowCount := WorkSheet.UsedRange.Rows.Count;

↓

StringGrid.RowCount := WorkSheet.UsedRange.Rows.Count+1;

KHE00221 2008-02-07 21:52:39 No: 29696

クリップボードを使用した高速版

procedure ExcelToStringGrid2(const FileName:String;StringGrid:TStringGrid);
var
    Excel: Variant;
    WorkBook: Variant;
    WorkSheet: Variant;
    Col,Row : Integer;
    ClipBoard : TClipBoard;
    StringList : TStringList;
    I,J1,J2,J3,I2 : Integer;
    S,S2 : String;
begin
    Excel := CreateOleObject('Excel.Application');
    try
      WorkBook := Excel.Workbooks.Add;
      try
        WorkBook  :=Excel.WorkBooks.Open(FileName := FileName , readOnly := False);
        WorkSheet := WorkBook.WorkSheets[1];
        WorkSheet.Select;
        WorkSheet.UsedRange.Copy;
        ClipBoard := TClipBoard.Create;
        try
          ClipBoard.Open;
          try
            S := ClipBoard.AsText;
            StringGrid.ColCount := WorkSheet.UsedRange.Columns.Count+1;
            StringGrid.RowCount := WorkSheet.UsedRange.Rows.Count+1;
            S2 := StringReplace(S,Char(13),'',[rfReplaceAll]);
            S  := StringReplace(S2,Char(10),Char(9),[rfReplaceAll]);

            for Col := 1 to WorkSheet.UsedRange.Columns.Count do
            begin
              StringGrid.ColWidths[Col]  := WorkSheet.UsedRange.Columns[Col].Width;
            end;

            for Row := 1 to WorkSheet.UsedRange.Rows.Count-1 do
            begin
              StringGrid.RowHeights[Row]  := WorkSheet.UsedRange.Rows[Row].Height;
            end;

            I := 1;
            for Row := 1 to StringGrid.RowCount-1 do
            begin
              for Col := 1 to StringGrid.ColCount-1 do
              begin
                J1 := PosEx(Char(9),S,I);
                StringGrid.Cells[Col,Row] := Copy(S,I,J1-I);
                I := J1 + 1;
              end;
            end;
          finally
            ClipBoard.Close;
          end;
        finally
          ClipBoard.Free;
        end;
      finally
        WorkBook.close;
        WorkBook:=unAssigned;
        WorkSheet:=unAssigned;
      end;
    finally
      Excel.Quit;
      Excel:=unAssigned;
    end;
end;

パラッゾ 2008-02-09 23:11:23 No: 29697

KHE00221さん、度々すみません・・。