OLEDB，编写Excel单元格而不引用撇号

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?

任何想法？

8 个解决方案

#1

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:

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...)

数据是否明确是文本，是否可以强制转换为数字格式？（抓着稻草......）

#2

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，所以请考虑一下这个问题，但是这里是直接来自stock表的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', '$1.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!

希望能够帮助您找到解决方案！

#3

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'，age = 99）。

This has worked for me..

这对我有用..

Hope it works for you also!

希望它也适合你！

#4

Remove IMEX=1 from your connection string.

从连接字符串中删除IMEX = 1。

http://www.connectionstrings.com/excel

#5

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时，在其前面加上撇号是一种简单的方法，可以将其作为文本字段。你确定数据实际上不包含撇号吗？如果它在入口时添加到数据中，您唯一的选择是在导入时捕获它们并在一些自定义代码中处理它们。

#6

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扫描所有列值。

#7

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中的单元格）。默认情况下，单元格将为“常规”类型，驱动程序可能正在添加撇号以强制您的数据以文本显示。

#8

-1

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.
输入一些虚拟值，如字符空格，0表示数值等。
Hide the first data row that has the dummy values and save the template
隐藏具有虚拟值的第一个数据行并保存模板

Now run your insert script using ADO.net

现在使用ADO.net运行插入脚本

-Venkat Kolla

#1