C# OLEDB，在没有前导撇号的情况下编写 Excel 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/755621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
OLEDB, Writing Excel cell without leading apostrophe
提问by Maciej
I'm writing to Excel file using OLEDB (C#). What I need is just RAW data format.
我正在使用 OLEDB (C#) 写入 Excel 文件。我需要的只是 RAW 数据格式。
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:
我试过像这样使用 IMEX=1 ：
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.
Microsoft Jet 数据库引擎找不到对象“C:\Temp\New Folder\MF_2009_04_19_2008-11-182009_DMBHCSAM1118.xls”。
确保该对象存在并且您正确拼写了它的名称和路径名。
Finally I've tried use IMEX=0 like this:
最后我试过像这样使用 IMEX=0 ：
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...)
不幸的是，撇号仍然存在问题（所以我的每个值看起来都是：'123、'abc 等...）
Any idea?
任何的想法？
采纳答案by Lunatik
http://support.microsoft.com/kb/257819has a statement to the effect that this behaviour might be unavoidable when inserting text into Excel via ADO:
http://support.microsoft.com/kb/257819有一个声明，表示在通过 ADO 将文本插入 Excel 时，这种行为可能是不可避免的：
A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.
使用 ADO 编辑 Excel 数据的注意事项：使用 ADO将文本数据插入 Excel 时，文本值前面带有单引号。这可能会导致以后在处理新数据时出现问题。
Is the data explicitly text, might it be coerced into a numeric format? (clutching at straws...)
数据是明确的文本，是否可能被强制转换为数字格式？（抓着吸管……）
回答by jwendl
回答by Gineer
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.
我知道在 Excel 中输入数据时，在它前面加上撇号是一种将其放入文本字段的简单方法。您确定数据实际上不包含撇号吗？如果它在输入时添加到数据中，您唯一的选择是在导入时捕获它们并在一些自定义代码中处理它们。
回答by Adarsha
Check the resistry Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
检查电阻 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.
这决定了在决定列的格式之前应该扫描多少行。默认值为 8，0 将强制 ADO 在选择适当的数据类型之前扫描所有列值。
回答by Ed Harper
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?
它可能不符合您的要求，尤其是当您需要在 Excel 工作表中设置格式时，但您是否考虑过将数据写入 CSV 文件并使用 .XLS 扩展名保存？
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.
您可以尝试的另一件事是在尝试加载数据之前，将 Excel 工作表中目标单元格的格式数据类型明确设置为键入“文本”（如通过 Excel 中的格式 > 单元格）。默认情况下，单元格的类型为“常规”，驱动程序可能会添加撇号以强制您的数据显示为文本。
回答by TimS
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:
你可以只使用 Excel DSN 吗？它似乎无处不在。我不知道 .NET，所以请稍加保留，但这是我直接从库存表中获取的 OLEDB 查询的连接字符串：
"Provider=MSDASQL.1;Persist Security Info=True;Extended Properties
=""DSN=Excel Files;DBQ=" & filePath & "\" & fileName &
";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"""
And I used this basic INSERT statement:
我使用了这个基本的 INSERT 语句：
INSERT INTO rngOutput VALUES (1, 'ABC', '.00', 1300)
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?
当我这样做时，我的数据范围内没有任何撇号。我也在使用 Excel 2007，我看到您使用 Excel 8.0 作为驱动程序？
Hopefully that nudges you toward a solution!
希望这能推动您找到解决方案！
回答by Lunatik
Try the following hack to resolve the issue. Modify the template as per the instructions
尝试以下 hack 来解决问题。按照说明修改模板
- In the first data row just below the header row. Format the columns in the required format.
- Enter some dummy values like space for characters, 0 for numeric values etc.
- Hide the first data row that has the dummy values and save the template
- 在标题行正下方的第一个数据行中。以所需格式格式化列。
- 输入一些虚拟值，例如字符的空格，数字的 0 等。
- 隐藏具有虚拟值的第一个数据行并保存模板
Now run your insert script using ADO.net
现在使用 ADO.net 运行您的插入脚本
-Venkat Kolla
-文卡特·科拉
回答by ramchandar
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).
为模板文件中附加了撇号的列插入一些虚拟值。例如，对于 Name 列放置类似这个 dummyname 的内容，而 age 列放置 99。而不是在模板中插入新行，只需更新该行（Update..... where Name = 'dummyname' and age =99）。
This has worked for me..
这对我有用..
Hope it works for you also!
希望它也适用于您！
