Populate listbox from excelsheet

[shwetas1]

Hi All,

I want to populate the listbox columwise from excelsheet using C# .net.
Column 1 of excelsheet should map with listbox1 and column 2 with listbox2,.....
Excel sheet is placed at folder C:\Data\..
Any ideas?

Thanks,
Shweta

[kvprajapati]

Use OleDB data provider or Office InterOp API.

[shwetas1]

Do you have that line of code.
Please share.

Thanks.

Use OleDB data provider or Office InterOp API.

[shwetas1]

Hi,

Could you please help to correct this code:

string excelFilePath = "d:\\documents and settings\\rsr44\\My Documents\\Exporttoxls\\TestReport.xls";
        string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
        Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";  
        string myExcelDataQuery = "Select * from [TestReport$],sExcelConnectionString";
        OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
        OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
        OleDbConn.Open();
        OleDbDataReader dr = OleDbCmd.ExecuteReader();

and please share if any one have i am new to it.

Thanks.

[kvprajapati]

Take a look at,

string cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\sample.xls;Extended Properties=" + "\"Excel 8.0;HDR=YES\"";
            using (System.Data.OleDb.OleDbDataAdapter adp=new System.Data.OleDb.OleDbDataAdapter("select * from [TestReport$]",cnstr))
            {
                DataTable dt = new DataTable();
                adp.Fill(dt);
                listBox1.DataSource = dt;
                listBox1.DisplayMember = dt.Columns[0].ColumnName;
                listBox1.ValueMember  = dt.Columns[1].ColumnName;
            }

[shwetas1]

Thanks for the response.

I am getting error at " adp.Fill(dt);"
Error :
'TestReport$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Please help me with this.

Thanks.

[shwetas1]

Any ideas?