[Alkis]

Hello all,

I'm creating a program and it is paramount to its functionality that it can open->read->close a Microsoft Excel (.xlsx) file.
Now, i've searched the forums with the keyword "Excel" and found a lot of stuff in many programming languages, none of which were any use to me.

I should pinpoint that i'm not that good at programming as university takes all my times, but every summer (like this one) i really enjoy tackling my problems via programming. The reason im saying that, is that, as i love programming, any insight you could offer, any additional information will be much appreciate it. i do want to learn as much as possible.

What i've written so far is this:

Imports Excel
Imports System.IO

Public Class Form1
    Dim stream As FileStream
    Dim excelReader As IExcelDataReader
    Dim i As Integer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        stream = File.Open("C:\Book1.xlsx", FileMode.Open, FileAccess.Read)

        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)

        Dim result As DataSet = excelReader.AsDataSet()

        For i = 0 To result.Tables.Item(i).Columns.Count - 1
            TextBox1.Text = TextBox1.Text & vbCrLf & result.Tables.Item(i).ToString
        Next

        i = 0
        While excelReader.Read()
            excelReader.GetInt32(0)
            TextBox2.Text = TextBox2.Text & " " & excelReader.GetString(i)
            i = i + 1
        End While

        excelReader.Close()

    End Sub
End Class

I understand what a great disaster it is as a code. beyond stupidlike i could add
However this is the first time i am working with excel and i know nothing as far as excel-through-Vb.net is concerned.

P.S i've also imported one more file for excel but it is not shown in the code window cuz it was a downloaded file and i had to loaded it from the settings

[Nightfish]

I do not see you mentioning what your actual problem is...? Other than "here is code, look at it!"

[EdGonz]

I think his project calls for opening, reading and closing an excel file; looks like he hasn't been able to do that.

Question is: why just not call Excel.exe and pass it the file name as a parameter?

[modi123_1]

Without some direct input from you I am a bit lost on what to help you with outside of throwing you MSDN tutorials. So, here you go:

http://support.microsoft.com/kb/301982

http://msdn.microsof...fice.11%29.aspx

http://www.dreaminco...fice-automation

[Jeff H]

If you find a example in C# for example
http://www.dreaminco...ost__p__1377819


You can just paste the code here to convert it

http://www.developer...t/csharp-to-vb/

[Alkis]

Here's exactly what i want the programme to do:

Lets say that we have an excel file that has "n" colums (1,2,3,4,.....n) and EA rows (A,B,C,....EA)

i want my program to read from the Excel file the rows: D-W (i.e. D,E,F,G....W) and put the data into an Array(n-1,19)
So that Array(0,0) will be Excel's D1, Array(0,1) will be Excel's D2 and so on

(i do not need help with inserting values to the array obviously)
I just dont know how to read the D1, from the excel file..

[Jeff H]

Using code from previous post with minor change.

If you can fill the array here are 2 ways to show the value of D1 and D2

Do not forget to reference Excel library and can import so you do not have type out long namespace

        Dim excel As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)


        MsgBox(ws.Range("D1").Value)
        MsgBox(ws.Range("D2").Value)

        MsgBox(ws.Cells(1, 4).Value2)
        MsgBox(ws.Cells(2, 4).Value2)

        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()

[Alkis]

Jeff H, on 21 July 2011 - 09:05 AM, said:

Using code from previous post with minor change.

If you can fill the array here are 2 ways to show the value of D1 and D2

Do not forget to reference Excel library and can import so you do not have type out long namespace

        Dim excel As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)


        MsgBox(ws.Range("D1").Value)
        MsgBox(ws.Range("D2").Value)

        MsgBox(ws.Cells(1, 4).Value2)
        MsgBox(ws.Cells(2, 4).Value2)

        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()

Kudos man! you did it!
thats EXACTLY what i wanted to do!
thank you very very much. i can now now move on with my project.

One last question now. i need to get the "n" each time.
As previously described, there will be "n" columns (1,2,3,4,...n)
n could be any number.
how can i know the last column that is written in a row?
(for example: i write 10 numbers in row "D". So the number "1" will be in "D1" the number "2" in "D2" and so on till "D10"
so "10" is my "n" this time. another time i could write 50 numbers.)
how can i get how many columns are written from the programme?

and a general question now:
if i have any more questions related to excel but not the same one.. should i post a new topic? or shall i reply here with the question?
(I, in fact, believe i will not have any more questions but, just in case..)

once again - thank you very much for your time and effort mate!

This post has been edited by Alkis: 21 July 2011 - 08:50 AM

[Jeff H]

Just to keep with how it is refered to in code I will say "D" is the column and your adding to the nth row. "Dx" where "D" is column and x is a number that represents the row.

Maybe someone has a better idea but the first thing that comes to mind is a empty cell could mean the end or you could have a certain value representing the end.

Can it have a empty cells?
For example
2
3
4
5
----empty
65

[Alkis]

No, there will be no empty cells whatsoever.
Also, i said i am gonna use cells from D1 to Wx (D1-Dx + E1-Ex + F1-Fx +...+ W1-Wx)
but the "x" will be the same so i need only find it once.

but now that you mention it, i guess i could make a while statement, right?

[fixo]

Try this console app
hope it make a sense

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Text
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Globalization
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

