Dear,
Below is my code where i'm successfully import data from Excel to GridView.
The Problem is I have two Column Hour & Description. When i import to an GridView Some of the Description data is not fully imported to an GridView some of the Text is missing. Means
In Excel
Hour Description
1 abcdefghijklmnopqrstuuudddd
In GridView
Hour Description
1 abcdefghijklmno
The Text is quit big around 64 words which inludes all characters also like 2 7/8"
Here VB.Net Code
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Partial Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim connString As String = ""
Dim strFileType As String = ".xls"
Dim path__1 As String = "c:\ccc.xls"
'Connection String to Excel Workbook
If strFileType.Trim() = ".xls" Then
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path__1 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
ElseIf strFileType.Trim() = ".xlsx" Then
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path__1 & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
End If
Dim query As String = "SELECT [Hours (hr)],[Description] FROM [sheet1$]"
Dim conn As New OleDbConnection(connString)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Dim cmd As New OleDbCommand(query, conn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
grvExcelData.DataSource = ds.Tables(0)
grvExcelData.DataBind()
da.Dispose()
conn.Close()
conn.Dispose()
End Sub
End Class
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="WebForm1.aspx.vb" Inherits="WebApplication49.WebForm1" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />
<asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Submit an article or tip