| | |
connecting excel to vb.net
Please support our VB.NET advertiser: Intel Parallel Studio Home
![]() |
Here's a function for loading an excel spreadsheet using ADO.NET. It's in C#, but you should know how to convert between the two languages. 

VB.NET Syntax (Toggle Plain Text)
using System; using System.Data; using System.Diagnostics; using System.ComponentModel; using System.Collections.Generic; using System.Data.OleDb; public class ExcelSpreadsheet { private List<string> _worksheets = new List<string>(); [Description( "Gets a list of worksheet names matching the DataSet table names." )] public List<string> WorkSheets { get { return _worksheets; } } private DataSet _spreadsheet = new DataSet(); [Description( "Gets a DataSet with each table representing one worksheet in the spreadsheet" )] public DataSet SpreadSheet { get { return _spreadsheet; } } // Disable the default constructor private ExcelSpreadsheet() { } public ExcelSpreadsheet( string filename ) { _spreadsheet = this.LoadSpreadsheet( filename, false ); } public ExcelSpreadsheet( string filename, bool hasHeader ) { _spreadsheet = this.LoadSpreadsheet( filename, hasHeader ); } /// <summary> Load the worksheet names </summary> private IEnumerable<string> GetWorksheets( OleDbConnection connection ) { try { DataTable schema = connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables , new object[] { null, null, null, "TABLE" } ); foreach ( DataRow row in schema.Rows ) { _worksheets.Add( row["TABLE_NAME"].ToString() ); } return _worksheets; } catch ( Exception ex ) { Trace.Write( ex.Message ); throw; } } private DataSet LoadSpreadsheet( string filename, bool hasHeader ) { try { DataSet spreadsheet = new DataSet(); string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=" + ( hasHeader ? "Yes" : "No" ) + ";IMEX=1\";"; using ( OleDbConnection connection = new OleDbConnection( connectionString ) ) { connection.Open(); using ( OleDbCommand command = new OleDbCommand() ) { using ( OleDbDataAdapter adapter = new OleDbDataAdapter( command ) ) { foreach ( string ws in this.GetWorksheets( connection ) ) { command.Connection = connection; command.CommandText = "select * from [" + ws + "]"; DataTable table = new DataTable( ws ); adapter.Fill( table ); spreadsheet.Tables.Add( table ); } } } connection.Close(); } return spreadsheet; } catch ( Exception ex ) { Trace.Write( ex.Message ); throw; } } }
The truth does not change according to our ability to stomach it.
You should just be able to do something like:
and then access the various methods. I'm not sure if you need to add it as a com object or something?
Another alternative, although not as flexible, would be to save your excel file as a comma separated value file, and parse in the stuff using string split (",") methods etc.
VB.NET Syntax (Toggle Plain Text)
Dim blah As Excel.Application
and then access the various methods. I'm not sure if you need to add it as a com object or something?
Another alternative, although not as flexible, would be to save your excel file as a comma separated value file, and parse in the stuff using string split (",") methods etc.
•
•
Join Date: Jun 2008
Posts: 23
Reputation:
Solved Threads: 0
you can connect excel file through vb.net with the help of oledb. check the following link.
http://vb.net-informations.com/excel...xcel_oledb.htm
bruce.
http://vb.net-informations.com/excel...xcel_oledb.htm
bruce.
•
•
Join Date: Oct 2008
Posts: 4
Reputation:
Solved Threads: 0
hi apchidara! here's how I did it using visual studio express and vb.net windows apps. hope you like it
don't forget the imports e.g., imports system.data.oledb
also don't forget to declare the dim DAx as oledbdataadapter and DTx as system.data.datatable
VB.NET Syntax (Toggle Plain Text)
Try Dim opn as New OpenFileDialog opn.InitialDirectory = "c:\wheremyfileis\myXLfiles" opn.Filter = "Excel Files (*.xls)|*.xls" opn.FilterIndex = 1 If opn.Showdirectory = Windows.Forms.DialogResult.Ok Then dim sb as system.text.stringbuilder = new system.text.stringbuilder("") sb.append("Provider=Microsoft.Jet.OLEDB.4.0;") sb.append("Data Source= ") sb.append(opn.FileName) sb.append(";Extended Properties = Excel 8.0;") CNx = New system.data.oledb.oledbconnection(mycnn) CNx.Open() end if DAx = New oledb.oledbdataadapter("select rcode, pcode, etc., "the exact column names found in your excel ", CNx) DTx = New DataTable DAx.Fill(DTx) Catch ex as exception End try
don't forget the imports e.g., imports system.data.oledb
also don't forget to declare the dim DAx as oledbdataadapter and DTx as system.data.datatable
![]() |
Similar Threads
- use excel in vb.net 2003 (VB.NET)
- Retriving data from excel sheet into an asp page (ASP)
- Open Excel Sheet in Vb.net (VB.NET)
- Excel from VB.NET (VB.NET)
- connecting to access via vb.net-HELP!!! (VB.NET)
Other Threads in the VB.NET Forum
- Previous Thread: which OPERATING SYSTEM is Best???
- Next Thread: Unable to retrieve String data from Excel to SQL Server database
| Thread Tools | Search this Thread |
"crystal .net .net2005 30minutes 2005 2008 access account arithmetic array assignment basic binary bing box button buttons center check code combobox component connectionstring convert crystalreport data database databasesearch datagrid datagridview design dissertation dissertations dissertationthesis dosconsolevb.net dropdownlist excel file-dialog firewall folder ftp google hardcopy image images insert isnumericfuntioncall login math memory mobile ms navigate net networking opacity output passingparameters peertopeervideostreaming picturebox picturebox1 port print problemwithinstallation project record reports" save savedialog searchbox serial soap sorting string table tcp temp text textbox timer toolbox trim update updown useraccounts usercontrol vb vb.net vb.netcode vb.netformclosing()eventpictureboxmessagebox vb.nettoolboxvisualbasic2008sidebar vb2008 vbnet view visual visualbasic visualbasic.net visualstudio web wpf






