如何从数据库中获取Excel文件(BLOB)并对其执行读/写操作

Question

我正在处理需要读取和修改Excel文件中的一些数据的应用程序。

现在我在本地做它并且它可以工作，但问题是 Excel 文件需要在服务器上。我使用TMemoryStream先LoadFromFile数据集，然后LoadFromStream数据集，如下所示：

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

其中文件名根据TOpenDialog抓取。

目前，Excel文件在数据库中，我正在寻找对其进行读/写操作的最佳方法。

是否有机会从客户端数据集中读取Excel文件，或者我应该将其保存在本地，执行操作，然后再次将其发送到服务器？问题是客户端根本不应该有权访问此文件。

要打开 Excel 文件，我使用：

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

所以仍然需要原始文件名才能打开Excel文件。

我非常感谢您的帮助和想法。谢谢！

Answer 1

此示例使用 Delphi Tokyo 10.2.2、DBISAM 作为数据库和 Excel 2016。这会将 Excel 文件从数据库加载到可以进行修改的TOleContainer中，然后将 Excel 文件保存回数据库。

我创建了一个名为 TOleContainerFileIO 的类，其中大部分工作都已完成。 虽然此示例使用 DBISAM，但TOleContainerFileIO使用 TDataset ，因此任何TDataset后代都可以使用。

下面是我的示例项目的主要表单代码。

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.

这是TOleContainerFileIO类

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.

这是主要形式的.DFM，如果您想简单地剪切和粘贴以进行测试。您需要将DBISAM表更改为您正在使用的任何内容。

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:UsersamazoDesktopOleContainerTest'
    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