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

名前

ホームページ(ブログ、Twitterなど)のURL (省略可)

本文

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

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;

←解決時は質問者本人がここをチェックしてください。

戻る