I am trying to automate excel using "jet provider 4.0". iam facing a strange problem, please find my approach and the problem below.
1) i have written the following code to read data from the excelsheet. iam passing source file name (srcFileName ) and worksheetname as parameters to this mehtod.
ObjCommand = new
OleDbCommand("SELECT * FROM [" + worksheetName +
"$]", objConn);objAdp =
new OleDbDataAdapter();
objAdp.SelectCommand = ObjCommand;
dataTable = new
DataTable();
objAdp.Fill(dataTable);
objConn.Close();
i have used IMEX=1 because my excel sheet contains text inbetween rows, so i was unable to read some of the data and one of the forum suggested in using IMEX=1 to read data in string format.
2) Please find below screenshot of the excel file along with some data. As can bee seen there are values like 6,5 and 6 (1st row) etc., ..but actually when we see these values in the excel formulae bar the values will be 5.923, 5.432, 6.456 respectively
for the first row and similarly for the remaining rows. They have applied some format for the excel cells to display in that manner. When i execute the above code and read excel data into datatable, i sometimes get the values as 6,5,6 and some times i get
the same cells as 5.923, 5.432, 6.456. even without changing any code iam getting this behaviour. Actually i need to read the values as 6,5 and 6 respectively.
Do i need to change anything in my code or do i need to completely remove my code and use interop dlls to read the data in our own format. But my primary concern is i need to read arount 30 excel worksheets with data in the range 0f 100 rows and 10 columns.
jet provider is lot quicker compared to using interop.
6
5
6
16
15
12
22
20
18
86%
78%
70%
1
0
1
0
1
0
24
10
27
25
28
33
25
26
21
Thanks in advance,
Bhargav
C#C#. NET 2005excelCom InteropC# code.net 2.0.net 2.0 c#excel spreadsheetc# com interop castC# codeC# 2005C# 2.0<C#>
bhargava.kva...
0 Points
1 Post
Excel Automation using jet provider 4.0
Dec 08, 2008 05:21 PM|LINK
hi Friends,
I am trying to automate excel using "jet provider 4.0". iam facing a strange problem, please find my approach and the problem below.
1) i have written the following code to read data from the excelsheet. iam passing source file name (srcFileName ) and worksheetname as parameters to this mehtod.
DataTable dataTable = null; OleDbConnection objConn = null; OleDbCommand ObjCommand = null; OleDbDataAdapter objAdp = null; string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcFileName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""; objConn = new OleDbConnection(connectionString);objConn.Open();
ObjCommand = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", objConn);objAdp = new OleDbDataAdapter();objAdp.SelectCommand = ObjCommand;
dataTable = new DataTable();objAdp.Fill(dataTable);
objConn.Close();
i have used IMEX=1 because my excel sheet contains text inbetween rows, so i was unable to read some of the data and one of the forum suggested in using IMEX=1 to read data in string format.
2) Please find below screenshot of the excel file along with some data. As can bee seen there are values like 6,5 and 6 (1st row) etc., ..but actually when we see these values in the excel formulae bar the values will be 5.923, 5.432, 6.456 respectively for the first row and similarly for the remaining rows. They have applied some format for the excel cells to display in that manner. When i execute the above code and read excel data into datatable, i sometimes get the values as 6,5,6 and some times i get the same cells as 5.923, 5.432, 6.456. even without changing any code iam getting this behaviour. Actually i need to read the values as 6,5 and 6 respectively.
Do i need to change anything in my code or do i need to completely remove my code and use interop dlls to read the data in our own format. But my primary concern is i need to read arount 30 excel worksheets with data in the range 0f 100 rows and 10 columns. jet provider is lot quicker compared to using interop.
Thanks in advance,
Bhargav
C# C#. NET 2005 excel Com Interop C# code .net 2.0 .net 2.0 c# excel spreadsheet c# com interop cast C# code C# 2005 C# 2.0 <C#>
dhawalseth
Member
4 Points
2 Posts
Re: Excel Automation using jet provider 4.0
Dec 09, 2008 09:10 AM|LINK
hi i have just finished some coding like this only, please keep me in the loop if any of you can achieve this.