Export to Excel from a LightSwitch HTML client

Question

Because a lot of the LightSwitch posts in the web are related to the deprecated silverlight desktop applications and not HTML 5 client applications, I hope you can help me to get the current technology status right, here.

My target:

• export data from a query (handling parameters) to a rather complex (multiple sheets, maybe predefined charts) Excel spreadsheet
• as little code as possible (means: I do not care to develop a datastream and construct HTML headers and stuff like this, but it would be nice to have this in a library)
• possibility to use templates and just fill it with data
• Visual Studio 2013 compatible
• No additional installations on the user side: the user should just be able to download an Excel-File
• server/development environment is IIS 7, MS SQL, Microsoft Visual Studio 2013

I found:

• Office Integration Pack -> [NO GO] Stackoverflow post: it is deprecated for web browser apps
• Microsoft Report Viewer -> looks good, but have to try it out to see if it works for templates
• SQL Server Reporting Service -> only found old posts, probably not the quickest option for the HTML client
• Telerik Reporting -> [NO GO] probably not supporting HTML clients anymore
• Active Reports example -> looks to be actively developed, may be worth an investment (it is commercial)

• DevExpress -> Would have been one of my favorite choices (from what I heard) but they do not support LightSwitch HTML client, and it seems like they in general do not continue LightSwitch support at all.

• OpenXML -> impressiv OpenSource project, allows templates, needs some coding efforts but looks trustworthy

• SpreadsheetLight (based on OpenXML)
• EPPlus (based on OpenXML) -> did not find any LightSwitch examples, but probably nice on the server side

My thoughts: Currently I am heading for OpenXML:

• active project
• documentation available (up-to-date examples)
• read somewhere about templates

Thank you, if you know other ways to go, or let me know if have experiences with one of those tools.

regards Spikey

Answer 1

I have used SQL Reporting Services, and it is a little long winded to set up as you stated but once you have it is very useful with LightSwitch HTML

from Lightswitch itself when you have generated the report I use the following code to download an exact version of the report to a PDF format. this can be done for excel files to. this is just on the execute code of a button

var InvoiceNum = screen.Invoice.InvoiceID;
window.open("http://yourservername/ReportServer_databasename/Pages/ReportViewer.aspx?%2fInvoice&rs:Command=Render&InvoiceID=" + InvoiceNum + "&rs:Format=PDF");

this passes the relevant information straight from the database via Lightswitch and to the report. remove this: "&rs:Format=PDF"); if you wish to view the report in the browser window. This way you can choose to save the report to which ever format you want. I thought I would include that extension mind as I found it extremely useful.

be sure to note that the /ReportServer_databasename is essential for download them as I found out. using just Reports_databasename means that the extension on the end does not work.

link to file extentions: http://msdn.microsoft.com/en-gb/library/ms154040.aspx

hope this helps.

Answer 2

I found this blog post (not a member of MSDN so unfortunately was not able to vote up) https://hgminerva.wordpress.com/2012/05/28/how-to-export-data-to-csv-using-microsoft-lightswitch-web-application/

It solved the problem beautifully. There was an updated version also posted in C# here: https://social.msdn.microsoft.com/Forums/vstudio/en-US/e5f0d29d-5133-4297-8d59-33a208bda903/removing-controlavailable-after-adding-controlavailable?referrer=http://social.msdn.microsoft.com/Forums/vstudio/en-US/e5f0d29d-5133-4297-8d59-33a208bda903/removing-controlavailable-after-adding-controlavailable?forum=lightswitch

The 3rd answer down also solves a small issue with exporting multiple pages.

ALL credit to hgminerva for the original VB and Otis Ranger for the update and C# version