connecting excel to vb.net

[apchidara]

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

[Hamrick]

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;
    }
  }
}

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.

[bruce2424]

you can connect excel file through vb.net with the help of oledb. check the following link.

http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm

bruce.

[rmjagnaan]

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