Excel Add-in in VB.Net - How to access the workbook/worksheet?

end of post

Private Sub Button1_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles Button1.Click
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        ' Create a new instance of Excel and start a new workbook.
        objApp = New Excel.Application()
        objBooks = objApp.Workbooks
        objBook = objBooks.Add
        objSheets = objBook.Worksheets
        objSheet = objSheets(1)

        'Get the range where the starting cell has the address
        'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
        range = objSheet.Range("A1", Reflection.Missing.Value)
        range = range.Resize(5, 5)

        If (Me.FillWithStrings.Checked = False) Then
            'Create an array.
            Dim saRet(5, 5) As Double

            'Fill the array.
            Dim iRow As Long
            Dim iCol As Long
            For iRow = 0 To 5
                For iCol = 0 To 5

                    'Put a counter in the cell.
                    saRet(iRow, iCol) = iRow * iCol
                Next iCol
            Next iRow

            'Set the range value to the array.
            range.Value = saRet

        Else
            'Create an array.
            Dim saRet(5, 5) As String

            'Fill the array.
            Dim iRow As Long
            Dim iCol As Long
            For iRow = 0 To 5
                For iCol = 0 To 5

                    'Put the row and column address in the cell.
                    saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()
                Next iCol
            Next iRow

            'Set the range value to the array.
            range.Value = saRet
        End If

        'Return control of Excel to the user.
        objApp.Visible = True
        objApp.UserControl = True

        'Clean up a little.
        range = Nothing
        objSheet = Nothing
        objSheets = Nothing
        objBooks = Nothing
    End Sub

Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet


xlsApp = New Excel.Application
xlsApp.Visible = True
xlsWB = xlsApp.Workbooks.Open(Path)
xlsSheet = xlsWB.Worksheets(1)

You can loop through xlsWB.Worksheets to access all the worksheets in the excel.

more samples refer
http://www.daniweb.com/forums/thread29055.html#
http://www.a1vbcode.com/snippet-3421.asp

Hi

you can access excel sheet  in vb.net easily ,to this you need add a lib to your project,using Microsoft.Office.Interop.Excel;

code:

Private bodynitsApplicationClass As ApplicationClass = Nothing
Private worksheetdetail As Microsoft.Office.Interop.Excel.Worksheet = Nothing

Public Sub ProcessExcell(ByVal filename As String, ByVal sheetno As String)
    
    If bodynitsApplicationClass Is Nothing Then
        bodynitsApplicationClass = New ApplicationClass()
    End If
    If Not System.IO.File.Exists(filename) Then
        UpdateControl("Source file does not exist inside " & filename)
        Exit Sub
    End If
    Try
        '  System.Data.DataTable extracteDataTAble = GetAllDataFromExcel(filename);
        Dim workbooks As Microsoft.Office.Interop.Excel.Workbook = bodynitsApplicationClass.Workbooks.Open(filename, 0, True, 5, "", "", _
            False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, vbTab, False, False, 0, _
            True, 1, 0)
        
        Dim sheets As Microsoft.Office.Interop.Excel.Sheets = workbooks.Worksheets
        TGSDTSysLogger.Log("Opening excel sheet...", LogForObjects.Adidas, LogInforType.Detail)
        
        
        ' get header sheet to read
        worksheetdetail = DirectCast(sheets.get_Item(Integer.Parse(sheetno)), Microsoft.Office.Interop.Excel.Worksheet)
        ' reade very cell based on your needs 
        
            
        Dim RLDZ As String = DirectCast(sheet.Cells(rowindex, 22), Microsoft.Office.Interop.Excel.Range).Value2.ToString().TrimEnd()
    Catch ex As Exception
        
        Throw ex
        
    End Try
End Sub

thank you

u simply download the office PIA and then put two dll in bin and add refereance to yr porject

Interop.Excel.dll

Interop.Microsoft.Office.Core.dll

 

then u will be able to access workbook/worksheet in your code as follows

using Excel;

using System.Runtime.InteropServices;

 

//using ApplicationClass u can access worksheet

Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

Excel.Sheets excelSheets = excelWorkbook.Worksheets;

currentSheet = "sheetname";

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

//u can get cell also in code

Excel.Range destination = (Excel.Range)excelWorksheet.get_Range("A" + RowCount1.ToString(), "M"+TotalRow.ToString());

Hi,

Best way to deal with Excel in VB is by using 3rd party component. It's much faster and easier way then Excel Automation. Try using GemBox spreadsheet component for working with Excel. Component is free for commercial use but with limit of 150 rows.

Here you can see a list of reason why GemBox.Spreadsheet much better tool to work with Excel Automation.

Filip
GemBox Software - easier and faster way to deal with Excel then Excel Automation