Page 1 of 1

Using Microsoft Excel as a Data Store Rate Topic: -----

#1 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Posted 15 January 2015 - 06:51 AM

This tutorial will demonstrate how to use Microsoft Excel as a data store. That is, how to read, write and update data in a Microsoft Excel Worksheet as if it were a database.

Why would you want to do this? Well, admittedly, if you are doing any real data processing then you will definitely want to use a proper database, but there are occassions where you already have data in an Excel file that you simply want to read or you may need to store some simple data that you want to pass around making an Excel file a lot easier.

To follow this tutorial you will need:

  • Microsoft Excel
  • VB.NET (code should work from .NET 2.0 upwards and may even work with .NET 1.1)


Creating a sample Excel file
Firstly, open Excel and create a new blank workbook. On Sheet1 enter the following column headers:

  • First Name
  • Last Name
  • Address
  • Telephone


Now add some data, preferably a few rows to demonstrate the functionality. Your sheet should look similar to:

Attached Image

Save this file in your desired location (I have chosen C:\Temp\Test.xlsx which you will see used in connection strings so be sure to modify your connection strings if you have chosen something different).

Create the test application
Fire up your Visual Studio and create a new Windows Forms project. Add a DataGridView (named excelDataGridView) to Form1. This will be used to display the data from your test Excel file.

Connecting to and retrieving data from a Microsoft Excel Worksheet
In order to treat Excel as a Database we will be using ADO.NET and in particular objects from the Syste.Data.OleDb namespace. So at the top of your code add the following two Imports statements:

Imports System.Data
Imports System.Data.OleDb



Next, add the following routine below the Form_Load event which will be responsible for retrieving all data from Sheet1 of your Workbook.

Private Sub RefreshData()

	'Create the connection string to connect to the Microsoft Excel Workbook
	Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\test.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

	'Create a standard SELECT SQL statement
	Dim selectStatement As String = "SELECT [First Name], [Last Name], Address, Telephone FROM [Sheet1$]"

	'Create a DataAdapter that will be used to populate a DataTable with data
	Dim adapter As New OleDbDataAdapter(selectStatement, connectionString)

	'Populate a DataTable
	Dim excelData As New DataTable
	adapter.Fill(excelData)

	'Display the data in the excelDataGridView
	excelDataGridView.DataSource = excelData

End Sub



Some things to note in the code above. The connection string used is for Excel 2007 and later and that it specifies that the first row should be treated as having headers (HDR=YES). If we specified HDR=NO then we would get an SQL error as when the DataAdapter attempts to execute the SELECT statement it would have no idea what [First Name] or [Last Name] refers too.

The other important piece to note is the SQL SELECT statement, in particular the use of [Sheet1$]. If you omit the $ sign then you will receive an error stating that the object (Sheet1) could not be found. Regardless of the name of the work sheet you must specify the $ sign at the end of it.

Now add a call to RefreshData() in your Form1_Load event:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

	RefreshData()

End Sub



Run the project and you should see your data in the DataGridView.

Writing data to a Microsoft Excel Worksheet

This next piece will demonstrate how to write data to the same Worksheet. Add a button to your form and call it writeDataButton and set its Text property to "Write Data". In it's click event handler, add the following code:

Private Sub writeDataButton_Click(sender As Object, e As EventArgs) Handles writeDataButton.Click

	'Create the connection string to connect to the Microsoft Excel Workbook
	Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\test.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

	'Create an INSERT INTO SQL statement
	Dim insertStatement As String = "INSERT INTO [Sheet1$] ([First Name], [Last Name], Address, Telephone) VALUES ('Nigel', 'Winterburn', 'Somewhere in England', '333 555 3333')"

	'Create a connection object to connect to the Excel Workbook 
	Dim connection As New OleDbConnection(connectionString)

	'Create a command object that will execute the insert statement
	Dim command As New OleDbCommand(insertStatement, connection)

	'Open the connection, execute the statement and close the connection
	connection.Open()
	command.ExecuteNonQuery()
	connection.Close()

	'Dispose of the connection and command objects
	connection.Dispose()
	command.Dispose()

	'Call the RefreshData routine so that you can see that data was indeed added.
	RefreshData()

End Sub



You will notice with the above code that we have had to create a specific connection object to connect to the Excel Workbook. This is because in our first piece of code that retrieves data, we use an OleDbDataAdapter which when the Fill method is called handles the opening and closing of a connection for us. This is not however the case with an OleDbCommand object so we need to do this work ourselves.

Updating data in a Microsoft Excel Worksheet
Updating data is very similar to writing data. Indeed the code is exactly the same except for the SQL statement.

Add another button to your form called updateDataButton and give it a Text value of "Update Data". Then add the following code to it's click event.

Private Sub updateDataButton_Click(sender As Object, e As EventArgs) Handles updateDataButton.Click

	'Create the connection string to connect to the Microsoft Excel Workbook
	Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\test.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

	'Create an UPDATE SQL statement
	Dim updateStatement As String = "UPDATE [Sheet1$] SET Address = 'Somewhere in UK' WHERE Address = 'Somewhere in England'"

	'Create a connection object to connect to the Excel Workbook 
	Dim connection As New OleDbConnection(connectionString)

	'Create a command object that will execute the update statement
	Dim command As New OleDbCommand(updateStatement, connection)

	'Open the connection, execute the statement and close the connection
	connection.Open()
	command.ExecuteNonQuery()
	connection.Close()

	'Dispose of the connection and command objects
	connection.Dispose()
	command.Dispose()

	'Call the RefreshData routine so that you can see that data was indeed updated.
	RefreshData()

End Sub



Deleting data from a Microsoft Excel Worksheet
Ah, now that you can't do. If you attempt to execute a DELETE statement against a Worksheet you will receive an ISAM error. You can delete cell values via an update statement and setting certain values to Null but you cannot delete a row.

So, that's it. A simple way to use Excel as a Data Store.

For more information on different conenction strings for different versions of Excel please see Excel connection strings

Is This A Good Question/Topic? 0
  • +

Replies To: Using Microsoft Excel as a Data Store

#2 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Posted 28 February 2015 - 03:05 PM

In the tutorial above, no exception handling has been added to keep it short and focused, but it would be advisable to note what exceptions can occur when dealing with databases (be it Excel, Access, Other).

When opening a connection to a database or Excel file using the OleDbConnection.Open method, two possible exceptions can be thrown:



And when issuing commands against an OleDb data source using the ExecuteNonQuery method as illustrated in the tutorial, the only exception that can be thrown (as documented by MSDN) is the InvalidOperationException linked above.

So, please do use Try, Catch blocks around the appropriate code, for example, opening a connection and execute a query such as a select statement would look something like the following:
Try
    'The code to open a connection and execute a statement 
Catch ex As InvalidOperationException
    MessageBox.Show(ex.Message)
Catch ex As OleDbException
    MessageBox.Show(ex.Message)
Finally
    'Any tidy up code here to release resources
End Try


This post has been edited by djjeavons: 28 February 2015 - 03:05 PM

Was This Post Helpful? 0
  • +
  • -

#3 Alamgir khan  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 34
  • Joined: 20-May 17

Posted 24 May 2017 - 11:31 AM

Thank you dear for sharing of this knowledge base thread with us.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1