Delphi reads Excel Data

It is easy to export data from a database to excel, but it is not that easy to import the data in Excel into the database through a program written in Delphi, and it is not comprehensive on the Internet, there are not a few complete and feasible solutions. The following are some of the solutions I have collected that I think are more feasible and do not dare to enjoy them exclusively. Therefore, they are used for your reference!

Procedure load (rowcount, colcount: integer; filename: string; var grid: tstringgrid );
// Read data from Excel to Grid
VaR
V: variant;
I, J: integer;
Begin
Grid. rowcount: = rowcount;
Grid. colcount: = colcount;
V: = createoleobject ('excel. application'); // create an OLE object
Try
Form2.show;
Form2.progressbar1. Position: = 0;
Form2.progressbar1. MAX: = 65535;
V. workbooks. Open (filename );
// For I: = 1 to rowcount do
For I: = 1 to 65535 do
For J: = 1 to colcount do
// If grid. cells [J-1, I-1] = ''then break;
Form2.progressbar1. Position: = I;
Grid. cells [J-1, I-1]: = V. workbooks [1]. Sheets [1]. cells [I, j];
V. workbooks [1]. close;
Finally
V. Quit;
Form2.close;
End
End;
{
Procedure save (tablename: string; grid: tstringgrid );
// Save the data in the grid to the SQL server data table
VaR
Valuesstr: string;
I, J: integer;
Begin
If not createtable (tablename, grid. colcount) then
Begin
Showmessage ('error on createtable ');
Exit;
End;
For I: = 1 to grid. RowCount-1 do
Begin
Valuesstr: = inttostr (I) + ',';
For J: = 0 to grid. ColCount-1 do
Valuesstr: = valuesstr + grid. cells [J, I] + ',';
If not insertone (tablename, valuesstr) then
Begin
Showmessage ('error on row ('+ inttostr (I) + ')');
Exit;
End;
End;
Showmessage ('data imported successfully ');
End;

 

Function insertone (const tablename, valuesstr: string): Boolean;
// Insert a record
VaR
Tmpstr, S: string;
P: integer;
Begin
Result: = true;
Tmpstr: = valuesstr;
With query1 do
Begin
Close;
SQL. Clear;
SQL. Add ('insert' + tablename + 'values (');
S: = '';
While tmpstr <> ''do
Begin
P: = pos (',', tmpstr );
S: = S + ''' + copy (tmpstr, 1, P-1) + ''',';
System. Delete (tmpstr, 1, P );
End;
S: = copy (s, 1, length (S)-1 );
SQL. Add (s );
SQL. Add (')');
Try
Execsql;
Except
Result: = false;
End;
End;
End;

Function createtable (const tablename: string; afieldcount: integer): Boolean;
// Create a table
VaR
Tmpstr: string;
I: integer;
Begin
Result: = true;
Tmpstr: = 'if exists (select * From sysobjects where name = '''
+ Tablename + ''') Drop table' + tablename + 'create table' + tablename + '(';
For I: = 1 to afieldcount do
Tmpstr: = tmpstr + 'F' + inttostr (I) + 'varchar (50 ),';
Delete (tmpstr, length (tmpstr), 1 );
Tmpstr: = tmpstr + ')';
With query1 do
Begin
Close;
SQL. Clear;
SQL. Add (tmpstr );
Try
Execsql;
Except
Result: = false;
End;
End;
End;
}