Quick way to load Excel spreadsheets

Question

I use this code to load an Excel spreadsheet containing only numbers. But it takes too much time to load the whole file into a stringgrid file, does anyone know a faster way to do this?

 procedure sh1(SheetIndex:integer); Var Xlapp1, Sheet:Variant ; MaxRow, MaxCol,X, Y:integer ; str:string; begin Str:=trim(form2.OpenDialog1.FileName); XLApp1 := createoleobject('excel.application'); XLApp1.Workbooks.open(Str) ; Sheet := XLApp1.WorkSheets[SheetIndex] ; MaxRow := Sheet.Usedrange.EntireRow.count ; MaxCol := sheet.Usedrange.EntireColumn.count; form2.stringgrid1.RowCount:=maxRow+1; form2.StringGrid1.ColCount:=maxCol+1; for x:=1 to maxCol do for y:=1 to maxRow do form2.stringgrid1.Cells[x,y]:=sheet.cells.item[y,x].value; XLApp1.Workbooks.close; end; procedure TForm2.Button1Click(Sender: TObject); begin if opendialog1.Execute then begin stringgrid1.Visible:=true; sh1(1); end; end; 

Answer 1

You can try to copy the entire range into a variant array. Sort of

 procedure sh1(SheetIndex:integer); Var Xlapp1, Sheet:Variant ; MaxRow, MaxCol,X, Y:integer ; str:string; arrData:Variant; begin Str:=trim(form1.OpenDialog1.FileName); XLApp1 := createoleobject('excel.application'); XLApp1.Workbooks.open(Str) ; Sheet := XLApp1.WorkSheets[SheetIndex] ; MaxRow := Sheet.Usedrange.EntireRow.count ; MaxCol := sheet.Usedrange.EntireColumn.count; //read the used range to a variant array arrData:= Sheet.UsedRange.Value; form1.stringgrid1.RowCount:=maxRow+1; form1.StringGrid1.ColCount:=maxCol+1; for x:=1 to maxCol do for y:=1 to maxRow do //copy data to grid form1.stringgrid1.Cells[x,y]:=arrData[y, x]; // do note that the indices are reversed (y, x) XLApp1.Workbooks.close; end;