[mostwanted115]

hello,

i am accesing an excel file from C# in MS visual studio 2005. the basic function is dome and working fine. the thing is i wanna parametrize the datasource, the follwong is the databse conncetion line:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\table.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

Instead of having Data Source=C:\table.xls, i need it to be c:\x
where x is the file name chosen from a drop down list. So if the user chooses google (string) from the dropdown list, the data source is then c:\google.xls
All excel files are already available in C:\.
Please help me implement this simple issue as it is vital to my final year project, else if it doesnt work i wont graduate this semester! i am in a bad situation here and i really appreciate anyhelp.
Attached is my project if needed. (am trying to display the first row of the selected excel file in a datagrid)
Thanks a lot

Attached File(s)

•  test.zip (854.23K)
  Number of downloads: 192

This post has been edited by Martyr2: 05 April 2008 - 10:38 AM

[Martyr2]

Well first of all you can start by putting the drop down box on your form and from the form load event you are going to load the drop down with something like this...

private void Form1_Load(object sender, EventArgs e)
{
            // Get an array of xls filenames from c:\
            String[] files = Directory.GetFiles("c:\\","*.xls");

            // Loop through each one and add it to the combobox
            foreach (String filename in files) {
                comboBox1.Items.Add(filename);
            }
}

So here we are basically loading up the combo box with filenames of all xls files in the c:\ directory. You can change this to load individual filenames if you chose. I wrote this to make it very automatic.

Next you need to move connectionString to a higher scope so that we can manipulate its value and the application can keep track of it. So put the definition right after the Form1() constructor function. Of course keep it initialized to c:\table. This is because if the user doesn't select an item from the combo and hit view instead, this will be the default table they see.

After that you are going to want to setup a SelectedIndexChanged event for the combobox. In that event you are going to manipulate the form level variable by including the value of the drop down in the string itself like so...

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Rewrite the string so that it includes the value selected from the combo.
            connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";

            // Call the view button click handler. Now this is a quick hack, but you 
            // could have easily moved the code out of the populate click event into a separate
            // function and just call that function from here and the populate click event.
            // Your choice.
            btnPopulate_Click(sender, e);

}

Please read the in code comments referring to the possible move of code to a separate function (I recommend that actually) but this example just uses a quick hack to bypass needing the function.

As you can see we take the string and tack in selected items value to build the string. The value of course will be the path to files we entered in the form load event.

Once that string is built we call the populate click event to cause the data connection to be established and to populate the grid.

Hopefully this makes sense. Again I recommend you put in a function to do the populating of the view and call it from both the selectedindexchanged event and from the btnPopulate click event.

Enjoy!

"At DIC we be solving problems 38732 times and it keeps getting funnier every time I see it!" (- Martyr2 adapted from Beetlejuice)

[mostwanted115]

thanks a lot martyr2, really appreciate. u really are talented.
the thing is i get 1 error:
The name 'Directory' does not exist in the current context
this is when compiling the 1st piece of code to fill in the list with the filenames.
i placed this function directly under public form1(). is that correct?
EDITED: i take that back, i fixed the error by using
System.IO.Directory.GetFiles instead.
Now, the problem is that the dropdown list is still empty,
as if this function haven't worked as planned. Please
help me on this. Thanks again.

This post has been edited by mostwanted115: 05 April 2008 - 04:53 PM

[Martyr2]

Did you put that code I told you to put into the load event of the form? Do you have your xls files in the c:\ directory? Did you then click the drop down button? If you did all this you should see them in the list. This is not advanced programming what so ever.

[mostwanted115]

Martyr2, on 5 Apr, 2008 - 05:07 PM, said:

Did you put that code I told you to put into the load event of the form? Do you have your xls files in the c:\ directory? Did you then click the drop down button? If you did all this you should see them in the list. This is not advanced programming what so ever.

here's the code, please advise where is exactly the load event of the form?

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;


namespace ReadExcel07
{
	
