Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 15 total
Thread Showing excel file from ewb
Sat, Apr 30 2016 4:33 AMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi All,

I hope I can get some usefull (as always) input on this issue:

I am storing files (excel etc) in a dbisam blob field, and from this
blob field I need to show the excel file (currently view mode) to the
user inside a browser (ewb). I think/know i need a module to handle part
of blob/file, but how about presenting the file, could I use fastreports
or reportbuilder or any other tool to do this?

I know PDF files can be done, and Tim has published a sample application
for this, but i am not sure about if it can be used for excel files also?

Thanks in advance.

Regards,
Hüseyin A.
Sat, Apr 30 2016 10:28 AMPermanent Link

Raul

Team Elevate Team Elevate

On 4/30/2016 4:33 AM, Hüseyin Aliz wrote:
>
> I am storing files (excel etc) in a dbisam blob field, and from this
> blob field I need to show the excel file (currently view mode) to the
> user inside a browser (ewb). I think/know i need a module to handle part
> of blob/file, but how about presenting the file, could I use fastreports
> or reportbuilder or any other tool to do this?

You have 2 issues here:
1. Browsers do not normally do in-line excel display (i.e. excel does
not embed itself in browser like pdf viewer does). Not sure about latest
excel 2016 or such but only thing i've seen for this was a activex
control and that's really not recommended

2. Your best bet likely would be to have your back end (module if using
EWB web server) to convert excel into something else. There are number
of options from something as simple as just converting it into html
representation or convert to JSON definition (to show in grid) or even
pdf. All of those would be easily displayable on browser side

Raul
Sat, Apr 30 2016 1:12 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Raul,

Thanks for the input. Html conversion could be an option.

Regards,

Hüseyin

On 30-04-2016 16:28, Raul wrote:
> On 4/30/2016 4:33 AM, Hüseyin Aliz wrote:
>>
>> I am storing files (excel etc) in a dbisam blob field, and from this
>> blob field I need to show the excel file (currently view mode) to the
>> user inside a browser (ewb). I think/know i need a module to handle part
>> of blob/file, but how about presenting the file, could I use fastreports
>> or reportbuilder or any other tool to do this?
>
> You have 2 issues here:
> 1. Browsers do not normally do in-line excel display (i.e. excel does
> not embed itself in browser like pdf viewer does). Not sure about
> latest excel 2016 or such but only thing i've seen for this was a
> activex control and that's really not recommended
>
> 2. Your best bet likely would be to have your back end (module if
> using EWB web server) to convert excel into something else. There are
> number of options from something as simple as just converting it into
> html representation or convert to JSON definition (to show in grid) or
> even pdf. All of those would be easily displayable on browser side
>
> Raul
Sat, May 14 2016 3:38 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi Raul,

I have lately tried to read forum posts, module manual and examples
regarding serving the file stored in a blob field to the client.

My requirements about showing the excel has changed, so the file (excel,
pdf, any type) must be downloaded to the local device (skipping opening
inside browser) and saved to dataset via module later if needed.

I am using ewb server on the server side, but am a little bit confused
about:

- Are ewbdatabase, ewbadapter, ewbsession etc) required when using
dbisam (query) in ewb module? Does ewb server handle this automatically?

=======================================================================================================

When I run the application and use DokDownload i get status code 0
(showing error message - some error occured!)..

I dont know where and why it fails, but sure there must exists an
logical explanation Smile

I am sorry if this post has become long (maybe i should have attached
the source instead of putting in this post).

Thanks in advance,

Regards,

Hüseyin

The code

=======================================================================================================

I am using following code in module:

procedure TOnlineModule.EWBModuleExecute(Request: TEWBServerRequest);
begin
   if AuthenticateUser(Request) then
      begin
      Request.ResponseHeaders.Add('Cache-Control: no-cache');
      case Request.RequestMethod of
         rmGet:
            begin
            if
