Using Spreadsheet control to export data from Database

7 posts, 0 answers
  1. Keith
    Keith avatar
    102 posts
    Member since:
    Mar 2015

    Posted 23 Oct 2015 Link to this post

    I have developed a web-based application using ASP.Net for the company I work for.

    I would like to make use of the new Spreadsheet control to export data from a database to an Excel .xlsx file on the client-side.  No need to view the data in the spreadsheet in the web browser.  Just would like to use the Spreadsheet control to export data from a database to an Excel .xlsx file on the client-side when the user clicks on a button on the ASP.Net page.

    Can that be done?

    Can you provide an example on how to do that with VB.Net as the code-behind?

    Sincerely,

    Keith Jackson

  2. Maxson Junior
    Maxson Junior avatar
    12 posts
    Member since:
    Oct 2013

    Posted 26 Oct 2015 Link to this post

    Use Kendo-UI instead of ASP.NET AJAX.

    ASP.NET AJAX won't work at this moment. (I believe...)

    In the example below, the database I use is MySQL.

     Default.aspx

    <script type="text/javascript" src="kendo/js/jquery.min.js"></script>
    <script type="text/javascript" src="JSZip/jszip.min.js"></script>
    <script type="text/javascript" src="kendo/js/kendo.all.min.js"></script>
    <link rel="stylesheet" type="text/css" href="kendo/styles/kendo.common.min.css" />
    <link rel="stylesheet" type="text/css" href="kendo/styles/kendo.default.min.css" />

    <div id="spreadsheet" style="width: 100%;"></div>
            <script>
                $(function () {
                    var dS = new kendo.data.DataSource({
                        transport: {
                            read: {
                                url: 'Handler1.ashx',
                                dataType: 'json',
                                type: 'POST',
                                contentType: 'application/json'
                            }
                        }
                    });
                    $("#spreadsheet").kendoSpreadsheet({
                        columns: 20,
                        rows: 100,
                        sheets: [{
                            name:"XXX",
                            dataSource: dS,
                            rows: [{
                                height: 40,
                                cells: [
                                    { bold: "true", background: "#9c27b0", textAlign: "center", color: "white" },
                                    { bold: "true", background: "#9c27b0", textAlign: "center", color: "white" }
                                ],
                            }],
                            columns: [
                                { width: 250 },
                                { width: 250 }
                            ]
                        }]
                                 
                    });
                  
            });
        </script>

     

    Handler1.ashx

    <%@ WebHandler Language="C#" Class="Handler1" %>
     
    using System;
    using System.Web;
    using System.Web.Script.Serialization;
    using System.Collections.Generic;
    using MySql.Data.MySqlClient;
    using System.Data;
     
    public class Handler1 : IHttpHandler {
         
        public void ProcessRequest (HttpContext context) {
            context.Response.ContentType = "text/json";
            List<AData> dtCollection = new List<AData>();
            MySqlConnection db = new MySqlConnection();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable dt = new DataTable();
            db.ConnectionString = "Data Source=localhost0;Port=3306;Database=wsdems;User Id=sa;Password=password";
            cmd.Connection = db;
            cmd.CommandText = "Select name,account_no from asset order by name";
            da.SelectCommand = cmd;
            da.Fill(dt);
            AData data = new AData();
            for (int i = 0; i < dt.Rows.Count; i++) {
                data = new AData();
                data.Name = dt.Rows[i][0].ToString();
                data.Account_Number = dt.Rows[i][1].ToString();
                dtCollection.Add(data);
            }
            JavaScriptSerializer oSer = new JavaScriptSerializer();
            String sJson = oSer.Serialize(dtCollection);
            context.Response.Write(sJson);
        }
      
        public bool IsReusable {
            get {
                return false;
            }
        }
     
    }

    AData.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
     
    /// <summary>
    /// Summary description for Data
    /// </summary>
    public class AData
    {
        public AData()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public string Name
        {
            get;
            set;
        }
        public string Account_Number
        {
            get;
            set;
        }
    }

    The result like the image. The data is loaded from MySQL database first. The xlsx file can be exported by clicking the export button on the toolbar.

  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Maxson Junior
    Maxson Junior avatar
    12 posts
    Member since:
    Oct 2013

    Posted 26 Oct 2015 Link to this post

    Administrator,

     Could you mind deleting the password of connection string from my latest reply? Thanks.

    Admin: Done.

  5. Keith
    Keith avatar
    102 posts
    Member since:
    Mar 2015

    Posted 26 Oct 2015 in reply to Maxson Junior Link to this post

    Thanks Maxson but I would rather not make a major change to using Kendo UI.  Besides in your example image, it is still displaying the spreadsheet in the browser which is not what I am looking for.

     

  6. Maxson Junior
    Maxson Junior avatar
    12 posts
    Member since:
    Oct 2013

    Posted 26 Oct 2015 in reply to Keith Link to this post

    Sorry, the method i mention does not meet your requirement. But i think the small trick can be done for you. (I do not test it myself).

    1. Using the method I mention before.

    2. set the spreadsheet style "display:none" or very small.

    3. add a button which action ​is the Spreadsheet client-side API (the saveAsExcel method) for initiating export via JavaScript

    Oh, but if you don't want to chage to use Kendo-UI. Sorry for the interception.

  7. Keith
    Keith avatar
    102 posts
    Member since:
    Mar 2015

    Posted 28 Oct 2015 Link to this post

    I still need help with this.  I am looking for a way to do this WITHOUT using Kendo UI.

    Please help!

    Sincerely,

    Keith Jackson

  8. Keith
    Keith avatar
    102 posts
    Member since:
    Mar 2015

    Posted 30 Oct 2015 in reply to Keith Link to this post

    I have found a better way to do this using SpreadProcessing instead of the Spreadsheet control.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017