How to get Excel file (BLOB) from Database and do read/write operations on it?

Question

I'm working on application where I need read and modify some data in Excel file.

For now I was doing it locally and it works, but the thing is that Excel file needs to be on the server. I used TMemoryStream to first LoadFromFile and next LoadFromStream to dataset as below:

fileStream := TMemoryStream.Create;
fileStream.LoadFromFile(sFileName);
cdsExcel.LoadFromStream(fileStream);

where sFileName is grabbed based on TOpenDialog.

For now Excel file is in DB and I'm looking for the best way to do read/write operations on it.

Is it any chance to read Excel file from client data set or should I save it locally, do operations and next send it to the server again? The thing is that client shouldn't have access to this file at all.

To open Excel file I use:

Wb := ExApp.Workbooks.Open(sFileName, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID);

so still need original Filename to open the Excel file.

I really appreciate your help and ideas. Thank you!

Answer 1

This example uses Delphi Tokyo 10.2.2, DBISAM as a database, and Excel 2016. This will load an Excel file from a database into an TOleContainer where you can do modifications, then save the Excel file back to the database.

I've created a class called TOleContainerFileIO where most of the work is done. While this example uses DBISAM, TOleContainerFileIO uses TDataset, so any TDataset descendant will work.

Here's the main form code for my sample project.

unit uMainForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, OleCtnrs, DB,  uOleContainerFileIO, dbisamtb,
  Vcl.Menus;

type
  TForm1 = class(TForm)
  tExcelDB: TDBISAMTable;
  MainMenu1: TMainMenu;
  File1: TMenuItem;
  LoadWorksheetfromDB1: TMenuItem;
  SaveWorksheettoDB1: TMenuItem;
  OleContainer1: TOleContainer;
  procedure FormShow(Sender: TObject);
  procedure FormClose(Sender: TObject; var Action: TCloseAction);
  procedure LoadWorksheetfromDB1Click(Sender: TObject);
  procedure SaveWorksheettoDB1Click(Sender: TObject);
private
  fOleContainerFileIO: TOleContainerFileIO;
public
end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
  tExcelDB.open;
  fOleContainerFileIO := TOleContainerFileIO.create(self, OleContainer1);   
end;

// TMainmenu menuitem, File > LoadWorksheetFromDB
procedure TForm1.LoadWorksheetfromDB1Click(Sender: TObject);
begin
  fOleContainerFileIO.LoadFileFromDB(tExcelDB, 'XLSFile');   
end;

// TMainmenu Menuitem, File > Save WorksheetToDB
procedure TForm1.SaveWorksheettoDB1Click(Sender: TObject);
begin
  fOleContainerFileIO.SaveFileToDb;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  fOleContainerFileIO.free;
end;

end.

And here's the TOleContainerFileIO class

unit uOleContainerFileIO;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ShellAPi, ExtCtrls, OleCtnrs, DB, ComCtrls;

type

  TOleContainerFileIO = class(TComponent)
    private
    fTempfilename: string;
    fContainer: TOleContainer;
    fDataset: TDataset;
    fBlobFieldname: string;
    function GetWindowsTempFolder: string;
  public
    constructor create(AOwner: TComponent; AContainer: TOleContainer); reintroduce;
    function Active: Boolean;
    procedure LoadFromFile(const AFilename: string);
    procedure LoadFileFromDB(ADataset: TDataSet; const ABlobFieldname: string);
    procedure SaveFileToDb;
end;


implementation

function TOleContainerFileIO.GetWindowsTempFolder: String;
var
  tempFolder: array[0..MAX_PATH] of Char;
begin   
  GetTempPath(MAX_PATH, @tempFolder);
  result := StrPas(tempFolder);
end;

constructor TOleContainerFileIO.create(AOwner: TComponent; AContainer: TOleContainer);
begin
  inherited create(AOwner);
  fContainer := AContainer;
end;

function TOleContainerFileIO.Active: boolean;
begin
  result := ((fDataset.Active) and (fContainer <> nil));
end;

