1. Home
  2. Forum
  3. ASP.NET MVC
  4. VB Export grid to excel

VB Export grid to excel

I've got a grid which is populated from an external API, which displays on screen fine. I'm trying to activate the button to export this to Excel.

problem 1 : When the export button is clicked, it tries to redirect to a page /ExportToExcel instead of the url /Invoice/ExportToExcel

problem 2 : the ExportToExcel function example you give on your website is in c#, I've tried writing it in VB but i'm getting 'Syncfusion.gridexcelconverter' and 'GridProperties' as known objects

problem 3 : the datasource appears to link directly back to the database - I can't do that; I pass the API's results to the form via a viewbag, but I can run the API query again if necessary in the controller, as long as I have a way of passing the details through.

controller imports :
Imports System.Web.Mvc
Imports System.Net
Imports Syncfusion.EJ.export
Imports Syncfusion.EJ
Imports Syncfusion.Linq
Imports Syncfusion.Compression
Imports Syncfusion.XlsIO

controller exporttoexcel function
Function ExportToExcel( GridModel as string )
        Dim gecc = New Syncfusion.gridexcelconverter
        dim exp = new ExcelExport()
        Dim  DataSource = new NorthwindDataContext().OrdersViews.ToList()
        dim obj =Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel)
        exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron")
 End Function

and on the page grid
            Dim gridbuilder = Html.EJ().Grid(Of NFSInvoiceHeader)("InvoiceGrid")
            gridbuilder.Datasource(ViewBag.InvoiceList)
            gridbuilder.ToolbarSettings( Sub (toolBar) toolBar.ShowToolbar().ToolbarItems (
                Sub (items)
                    items.AddTool(ToolBarItems.ExcelExport )
                End Sub))
            gridbuilder.Columns(
    Sub(col)
        col.Field("invDate").Format("{0:dd/MM/yyyy}").HeaderText("Date").Width(10).Add()
        col.Field("invNumber").HeaderText("Number").Width(20).Add()
        col.Field("invNet").HeaderText("Net").Width(20).Add()
        'col.Field("invVAT").HeaderText("VAT").Width(30).Add()
        'col.Field("invGross").HeaderText("Gross").Width(20).Add()
        col.Field("invNumber").HeaderText("View").Width(20).Add()
    End Sub)
            gridbuilder.AllowGrouping()
            gridbuilder.AllowSorting()
            gridbuilder.Render()
            gridbuilder.AllowPaging()

Can anyone point me in the right direction?

thanks


4 Replies
SE Sathyanarayanamoorthy Eswararao Syncfusion Team March 22, 2018 04:27 PM UTC

Hi Ross, 

Thanks for contacting Syncfusion support. 

Query 1: When the export button is clicked, it tries to redirect to a page /ExportToExcel instead of the url /Invoice/ExportToExcel 
 
By default when export button is clicked then ExportToExcel method is called. If you want to use the custom method for Exporting then we suggest you to use the Mappers property of grid. Please refer the below documentation for details of Mappers property of Grid. 


Query 2: I've tried writing it in VB but i'm getting 'Syncfusion.gridexcelconverter' and 'GridProperties' as known objects. 

We are able to reproduce the issue if we use the provided code example. To avoid this we suggest you to use the GetType method instead of TypeOf method for getting the GridProperties in the Exporting method. Also please share the purpose of using 'Syncfusion.gridexcelconverter'. 
 
Refer the below code example. 
 
   Function ExportToExcel(GridModel As String) 
            Dim exp As ExcelExport = New ExcelExport() 
            Dim DataSource = New NorthwindDataContext().OrdersViews.ToList() 
            Dim obj = Syncfusion.JavaScript.Utils.DeserializeToModel(GetType(GridProperties), GridModel) 
            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, False, False, "flat-saffron") 
        End Function 
 

Query 3: the datasource appears to link directly back to the database - I can't do that; I pass the API's results to the form via a viewbag, but I can run the API query again if necessary in the controller, as long as I have a way of passing the details through. 
 
We are quite unclear about this query. Please provide the following details. 

  1. Do you need to pass the dataSource details of grid to the controller side.
  2. Do you need to bind the data locally to the grid instead of fetching from the database.
  3. Share the exact scenario of your requirement.

The provided details will help us to analyze the issue and provide the solution as soon as possible. 

Regards, 
Sathyanarayanamoorthy 


RW Ross Woodward March 23, 2018 07:03 PM UTC

Hi there,

clarifying point 3 - we retrieve the data from an API using a 3rd party website. we then have that data in a model that we pass down to the grid control.

Therefore, when we export to excel, we need ideally to use the data that's already within the grid rather than calling the API again - if possible. I cannot get direct access to the database - it HAS to go via the API.

datasource -> API (get list of invoice details for example -> model within controller -> razor grid -> model within export controller -> excel
                                                                                                 
does this make more sense?

ross 

RW Ross Woodward March 23, 2018 07:36 PM UTC

sorry, trying your suggestion
dim obj =Syncfusion.JavaScript.Utils.DeserializeToModel(GetType(GridProperties), GridModel)

still leads to type GridProperty is undefined

I've got these imports :

'these are bold so assigned here
Imports System.Net
Imports Syncfusion.XlsIO
Imports Syncfusion.EJ.export

'these are greyed out so inherited
Imports Syncfusion.XlsIOBaseAssembly
Imports Syncfusion.EJ
Imports Syncfusion.Linq
Imports Syncfusion.Compression
Imports Syncfusion.JavaScript.EJTagHelper
Imports System.Web.Mvc

am I missing one?

my revised function is currently
     Function ExportToExcel( GridModel as string )
        dim exp = new ExcelExport()
        'Dim  DataSource = new NorthwindDataContext().OrdersViews.ToList()
        Dim url, json As String
        url = GlobalVariables.NFS_API_URL & "api/NFSAPI/GetInvoiceList?sessionID=" & Session("sessionID") & "&conID=" & collection("conID")
        Using client As New WebClient()
            json = client.DownloadString(url)
        End Using
        Dim myInvoiceList As List(Of NFSInvoiceHeader)
        myInvoiceList = Newtonsoft.Json.JsonConvert.DeserializeObject(Of List(Of NFSInvoiceHeader))(json)
        ViewBag.InvoiceList = myInvoiceList
  dim obj =Syncfusion.JavaScript.Utils.DeserializeToModel(GetType(GridProperties), GridModel)
  exp.Export(obj, myInvoiceList, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron")
 End Function

Ross.

SE Sathyanarayanamoorthy Eswararao Syncfusion Team March 26, 2018 05:12 PM UTC

Hi Ross, 

Query: GridProperty is undefined 

In your code example we found that you have missed the Syncfusion.Javascript.Models namespace in the controller. 
To use the GridProperties in the controller you have to import the Syncfusion.Javascript.Models namespace. 

Query: when we export to excel, we need ideally to use the data that's already within the grid rather than calling the API again - if possible 
 
We already have a Knowledge Base documentation for this query. Please refer the below link for the Documentation. 


In the above KB documentation we have passed only the current page records and retrieved them in the server side while exporting. We suggest you to use the dataSource of the grid as per your requirement. Refer the below code example for the changes that need to be made in the code of KB. 


 
<script> 
    function OnToolbarClick(args) { 
        if (args.itemName.indexOf("Export") > -1) {//if no selectedRecords, currenviewdata will be exported 
            this.model["currentData"] = JSON.stringify(this.model.dataSource); 
        } 
    } 
</script> 
 

If you need any further assistance please get back to us. 

Regards, 
Sathyanarayanamoorthy 




Loader.
Up arrow icon