AnsiSameText(Request.RequestParams.Values['method'],'load') then
               begin
               TempID:=Request.RequestParams.Values['ID'];
               if (TempID <> '') then
                  begin
                  try
                  AbnDok(Request);
                  finally
                     FreeAndNil(TempFileStream);
                  end;
                  end
               else
                  Request.SendError(HTTP_BAD_REQUEST,'Markeret fil
kunne ikke hentes fra serveren');
               end
            else
               Request.SendError(HTTP_BAD_REQUEST,'Ugyldig forespørgsel');
            end;
         rmHead:
            begin
            if
AnsiSameText(Request.RequestParams.Values['method'],'load') then
               begin
               TempID:=Request.RequestParams.Values['ID'];
               if (TempID <> '') then
                  begin
                  try
                   AbnDok(Request);
                  finally
                     FreeAndNil(TempFileStream);
                  end;
                  end
               else
                  Request.SendError(HTTP_BAD_REQUEST,'Markeret fil
kunne ikke hentes fra serveren');
               end
            else
               Request.SendError(HTTP_BAD_REQUEST,'Ugyldig forespørgsel');
            end;
      end
      end
   else
      Request.SendError(HTTP_BAD_REQUEST,'Login kunne ikke gennemføres');
end;

procedure TOnlineModule.AbnDok(Request: TEWBServerRequest);
begin
  with ProjektDokument do begin
  if active then close;
  unprepare;
  prepare;
  params[0].asinteger := strtoint(TempID);
  open;
  end;
  TempFileName := ProjektDokument.FieldByName('Filnavn').AsString;
  blob :=
ProjektDokument.CreateBlobStream(ProjektDokument.FieldByName('dokument'),
bmRead);
  try
    blob.Seek(0, soFromBeginning);
    with TFileStream.Create(TempFileName, fmCreate) do
      try
        CopyFrom(blob, blob.Size)
      finally
        Free
      end;
  finally
    blob.Free
  end;
Request.SendCustomContentStream(TempFileStream,'application/pdf','attachment;filename="'+TempFileName+'"');
end;

And this is the code from ewb client:

procedure TDokumenterForm.DokDownloadClick(Sender: TObject);
begin
  with DOKServerRequest do
      begin
      Params.Values['method']:='load';
Params.Values['ID']:=inttostr(Projektdokumenter.columns['ID'].asInteger);
      URL:='modules/onlinemod';
      Execute;
  end;
end;

procedure TDokumenterForm.DOKServerRequestComplete(Request: TServerRequest);
begin
 if (Request.StatusCode=200) then
 showmessage(Request.ResponseContent.Text)
 else
 MessageDlg('Some error occured!','Error',mtError,[mbOk],mbOk);
end;

Mon, May 16 2016 9:57 AMPermanent Link

Raul

Team Elevate Team Elevate

On 5/14/2016 3:38 PM, Hüseyin Aliz wrote:
> My requirements about showing the excel has changed, so the file (excel,
> pdf, any type) must be downloaded to the local device (skipping opening
> inside browser) and saved to dataset via module later if needed.
>
> I am using ewb server on the server side, but am a little bit confused
> about:
>
> - Are ewbdatabase, ewbadapter, ewbsession etc) required when using
> dbisam (query) in ewb module? Does ewb server handle this automatically?

Hüseyin,

I think there are 2 things at play here to look at

1. Can EWB server binary data back from blob ?

Answer is yes - EWB web server has built-in capability to deal with this
that might be good enough for what you need.

You should be able to just access it simply by using the URL for the
column (i.e. /datasets?dataset=<dataset name>&method=load&column=<column
name>&row=<row number>).

It uses a <column_name>_ContentType column to populate the proper
content type also so make sure this is correct for your data.

Look at include sample called Multimedia to see it all in action.

Using Elevate soft hosted version of the sample you can see trace the
requests and see that album image (which is a dbisam blob) is loaded
using like this  :
http://www.elevatesoft.com:8081/multimedia/datasets?dataset=Albums&method=load&column=CoverArt&row=7

Loading excel/pdf/etc is same idea - make sure your blob contains the
file and contenttype column is populated and issue appropriate web request.


2. The other issue is what are you going to do with the data once it
arrives at the browser ?

Simply downloading it is not really going to help you (since you cannot
really save anything to local file system from javascript) and of course
javascript/ewb cannot do anything with excel or such.

Two options i can suggest are:
a. use TPlugin to load the URL so it can host the content - this should
work fine for anything that browser knows how to handle
(PDF/audio/video/etc). What will be more problematic will be excel etc.
(you can use tbrowser here also)

b. Use the URL to populate a TLink so when user clicks it the browser
will do whatever it's default action is for content type (for example
for excel it shoudl download the file, save it and launch excel).

Others might have better suggestions

Raul
Mon, May 16 2016 2:47 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Raul,

Very valuable information, thank you Smile

Regards,

Hüseyin