Procedure TOleContainerFileIO.LoadFromFile(const AFilename: string);
begin
  fContainer.CreateObjectFromFile(AFilename, false);
  fContainer.AutoActivate := aaManual;
  fContainer.Iconic := false;
  fContainer.CopyOnSave := false;
  fContainer.DoVerb(ovShow);
  fContainer.Run;
end;

procedure TOleContainerFileIO.LoadFileFromDB(ADataset: TDataSet; const ABlobFieldname: string);
var
  fs: TFileStream;
  bs: TStream;
begin
  fDataset := ADataset;
  fBlobFieldname := ABlobFieldname;

  if fDataset.Active = False then
    raise exception.Create('Load document from Database failed, Dataset is not active');

  if fDataset.Fields.FindField(fBlobFieldname)= nil then
    raise exception.Create('Load document from Database failed, '+fBlobFieldname+' not found');

  bs := fDataset.CreateBlobStream(fDataset.FieldByName(fBlobFieldname), bmRead);
  try
    bs.seek(0, soFromBeginning);
    // create temp file, could use guid.xls here instead of random..
    fTempfilename := GetWindowsTempFolder+'Tempfile-'+inttostr(random(100000000))+'.xls';
    fs := TFileStream.Create(fTempfilename, fmCreate);
    try
      fs.CopyFrom(bs, bs.size)
    finally
      fs.free;
    end;
  finally
    bs.Free;
  end;

  fContainer.CreateObjectFromFile(fTempfilename, false);

  try
    fContainer.AutoActivate := aaManual;
    fContainer.Iconic := False;
    fContainer.CopyOnSave := false;
    fContainer.DoVerb(ovShow);
    fContainer.Run;
  finally
    Deletefile(fTempfilename);
  end;
end;

procedure TOleContainerFileIO.SaveFileToDb;
var
  fs: TFileStream;
  bs: TStream;
begin
  if fDataset.Active = False then
    raise exception.Create('Load Excel from Database failed, Dataset is not active');

  if fDataset.Fields.FindField(fBlobFieldname)= nil then
    raise exception.Create('Save document to Database failed, '+fBlobFieldname+' does not found');

  fContainer.OldStreamFormat := true;
  fContainer.SaveAsDocument(fTempfilename);   
  fDataset.edit;
  bs := fDataset.CreateBlobStream(fDataset.FieldByName(fBlobFieldname), bmWrite);
  try
    try
      bs.seek(0, soFromBeginning);
      fs := TFileStream.Create(fTempFilename, fmOpenRead, fmShareDenyNone);
      try
        try
          bs.CopyFrom(fs, fs.size);
          fDataset.Post;
        finally
          fs.free;
        end;
      finally
        DeleteFile(fTempfilename);
      end;
    except
      fDataset.Cancel;
      raise;
    end;
  finally
    bs.free;
  end;
end;

end.

And here's the main form's .DFM in case you want to simply cut and paste to test. You'll need to change the DBISAM table to whatever it is you're using is all..

object Form1: TForm1
  Left = 554
  Top = 153
  Caption = 'Excel OleContainer Test'
  ClientHeight = 606
  ClientWidth = 885
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  Menu = MainMenu1
  OldCreateOrder = False
  OnClose = FormClose
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object OleContainer1: TOleContainer
    Left = 0
    Top = 0
    Width = 885
    Height = 606
    Align = alClient
    Caption = 'OleContainer1'
    TabOrder = 0
    ExplicitLeft = 8
    ExplicitTop = 8
    ExplicitWidth = 1073
    ExplicitHeight = 772
  end
  object tExcelDB: TDBISAMTable
    DatabaseName = 'C:\Users\amazo\Desktop\OleContainerTest'
    EngineVersion = '4.44 Build 3'
    TableName = 'ExcelDB'
    Left = 64
    Top = 96
  end
  object MainMenu1: TMainMenu
    Left = 64
    Top = 176
    object File1: TMenuItem
      Caption = 'File'
      object LoadWorksheetfromDB1: TMenuItem
        Caption = 'Load Worksheet from DB'
        OnClick = LoadWorksheetfromDB1Click
      end
      object SaveWorksheettoDB1: TMenuItem
        Caption = 'Save Worksheet to DB'
        OnClick = SaveWorksheettoDB1Click
      end
    end
  end
end