blocks|key|4679321|text|我想出了解决这个问题的办法，我把连接字符串改成了最新的oleDB提供程序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4679322|var+connectionString+=+string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data+Source={0};Extended+Properties='Excel+12.0+Xml;HDR=YES;'",+fileName);|code-block|syntax|javascript|4679323|我还确保我的excel数据最后一行之后的空格不会被读取为值。|4679324|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I sort of figured out a work around to this problem I changed the connection string to the latest oleDB provider.

<pre><code>var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'", fileName);
</code></pre>

I also made sure that the empty space after the last row of my excel data is not being read as a value.

blocks|key|2578059|text|查找不同的日期格式，在我的示例中，其中一列是dd/mm/yyyy格式，另一列是mm/dd/yyyy格式。一旦纠正了日期格式，数据导入就可以工作了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2578060|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Look for different date formats, in my case one of the column was in dd/mm/yyyy format and the other was in mm/dd/yyyy. Once the date formats were rectified data import worked.

blocks|key|2579781|text|在我的例子中，我在表格中手动插入了一个格式错误的日期，它是01/01/0019而不是01/01/2019。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2579782|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In my case, I had a date in my table inserted manually with the wrong format, it was 01/01/0019 instead of 01/01/2019.

blocks|key|2578084|text|我在oledb中阅读excel时遇到了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2578085|我发现在excel中，起始记录中的许多日期列都是空的。我在excel中有6000条记录，其中一个数据列有大约4000个空单元格，所以excel不能理解要转换什么单元格，所以我添加了一个用日期填充的虚拟记录并处理我的文件。或者，您可以移动顶部有日期值的几条记录|2578086|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I have this issue while reading excel in oledb.
I found that in excel many date columns are empty in the beginning records. i had 6000 records in excel and one of the datecolumn has around first 4000 empty cells so excel is not able to understand what to convert the cell so i added one dummy record fill with date and process my file. Or you can move few records which has date value in it at top

blocks|key|4679530|text|你怎么解决这个问题呢？您好！将您的数据插入到第1行的虚拟行中，并强制将有问题的列转换为字符串(仅在本例中-这是一个数据类型错误，因此请根据类型应用修复)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4679531|有必要理解数据适配器做了什么，它通过检查默认情况下的前8行数据(sans+header+if+HDR=Yes+in+connect+string)来解释每一列的数据类型，并决定一种数据类型(它可以被覆盖-yes有一个覆盖-在连接字符串中为16行-几乎从来不是很有用)。|4679532|数据适配器可以做其他令人讨厌的事情，比如在混合数据类型的列中跳过字符串，比如string/double+(它实际上只是一个字符串列，但如果第一行都是双精度的，则不会跳到适配器)。它甚至不会给你这个例子中的错误。|4679533|这通常发生在来自包含“自由格式”列的ERP源的数据中。用户定义的列是常见的可疑列。它可能很难在其他数据类型问题中找到。我曾经花了相当多的时间来解决一个列的问题，该列的类型是最大长度为255个字符的字符串。在数据的深层，有一些单元格超过了这个长度并抛出了错误。|4679534|如果您不想在使用数据适配器时达到“基因工程”的水平，那么解决此类问题的最快方法就是对您的数据进行处理，并将有问题的列强制为正确的类型(或不正确的类型，如果需要，您可以在自己的代码中进行更正)。B计划是将数据返回给客户/用户，并告诉他们进行更正。祝B计划好运。它不是A计划是有原因的。|4679535|更多关于通过连接字符串和适配器的类似问题的操作-但要小心，结果不会是100%25愚蠢的。我已经广泛地测试了如何更改IMEX和HDR设置。如果你想快速完成这个项目，嗨！杰克数据。OleDB+&+mixed+Excel+datatypes+:+missing+data|offset|length|4679536|这是另一个上下文相似的帖子，注意所有可能的耗时的解决方案。我还没有确信有更好的解决方案，它完全违背了程序员每天早上带到键盘上的逻辑。太糟糕了，你有工作要做，有时候你还得当个黑客。DateTime+format+mismatch+on+importing+from+Excel+Sheet|4679537|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data|1|https://stackoverflow.com/questions/965017/datetime-format-mismatch-on-importing-from-excel-sheet^0|0|0|0|0|0|2E|18|0|0|2H|1I|1|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|11|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@]|9|@$L|15|M|16|1|17]]|A|$]]|$1|N|3|O|5|6|7|18|8|@]|9|@$L|19|M|1A|1|1B]]|A|$]]|$1|P|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]|X|$5|S|T|U|A|$V|Y]]]]

