Click here to Skip to main content
Click here to Skip to main content

Excel Connectivity in VB.NET

, 18 Aug 2005
Rate this:
Please Sign up or sign in to vote.
If you want to fetch data from an Excel stylesheet and show it in a DataGrid then download this source code.

Introduction

Often we are in a need to convert Excel data to XML stream/XML file which can be used as a feed to various applications like web services or middle tiers such as BizTalk 2004. There will be many situations where we need to validate the format of Excel data sheet against a specified XML schema. We will also be required to generate XML schema based on an Excel Work Sheet. This utility along with the library will help you to accomplish the same.

The following are the salient features of this library:

  1. Usage of Microsoft Jet Engine to connect to Excel.
  2. Conversion of Excel Worksheet/Workbook to XML file and XML Schema.
  3. Generation of XML file and XML Schema based on provided range.
  4. Validation of Excel Worksheet/Workbook against the provided XML Schema.
  5. Provision of batch processing capability.

In this article, we will discuss the implementation of the library functions. The library contains the core functionality to access and manipulate Excel data.

The utility will merely call the appropriate functions from the library. In this way, one can use this same library in ASP.NET applications also with minute changes.

Connectivity Options

There are two ways to manipulate an Excel file. It can be done either by using Microsoft Office Component (check out here) or with Microsoft Jet Engine.

As per Microsoft recommendation, it is not advisable to use Office components on the server. It means that if you want to use this library for a server application, it’s not a good idea to use the Office component. So the connection will be done using Jet Engine.

Connection to Excel using Jet Engine

To connect to Excel, one can use OleDb objects that will treat Excel as a database, and then the required information can be easily fetched by using SQL queries. The important steps that have to be considered while connecting to Excel are as follows:

  • Connection String:

    The connection string should be set to the OleDbConnection object. This is very critical as Jet Engine might not give a proper error message if the appropriate details are not given.

    Syntax: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1".

  • Definition of Extended Properties:
    • Excel = <No>

      One should specify the version of Excel Sheet here. For Excel 2000 and above, it is set it to Excel 8.0 and for all others, it is Excel 5.0.

    • HDR= <Yes/No>

      This property will be used to specify the definition of header for each column. If the value is ‘Yes’, the first row will be treated as heading. Otherwise, the heading will be generated by the system like F1, F2 and so on.

    • IMEX= <0/1/2>

      IMEX refers to IMport EXport mode. This can take three possible values.

      • IMEX=0 and IMEX=2 will result in ImportMixedTypes being ignored and the default value of ‘Majority Types’ is used. In this case, it will take the first 8 rows and then the data type for each column will be decided.
      • IMEX=1 is the only way to set the value of ImportMixedTypes as Text. Here, everything will be treated as text.

For more info regarding Extended Properties, check this out.

Loading of data in to Dataset

After successfully connecting to Excel using Jet Engine, it is easy to the load the data in to DataSet. One has to write a query similar to ANSI-92 with the only changes being that each Excel sheet will be treated as a table and the table name will be the sheet name with “$”. The range can also be specified after the “$” sign.

 <FONT size=2><P></FONT><FONT color=#0000ff size=2>Public</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Function</FONT><FONT size=2> ImportAttendence(</FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> PrmPathExcelFile </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String</FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> DataGrid1 </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> DataGrid)</P><P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> MyConnection </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.Data.OleDb.OleDbConnection</P><P></FONT><FONT color=#0000ff size=2>Try</P></FONT><FONT size=2><P></FONT><FONT color=#008000 size=2>''''''' Fetch Data from Excel</P></FONT><FONT size=2><P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> DtSet </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.Data.DataSet</P><P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> MyCommand </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.Data.OleDb.OleDbDataAdapter</P><P> </P><P>MyConnection = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _</P><P>"data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")</P><P></FONT><FONT color=#008000 size=2>' Select the data from Sheet1 of the workbook.</P></FONT><FONT size=2><P>MyCommand = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)</P><P> </P><P>MyCommand.TableMappings.Add("Table", "Attendence")</P><P>DtSet = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> System.Data.DataSet</P><P>MyCommand.Fill(DtSet)</P><P></FONT><FONT color=#008000 size=2>'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P></FONT><FONT size=2><P>DataGrid1.DataSource = DtSet.Tables(0)</P><P>MyConnection.Close()</P><P></FONT><FONT color=#0000ff size=2>Catch</FONT><FONT size=2> ex </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> Exception</P><P>MyConnection.Close()</P><P></FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Try</P></FONT><FONT size=2><P></FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Function</FONT></P>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

