Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am trying to read data from an Excel spreadsheet without having to declare:

Excel.Application, etc

Is there a way via ADO.Net or somehow using ODBC to retrieve the data ?

e.g find a certain value, range, worksheet ?

Thanks,


Jase
Posted
Comments
HielturryKory 26-Feb-16 5:27am    
Here is another approach how you can read excel files with C# by using this excel library for .NET framework. This can enable you to achieve more fine grained tasks and manipulations over the excel file's content. After reading it you get this in memory hierarchy which enables you, among other, to iterate over rows (and its cells), columns (and its cells), specified cell range, etc.

Sure. Something like this is what you are looking for, I believe:

http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp[^]
 
Share this answer
 
Comments
AU Jase 29-Jun-12 0:54am    
what happens if you want to select a range,
to drop a column, drop rows ?
Here it is how to do it using ODBC and ADO.Net

C#
private void Form1_Load(object sender, EventArgs e)
{   
   try
   {        
        string sheetName = "Sheet1$";// Read the whole excel sheet document      
        DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", sheetName);
        dataGridView1.DataSource = sheetTable;

        string sheetNameWithRange = "Sheet1$A1:D10"; // Read excel sheet document from A1 cell to D10 cell values.
        DataTable sheetTableWithRange = loadSingleSheet(@"C:\excelFile.xls",sheetNameWithRange);
        dataGridView2.DataSource = sheetTableWithRange;
   }
   catch (Exception Ex)
   {
        MessageBox.Show(Ex.Message, "");
   }  
}        

private OleDbConnection returnConnection(string fileName)
{
    return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}

private DataTable loadSingleSheet(string fileName, string sheetName)
{           
    DataTable sheetData = new DataTable();
    using (OleDbConnection conn = this.returnConnection(fileName))
    {
       conn.Open();
       // retrieve the data using data adapter
       OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
        sheetAdapter.Fill(sheetData);
    }                        
    return sheetData;
}
 
Share this answer
 
v2
Comments
Pandvi 28-Jun-12 21:27pm    
Good! my 5!
Wonde Tadesse 28-Jun-12 22:27pm    
Thanks
Manoj Kumar Choubey 29-Jun-12 0:49am    
nice
Wonde Tadesse 29-Jun-12 23:06pm    
Thanks
AU Jase 1-Jul-12 19:28pm    
is there a way to retrieve a specific range in excel (like GetRange("A1")?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900