This is a migrated thread and some comments may be shown as answers.

Using Spreadsheet control to export data from Database

8 Answers 316 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Keith
Top achievements
Rank 1
Keith asked on 23 Oct 2015, 07:58 PM

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

8 Answers, 1 is accepted

Sort by
0
Maxson Junior
Top achievements
Rank 2
answered on 26 Oct 2015, 08:14 AM

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.

0
Maxson Junior
Top achievements
Rank 2
answered on 26 Oct 2015, 08:19 AM

Administrator,

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

Admin: Done.

0
Keith
Top achievements
Rank 1
answered on 26 Oct 2015, 12:09 PM

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.

 

0
Maxson Junior
Top achievements
Rank 2
answered on 27 Oct 2015, 02:40 AM

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.

0
Keith
Top achievements
Rank 1
answered on 28 Oct 2015, 02:31 PM

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

Please help!

Sincerely,

Keith Jackson

0
Keith
Top achievements
Rank 1
answered on 30 Oct 2015, 06:08 PM
I have found a better way to do this using SpreadProcessing instead of the Spreadsheet control.
0
Julie
Top achievements
Rank 1
answered on 29 Apr 2020, 06:41 PM
Can you show your example of how you use spreadprocessing to do this?
0
Peter Milchev
Telerik team
answered on 04 May 2020, 12:12 PM

Hello Julie,

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

Regards,
Peter Milchev
Progress Telerik

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
Tags
Spreadsheet
Asked by
Keith
Top achievements
Rank 1
Answers by
Maxson Junior
Top achievements
Rank 2
Keith
Top achievements
Rank 1
Julie
Top achievements
Rank 1
Peter Milchev
Telerik team
Share this question
or