JaspalSingh
Instructor/Trainer
India India
Hello My name is Jaspal Singh

Comments and Discussions

 
QuestionExcel database connection PingroupR. S. Verma6-Jan-13 20:15 
GeneralMy vote of 5 PinmemberBawa90424-Nov-11 22:15 
GeneralAlternative PinmemberFilipKrnjic9-Jul-09 1:28 
GeneralBroken link in article PinmemberMarkus00004-Feb-09 7:43 
Generalexcel to datagrid in vb.net PinmemberCarloSan28-Aug-08 20:01 
Generalreading data from excel and storing it into sql server 2005 using c#.net 2005 Pinmemberravisakee26-Aug-08 18:52 
QuestionI get error to try read a excel file PinmemberMikisawa1-Jul-08 4:10 
Questionstreaming data of excel cell when the file is open to text box PinmemberMember 435308128-Jun-08 15:46 
GeneralVery Urgent [ About the exporting of ole object from vb to Excel] Pinmemberramasamy16-Apr-08 1:01 
QuestionHow to export OLE Object to Excel [Pleeease Sir, Am waiting for ur Response] Pinmemberramasamy14-Apr-08 17:58 
Questionvery urgent PinmemberNirmalaKannan24-Mar-08 23:54 
AnswerRe: very urgent PinmemberBarbaMariolino4-Jun-08 1:23 
Questionvery urgent PinmemberNirmalaKannan24-Mar-08 23:54 
Questionvery urgent PinmemberNirmalaKannan24-Mar-08 23:45 
Generalexcel data to vb.net Pinmemberadam.wolarczuk30-Sep-07 17:42 
Generalexel and vb.net Pinmemberapchidara27-Aug-07 5:22 
Questionembeded apostrophe Pinmemberusfp_nut9-Apr-07 13:22 
AnswerRe: embeded apostrophe PinmemberEasy....19-Apr-07 11:34 
GeneralRe: embeded apostrophe PinmemberNew78012-May-08 22:37 
GeneralRe: embeded apostrophe PinmemberMarvPerk312-Jan-10 6:11 
QuestionThe other way around? Pinmemberseppe00129-Mar-07 0:10 
Questionexcel connectivity to asp.net pages Pinmemberbadki11-Mar-07 20:05 
AnswerRe: excel connectivity to asp.net pages PinmemberKunal sharma21-Mar-07 22:49 
AnswerRe: excel connectivity to asp.net pages PinmemberFilipKrnjic31-Aug-09 6:23 
QuestionUrgent: Excel Data Type problem Pinmemberxxxxxxxxxchandra5-Mar-07 23:21 
GeneralHelp me PinmemberLuis Agape18-Jan-07 10:27 
GeneralHelp me PinmemberLuis Agape18-Jan-07 10:25 
GeneralIt is already opened exclusively by another user, or you need permission to view its data. Pinmemberimprimis13-Sep-06 2:07 
GeneralIts not working Pls help me soon PinmemberBhuvaneswari R28-Aug-06 21:14 
Generalnot workign on server also Pinmembercool_b29-Jun-06 22:06 
Generalnot working on server Pinmemberamit__816-May-06 19:09 
QuestionWhat if the first sheet is not called sheet1 Pinmemberibalthasar27-Mar-06 13:14 
AnswerRe: What if the first sheet is not called sheet1 PinmemberPaulius4-Apr-06 4:59 
GeneralSend me error when file is open from Ms Excel Pinmemberraarno12-Oct-05 13:17 
GeneralYou might want to check your &quot;source code&quot; link Pinmemberfwsouthern18-Aug-05 15:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140718.1 | Last Updated 18 Aug 2005
Article Copyright 2005 by JaspalSingh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid