Silverlight server side excel report generation based on database query

Question

I am in a process of building an intranet application entirely using silverlight 3. On one of the page, I need to generate an excel report. Users can select few parameters through UI and will hit submit button, then silverlight shall generate an excel report & popup a window allowing users to save the generated report.

Is there anyway to implement this in easy and straight forward way?

The following are the steps I could think of but still not completely clear how to approach this problem.

1. User selects few comboboxes, and selects listbox
2. User hits the submit button
3. btnSubmit_Click(submit button click event handler) will call "takeQueryParamsAsync" WCF service call
4. WCF service on server side creates a dynamic sql query and executes the query and fetches the data
5. Silverlight client gets notified that fetching data is completed by calling the callback function "takeQueryParamsAsync_Completed"

now how the silverlight client requests the generation of the report from the server and how will it give generated report to the user? Does it have to call another wcf service in the "takeQueryParamsAsync_Completed" callback function to request the report file from the fetched data? If so how will wcf service remember that it is the same client that requested data fetch from db with those specific query parameters? Do I have to maintain state between wcf service and silverlight? Isnt there any simpler solution?

client side excel report generation is not an option(because the generated excel file shall contain excel charts). Is it really that hard? or Am I just overcomplicating myself not knowing how to implement it?

Any pointers or code samples will be great. Thank you.

I am sure that there should be an elegant solution for this.

Answer 1

Okay, I have a few ideas about this:

Are you going to use the Silverlight SaveDialog to download the file? First off, and I think you know this, it is the only way of saving files to the user's computer using Silverlight 3. If you are going to use that method, then you need a user driven event to save the file (i.e. A button click).

So yes, you will have to go for that extra call, but not from inside the takeQueryParamsAsync_Completed code. What you can do, is to active a button, or something else, that will inform the user that the report is generated and have them click a download button. There are other options though, I recently implemented a download option that made use of URL redirection to the file URL on the server, and that enabled me to get around the whole issue of (No default filename option, cannot just open the file but has to save it to the HDD first, etc.).

As to the issue with the state thing. It really depends on where you store the generated report. Do you store it on the hard drive, in a database, or keep it in memory? This effects how you can recall the file. One of the simple ways of doing it, is to return a key to the client, that it passes back to the server as a key that links to the file. That way you do not have to maintain state between the WCF service and Silverlight.

EDIT Well, on the Wcf Service side you will require an extra service. Mine looks something like

<service behaviorConfiguration="NewBehavior" name="ALMWCFHost.FileProvider.FileService">
   <endpoint address="" behaviorConfiguration="NewBehavior1" binding="webHttpBinding"
      bindingConfiguration="" name="File" contract="ALMWCFHost.FileProvider.IFileService" />
    <host>
      <baseAddresses>
        <add baseAddress="http://localhost:8021/Files/" />
      </baseAddresses>
    </host>
  </service>

You will also have to create a new endpoint behavior

<endpointBehaviors>
<behavior name="NewBehavior1">
    <webHttp />
</behavior></endpointBehaviors>

This will create a webHttp service.

Now you will create some endpoints:

[OperationContract, WebGet(UriTemplate = "GetFile/{filename}")]

    Stream GetFile(string filename);

    [OperationContract, WebGet]
    Stream GetYCFile(string date, string type, string format);

    [OperationContract, WebGet(UriTemplate = "GetFiles/Files.zip?filenames={querystring}")]
    Stream GetFiles(string querystring);

Any query string you pass with the address will be parsed to the variables.

Then on the silverlight client side I simply created a custom URI (In your case, lets say the file is called 'NewReport.xls') that points to the call 'GetFile/{filename}', then your Uri will look something like http://localhost/FileService.svc/GetFile/NewReport.xls

Now all you do is navigate to that URL with something like HtmlPage.Window.Navigate(page, "_self");. That will allow you to download the file, and still stay on the same Silverlight page as you were.

Answer 2

If you have ASP.NET available you could create a HTTP handler which you could call using the HTML bridge in Silverlight which would take your report parameters in the querystring. Then generate the report and send it back to the client.

string printUrl = string.Format("createxlsreport.ashx?param1={0}", param1);
HtmlPage.Window.Navigate(new Uri(printUrl, UriKind.Relative), "_blank", "toolbar=yes,location=no,status=no,menubar=no,resizable=yes");

Send the file back to the client:

private void SendFileToClient(byte[] file, string fileName)
{
    HttpResponse Response = HttpContext.Current.Response;
    Response.Clear();
    Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
    // Add a HTTP header to the output stream that contains the 
    // content length(File Size). This lets the browser know how much data is being transfered
    Response.AddHeader("Content-Length", file.Length.ToString());
    // Set the HTTP MIME type of the output stream
    Response.ContentType = "application/vnd.ms-excel";
    // Write the data out to the client.
    Response.BinaryWrite(file);
    Response.End();
}