'' Tested on MS Excel 2007 (student release) only
'' References -> COM -> Microsoft.Excel XX.0 Object Library
''(or:  C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll)
Module Module1
    Dim lc As Integer
    Public Sub Main(ByVal args As String())
        testlastCell()

        Console.Read()
    End Sub

    Public Sub testlastCell()
        '' This line is very important!
        Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US") '<-- change culture on whatever you need

        Dim xlApp As Excel.Application = Nothing
        Dim xlBooks As Excel.Workbooks = Nothing
        Dim xlBook As Excel.Workbook = Nothing
        Dim xlSheet As Excel.Worksheet = Nothing
        Dim xlRange As Excel.Range = Nothing
        Dim missing As Object = Type.Missing
        Try

            xlApp = New Excel.Application()
            xlApp.Visible = True
            xlApp.DisplayAlerts = False
            xlApp.UserControl = True
            xlBooks = DirectCast(xlApp.Workbooks, Excel.Workbooks)
            'Change full path of Excel file here:
            xlBook = DirectCast(xlBooks.Open("YourExcelFileName", True, False, missing, "", missing, _
             False, missing, missing, True, missing, missing, _
             missing, missing, missing), Excel.Workbook)
            'get first sheei in the workbook
            xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)
            'get used range of certain sheet
            xlRange = DirectCast(xlSheet.UsedRange, Excel.Range)
            'get colunmn "D" of this range
            xlRange = DirectCast(xlRange.Columns("D"), Excel.Range)
            'get the last blank cell entire this column from up to down:
            xlRange = xlRange.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, missing)
            System.Console.WriteLine("Found the blank cell in column ""D"" at: " & vbLf & "Row: " & xlRange.Row.ToString)
            DirectCast(xlBook, Excel._Workbook).Close(False, missing, missing) ''<-- see here

        Catch ex As Exception
            System.Console.Write("Error:" & vbLf & ex.Message & "Trace: " & vbLf & ex.StackTrace)
        Finally
            xlApp.Quit()
            releaseObject(xlApp)
        End Try
    End Sub



    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
            obj = Nothing
        Catch ex As System.Exception

            System.Diagnostics.Debug.Print(ex.ToString())
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Module

[Jeff H]

Then depending on how you want to read it


D1....Dn E1...EN
or
D1..W1 Dn....Wn

Updated to add to 2 Listboxes both ways

Not very robust and is hard-coded but just for a simple example

        Dim excel As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        Dim row As Integer = 1

        Do While (ws.Cells(row, 4).Value2 <> Nothing)

            For col As Integer = 4 To 23
                ListBox1.Items.Add(ws.Cells(row, col).Value2.ToString())
            Next

            row += 1
        Loop


        For col As Integer = 4 To 23
            row = 1
            Do While (ws.Cells(row, col).Value2 <> Nothing)
                ListBox2.Items.Add(ws.Cells(row, col).Value2.ToString())
                row += 1
            Loop
        Next

        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()

This post has been edited by Jeff H: 21 July 2011 - 12:38 PM

[fixo]

Try this code as well

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Text
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Globalization
Imports System.Threading

Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.ListBox1.Items.Clear()
        testXL(Me.ListBox1)
    End Sub

    Public Sub testXL(ByVal list As System.Windows.Forms.ListBox)
        '' This line is very important!
        Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US") '<-- change culture on whatever you need

        Dim xlApp As Excel.Application = Nothing
        Dim xlBooks As Excel.Workbooks = Nothing
        Dim xlBook As Excel.Workbook = Nothing
        Dim xlSheet As Excel.Worksheet = Nothing
        Dim xlRange As Excel.Range = Nothing
        Dim missing As Object = Type.Missing
        Try

            xlApp = New Excel.Application()
            xlApp.Visible = True
            xlApp.DisplayAlerts = False
            xlApp.UserControl = True
            xlBooks = DirectCast(xlApp.Workbooks, Excel.Workbooks)
            'Change full path of Excel file here:
            xlBook = DirectCast(xlBooks.Open("yourFile.xls", True, False, missing, "", missing, _
             False, missing, missing, True, missing, missing, _
             missing, missing, missing), Excel.Workbook)
            'get first sheei in the workbook
            xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)
            'get used range of certain sheet
            xlRange = DirectCast(xlSheet.UsedRange, Excel.Range)

            'get colunmn "D" of this range
            xlRange = DirectCast(xlRange.Columns("D"), Excel.Range)
            'get the last blank cell entire this column from up to down:
            xlRange = xlRange.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, missing)
            Dim row As Integer = xlRange.Row
            For i As Integer = 1 To row - 1
                xlRange = DirectCast(xlSheet.Rows(i), Excel.Range)
                Dim last As Integer = DirectCast(xlRange.EntireRow, Excel.Range).End(XlDirection.xlToRight).Column
                For j = 1 To last
                    Dim xlCell As Excel.Range = DirectCast(xlSheet.Cells(i, j), Excel.Range)
                    list.Items.Add(xlCell.Value2)
                Next
            Next

            DirectCast(xlBook, Excel._Workbook).Close(True, missing, missing) ''<-- see here

        Catch ex As Exception
            MsgBox("Error:" & vbLf & ex.Message & "Trace: " & vbLf & ex.StackTrace)
        Finally
            xlApp.Quit()
            releaseObject(xlApp)
        End Try
    End Sub



    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
            obj = Nothing
        Catch ex As System.Exception

            System.Diagnostics.Debug.Print(ex.ToString())
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

[Alkis]

thank you all very much!
you've helped me and i've succeeded in what i wanted to do