Implementing jTable jquery plugin on my mojoportal site and I wanted to utilize the Excel download feature

I ended up using http://closedxml.codeplex.com/  after reading this blog.

http://jgreisen.blogspot.com/2012/04/export-to-excel-from-datatable-in-web.html

jTable is using webmethods for everything and no postbacks and the examples in the blog use a postback event from an asp:button.  I wanted to use the function provided inherent to jTable to start the Excel download.

What I ended up doing was using a hidden textbox to fire a 'ValueChanged' event which will run the event needed at the server to create the excel file and stream it to the response object based off of this discussion on Stackoverflow

http://stackoverflow.com/questions/8327359/how-to-trigger-asphiddenfield-onvaluechanged-event-using-jquery

Since I am developing a mojoportal module which is an ascx file there is the complexity of searching for the textbox using id$=

So in the ascx HTML I added

<asp:TextBox runat="server" ID="hdntextbox" Value="" Style="display:none;" AutoPostBack="true" OnTextChanged="hdntextbox_TextChanged"></asp:TextBox>

And  then in the js file I added this into my jTable like explained in the API http://www.jtable.org/ApiReference#genopt-toolbar

​I used Math.random to make sure that the textbox value changed on every call.

toolbar: {
		items: [{
		icon: '/Data/jtable.2.3.0/excel.png',
		text: 'Export to Excel',
		click: function () {
									 
		$('input[id$=hdntextbox]').val(Math.random()).change(); 
									
			}
						
			}]
	},

On the server side taken from the blog above and using mojoportals datalayers with a 'Client' object:

Added init:

this.hdnExcel.ValueChanged += new EventHandler(HiddenExcel_Changed);

...

protected void HiddenExcel_Changed(object sender, EventArgs e)
        {

            HiddenField txt = (HiddenField)sender;
            ClientRepository ccr = new ClientRepository();
            List cl = new List();
            DataTable dt = new DataTable();
            string export = "client";
            try
            {

                switch (export)
                {

                    case "client":
                        {
                            cl = ccr.GetAll();
                            dt = GetClientDt(cl);

                            exportExcel(dt, "Client List");

                            break;
                        }

                    default:
                        // You can use the default case.

                        break;
                }
            }
            catch (Exception ex)
            {
                string exp = ex.ToString(); //Setup a breakpoint here to verify any exceptions raised.
            }
  
        }

You can see I added the switch in for the future if you want to pass in another parameter to create a different spreadsheet.

I created a function to make a DataTable out of the mojoportal data object based on this blog:  http://beyondtechs.blogspot.com/2013/03/convert-object-into-datatable-c.html

private static DataTable GetClientDt(List clients)
    {
        DataTable dt = new DataTable();

        try
        {
            dt.TableName = "Clients";

            foreach (PropertyInfo property in clients[0].GetType().GetProperties())
            {
                dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));
            }

            foreach (var client in clients)
            {
                DataRow newRow = dt.NewRow();
                foreach (PropertyInfo property in client.GetType().GetProperties())
                {
                    newRow[property.Name] = client.GetType().GetProperty(property.Name).GetValue(client, null);
                }
                dt.Rows.Add(newRow);
            }
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
    }

Note: this will return all the ID's and everything.  You can adjust this function to only return the 'Client Name' for example:

Then finally the Excel export function.

private static void exportExcel(System.Data.DataTable data, string reportName)
    {
        var wb = new XLWorkbook();

        // Add DataTable as Worksheet
        wb.Worksheets.Add(data);

        // Create Response
        HttpResponse response = HttpContext.Current.Response;

        //Prepare the response
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        response.AddHeader("content-disposition", "attachment;filename=" + reportName + ".xlsx");

        //Flush the workbook to the Response.OutputStream
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(response.OutputStream);
            MyMemoryStream.Close();
        }

        response.End();
    }

You will need references to:

• DocumentFormat.OpenXml
• ClosedXML

and imports

using ClosedXML.Excel;

Anyway, hope this helps someone out.