Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
486 views
in Technique[技术] by (71.8m points)

c# - OLEDB, Writing Excel cell without leading apostrophe

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:TempNew FolderMF_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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

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.

Is the data explicitly text, might it be coerced into a numeric format? (clutching at straws...)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...