Jquery, C#, ASP.Net, mojoportal Adventures

jquery, jTable Excel download, ASP.Net, C#, mojoportal 

Posted by run8tech Wednesday, September 11, 2013 3:01:00 PM Categories: jquery jtable web services

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.