从 Excel 读取数据

发布于 2024-10-25 06:01:03 字数 1231 浏览 3 评论 0原文

我一直在尝试从 Excel 文件中读取数据。已经成功了，但是我遇到了一个问题。每当单元格的格式与单元格中输入的数据不匹配时，我都会得到空数据

例如

如果数据单元格的格式为日期 - dd/mm /yyyy，用户输入13/17/2011，因为日期格式和输入的日期是矛盾的，所以Excel给了我完全空的单元格。仅当单元格格式为文本时，我才能获取输入的数据。

如果输入的日期格式不符合单元格格式集，为什么 Excel 文件会给出空单元格？

这是读取excel数据的代码

if(fileEXT.Equals(".xls"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0");
}
else if(fileEXT.Equals(".xlsx"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=\"Excel 12.0;HDR=YES;\"");
}
else if(fileEXT.Equals(".xlsm"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 12.0 Macro");
}

oledbConn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds,"LocationDetails");

原文

I have been trying to read data from an excel file. It has been successful, but I enountered a problem. Whenever the format of the cell and the data entered in the cell is not matching then I get empty data

e.g

If the data cell is formatted as Date - dd/mm/yyyy, and the user enters 13/17/2011, the as the date format and the date entered is contradictory so the excel gives me entirely empty cell. Only if the cell format is text I get the data as entered.

Why is the excel file giving me empty cell in case the entered date format is not complying with the cell format set?

This is the code that reads the excel data

if(fileEXT.Equals(".xls"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0");
}
else if(fileEXT.Equals(".xlsx"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=\"Excel 12.0;HDR=YES;\"");
}
else if(fileEXT.Equals(".xlsm"))
{
   oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 12.0 Macro");
}

oledbConn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds,"LocationDetails");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梨涡少年 2024-11-01 06:01:03

您可以将连接字符串更改为

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1"

HDR=是；IMEX=1

告诉 OldDb 驱动程序列中的数据采用不同的格式。

You can change your connection string to

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../Portal_Docs/UploadDocs/"+filename+"")+";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1"