Join 136,062 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 1,588 people online right now. Registration is fast and FREE... Join Now!
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:
CODE
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
This post has been edited by Martyr2: 5 Apr, 2008 - 09:38 AM
Attached File(s) test.zip ( 854.23k )
Number of downloads: 49
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...
csharp
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...
csharp
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)
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: 5 Apr, 2008 - 03:53 PM
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.
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?
CODE
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);
// 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;
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
csharp
// 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: 6 Apr, 2008 - 04:52 AM
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:
CODE
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;
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); }
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?
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?
| 