Category: Visual Studio

02 Feb 2015 00:35

Joined:	04/12/2012
Level:	Silver
Points:	102

Points:	5

Import Excel to Gridview

Dear All,

i need to import excel sheet to vb.net Gridview, and the Gridview datas have to insert into sql server.
it's happening.
But my problem is, while i import from excel to gridview, it also added some extra rows in gridview,
for example, excel contains 3 rows, while it comes to gridview it has 6 rows.

Need to remove empty rows in gridview ?

My Code is ,

Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim DT As DataTable = New DataTable
cn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtPath.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Contact$]", cn)
cn.Open()
cmd.Fill(DT)
'Load the values from Data Table to Grid View

grdContactDetails.DataSource = DT

Regards,
Ramkumar G.R

#756137

02 Feb 2015 01:39

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi,

Nothing issue in your coding side, the issue is in Excel sheet. I think you are copied rows from one sheet to another, at the time of coping the rows might be copied duplicate cells.

As per your point of view you are saying from 4th row to 6th row you are getting this issue right.?

compare 6th row with 7th row in excel sheet there is some difference in cells. I mean up to 6th row the cells are enabled from 7th row onwards its disabled that is the reason it show up to 6th row. If you want to remove unwanted spaces rows. Then select those rows in your excel sheet and delete those and then check it once.

Hope you got my point.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#756138

02 Feb 2015 01:39

Joined:	28/06/2007
Level:	Diamond
Points:	56294

Points:	4

Here is the code to read the data from the excel file and then bind it to the grid view.


String fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
String connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

SqlDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
Dataset ds = new DataSet();

adapter.Fill(ds);

DataTable data = ds.Tables[0];

Thanks & Regards
Anil Kumar Pandey
Microsoft MVP, DNS MVM

Import Excel to Gridview

About dotnetspider.com

Quick Links

General

Services