hi every one I have a lot of excel data sheets and I need to work with them . how can I connect my code with the excel files . is it the same as ADO.net while u do with access?
please help me or send me a link about EXCEL ANV VB.NET CONNECTIVITY i al so lost .
thanks

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. :)

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;
    }
  }
}
Member Avatar for iamthwee

You should just be able to do something like:

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.

hi apchidara! here's how I did it using visual studio express and vb.net windows apps. hope you like it

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.