ACE.OLEDB提供程序错误地读取一些列名 [英] ACE.OLEDB provider incorrectly reads some column names

问题描述

我正在使用ACE.OLEDB从C＃应用程序中读取一个excel文件。
一直到目前为止，一切工作都很好，直到今天我注意到其中一个列名称读取不正确。

这是我在我的excel文件中/ p>

这是我在调试器中所拥有的

基本上将点（。）替换为哈希（＃）由于某些原因

代码很简单，在大多数情况下可以正常工作，不要以为问题出在，只是为了清楚起见， p>

  DataTable data = new DataTable（）; 
 string strAccessConn =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path.FullName +;扩展属性= \Excel 12.0 \; \HDR = YES\ ; \ IMEX = 1; \; 
 OleDbConnection myAccessConn = new OleDbConnection（strAccessConn）; 
 string strAccessSelect =select * from [+ SheetName +];; 
 OleDbCommand myAccessCommand = new OleDbCommand（strAccessSelect，myAccessConn）; 
 OleDbDataAdapter myDataAdapter = new OleDbDataAdapter（myAccessCommand）; 
 myDataAdapter.Fill（0，maxRows，data）; 
  

有没有什么我在做错了，还是OLEDB中的错误？

解决方案

此问题只发生在标题行中，这是因为它阻止了Excel文件头中的点（。）。在Excel文件的标题中的Dot（当转换为XML时）可能会导致一些问题。尽管XML中的。没有任何意义，但是前后连贯的Excel必须有额外的约定（不是100％）。所以在你输入数据的时候可以简单地把＃并将其转换为excel中的标题中的。 c $ c> c $ c

为了解决这个问题，您可以通过将连接字符串头文件格式更改为 HDR = No 来简单地转动头文件。当数据获取不作为标题时，普通的行可以很容易地被处理为浮点数和。不会引起任何问题。

I'm reading an excel file from a C# app using ACE.OLEDB. Everything has worked fine so far until today I noticed that one of the column names is read incorrectly.

Here's what I've got in my excel file

Here's what I've got in my debugger

Basically the dot (".") is replaced with a hash ("#") for some reason.

The code is straightforward and works fine for the most part, don't think the problem is there, but will show it here just for the sake of clarity.

DataTable data = new DataTable();    
string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path.FullName + ";Extended Properties=\"Excel 12.0\";\"HDR=YES\";\"IMEX=1;\"";     
OleDbConnection myAccessConn = new OleDbConnection(strAccessConn);
string strAccessSelect = "select  * from [" + SheetName + "];";
OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);                           
myDataAdapter.Fill(0, maxRows, data);  

Is there something I'm doing wrong or is it a bug in OLEDB?

解决方案

This issue only happens in the Header row and it's due to the fact that it prevents dots (.) from showing in the Excel file's header. Dot's in the header for Excel file (when translated to XML) can cause some issues. Despite the fact that there is no significance for . in XML, how Excel serialize back and forth must have extra conventions (not 100% on this). So while entering data in you can simply put # and it get's translated to . in the header in excel and when you're outputting a header with a . it will show up as # instead.

To go around this issue you can simply turn your headers of by changing your connection string header syntax to HDR=No. When data get in not as headers but normal rows then can be easily handled as float numbers and . won't cause any issues.

这篇关于ACE.OLEDB提供程序错误地读取一些列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！