	public partial class Form1 : Form
	{
		//private System.Windows.Forms.DataGridView dgvExcelList;
		public Form1()
		{
			InitializeComponent();
		}
		private void Form1_Load(object sender, EventArgs e)
		{
			int index = 0;
			// Get an array of xls filenames from c:\
			String[] files = System.IO.Directory.GetFiles("C:\\files", "*.xls");
			
			// Loop through each one and add it to the combobox
			foreach (String filename in files)
			{
				//comboBox1.Items.Add(filename);
				
				comboBox1.Items.Insert(index, filename);
			}
		}
		private void btnPopulate_Click(object sender, EventArgs e)
		{
						
			// You can change C:\Members.xlsx to any path where 
			// the file is located.
			string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
			   Data Source=C:\table;Extended Properties=""Excel 8.0;HDR=YES;""";

			// if you don't want to show the header row (first row)
			// use 'HDR=NO' in the string

			string strSQL = "SELECT TOP 1 * FROM [table$]";

			OleDbConnection excelConnection = new OleDbConnection(connectionString);
			excelConnection.Open(); // This code will open excel file.

			OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
			OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

			// create data table
			//DataTable dTable = new DataTable();
			//create Dataset
			DataSet ds = new DataSet();
			//dataAdapter.Fill(dTable);
			try
			{
				dataAdapter.Fill(ds, "table");
				DataTable dt = new DataTable();
				dataAdapter.Fill(dt);
				this.dgvExcelList.DataSource = dt.DefaultView;
				//dg.DataSource = ds.Tables[0].DefaultView;
				//dg.DataBind();
			}
			// bind the datasource
			//dataBingingSrc.DataSource = dTable;

		// assign the dataBindingSrc to the DataGridView
			//dgvExcelList.DataSource = dataBingingSrc;

		// dispose used objects
			//dTable.Dispose();
			//dataAdapter.Dispose();
			//dbCommand.Dispose();
			finally
			{
				excelConnection.Close();
				excelConnection.Dispose();
			}
		}
				private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
		{
		   
		}

		//private void dgvExcelList_CellContentClick(object sender, DataGridViewCellEventArgs e)
		//{

		//}
	}
}

All excel files r in place. thanks man. i really owe u.

[PsychoCoder]

The way your code is now, using Insert instead of Add, is going to give you problems as you are using the variable index which you set to 0, but never increment it. Also, I thought I read that all your files are in C:\, but in your code you're using C:\files. So if your files are in C:\files all you need is

// Get an array of xls filenames from c:\
string[] files = System.IO.Directory.GetFiles("C:\\files", "*.xls");
            
// Loop through each one and add it to the combobox
foreach (string filename in files)
{
       comboBox1.Items.Add(filename);
}

This post has been edited by PsychoCoder: 06 April 2008 - 05:52 AM

[mostwanted115]

EDIT: Now, thanks to you guys and to you martyr2, the code serves it's basic objective.
I can now select the available excel files from the drop down list and view the first row accordingly.
There are some minor bugs like setting an error handler where if the user didnt select any file and pressed
the view button, an error message must appear asking the user to make a selection first. i don't know how to make that.
The working code is now as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;


namespace ReadExcel07
{
	public partial class Form1 : Form
	{
		//private System.Windows.Forms.DataGridView dgvExcelList;
		public Form1()
		{
			InitializeComponent();
		}

		private void btnPopulate_Click(object sender, EventArgs e)
		{
			//string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
			//Data Source=C:\table;Extended Properties=""Excel 8.0;HDR=YES;""";
			string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; 
			Data Source=" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "; Extended Properties=\"Excel 8.0;HDR=YES;\"";
			// if you don't want to show the header row (first row)
			// use 'HDR=NO' in the string

			string strSQL = "SELECT TOP 1 * FROM [table$]";

			OleDbConnection excelConnection = new OleDbConnection(connectionString);
			excelConnection.Open(); // This code will open excel file.

			OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
			OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

			// create data table
			//DataTable dTable = new DataTable();
			//create Dataset
			DataSet ds = new DataSet();
			//dataAdapter.Fill(dTable);
			try
			{
				dataAdapter.Fill(ds, "table");
				DataTable dt = new DataTable();
				dataAdapter.Fill(dt);
				this.dgvExcelList.DataSource = dt.DefaultView;
				//dg.DataSource = ds.Tables[0].DefaultView;
				//dg.DataBind();
			}
			
		// assign the dataBindingSrc to the DataGridView
			//dgvExcelList.DataSource = dataBingingSrc;

			finally
			{
				excelConnection.Close();
				excelConnection.Dispose();
			}
		}

		private void Form1_Load(object sender, EventArgs e)
		{
			// Get an array of xls filenames from c:\
			String[] files = System.IO.Directory.GetFiles("c:\\files", "*.xls");

			// Loop through each one and add it to the combobox
			foreach (String filename in files)
			{
				comboBox1.Items.Add(filename);
			}
		}
		
		private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
		{
			// Rewrite the string so that it includes the value selected from the combo.
			//string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; 
			//Data Source=" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "; Extended Properties=\"Excel 8.0;HDR=YES;\"";
			// Call the view button click handler. Now this is a quick hack, but you 
			// could have easily moved the code out of the populate click event into a separate
			// function and just call that function from here and the populate click event.
			// Your choice.
			//btnPopulate_Click(sender, e);
		}

		//private void dgvExcelList_CellContentClick(object sender, DataGridViewCellEventArgs e)
		//{

		//}
	}
}

This post has been edited by mostwanted115: 06 April 2008 - 11:17 AM

[mostwanted115]

can just someone help me on making the error message as indicated in my previous post, please?

[mostwanted115]

Hi again.

Now everything is working fine, except the error message i intend to appear when no item is selected. Anyway that's a minor issue.

The drop down list now contains items that show their full pah like:
c:\files\google.xls
c:\files\micro.xls

is there a way i hide the characters (and not remove or delete) "c:\files\" and ".xls" ?

string.substring and string.remove will ruin the selected index that is used in the data source up in the connection string, so i can't really use them. That is if i us substring to remove "c:\files\", the code will no longer recognize the path and cannot open the excel file.
is there a way i can just hide these characters in the application without actually deleting'em?

thanks

[mostwanted115]

I got a solution for the above issue, but still at the design level, i need help coding it.
The soln is to use an array whose indexes are the filenames like google, microsoft, .. and whose elements are the full path like c:\files\google.xls.
The slected index at the data source will thus be the arrays elemnt and the item filled in the dropdownlist (comboBox1) should be the name of the file.
Now i need help to code this, anyone volunteers? or anyone got a better idea to hide the un-needed characters from the items in the dropdown list without corrupting the datasource?