将数据库读取为所有文本
我正在将 xlsx 文件作为数据库读取来完成一些工作，
我注意到它以 int 和 date 的形式读取一些字段，尽管我只想将其全部作为文本输入。有没有办法覆盖这个功能？
下面的代码
（请随时指出我可以用我的代码做得更好的任何事情）
private DataSet ExceltoDT(OpenFileDialog dialog)
{
try
{
string connst = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dialog.FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO\";";
string sheet = "Sheet1$";
string strSQL = "SELECT * FROM [" + sheet + "]";
//string Table = "081710";
OleDbConnection xlsdb = new OleDbConnection(connst);
xlsdb.Open();
OleDbDataAdapter adp = new OleDbDataAdapter(strSQL, xlsdb);
DataSet ds2 = new DataSet();
adp.Fill(ds2);
adp.Dispose();
xlsdb.Close();
xlsdb.Dispose();
return ds2;
}
catch (StackOverflowException stack_ex2)
{
MessageBox.Show("(2007 Excel file) Stack Overflowed!" + "\n" + stack_ex2.Message);
return null;
}
catch (OleDbException ex_oledb2)
{
MessageBox.Show("An OleDb Error Thrown!" + "\n" + ex_oledb2.Message);
return null;
}
}
Im reading a xlsx file as a db to do some work
i noticed that its reading some feilds in as int and date even though i just want it all to come in as text . is there anyway to override this feature?
Code below
(feel free to point out anything i could be doing better with my code as well)
private DataSet ExceltoDT(OpenFileDialog dialog)
{
try
{
string connst = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dialog.FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO\";";
string sheet = "Sheet1$";
string strSQL = "SELECT * FROM [" + sheet + "]";
//string Table = "081710";
OleDbConnection xlsdb = new OleDbConnection(connst);
xlsdb.Open();
OleDbDataAdapter adp = new OleDbDataAdapter(strSQL, xlsdb);
DataSet ds2 = new DataSet();
adp.Fill(ds2);
adp.Dispose();
xlsdb.Close();
xlsdb.Dispose();
return ds2;
}
catch (StackOverflowException stack_ex2)
{
MessageBox.Show("(2007 Excel file) Stack Overflowed!" + "\n" + stack_ex2.Message);
return null;
}
catch (OleDbException ex_oledb2)
{
MessageBox.Show("An OleDb Error Thrown!" + "\n" + ex_oledb2.Message);
return null;
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在每个单元格值前面添加一个
'（撇号）。这将告诉 Excel“将其视为文本，即使它看起来像数字/日期/其他内容”。不是你想要的吗？然后不要使用数据库连接器，因为它已严重损坏。当您有一列包含混合单元格时，您会注意到这一点。在这种情况下，数据库驱动程序将查看前 8 行并将类型设置为它找到的大多数类型，并为该列中不适合的任何内容返回 NULL。 您可以通过破解注册表来修复该问题。
相反，使用 OLE API 打开工作簿，然后从那里开始，逐行读取，根据需要转换数据（此 一长串帖子 应该包含从 C# 访问 Excel 的所有可能方法以及您可以找到的所有错误和问题遇到）。
Add a
'(apostrophe) in front of every cell value. That will tell Excel "Treat this as text even when it looks like a number/date/whatever".Not what you want? Then don't use the DB connector because it's badly broken. You'll notice that when you have a column with cells that are mixed. In that case, the DB driver will look at the first 8 rows and set the type to the majority of types it finds and return NULL for anything in that column that doesn't fit. You can fix that by hacking your registry.
Instead use the OLE API to open the Workbook and then start from there, reading row by row, converting the data as you need (this long list of posts should contain about every possible way to access Excel from C# plus all the bugs and problems you can encounter).