How can you fix this? Hi!jack your data with a dummy row at row 1 and force the column(s) in question into a string (in this case only - it is a data type error, so apply the fix according to the type).
It is necessary to understand what the data adaptor does, which interprets the data type of each column by examining, by default, the first 8 rows of data (sans header if HDR=Yes in connect string) and deciding on a data type (it can be over-ridden -yes there is an override - in the connection string to 16 rows - almost never very helpful).
Data adaptors can do other nasty things, like skip strings in columns of mixed data types, like string/double (which is really just a string column, but not to the adaptor if the first rows are all double). It won't even give you the courtesy of an error in this example.
This often occurs in data coming from ERP sources that contains &quot;Free Form&quot; columns. User defined columns are the usual suspects. It can be very difficult to find in other data type issues. I once spent quite a bit of time resolving an issue with a column that typed as a string with a max length of 255 chars. Deep in the data there were cells that exceeded that length and threw errors.
If you don't want to advance to the level of &quot;Genetic Engineering&quot; in working with data adaptors, the fastest way to resolve an issue like this is to hi!jack your data and force the column(s) in question to the correct type (or incorrect, which you can then correct in your own code if need be). Plan B is to give the data back to the customer/user and tell them to correct it. Good luck with Plan B. There is a reason it isn't Plan A.
More on manipulating via the connection string and similar issues with the adaptor - but be wary, results are not going to be 100% fool proof. I've tested changing IMEX and HDR settings extensively. If you want to get through the project quickly, hi!jack the data. <a href="https://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data">OleDB &amp; mixed Excel datatypes : missing data</a>
Here is another posting similar in context, note all of the possible time consuming solutions. I have yet to be convinced there is a better solution, and it simply defies the logic a programmer brings to the keyboard every morning. Too bad, you have a job to do, sometimes you have to be a hack. <a href="https://stackoverflow.com/questions/965017/datetime-format-mismatch-on-importing-from-excel-sheet">DateTime format mismatch on importing from Excel Sheet</a>

I have been working with excel spreadsheets and so far I never had any problems with them.. But this error,"Not a legal OleAut date.", showed up out of the blue when I tried to read an excel file. Does anyone know how I can fix this. Here is the code I use to read the excel and put the data into a dataset. It has worked fine previously but after I made some changes (which doesn't involve dates) to the data source this error showed up.

<pre><code>var fileName = string.Format("C:\\Drafts\\Excel 97-2003 formats\\All Data 09 26 2012_Edited.xls");
 var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

 var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

 DataSet Originalds = new DataSet();
 adapter.Fill(Originalds, "Employees"); // this is where the error shows up
</code></pre>

How to fix "Not a legal OleAut date." when reading an Excel file in C#?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我一直在使用excel电子表格，到目前为止，我从未遇到过任何问题。但是，当我试图读取excel文件时，这个错误"Not a legal OleAut date.“突然出现了。有人知道我怎么解决这个问题吗。下面是我用来读取excel并将数据放入数据集中的代码。它以前工作得很好，但在我对数据源进行了一些更改(不涉及日期)之后，出现了这个错误。var fileName = string.Format("

问如何修复"Not a legal OleAut date“在C#中读取Excel文件时？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何修复"Not a legal OleAut date“在C#中读取Excel文件时？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何修复"Not a legal OleAut date“在C#中读取Excel文件时？
EN