locked
Importing Data from Excel to SQL Server Using EPPlus RRS feed

  • Question

  • User766478441 posted

    I'm trying to use the EPPlus package in a ASP.NET (VB.NET) project and having trouble converting the code from C# to VB and getting the error "'ToDataTable' is not a member of 'OfficeOpenXml.ExcelPackage'. I put the namespace file in the App_Code folder is that the right place? I don't why my code can't find the ExcelPackageExtensions class and ToDataTable function. 

    .aspx.vb page:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        If FileUpload1.HasFile Then
    
            If Path.GetExtension(FileUpload1.FileName) = ".xlsx" Then
                Dim package As ExcelPackage = New ExcelPackage(FileUpload1.FileContent)
                GridView1.DataSource = package.ToDataTable()
                GridView1.DataBind()
            End If
        End If
    End Sub

    aspx page:

    <%@ Page Title="PCA Complaints" Language="VB" MasterPageFile="~/Site.master" AutoEventWireup="true" 
    CodeFile="Default.aspx.vb" Inherits="EPPlus_Code" %>
    
    
    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
    <asp:FileUpload ID="fileUpload1" runat="server" CssClass="btn-primary btn-sm" />
    
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    CssClass="table table-hover table-striped table-bordered table-condensed GridView">
    </asp:GridView>

    I put this class file in App_Code folder and not sure it belongs there.

    Imports OfficeOpenXml
    Imports System.Data
    Imports System.Linq
    Imports System.Runtime.CompilerServices
    
    Public Class ExcelPackageExtensions
    
        Public Shared Function ToDataTable(ByVal package As ExcelPackage) As DataTable
            Dim workSheet As ExcelWorksheet = package.Workbook.Worksheets.First
            Dim table As DataTable = New DataTable
            For Each firstRowCell In workSheet.Cells(1, 1, 1, workSheet.Dimension.End.Column)
                table.Columns.Add(firstRowCell.Text)
            Next
            Dim rowNumber = 2
            Do While (rowNumber <= workSheet.Dimension.End.Row)
                Dim row = workSheet.Cells(rowNumber, 1, rowNumber, workSheet.Dimension.End.Column)
                Dim newRow = table.NewRow
                For Each cell In row
                    newRow((cell.Start.Column - 1)) = cell.Text
                Next
                table.Rows.Add(newRow)
                rowNumber = (rowNumber + 1)
            Loop
    
            Return table
        End Function
    End Class
    

    Thursday, December 10, 2020 1:03 AM

All replies

  • User1535942433 posted

    Hi evanburen,

    Accroding to your description,package havn't the method of ToDataTable().You could do this:

    Replace:

    GridView1.DataSource = package.ToDataTable()

    To:

    GridView1.DataSource = ExcelPackageExtensions.ToDataTable(package)

    And the ExcelPackageExtensions class file isn't  unnecessary in the App_Code folder.

    Best regards,

    Yijing Sun

    Thursday, December 10, 2020 2:54 AM
  • User766478441 posted

    Thanks for the assistance. I changed it to 

    GridView1.DataSource = ExcelPackageExtensions.ToDataTable(package)

    but still getting the error message "BC30451: 'ExcelPackageExtensions' is not declared. It may be inaccessible due to its protection level.

    Thursday, December 10, 2020 1:37 PM
  • User1535942433 posted

    Hi evanburen,

    Accroding to your description,I have delete ExcelPackageExtensions class file to reproduce your issue.As far as I think,you don't create ExcelPackageExtensions class correctly.

    1.I suggest you could check if ExcelPackageExtensions class file's name is right and it isn't spelling mistake.

    2.You could delete the ExcelPackageExtensions class you haved. And you create the class name ExcelPackageExtensions again.

    Best regards,

    Yijing Sun

    Friday, December 11, 2020 8:09 AM