Want to build the ChatGPT based Apps? Start here
Become a member
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
C# Corner Home
Technologies
Monthly Leaders
ASK A QUESTION
Forum guidelines
Hanusha Loujith
1.8k
292
5.6k
Reading Data from excel
Mar 11 2021 4:53 PM
Hello,
I have an application which is reading data from excel. I am using the below connection strings:
<add name=
"Excel03ConString"
connectionString=
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"
/>
<add name=
"Excel07ConString"
connectionString=
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'"
/>
When a column is having data like below(some cells with only numeric value and one cell with alphanumeric), the data which is having alphanumeric(ie, data in the 3rd row-23A) is not reading from the excel.The data coming to the datatable is shown in the image image.
Value in the excel file:
Size
34
23A
45
Data coming in the datatable
Below is the code used to read data from excel. Please check and suggest a way to fix the issue:
string
path = Server.MapPath(
"~/Upload/"
);
string
filePath =
string
.Empty;
string
extension =
string
.Empty;
DataTable dtsheet =
new
DataTable();
DataSet ExcelData =
new
DataSet();
if
(postedfile !=
null
)
{
if
(Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedfile.FileName);
extension = Path.GetExtension(postedfile.FileName);
postedfile.SaveAs(filePath);
}
string
connectionstring =
string
.Empty;
switch
(extension)
{
case
".xls"
:
//for 97-03
connectionstring = ConfigurationManager.ConnectionStrings[
"Excel03ConString"
].ConnectionString;
//connectionstring = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break
;
case
".xlsx"
:
//07 and above
connectionstring = ConfigurationManager.ConnectionStrings[
"Excel07ConString"
].ConnectionString;
break
;
}
connectionstring =
string
.Format(connectionstring, filePath);
using
(OleDbConnection connExcel =
new
OleDbConnection(connectionstring))
{
using
(OleDbCommand cmdExcel =
new
OleDbCommand())
{
using
(OleDbDataAdapter odaExcel =
new
OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//firstsheetname
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null
);
sheetName = dtExcelSchema.Rows[0][
"TABLE_NAME"
].ToString();
connExcel.Close();
//read data
connExcel.Open();
cmdExcel.CommandText =
"SELECT * FROM ["
+ sheetName +
"]"
;
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dtsheet);
connExcel.Close();
}
}
}
Thanks,
Hanusha
Reply
Answers (
1
)
Gridview bind, searching and pagination for jquery using asp.net
Change text name