vb.net and excel

Question

i've used vb.net and visual studio before and have some basic coding experience(manipulate pdf's using syncfusion, import resource,etc) I'm trying to be able to retrieve value from ecxel cells when i user select certain check boxes. for example the excel sheet is 20 rows by 20 colums,

first colum is ages, then depending on different inputs from the user i need to get the value from certain cells to assign for variables in my program.

example: if user is 30 years old then i need values from 4 cells to put into 4 different variables.

            if user is 43 years old then i need valuse from 4 different cells to use in same variables. 

not looking for anybody to write the code for me, can someone point me in the right direction for a resource or guide or show me an example and i can finshi. , i have looked up on google and found some things but its only for one cell not multiple. i'm using VB as the language.

THank you very much

Answer 1

If you want to use xls files, use NPOI. Please install from Nuget.

You can read it with the code below.

Imports System.IO
Imports NPOI.SS.UserModel

Module Module1
    Sub Main()
        Dim filePath As String = "your file name.xls"
        Dim workbook As IWorkbook
        Using fileStream As New FileStream(filePath, FileMode.Open, FileAccess.Read)
            workbook = WorkbookFactory.Create(fileStream)
        End Using
        Dim sheet = workbook.GetSheetAt(0)
        Dim cellValue(19, 19) As String
        For row = 0 To 19
            For col = 0 To 19
                cellValue(row, col) = sheet.GetRow(row).GetCell(col).ToString()
            Next
        Next
    End Sub

End Module

Answer 2

Hi @Joseph Hedbon ,

Based on your description, I think you can use OLEDB to read and write to Excel.

I wrote an example to bind all the data to the Gridview, then filter out the desired data based on the dropdown list, and put the Rate in the Lable.

You can modify the code according to your needs.

       <asp:DropDownList ID="dropdown1" runat="server" OnSelectedIndexChanged="ddlAge_SelectedIndexChanged" AutoPostBack="true" AppendDataBoundItems="True">
           <asp:ListItem Selected="True" Value="Choose">- Choose -</asp:ListItem>
       </asp:DropDownList>
       <asp:GridView ID="Grid1" runat="server">
       </asp:GridView>
       <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>|
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>|
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>|
<asp:Label ID="lbl1" runat="server" />

Dim x As OleDbConnection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        GenerateExcelData("Choose")
    End If
End Sub
Protected Sub ddlAge_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    GenerateExcelData(dropdown1.SelectedValue)
End Sub
Private Sub GenerateExcelData(ByVal age As String)
    Try
        Dim read As String = Path.GetFullPath(Server.MapPath("~/Book2.xlsx"))

        If Path.GetExtension(read) = ".xls" Then
            x = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & read & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""")
        ElseIf Path.GetExtension(read) = ".xlsx" Then
            x = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & read & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")
        End If

        x.Open()
        Dim y As OleDbCommand = New OleDbCommand()
        Dim z As OleDbDataAdapter = New OleDbDataAdapter()
        Dim dset As DataSet = New DataSet()
        y.Connection = x
        y.CommandType = CommandType.Text
        y.CommandText = " SELECT distinct([Age]) FROM [Sheet1$]"
        z = New OleDbDataAdapter(y)
        z.Fill(dset, "Age")
        dropdown1.DataSource = dset.Tables("Age").DefaultView

        If Not IsPostBack Then
            dropdown1.DataTextField = "Age"
            dropdown1.DataValueField = "Age"
            dropdown1.DataBind()
        End If

        If Not String.IsNullOrEmpty(age) AndAlso age <> "Choose" Then
            y.CommandType = CommandType.Text
            y.CommandText = " SELECT *  FROM [Sheet1$] where Age= @Age"
            y.Parameters.AddWithValue("@Age", age)

            Using reader As OleDbDataReader = y.ExecuteReader()

                While reader.Read()
                    Label1.Text = reader("Rate1").ToString()
                    Label2.Text = reader("Rate2").ToString()
                    Label3.Text = reader("Rate3").ToString()
                End While
            End Using

            x.Close()
        Else
            y.CommandText = "SELECT * FROM [Sheet1$]"
        End If

        z = New OleDbDataAdapter(y)
        z.Fill(dset)
        Grid1.DataSource = dset.Tables(1).DefaultView
        Grid1.DataBind()
    Catch ex As Exception
        lbl1.Text = ex.ToString()
    Finally
        x.Close()
    End Try
End Sub

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.