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;
You may need to repair Office, or there may be a hidden workbook starting up with the application that is taking a long time to close. Click View>Unhide to see if anything is there. My second option for you is to try running it in a safe mode. Then kindly try to uninstall and reinstall it.
Extracting data from a closed file in another workbook is a common request by most of the excel user. They would like to pull or consolidate data from closed files; however, this is not possible.
When Excel apps opens, you may also click File>Info>Manage Workbook>Recover Unsaved Workbooks, see if you could find your workbook. If you find it, select it and click Open and save it again.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With