On 16-05-2016 15:57, Raul wrote:
> On 5/14/2016 3:38 PM, Hüseyin Aliz wrote:
>> My requirements about showing the excel has changed, so the file (excel,
>> pdf, any type) must be downloaded to the local device (skipping opening
>> inside browser) and saved to dataset via module later if needed.
>>
>> I am using ewb server on the server side, but am a little bit confused
>> about:
>>
>> - Are ewbdatabase, ewbadapter, ewbsession etc) required when using
>> dbisam (query) in ewb module? Does ewb server handle this automatically?
>
> Hüseyin,
>
> I think there are 2 things at play here to look at
>
> 1. Can EWB server binary data back from blob ?
>
> Answer is yes - EWB web server has built-in capability to deal with
> this that might be good enough for what you need.
>
> You should be able to just access it simply by using the URL for the
> column (i.e. /datasets?dataset=<dataset
> name>&method=load&column=<column name>&row=<row number>).
>
> It uses a <column_name>_ContentType column to populate the proper
> content type also so make sure this is correct for your data.
>
> Look at include sample called Multimedia to see it all in action.
>
> Using Elevate soft hosted version of the sample you can see trace the
> requests and see that album image (which is a dbisam blob) is loaded
> using like this  :
> http://www.elevatesoft.com:8081/multimedia/datasets?dataset=Albums&method=load&column=CoverArt&row=7
>
> Loading excel/pdf/etc is same idea - make sure your blob contains the
> file and contenttype column is populated and issue appropriate web
> request.
>
>
> 2. The other issue is what are you going to do with the data once it
> arrives at the browser ?
>
> Simply downloading it is not really going to help you (since you
> cannot really save anything to local file system from javascript) and
> of course javascript/ewb cannot do anything with excel or such.
>
> Two options i can suggest are:
> a. use TPlugin to load the URL so it can host the content - this
> should work fine for anything that browser knows how to handle
> (PDF/audio/video/etc). What will be more problematic will be excel
> etc. (you can use tbrowser here also)
>
> b. Use the URL to populate a TLink so when user clicks it the browser
> will do whatever it's default action is for content type (for example
> for excel it shoudl download the file, save it and launch excel).
>
> Others might have better suggestions
>
> Raul
Mon, May 16 2016 4:22 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Raul,

Now i've managed to change my code, so if I use Tlink, the blob file are
showed inside browser (pdf), but in this case, path to the blob file are
showed in browser address bar - like:

/datasets?dataset=<dataset name>&method=load&column=<column
name>&row=<row number>

The user can simply change <row number> to get another file which i am
not interested in Smile

The next option tplugin/tbrowser are more usable but here i need to
change the filename which are downloaded (excel file downloads - pdf
file show inside..) currently it sets "dataset" as filename. Another
(for me) option would be to force the user to download the file
regardless if file type are known etc, kind of "downloadonly" property
for tplugin/tbrowser Smile

Thanks,

Hüseyin


On 16-05-2016 15:57, Raul wrote:
> On 5/14/2016 3:38 PM, Hüseyin Aliz wrote:
>> My requirements about showing the excel has changed, so the file (excel,
>> pdf, any type) must be downloaded to the local device (skipping opening
>> inside browser) and saved to dataset via module later if needed.
>>
>> I am using ewb server on the server side, but am a little bit confused
>> about:
>>
>> - Are ewbdatabase, ewbadapter, ewbsession etc) required when using
>> dbisam (query) in ewb module? Does ewb server handle this automatically?
>
> Hüseyin,
>
> I think there are 2 things at play here to look at
>
> 1. Can EWB server binary data back from blob ?
>
> Answer is yes - EWB web server has built-in capability to deal with
> this that might be good enough for what you need.
>
> You should be able to just access it simply by using the URL for the
> column (i.e. /datasets?dataset=<dataset
> name>&method=load&column=<column name>&row=<row number>).
>
> It uses a <column_name>_ContentType column to populate the proper
> content type also so make sure this is correct for your data.
>
> Look at include sample called Multimedia to see it all in action.
>
> Using Elevate soft hosted version of the sample you can see trace the
> requests and see that album image (which is a dbisam blob) is loaded
> using like this  :
> http://www.elevatesoft.com:8081/multimedia/datasets?dataset=Albums&method=load&column=CoverArt&row=7
>
> Loading excel/pdf/etc is same idea - make sure your blob contains the
> file and contenttype column is populated and issue appropriate web
> request.
>
>
> 2. The other issue is what are you going to do with the data once it
> arrives at the browser ?
>
> Simply downloading it is not really going to help you (since you
> cannot really save anything to local file system from javascript) and
> of course javascript/ewb cannot do anything with excel or such.
>
> Two options i can suggest are:
> a. use TPlugin to load the URL so it can host the content - this
> should work fine for anything that browser knows how to handle
> (PDF/audio/video/etc). What will be more problematic will be excel
> etc. (you can use tbrowser here also)
>
> b. Use the URL to populate a TLink so when user clicks it the browser
> will do whatever it's default action is for content type (for example
> for excel it shoudl download the file, save it and launch excel).
>
> Others might have better suggestions
>
> Raul
Tue, May 17 2016 9:54 AMPermanent Link

