•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the VB.NET section within the Software Development category of DaniWeb, a massive community of 423,380 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,836 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our VB.NET advertiser: Programming Forums
Views: 3395 | Replies: 2
![]() |
•
•
Join Date: Aug 2007
Location: france, malaysia
Posts: 18
Reputation:
Rep Power: 2
Solved Threads: 0
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. 

csharp 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.
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.
![]() |
•
•
•
•
•
•
•
•
DaniWeb VB.NET Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
- use excel in vb.net 2003 (VB.NET)
- Open Excel Sheet in Vb.net (VB.NET)
- Excel from VB.NET (VB.NET)
- Retriving data from excel sheet into an asp page (ASP)
- connecting to access via vb.net-HELP!!! (VB.NET)
Other Threads in the VB.NET Forum
- Previous Thread: sms
- Next Thread: how to load specific data into a texbox...



Linear Mode