Excel in ASP.NET using Gridview

Posted by: rsayers on 10 September 2017, 11:42 am EST

  • Posted 10 September 2017, 11:42 am EST

    I am attempting to import an excel spreadsheet into a C1WebGrid. I have used the example from another forum topic, http://our.componentone.com/groups/asp-net/studio-for-asp-net/forum/topic/excel-for-asp-net/, to no avail. I have set the AutoGenerateColumns="true" in the .aspx and coded the example's GetDataTable and LoadSpeadSheet functions. I've also coded the grid's DataBound event to add the columns of the datatable to the grid. However, the grid never displays on my web page. Do you have a complete code sample using C1.C1Excel.2 and C1.Web.C1WebGrid.2?

    Thank you for your time and effort,
    Rodney
  • Replied 10 September 2017, 11:42 am EST

    Hello Rodney,

    Unfortunately we officially ended support on March 16th 2011 of the C1WebGrid :
    http://www.componentone.com/SuperPages/PressReleases/?newsID=45

    But we do support C1GridView (C1WebUI and Wijmo). I am attaching a sample for loading an excel file in Wijmo grid. You can use same code for C1GridView (C1WebUI).

    Let me know in case you have further queries.

    Thanks,
    Richa
    2011/11/Sample_WijGrid.zip
  • Replied 10 September 2017, 11:42 am EST

    Richa,

    Thank you for the reply and the example. I have tried the example you provided using the C1GridView with no success.

    The following is the aspx markup...

    <%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/DCI.Web.Core.Master" CodeBehind="CRD0210.aspx.vb" Inherits="DCI.Web.Core.CRD0210" %>
    <%@ Register Assembly="C1.Web.UI.Controls.3" Namespace="C1.Web.UI.Controls.C1GridView" TagPrefix="C1gv" %>





    ...(I've remove a large unrelated portion)...

    Collapsed="false" CollapsedImage="<%$ Image: CollapseBottom %>" ExpandedImage="<%$ Image: CollapseTop %>"
    CollapseControlID="imgDepartmentPanel" ExpandControlID="imgDepartmentPanel" ImageControlID="imgDepartmentPanel"
    SuppressPostBack="True">







    EXCEL

    ImageUrl="<%$ Image: CollapseTop %>" ToolTip="Show/Hide Department panel" />







    ScrollSettings-ScrollMode="ScrollBar" ScrollSettings-ScrollOrientation="Both">





    The following is the VB code behind...

    this is in a button function...
    gvDepartments.DataSource = GetDataTable()
    gvDepartments.DataBind()

    Public Function GetDataTable() As DataTable
    Try
    xlBook = LoadSpreadSheet(Server.MapPath("Book1.xlsx"))
    XLSheet = xlBook.Sheets(0)

    dtSheet = New DataTable(XLSheet.Name)
    Dim intColumn As Integer = 0

    For i As Integer = 0 To XLSheet.Columns.Count - 1
    'The fourth row contains the column titles...
    If XLSheet(3, i).Value IsNot Nothing Then
    If XLSheet(3, i).Value.ToString.Trim.Length > 0 Then
    dtSheet.Columns.Add(XLSheet(3, i).Value.ToString)
    Else
    intColumn += 1
    dtSheet.Columns.Add("Column " + intColumn.ToString)
    End If
    End If
    Next

    For i As Integer = 0 To XLSheet.Rows.Count - 1
    Dim drSheet As DataRow = dtSheet.NewRow
    For c As Integer = 0 To dtSheet.Columns.Count - 1
    If xlSheet.GetCell(i, c) IsNot Nothing AndAlso _
    xlSheet.GetCell(i, c).Value IsNot Nothing AndAlso _
    xlSheet.GetCell(i, c).Value.ToString.Trim.Length > 0 Then
    drSheet(c) = xlSheet.GetCell(i, c).Value.ToString
    Else
    drSheet(c) = String.Empty
    End If
    Next
    dtSheet.Rows.Add(drSheet)
    Next
    Catch ex As Exception
    GlobalException.ThrowException(Me.ToString, System.Reflection.MethodBase.GetCurrentMethod.Name, Nothing, ex)
    End Try
    End Function
    Public Function LoadSpreadSheet(ByVal strPath As String) As C1XLBook
    Try
    xlBook = New C1XLBook
    xlBook.Load(strPath, FileFormat.OpenXml, True)
    Catch ex As Exception
    GlobalException.ThrowException(Me.ToString, System.Reflection.MethodBase.GetCurrentMethod.Name, Nothing, ex)
    End Try
    Return xlBook
    End Function

    Follows is a Firebug representation after the page is rendered...

























    I've also attached the spreadsheet I'm trying to load.

    As you can see, the column group is empty.
    What could I be doing incorrectly?
    Thank you, again, for you time and effort,
    Rodney
    2011/11/Book1.xlsx
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels