Click here to Skip to main content
15,917,928 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,
Can anyone tell me how to read the content of an excel file without looping through each cell of the excel file.
The main thing is that I should avoid looping through each cell for reading the content of the excel file.

Thanks & Regards,
Ramana
Posted
Comments
BobJanova 19-Apr-11 5:17am    
This smells like a homework question. Why don't you want to loop?
Ramana Bellary 19-Apr-11 6:29am    
It may take much time to loop through each and every cell and read the content. Instead I'm looking for some approach where I can read the entire content at once.
BobJanova 19-Apr-11 14:18pm    
Then you need to use an array language, for example APL. But C#'s looping is very fast, and I think the Excel COM interface is entirely built around loops and iterators anyway. There is no way that looping around the cells is going to be the performance bottleneck in any non-trivial app.

You could use OleDb to connect to the workbook and read the data into a DataSet

http://forums.asp.net/t/1255191.aspx/1?Read+data+from+Excel+to+a+datatable+C+[^]
 
Share this answer
 
Comments
Ramana Bellary 19-Apr-11 4:50am    
Thanks for your reply. I don't want in Ado.Net .
Dylan Morley 19-Apr-11 4:52am    
So you don't want to use automation and you don't want to use data access (Ado.Net)

What *do* you want?!
Ramana Bellary 19-Apr-11 5:05am    
I want to read the content present in a Excel WorkSheet without loopiing through each cell.
Dylan Morley 19-Apr-11 5:06am    
Right - so why not use data access, OleDb? That doesn't loop through each cell.

Why don't you want to use Ado.Net?
Ramana Bellary 19-Apr-11 6:27am    
I want to read the excel content , convert it into binray format and then insert into DataBase. I'm not sure if it can be done by using Ado.Net or not. If it is possible can u provide me the source code ?
Will looking into the ADO.Net[^] angle help?
 
Share this answer
 
Comments
Ramana Bellary 19-Apr-11 4:49am    
no
walterhevedeich 19-Apr-11 5:24am    
Any logical reason why you dont want to do it in ADO.Net? Just Curious.
Ramana Bellary 19-Apr-11 6:27am    
I want to read the excel content , convert it into binray format and then insert into DataBase. I'm not sure if it can be done by using Ado.Net or not. If it is possible can u provide me the source code ?
walterhevedeich 19-Apr-11 9:38am    
not that I know of. The only possible way I know is to use ADO.Net. And since you are inserting it to the database, why not use this approach?
As far as I know, there is no way to read it "without" looping through the cells. Think about it, if you don't read the cells, then how are you going to get its value.

But I can suggest you a way to reduce the looping.
You can use the UsedRange property of the Sheet which will return you the range where your cells have values, so that you don't have to read the whole sheet searching for values.

C#
Using xl = Microsoft.Office.Interop.Excel;
private xl.Application xlApp;

xlApp = Globals.ThisAddin.Application;
xl.WorkBook workbook = xlApp.ActiveWorkbook;  //Get the active workbook object
xl.WorkSheet worksheet = workbook.ActiveSheet;  //Get the active worksheet object
xl.Range usedRange = worksheet.UsedRange;  //This will return the whole range where you data is used in it
String value;
// Loop through the range to get data of each cell
foreach(xl.Range rng in usedRange.Cells)
{
 value = rng.Value;
}
 
Share this answer
 
Comments
Ramana Bellary 19-Apr-11 4:50am    
Thanks for your reply.Even here it will loop through each cell, which I don't want.
Tarun.K.S 19-Apr-11 5:00am    
Yes it will, but only in a certain range which is guaranteed to have data in the cell.
Ramana Bellary 19-Apr-11 5:11am    
Instead of looping through each used cell , can't get the entire data at once ?
Ramana Bellary 19-Apr-11 5:04am    
I want to read the excel content without looping through each cell , even in case of Used range also. Is it possible to do so ?
Tarun.K.S 19-Apr-11 6:05am    
If you have small data, then just use UsedRange, because using DataSet(as described in other answers) replicates the whole sheet into a database. Anyway even after using DataSet, you have to loop through the database to get the value.

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