OLEDB，写入不带前导撇号的 Excel 单元格
我正在使用 OLEDB (C#) 写入 Excel 文件。 我需要的只是RAW数据格式。
我注意到所有单元格（标题和值）都以撇号（'）为前缀，
这是避免将它们添加到所有文本单元格中的方法吗？
这是我的连接字符串：
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
我尝试像这样使用 IMEX=1：
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
但之后我收到以下错误：
Microsoft Jet 数据库引擎可以找不到对象 'C:\Temp\新文件夹\MF_2009_04_19_2008-11-182009_DMBHCSAM1118.xls'。
确保该对象存在，并且正确拼写其名称和路径名称。
最后我尝试像这样使用 IMEX=0：
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"";
这次没有引发异常。
不幸的是，撇号仍然存在问题 （所以我的每个值看起来像：'123，'abc 等...）
有什么想法吗？
I'm writing to Excel file using OLEDB (C#).
What I need is just RAW data format.
I've noticed all cells (headers and values) are prefixed by apostrophe (')
Is it a way to avoid adding them in all text cells?
Here is my connection string:
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
I've tried use IMEX=1 like this:
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
But after that I'm receiving below error:
The Microsoft Jet database engine could not find the object
'C:\Temp\New Folder\MF_2009_04_19_2008-11-182009_DMBHCSAM1118.xls'.
Make sure the object exists and that you spell its name and the path name correctly.
Finally I've tried use IMEX=0 like this:
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"";
This time no exeptions raised.
Unfortunately there is still problem with apostrophes
(so each my values looks as: '123, 'abc etc...)
Any idea?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
检查电阻
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
这决定在决定列的格式之前应扫描多少行。
默认值为 8，0 将强制 ADO 在选择适当的数据类型之前扫描所有列值。
Check the resistry
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
This decides how many rows should be scanned before deciding the format for the column.
Default is 8, and 0 will force ADO to scan all column values before choosing the appropriate data type.
它可能不适合您的要求，特别是如果您需要在 Excel 工作表中设置格式，但您是否考虑过将数据写入 CSV 文件并使用 .XLS 扩展名保存？
您可以尝试的另一件事是在尝试加载数据之前，显式设置 Excel 工作表中目标单元格的格式数据类型以键入“文本”（如通过 Excel 中的“格式”>“单元格”）。 默认情况下，单元格的类型为“常规”，驱动程序可能会添加撇号以强制将数据显示在文本中。
It may not suit your requirements, particularly if you need to set up formatting in the Excel sheet, but have you considered writing the data out to a CSV file and saving it with an .XLS extension?
The other thing you could try would be to explicitly set format data types of your target cells in the Excel sheet to type "Text" (as through Format > Cells within Excel) before you attempt to load data. By default, the cells will be of type "General", and the driver may be adding the apostrophe to force your data to be displayed at text.
尝试以下技巧来解决该问题。 说明修改模板
运行插入脚本
现在使用 ADO.net -Venkat Kolla
Try the following hack to resolve the issue. Modify the template as per the instructions
Now run your insert script using ADO.net
-Venkat Kolla
http://support.microsoft.com/kb/257819 有一项声明，大意是：通过 ADO 将文本插入 Excel 时，这种行为可能是不可避免的：
数据是否明确为文本，是否可能被强制转换为数字格式？ （抓着稻草……）
http://support.microsoft.com/kb/257819 has a statement to the effect that this behaviour might be unavoidable when inserting text into Excel via ADO:
Is the data explicitly text, might it be coerced into a numeric format? (clutching at straws...)
你可以只使用Excel DSN吗？ 它似乎非常普遍。 我不了解 .NET，所以对此持保留态度，但这是直接从库存表进行 OLEDB 查询的连接字符串：
我使用了这个基本的 INSERT 语句：
当我这样做时，我没有我的数据范围内的任何撇号。 我也在使用 Excel 2007，我看到您使用 Excel 8.0 作为驱动程序？
希望这能促使您找到解决方案！
Could you just use the Excel DSN? It seems to be pretty ubiquitous. I don't know .NET, so take this with a grain of salt, but here's my connection string for an OLEDB Query straight from a stock table:
And I used this basic INSERT statement:
When I did this, I didn't have any apostrophes in my data range. I'm also using Excel 2007, and I see you're using Excel 8.0 as your driver?
Hopefully that nudges you toward a solution!
为模板文件中附加撇号的列插入一些虚拟值。 例如，在“名称”列中输入类似 dummyname 的内容，在“年龄”列中输入 99。无需在模板中插入新行，只需更新该行（更新......其中名称 = 'dummyname' 且年龄 =99）。
这对我有用......
希望它也对你有用！
Insert some dummy values for the columns which has apostrophe attached in the template file. Say for example for Name column put something like this dummyname, and age column put 99. Instead of inserting a new row in the template just update the row (Update..... where Name = 'dummyname' and age =99).
This has worked for me..
Hope it works for you also!
从连接字符串中删除 IMEX=1。
http://www.connectionstrings.com/excel
Remove IMEX=1 from your connection string.
http://www.connectionstrings.com/excel
我知道在 Excel 中输入数据时，在其前面加上撇号是将其输入文本字段的简单方法。 您确定数据实际上不包含撇号吗？ 如果它在输入时添加到数据中，您唯一的选择是在导入时捕获它们并在一些自定义代码中处理它们。
I know that when entering data into Excel, prefixing it with an apostrophe is an easy way to make it into a text field. Are you sure the data does not actually contain the apostrophe? If it's added to the data at entry time, your only option would be to catch them at import time and dealing with them in some custom code.