0

I have an excel file with two columns. Also in windows form i have 3 textbox and one button. j=Rows k=columns What i want to do is, When i press the button if my textbox1.text= (j,k) then textbox2.text= (j,k+1) and textbox3.text=(j+1,k). I have made it with using interop,

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
    Dim arrayData(,) As String
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
                Try
            Dim xl As Excel.Application = New Excel.Application()
            Dim workbook As Excel.Workbook = xl.Workbooks.Open("C:\Users\tr100420\Documents\Visual Studio 2010\Projects\ExcelFunction\DB.xlsx")
            Dim sheet As Excel.Worksheet = workbook.Sheets(1)
            Dim Obj
            Dim dataRange As Excel.Range = sheet.Range("A1", "C500")
            Dim empNames As Excel.Range = sheet.Range("C1", "C5")
            ReDim arrayData(dataRange.Rows.Count, dataRange.Columns.Count)
            Dim j, k As Integer
            For j = 1 To dataRange.Rows.Count
                For k = 1 To dataRange.Columns.Count
                    arrayData(j - 1, k - 1) = dataRange.Cells(j, k).Value
                    If TextBox1.Text = Convert.ToString(dataRange.Cells(j, k).Value2) Then
                       TextBox2.Text = dataRange.Cells(j, k + 1).Value
                        TextBox3.Text = dataRange.Cells(j + 1, k).Value
                    End If
                Next k
            Next j
workbook.Close() : xl.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xl) : xl = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook) : workbook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet) : sheet = Nothing

But this method is working too much slow. Is there any solutions for the make it faster or if you have another method to connect and read excel could you please share with me... thank you..

BigBen
  • 46,229
  • 7
  • 24
  • 40
Şahin Konak
  • 1
  • Since you have the *coordinates* in `textbox1`, why don't you go get just those cells? You're iterating the whole Range and you don't break out of the loop when the *position* is found. `empNames` is never used. Some COM objects are not marshaled or you're trying to marshal in the wrong *sequence*, so the Excel instances you create are probably all there. Open up TaskManager to verify. – Jimi Sep 18 '20 at 13:29
  • Thank you for your answer. I dont know how to break out of the loop. Could you please show me a short sample about this issue if you have one? I've changed the marshal realesed sequence.1-dataRanges,2-sheet,3-workbook,4-application and erased the empNames. – Şahin Konak Sep 18 '20 at 13:44
  • [Breaking/exit nested for in vb.net](https://stackoverflow.com/a/5312563/7444103). But, as mentioned, you don't really need those loops. Are you sure you want to use Office Interop here? Are you coding this for yourself or for someone else? Meaning, someone else that does have Office/Excel installed in their machines? Have you tried [EPPlus](https://github.com/JanKallman/EPPlus), for example? Or standard OleDb queries (with `ACE.OLEDB.12`/`16` providers)? – Jimi Sep 18 '20 at 13:51
  • I am coding for someone else actually. I have tried OleDb queries but i couldn't get that other columns from excel. For example: textbox2.text=(j,k+1) i couldn't get k+1 Because of that i tried those loops.. – Şahin Konak Sep 18 '20 at 14:07
  • To leave a for loop `Exit For` – Mary Sep 18 '20 at 22:37

0 Answers0