URGENT : How to create an excel file from web method?

URGENT : How to create an excel file from web method?

  
Dear Community,

We are using Outsystems to expose web services to be consumed by our application. In the process, we need a functionality to create an excel sheet from the record list. For this, we were exploring RecordListToExcel feature.

But where would it create my excel file as it does not accept any folder path parameter?

On exploring it further, we observed that we need Download tool to actually get the file but it has a restriction that it is only available in a Screen Action in a Web flow. But we need to have it in the web method that can be called from our client application. 

This is an urgent requirement for us so please share your comments with us on how can we achive this in Outsystems.

Regards,
Shiva
Hi Shiva,

The Download note is just a means to get any Binary Data variable (be it an excel or something else) downloaded via a browser. To send your RecordListToExcel-produced excel down a Web Method request, simply create a Binary Data output variable on that Web Method and assign the RecordListToExcel output to it.

Of course, the caller of the Web Method will need to know that the incoming Binary Data payload is actually the excel, and will need to save it to wherever it makes sense.

Regards,


Miguel
Dear Miguel,

Thanks for your reply. I have a little confusion, so it would be great if you could please explain it little further. This is what I have understood till now:
  1. Create a web method where in we will have a binary type variable to store the output of RecordListToExcel control.
  2. Our client will call this web service method.
Our task is to create the file on SERVER. Our outsystems web services are also on that server.

Few Clarifications required
  1. I guess, just declaring a binary variable in the web method will not create the excel. Is there any tool/control in outsystems that accepts the binary variable and creates the excel file on the server itself? I mean, in the web method design, what next should be there in the design flow after RecordListToExcel control? If yes, what is it?
  2. Can we also send the path to the outsystems web method where we want to create/save this excel file? We would want to store it at a location of our choice. If yes, how to do that.

My guess work

I believe Extention File system can do the trick here. It must be having a function that accepts binary input and writes/creates a file. We can use that one, if it exists. Please validate my assumption.


It would be really great if you could please share some sample example with us on how to do that in detail so that we can get a fair understanding of the solution.

Please let me know if you need more details around this.

Thanks in advance.

Regards,
Shiva

Hi Shiva,

I think I now get what you mean: you want to produce the excel on the server but then save it to the server's filesystem rather than return it to the Web Service caller, is that correct?

Well, this is doable but it probably is not what you want. You have to remember that by default any Web Applications, regardless of what technology, cannot write to the filesystem for security reasons. So you probably would have to do some access configuration on the machine so that the ASPNET user could write to a folder.

May I ask why you would want to save to filesystem rather than just save the Excel in database and retrieve it later from a screen?

Regards,


Miguel
Dear Miguel,

Thanks for your response. You are right in terms of what I want to do.

Actually we are using Silverlight on client side and actually we want to send emails to some people with attachments in it. We are exploring on the possibilities to send out emails with attachments from outsystems (I hope outsystems can do it). Thus all this will be on server side.

Since files are to be send as attachments, we need to have them physically on the server. Moreover, having files on the server will allow us to open and see the contents the file anytime. So it is like our strict requirement to create excel files on server side.

Waiting for your comments. Please explain with an example and detailed steps for any setting that I must do to to achive what I'm looking at.

Regards,
Shiva
Hi again,

I understand what you are saying and I do not dispute that you need to create the excel files on the server. What I am saying is that you probably don't need them to be saved on the filesystem of the server if they can be saved in the database after being produced. This is especially true if you want to send them as attachments in emails produced using the Agile Platform.

My guess would be you would want to do something like this:

1) Create a web service: inside one of the web methods you would do a query (that produces a RecordList), feed that RecordList into a RecordListToExcel node (that produces a BinaryData that _is_ an excel) and store it in an Entity (=table) in the Database, let's call it GeneratedExcel;

2) Then at some time, maybe on a Timer (i.e. asynchronously), you would go through all the mail recipients (maybe another Entity on your eSpace), use the Email sending  functionality of the platform and query the records in GeneratedExcel and use the AttachFile action on the Email preparation to attach these Binary Data's (that are excels, remember) to send them;

3) You could also add some sort of "retrieve GeneratedExcels" Web screen to your eSpace that would allow users to fetch generated excels on demand, should the need arise;

You can learn a bit more on the Email functionality by looking into the "Sending an Email" how to from Service Studio (or check out http://www.youtube.com/watch?v=cTqMC7Ktsqc).

Writing stuff into filesystem from a Web Application is normally a bad idea, as it is a maintenance nightmare for example when you decide to move the eSpace to another server and the file shares are not on that environment.

Have a look into the video and ponder on my suggestions above to see if they meet your demands.


Miguel