Using Spreadsheet control to export data from Database

[Keith]

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

[Maxson Junior]

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.

[Maxson Junior]

Administrator,

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

Admin: Done.

[Keith]

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.

 

[Maxson Junior]

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.

[Keith]

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

Please help!

Sincerely,

Keith Jackson

[Keith]

I have found a better way to do this using SpreadProcessing instead of the Spreadsheet control.

[Julie]

Can you show your example of how you use spreadprocessing to do this?

[Peter Milchev]

Hello Julie,

You can check the following Feature Request and use the explanations shared there:

• https://feedback.telerik.com/document-processing/1356067-spreadprocessing-introduce-conversion-from-datatable-to-radworkbook-radworksheet-and-vice-versa

Regards,
Peter Milchev
Progress Telerik