Excel to Delphi to MySQL db

It’s long time since I used Delphi, but recently I’ve got the chance to do that
Began from the asking how the way Delphi accesses excel file, then eventually I tried to make it.

Not too bad, as starting from internet I got reference directly from microsoft site, but the example only shows how to automate excel to create a new workbook, whereas I need the inverse, which is slow to find the examples

Finally, from that example and knowledges I’ve had, and partially any similar examples from internet I collected, I started to type the inverse from that example.
Below is the script I made as function form. That function will give back the result to TStringGrid, but ofcourse can be modified as needed. I used Borland Delphi 6.0 and have Microsoft Excel 2003 installed at my computer.

function GiveRowHeader(x: Integer): String;
begin
  if(x > 26) then Result := GiveRowHeader(x div 26) + GiveRowHeader(x mod 26)
  else Result := Chr( Ord( 'A' ) + x - 1 );
end;

procedure LoadFromXLFile(AFileName: String; ASheetNum: Integer;
        AStringGrid: TStringGrid; var OSheetName: String);
var
  ARow, ACol: Integer;
  i, j: Integer;
  XLApp, XLBook, XLSheet: Variant;
begin
  // Create OLE Object to Excel Application
  XLApp := CreateOleObject('Excel.Application');
  // Hide the Excel
  XLApp.Visible := False;
  // Open file
  XLApp.Workbooks.Open(AFileName,
                       EmptyParam, //UpdateLinks: OleVariant
                       EmptyParam, //ReadOnly: OleVariant
                       EmptyParam, //Format: OleVariant
                       EmptyParam, //Password: OleVariant
                       EmptyParam, //WriteResPassword: OleVariant
                       EmptyParam, //IgnoreReadOnlyRecommended: OleVariant
                       EmptyParam, //Orign: OleVariant
                       EmptyParam, //Delimiter: OleVariant
                       EmptyParam, //Editable: OleVariant
                       EmptyParam, //Notify: OleVariant
                       EmptyParam, //Converter: OleVariant
                       EmptyParam, //AddToMru: OleVariant
                       0
                       );
  XLBook := XLApp.ActiveWorkbook;
  XLSheet := XLBook.WorkSheets[ASheetNum];
  XLSheet.Activate;
  XLSheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
  OSheetName := XLSheet.Name;
  // Get last row value
  ARow := XLApp.ActiveCell.Row;
  AStringGrid.RowCount := ARow + 1;
  // Get rightmost column value
  ACol := XLApp.ActiveCell.Column;
  AStringGrid.ColCount := ACol + 1;

  // Doing something with the content 
  for i := 1 to ARow do
  begin
    AStringGrid.Cells[0, i] := IntToStr(i);
    for j := 1 to ACol do
    begin
      AStringGrid.Cells[j, 0] := GiveRowHeader(j);
      AStringGrid.Cells[j, i] := XLSheet.Cells[i, j];
    end;
  end;

  // Then exit the Excel if done
  if not VarIsEmpty(XLApp) then
  begin
    XLApp.DisplayAlerts := False;  // disable any alert from the Excel
    XLApp.Quit;                    // now quit
    XLApp := Unassigned;           // then unassign all OLE Object has been created
    XLBook := Unassigned;
    XLSheet := Unassigned;
  end;
end;

Remember to add Excel2000 in uses, since xlCellTypeLastCell is defined inside that unit.

uses Excel2000;

Successfully wrote down that function, this is not enough if we don’t give export functionality to my new application I’ve just made. Neither to export just to plain text file, I decided to add it export to MySQL db functionality…

So, below I added for that purpose, which action is run when Button3 is clicked. In addition I used Memo1 for log progress status.

procedure TForm1.Button3Click(Sender: TObject);
var StrSQL, TableName: String;
    RetVal, ARow, ACol: Integer;
begin
  Memo1.Clear;
  // Try to connect to MySQL Server
  mysql_connect(@mysqlrec, PChar('localhost'), PChar('username'), PChar('password'));
  if mysqlrec._net.last_errno = 0 then
  begin
    Memo1.Lines.Add('STATUS: Successfully connected to MySQL Server');
    Connected := 1;
  end
  else
    Memo1.Lines.Add('ERROR: ' + Trim(mysqlrec._net.last_error) +
            ' [Kode: '+IntToStr(mysqlrec._net.last_errno)+']');
  // Try to attach to the database, create if not exists
  mysql_query(@mysqlrec,
    PChar('CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test_nuedcsoft` ' +
            '/*!40100 DEFAULT CHARACTER SET latin1 */;'));
  Memo1.Lines.Add('QUERY: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test_nuedcsoft` '+
            '/*!40100 DEFAULT CHARACTER SET latin1 */;');
  RetVal := mysql_select_db(@mysqlrec, PChar('test_nuedcsoft'));
  if RetVal <> 0 then
    Memo1.Lines.Add('ERROR: Error attaching to database `test_nuedcsoft` '+
            '[Kode: '+IntToStr(RetVal)+']' )
  else
    Memo1.Lines.Add('STATUS: Successfully attached to database `test_nuedcsoft`');

  TableName := StringReplace(LowerCase(Trim(Edit2.Text)), ' ', '_', [rfReplaceAll]);
  // First make sure to insert from the start, by delete the table
  mysql_query(@mysqlrec, PChar('DROP TABLE IF EXISTS `' + TableName + '`;'));
  Memo1.Lines.Add('QUERY: DROP TABLE IF EXISTS `' + TableName + '`;');
  // Then re-create the table
  StrSQL :=
    'CREATE TABLE `' + TableName + '` (' +
    '`Id` smallint(5) unsigned NOT NULL default ''0'',';
  for ACol := 1 to StringGrid1.ColCount - 1 do
    StrSQL := StrSQL + '`' + LowerCase(GiveRowHeader(ACol)) +
            '` varchar(200) default NULL,';
  StrSQL := StrSQL + 'PRIMARY KEY  (`Id`)' +
    ' ) ENGINE=MyISAM DEFAULT CHARSET=latin1;';
  mysql_query(@mysqlrec, PChar(StrSQL));
  Memo1.Lines.Add('QUERY: '+StrSQL);
  // Start to insert the data
  for ARow := 1 to StringGrid1.RowCount - 1 do
  begin
    StrSQL := 'INSERT INTO `' + TableName + '` (`Id`';
    for ACol := 1 to StringGrid1.ColCount - 1 do
      StrSQL := StrSQL + ',`' + LowerCase(GiveRowHeader(ACol)) + '`';
    StrSQL := StrSQL + ' ) VALUES (' + IntToStr(ARow);
    for ACol := 1 to StringGrid1.ColCount - 1 do
      StrSQL := StrSQL + ',''' + StringGrid1.Cells[ACol, ARow] + '''';
    StrSQL := StrSQL + ');';
    Memo1.Lines.Add('QUERY: '+StrSQL);
    mysql_query(@mysqlrec, PChar(StrSQL));
  end;
  // Disconnected from MySQL Server
  mysql_close(@mysqlrec);
  Memo1.Lines.Add('STATUS: Disconnected from MySQL Server');
  Connected := 0;
end;

Before, I defined global variables first for that needed above implementation declaration.

var
  Form1: TForm1;
  mysqlrec: mysql; //Global mysql struct
  Connected: Integer = 0; //Global var to keep track of whether mysql is connected

implementation

{$R *.dfm}

I used Simple MySQL Delphi Connection library for that to be done, so I defined that unit in uses.

uses _libmysq;

Done! Now my application has Import from Excel and Export to MySQL ability.

P.S.:
If you need that project’s source code, I am willing to email you just for free.