reading data from an excel file

[room24]

hi, anybody out there that has an example of how to open and excel file from delphi and read the contents into a table on a MSSQL database?? if not please list sources i can read up to find out how to acheive this thankyou

 

[hilfy]

See this faq: faq102-1562

-Dell

 

[hilfy]

Actually, the Faq is about exporting to Excel, but you can use some of the same techniques to import from Excel.

-Dell

 

[Pikkunero]

Have you solved this one yet?

If not, i have some sources that demostrate how to read data to excel file and the add it to a component derived from TDataset.

 

[LastCyborg]

I think you should tell us how to do that.

--- LastCyborg ---

 

[Pikkunero]

Sorry for the delay. I was away for the weekend. Here goes. Might be a bit long post...

NOTE! For this to work it is essential that Excel IS installed on the computer where this code is executed.

First of all i'm using TExcelApplication, TExcelWorkbook and TExcelWorksheet components on a datamodule. When i create the datamodule i also create these components at runtime. If you have Enterprise version of Delphi these components are in your component palette under servers tab. If your version is professional you have to use OLE and variants to create the application and then connect to it.

My code should work quite well also "the OLE way"

SO this is the code in the DatamoduleCreate event:

    ExcelApplication1 := TExcelApplication.Create(Self);
    ExcelWorkbook1    := TExcelWorkbook.Create(ExcelApplication1);
    ExcelWorksheet1   := TExcelWorksheet.Create(ExcelWorkbook1);
    Application.ProcessMessages;
    ExcelApplication1.Connect;

Off course you need to destroy these components properly so this code goes to DatamoduleDestroy:

    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Destroy;
    ExcelApplication1.Free;
    ExcelApplication1 := nil;

Hehe... just making sure above that the Application is surely closed

In both previous cases you might want to add try-except block around the code... just to make sure

Next here are the procedures that i use when reading data from an excel file and add it to a TDataset. NOte that this also creates the fields to the dataset.

procedure CreateDatasetFromExcelFile(CellFrom,
  CellTo: string; var Dataset: TDataset);
var
  VarArray      : Variant;
  iCol,
  iRow          : Integer;
  T             : TStringField;
  sTmpName      : string;
  iCounter      : integer;
  iField        : Integer;
begin
  VarArray := VarArrayOf(['']);
  {
    Check the function FillVarArrayFromExcelRange for additional information.
    It is in this unit.
  }
  FillVarArrayFromExcelRange(CellFrom, CellTo, VarArray);
  {
    Create fields to dataset.
  }
  if Dataset.Active then
  begin
    Dataset.Close;
  end;

  iField := Ord('A');

  Dataset.FieldDefs.BeginUpdate;

  for iCol := VarArrayLowBound(VarArray, 2) to VarArrayHighBound(VarArray, 2) do
  begin
    T := TStringField.Create(Dataset);
    T.FieldName := Chr(iField);
    Inc(iField);

    sTmpName := Dataset.Name + T.FieldName;
    {
      Remove spaces (other unwanted (special) chars can be parsed here also )
      from column headers.
    }
    while Pos(' ', sTmpName) > 0 do
      sTmpName[Pos(' ', sTmpName)] := '_';

    while Pos('.', sTmpName) > 0 do
      sTmpName[Pos('.', sTmpName)] := '_';

    while Pos(',', sTmpName) > 0 do
      sTmpName[Pos(',', sTmpName)] := '_';

    T.Name      := sTmpName;
    T.Index     := Dataset.FieldCount;
    T.DataSet   := Dataset;
    Dataset.FieldDefs.Update;
  end;

  Dataset.FieldDefs.EndUpdate;
  Dataset.Open;
  {
    Add data to dataset.
    VarArrayLowBound(VarArray, 1) is the header row so start from the next one. Also there might not be any header row so the can be edited.
  }
  for iRow := VarArrayLowBound(VarArray, 1) + 1 to VarArrayHighBound(VarArray, 1) do
  begin
    Dataset.Append;
    iCounter := 1;

    for iCol := VarArrayLowBound(VarArray, 2) to VarArrayHighBound(VarArray, 2) do
    begin
      Dataset.Fields[iCounter].AsString := VarToStr(VarArray[iRow, iCol]);
      Inc(iCounter);
    end;
    Dataset.Post;
  end;
  Dataset.First;
end;

procedure FillVarArrayFromExcelRange(CellFrom, CellTo: string;
  var VarArray: variant);
begin
  {
    Add the workbook. Without it you can do nothing.
  }
  ExcelApplication1.Workbooks.Add(ExcelFile, 0);
  {
    Connect to the workbook.
  }
  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
  ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveSheet as _WorkSheet);
  {
    Get the values to the Variant. That's about it.
  }
  VarArray := ExcelWorksheet1.Range[CellFrom, CellTo].Value;
  {
    Disconnect from worksheet and workbook and close workbook.
  }
  ExcelWorksheet1.Disconnect;
  ExcelWorkbook1.Disconnect;
  ExcelApplication1.Workbooks.Close(0);
end;

And finally here is the code i'm using to read the data from the Excel file. TOLEFunc is the datamodule i'm using and there are also all related code. The ExcelFile is a property in the TOLEFunc unit.

    Application.CreateForm(TOLEFunc, OLEFunc);
    OLEFunc.ExcelFile := beFile.Text;
    OLEFunc.CreateDatasetFromExcelFile(edCellFrom.Text, edCellTo.Text, TDataSet(dxMDLocation));
    OLEFunc.Destroy;
    Application.ProcessMessages;

Now that you have all the data in the TDataset you just go through the datasets records and read all necessary data from it and add it one way or the other to your choice of database.

I hope i didn't leave anything out. And i hope this gets you going. I can't find my other ole example for now but if you don't have the components i described in the beginning then you need to declare a variable and use it somewhat like this...

This is just an example and i don't remember if it's right or wrong but you should get the idea. I found this from google at some point... try searching groups.google.com with keywords "re delphi ole automation"

var
  OleApp : Variant; //requires something to the uses...

  OleApp := CreateOleObject('Excel.Application');
  OleApp.WorkBooks.Open('FileName'
  //This goes to the FillVarArrayFromExcelRange procedure
  VarArray := OleApp.Workbooks('WorkBookName').Sheets('SheetName').Range['A1', 'B3'].Value;
//And finally quit the application
  OleApp.Quit;

I hope this gets you started!