Fast way to load Excel spreadsheet [closed]

Question

I'm using this code to load an excel spreadsheet containing only numbers. But it takes too long to load the whole file into a stringgrid, 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;

Heliotrope

I use it to open .xlsx, is there a way to do so without requiring 3rd party components. – Heliotrope

because there seems to be few "free" components for handling excel on delphi. – Heliotrope

But is there a component out there really worth using? Because with this spreadsheet I'd like to operate with alot of things, plot charts, algorithms, etc. – Heliotrope

This seems like another one of those "I want a free component" questions. You didn't say in the question that you wanted it to be free. But you do in the comments. Parsing a .xlsx file is tricky. You aren't going to get the code in an SO answer. So you can surely do the web search as well as we can. Did your web search yield any results. – Olericulture

Anyway, by far the easiest solution would be to fix your COM code so that it worked quickly. Don't start a new instance of Excel every time. Start it once and re-use it. Stop using late binding, that's really going to sting performance. And don't pull cells out one at a time. Pull the entire range out into a variant array. Then you code will fly. – Olericulture

Set the stringgrid to be visible only after having read in the data. That should save a few milliseconds. – Wholewheat

Answer 1

You can try to copy the whole range to a variant array. Something like

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;

Julissajulita

Well, not really. Actually, I've just copied that from another code that writes to Excel. I'll edit the code. – Julissajulita

Thanks daniel, pretty instantaneous now. Thank you :D – Heliotrope

It's a great code. I also recommends process excel's errors in cells (otherwise you will get AV): v: Variant; v := arrData[y, x]; if VarIsError(v) then grid.Cells[x,y] := 'error' else grid.Cells[x,y] := v; – Boydboyden