VB.NET - 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 http://www.gemboxsoftware.com/GBSpreadsheet.htm 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 http://www.gemboxsoftware.com/Excel-Automation.htm.

Filip
GemBox Software - http://www.gemboxsoftware.com/