Archive for June, 2009

How to Read Excel Files in VB.Net

Excel worksheets can read in Visual studio by using Office API’s. To get start working with Excel file we first need to understand the structure of Excel files. For programming Excel files we first creates an Excel Application object, read Excel file in workbook and read sheets from this work book.

Excel Read Workflow

To get start with Excel programming you first need to add Microsoft.Office.Interop.Excel reference in your project. Instead of writing the simple code for reading Excel file i have created one class for reading excel file and it also help you read workbook and worksheets. you can directly use this class in your project to read excel file.

Microsoft.Office.Interop.Excel

Imports OfficeExcel = Microsoft.Office.Interop.Excel
Namespace MyExcel

    Public Class ExcelRead
        Dim _xlApp As OfficeExcel.Application
        Dim _xlWorkBook As OfficeExcel.Workbook
        Dim _xlWorkSheet As OfficeExcel.Worksheet
        Dim _XlFilePath As String
#Region "Constructor"
        Public Sub New(ByVal filepath As String)
            _XlFilePath = filepath
            OpenExel(_XlFilePath)
        End Sub
#End Region

#Region "Protected Method"
        Protected Sub OpenExel(ByVal fileName As String)
            _xlApp = New OfficeExcel.ApplicationClass
            _xlWorkBook = _xlApp.Workbooks.Open(fileName)
        End Sub
#End Region

#Region "Public Functions"
        Public Function GetWorkSheet(ByVal sheetname As String) As OfficeExcel.Worksheet
            For Each xlsheet As OfficeExcel.Worksheet In Me.XLWorkBook.Sheets
                If xlsheet.Name = sheetname Then
                    _xlWorkSheet = xlsheet
                    Return _xlWorkSheet
                End If
            Next
            Return _xlWorkSheet
        End Function
        Public Function getExcelWorkBookNames() As List(Of String)
            Dim workBookNames As New List(Of String)
            For Each wb As OfficeExcel.Worksheet In Me._xlApp.Worksheets
                workBookNames.Add(wb.Name)
            Next
            Return workBookNames
        End Function
        Public Function XLFilePath() As String
            Return _XlFilePath
        End Function
        Public Sub closeXL()
            releaseObject(Me._xlApp)
            releaseObject(Me._xlWorkBook)
            releaseObject(Me._xlWorkSheet)
        End Sub
#End Region

#Region "Read Only Property"
        Public ReadOnly Property XlApplication() As OfficeExcel.Application
            Get
                Return _xlApp
            End Get
        End Property
        Public ReadOnly Property XLWorkBook() As OfficeExcel.Workbook
            Get
                Return _xlWorkBook
            End Get
        End Property
        Public ReadOnly Property XLWorkSheet() As OfficeExcel.Worksheet
            Get
                Return _xlWorkSheet
            End Get
        End Property
#End Region

#Region "Private Functions"
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()

            End Try
        End Sub
#End Region
    End Class

End Namespace

My next post use the same class that today we created to learn how to Format excel cells and draw charts.

Microsoft SQL Server 2005 developer’s guide

Recently i started working on SQL Server 2005  development and developing applications using SQL Server 2005. Microsoft SQL Server 2005 Developer guide help me a lot and might be useful for others also. most importantly this book can use without any cost (web version). If you want a local then you should purchase a copy of this book.

Download Web Deployment Tool

The Web Deployment Tool is a tool for simplifying migration, management and deployment of Web applications, sites and servers. It can be used to package a Web site, automatically including content, configuration, certificates and databases. It can be used to synchronize between IIS 6.0 or IIS 7.0, or to migrate from IIS 6.0 to IIS 7.0. The packages created can be used for versioning, backup or deployment.

Features

Package your Web applications:

  • Packages a Web application or an entire site, including any associated databases.
  • Ability to package certificates, ACLs, COM, GAC and Registry settings.
  • Supports both live servers and zipped packages as a source or destination target Web applications.  
  • Seamless integration into the IIS 7.0 Manager and Visual Studio 10 interface.
  • Seamless integration into the Web Platform Installer for even simpler installations of community web applications.

Deploy your Web applications:

  • Administrative privileges are not required in order to deploy Web applications.
  • Ability to add powerful parameters to change text in files when they are deployed (such as prompting to replace a connection string when deploying from QA to staging environments).
  • Integration with the IIS 7.0 Web Management Service (WMSVC) for remote deployment by non-administrators.
  • Server administrators have granular control over the operations that can be performed and can delegate tasks to non-administrators.
  • In addition to the IIS Manager and Visual Studio 10, tasks can be performed using the command-line, PowerShell cmdlets or APIs. 

Synchronize your Servers:

  • Ability to synchronize or migrate the entire Web server, or a single Web site or application.
  • Synchronizes only data that has changed.
  • Ability to detect missing dependencies during synchronization.
  • Automatically gathers the following when you sync a Web site:
    • Content
    • IIS configuration
    • Certificates
    • Registry keys
    • ASP.NET configuration
  • Ability to use a secure service (authorized for server administrators only and can be secured with HTTPS, etc.) to synchronize the machines.

Download Web Deployment Tool – RC1 – x86.
Download Web Deployment Tool – RC1 – x64