We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Using VB .Net to parse an excel file

jimmychippewa
on
Medium Priority
476 Views
Last Modified: 2010-05-18
Hi

I am current writting a VB .Net app that needs to do the following:

open an existing excel workbook and select a worksheet
search the cells in one column for a string
continue searching the cells in the same column for another string
copy the cells between both strings and push them into a db

I've having difficulty finguring out how to search the cells into order to create the range object, w/o creating the range object to begin wth.

I haven't worked with the Excel object model before so any assistance would be appreciated
Comment
Watch Question

Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
this code will do ... all you have to do is change sheet name and the function'll return DataSet which you can easily manage


private DataSet TranslatExcelToDataset(string filePath)
{
      string ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"{0}\"; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"", filePath);
      OleDbConnection Connection = new OleDbConnection(ConnectionString);
      OleDbDataAdapter Adapter = new OleDbDataAdapter();
      OleDbCommand Command = new OleDbCommand("SELECT * FROM [Sheet1$]", Connection);
      DataSet DS = new DataSet("Table");
      Adapter.SelectCommand = Command;
      Adapter.Fill(DS);
      return DS;
}
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.