Export to Excel Functionality in LightSwitch HTML client (VS 2013)

Question

Hi team,

i want Export to Excel Functionality in LightSwitch HTML client (VS 2013). All export to excel functionalities mentioned in forum are only related to LS desktop app. Please anyone help me.

Scenario:

I have one browse page with Export to excel Button, if user click on that button i need to export the data in the browse screen to Excel. Is that possible in LS HTML web app in VS 2013 ?

---

Srikanth Anantharaman

Answer 1

HI Srikanth,

If you want to export to Excel  in LightSwitch Html Client, you could have a try use Excel Importer Extension, generally it's not recommanded to do that. Here is our previous discussion about this topic:LightSwitch HTML Client: How to export to Excel or Word

You can also follow the demo Michael mentioned in the link above.

Best regards,

Angie

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Angie thanks for your reply, but i am using LS 2013 HTML app, for which the button execute operation need to write on the JS, but in the above mentioned sample link they are mentioned on writing the server side and mostly in VS 2012 desktop web client which will not be compatible in 2013 HTML client.

---

Srikanth Anantharaman

Answer 3

Hi,
I use a generic handler for this kind of stuff. I have Office installed on the IIS. If you don't want to install Excel on the Server (it's not supported but for smaller Environments it's working very well) You can use the Excel DLL from Syncfusion or use the XML sdk.

I hope this helps you to find a start. Please let me know if you have furter questions.,

Kind Regards

Thomas

Answer 4

Hi Thomas,

The XML sdk is fully mentioned for Microsoft word, not for microsoft Excel. Do you have any link that is using XML sdk for Excel. It will be very helpful for me, since LS is very new to me. (sorry for the too much dumb reply)

---

Srikanth Anantharaman

Answer 5

Hi Srikanth,
we have 2 things to differentiate. The LS piece is the generic handler. See the code below. The second piece is the Excel SDK I found a code Project here. I insert some code for the LS and the generic handler. The Excel XML code I cannot deliver, because I use Office on the Server.

LS:
In your browse Screen add a button with an own function and insert this snippet:

myapp.AddEditAngebotSetItem.Export2Excel_execute = function (screen) {
    var paraAngebotID = screen.AngebotSetItem.Id;
    //http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/174/Full-Control-LightSwitch-ServerApplicationContext-And-Generic-File-Handlers-And-Ajax-Calls.aspx
    myapp.commitChanges().then(null, function red(e) {
        throw (e);
        return;
    }).then(function (op) {
        $.ajax({
            type: 'post',
            data: {
                pAngebotID: paraAngebotID,
            },
            url: '../WebRequests/Export2Excel.ashx',
            success: function success(result) {
                alert(result);
            }
        });
    })
};
Within the above text there are a few links which explain the Details.

Second Step. Append a ASHX (Generic File) to your Project. This article explains this in more Detail.

In the ASHX File insert your code with wrting the Excel File:

    Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
'Create your Excel context
        Dim str As String = "Hier ging was schief"
Dim Yourparameter = context.Request.Params("pAngebotID")
        Using LSContext As ServerApplicationContext = ServerApplicationContext.CreateContext()
            Try
dim othingstobringtoExcel = from o as AngebotsItem in Context.Dataworkspace.Data.Projekt where o.id = Yourparameter 

for each element in othingstobringtoExcel 
'insert to Excel cell
next
            Catch ex As Exception
                str = "Something went wrong " + ex.Message
            Finally
            End Try
        End Using

'Close excel
        context.Response.ContentType = "text/plain"
        context.Response.Write(str)
End

I hope this clarifies a few things.

Kind Regards

Thomas

Answer 6

Angie/Thomas,

My API controller is 

using Microsoft.LightSwitch;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Web.Http;

namespace LightSwitchApplication
{
    public class ReportController : ApiController
    {
        private static readonly MediaTypeHeaderValue _mediaType = MediaTypeHeaderValue.Parse("text/csv");

        // GET api/<controller>
        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        // GET api/<controller>/5
        public string Get(int id)
        {
            return "value";
        }

        // POST api/<controller>
        public void Post([FromBody]string value)
        {
            
        }

        // PUT api/<controller>/5
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE api/<controller>/5
        public void Delete(int id)
        {
        }

        [HttpGet]
        public HttpResponseMessage FilterEmployeeName(string empName)
        {
            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            MemoryStream memStream = new MemoryStream(encoding.GetBytes(GetEmployeeNames(empName).ToString()));
            HttpResponseMessage fullResponse = Request.CreateResponse(HttpStatusCode.OK);
            fullResponse.Content = new StreamContent(memStream);
            fullResponse.Content.Headers.ContentType = _mediaType;
            string fileName = String.Format("data-{0}.csv", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
            fullResponse.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("fileName") { FileName = fileName };
            return fullResponse;
        }

        public static StringBuilder GetEmployeeNames(string empName)
        {
            StringBuilder stringBuilder = new StringBuilder();

            using (ServerApplicationContext ls = ServerApplicationContext.CreateContext())
            {
                var query = ls.DataWorkspace.ApplicationData.Employees.Where(c => c.Name.Contains(empName));
                foreach (string name in query)
                {
                    stringBuilder.AppendLine("testline");
                }
            }

            return stringBuilder;

        }

        public bool IsReusable
        {
            get { throw new NotImplementedException(); }
        }

    }
}

I want toroute tp 'FilterEmployeeName' method from my Client side. in the above link you have given they have mentioned it for Server side redirect.

so below is the code referred in the example,

partial void CustomersByLastNameViaWebApi_Execute()
        {
            string lastNameSearchParam = this.ShowInputBox("Give Last name", "Create Report");

            Dispatchers.Main.Invoke(() =>
            {
                Uri baseAddress = LightSwitchCommandProxy.GetBaseAddress();

                string url = baseAddress.AbsoluteUri +  @"api/Report/CustomersByLastName/?lastName=" + lastNameSearchParam;
                HtmlPage.Window.Navigate(new Uri(url), "_blank");
            });
        }

i want to work it out in the same way for my client side JS button click,

myapp.BrowseEmployeeFilter.ExportToExcel_execute = function (screen) {

    var filter = "name1";
    var clientUrl = '@Url.RouteUrl("ApiControllerAction", new { httproute="", controller = "Client", action = "clients" id='+filter.toString+' })';
    alert(clientUrl);
//Tried this but not working for me
};

and i have added my controller in the server project,

please suggest some idea how to redirect to Webapi controller method from my JS client side.

---

Srikanth Anantharaman

Answer 7

Hello Srikanth,
using the WebApi Controller is very good described in the article (please look after part 1 and 2 aswell)  and you will find some more around. The above article is for Word I know but the way using this is the same.

Hope this helps
Kind Regards

Thomas

Answer 8

Sorry for the delayed reply and thanks for the suggestions Thomas. I have implemented the same and its working fine now. 

---

Srikanth Anantharaman