Doubt in " HOW TO: Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .NET"

[RK]

Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/default.aspx?scid=kb;en-us;306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK.

 

[onedaywhen]

I haven't tested this with ADO.NET (works fine with ADO classic so
consider using OLEDB) but instead of using a defined name e.g.

SELECT * FROM myRange1

try using a range address which is guaranteed to include your data
e.g.

SELECT * FROM [MySheet$A1:G65536]

Depending on connection properties, you shouldn't actually get 65536
rows, rather only the rows which Excel thinks contain data (equivalent
of Excel's UsedRange, perhaps?)