Raul

Team Elevate Team Elevate

On 5/16/2016 4:22 PM, Hüseyin Aliz wrote:
> The user can simply change <row number> to get another file which i am
> not interested in Smile

That could be a problem. I don't think there is an easy way out of this
using the built-in capability.

You'd have to use custom web server modules to handle more complex
download scenarios where the URL is non-guessable.

One option is to use a web request to your custom web server module to
create a download session (i.e. reference the table/row/column in
question) that is identified by random ID (like guid) and then you can
use TLink with URL that includes the random ID (it would be very
difficult to guess but you'd need your own module to provide the actual
file download).

Simplified version of this would be to dump the blobs to the file system
with random name and then serve them up that way (as files). The DB
table would just contain the file name )or relative URL) so your EWB app
would know how to populate the TLink URL properly.


> The next option tplugin/tbrowser are more usable but here i need to
> change the filename which are downloaded (excel file downloads - pdf
> file show inside..) currently it sets "dataset" as filename. Another
> (for me) option would be to force the user to download the file
> regardless if file type are known etc, kind of "downloadonly" property
> for tplugin/tbrowser Smile

I don't know of automatic way to achieve this. The reason it opens
embedded in browser is that the browser "knows" the file type and then
shows it. User can change mime type behavior but of course you can't
rely on that.

One way around would be to force a generic "application/octet-stream"
mime type (or something similar) - browser should prompt user for
download for this.

Apossible issue is that some browsers try to be helpful so if they see
"pdf" extension for example they might still try to show it.

I'd say try to experiment a bit

Raul
Tue, May 17 2016 12:23 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

<< The next option tplugin/tbrowser are more usable but here i need to change the filename which are downloaded (excel file downloads - pdf file show inside..) currently it sets "dataset" as filename. Another (for me) option would be to force the user to download the file regardless if file type are known etc, kind of "downloadonly" property
for tplugin/tbrowser Smile>>

Check out the PDFModule example web server module project - it shows you how to deal with this.

In general, you're not going to want to use the dataset handling for what you're trying to do, but rather do something more like what the PDFClient/PDFModule example projects are doing.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, May 17 2016 4:54 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Raul,

Thanks for your suggestions, very useful indeed. I ended up using a
tbrowser to open the files from blob file and showing them embedded
(pdf), and only issue left for me now is the name of the downloaded file
(unknown type -> dataset.zip), if it can be changed so it uses filename
stored in another column from the table, i will be happy, if not i need
to use more time learning how to setup a module for other ways of doing
what i want.. Thanks again Smile

Regards,

Hüseyin



On 17-05-2016 15:54, Raul wrote:
> On 5/16/2016 4:22 PM, Hüseyin Aliz wrote:
>> The user can simply change <row number> to get another file which i am
>> not interested in Smile
>
> That could be a problem. I don't think there is an easy way out of
> this using the built-in capability.
>
> You'd have to use custom web server modules to handle more complex
> download scenarios where the URL is non-guessable.
>
> One option is to use a web request to your custom web server module to
> create a download session (i.e. reference the table/row/column in
> question) that is identified by random ID (like guid) and then you can
> use TLink with URL that includes the random ID (it would be very
> difficult to guess but you'd need your own module to provide the
> actual file download).
>
> Simplified version of this would be to dump the blobs to the file
> system with random name and then serve them up that way (as files).
> The DB table would just contain the file name )or relative URL) so
> your EWB app would know how to populate the TLink URL properly.
>
>
>> The next option tplugin/tbrowser are more usable but here i need to
>> change the filename which are downloaded (excel file downloads - pdf
>> file show inside..) currently it sets "dataset" as filename. Another
>> (for me) option would be to force the user to download the file
>> regardless if file type are known etc, kind of "downloadonly" property
>> for tplugin/tbrowser Smile
>
> I don't know of automatic way to achieve this. The reason it opens
> embedded in browser is that the browser "knows" the file type and then
> shows it. User can change mime type behavior but of course you can't
> rely on that.
>
> One way around would be to force a generic "application/octet-stream"
> mime type (or something similar) - browser should prompt user for
> download for this.
>
> Apossible issue is that some browsers try to be helpful so if they see
> "pdf" extension for example they might still try to show it.
>
> I'd say try to experiment a bit
>
> Raul
Page 1 of 2Next Page »
Jump to Page:  1 2
Image