Add apostrophe character to Excel export with OleDB connection

Question

I want to generate Excel report identical that we use before (with old version of Excel). The only problem is that all cells in old style reports were presented as strings with apostrophe character:

I created basically the same report with the next code:

oleDbConnection = new System.Data.OleDb.OleDbConnection(
            "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            fileFullPath +  
            ";Extended Properties='Excel 12.0 Xml;HDR=YES' ");
oleDbConnection.Open();
oleDbCommand.Connection = oleDbConnection;

string commandHeader = String.Join("] char(255), [", headers);
commandHeader = "[" + commandHeader + "]";
commandHeader = "CREATE TABLE data (" + commandHeader + " char(255))";

oleDbCommand.CommandText = commandHeader;
oleDbCommand.ExecuteNonQuery();

foreach (var item in exportList)
{
     string line = String.Join("\",\"\'", new string[] {item.Foreman_ID, item.DateApp.Date.ToString("yyyyMMdd"), item.TimeApp,
     item.Employee_ID, item.ProductionOrder, item.OperationNumber,
     item.ConfirmationNumber, item.date.Date.ToString("yyyyMMdd"), item.TotalHours.ToString("0.000").Replace(",", "."), item.SalaryType, item.TimeType,
     item.ExtraPrice.ToString("0.00").Replace(",", "."), item.ExtraHours, item.ActualPC, item.PcPriceSplit, item.CostCenter});
     line = line.Replace(" ", String.Empty);
     line = "\"\'" + line + "\"";

     oleDbCommand.CommandText = "Insert into data values(" + line + ")";
     oleDbCommand.ExecuteNonQuery();
}

oleDbConnection.Close();

This code generate the same rows where every cell begins with apostrophe character. But if I open generated Excel, then I still see my apostrophe:

If I press on the cell and then will press enter, then this apostrophe will dissapear.

wer is the pblm in the line ,`oleDbCommand.CommandText = "Insert into data values(" + line + ")"`; is it shows the error — Thomas, Feb 23 '17 at 08:57
No, that line works normally. Report is generated but it looks differently with the one that was used before. In new reports a apostrophe character is still visible in cells. — Serbin, Feb 23 '17 at 09:03
Have you tried removing backslash before the actual single quote char in places like `\"\'\'"`? — andrews, Feb 23 '17 at 09:05
i think in the code some were u trying to put two string(`'`) here(\'\') thats y it is coming — Thomas, Feb 23 '17 at 09:06
`string line = String.Join("\",\"\'\'", new string[] {item.Foreman_ID, item.DateApp.Date.ToString("yyyyMMdd"), item.TimeApp,` check thisline and clear one `'` — Thomas, Feb 23 '17 at 09:07
also try adding IMEX=1 to this line `'Excel 12.0 Xml;HDR=YES'` so that it looks like `'Excel 12.0 Xml;HDR=YES;IMEX=1;'` . — andrews, Feb 23 '17 at 09:17
`string line = String.Join("\",\"\'", ` is correct string. The old one with two ' was a mistake (after testing). The final string looks like `"Insert into data values("'213.45", "'Text", "'00.00", ...)"` — Serbin, Feb 23 '17 at 09:39
remove `'` completely in the final string AND add IMEX=1 to connection string. — andrews, Feb 23 '17 at 09:41
correction: actually the value of `IMEX` for updates should be either 0 or 2. I've just checked some of my export to excel stored procedures and they use `IMEX=0` when exporting from DB to Excel. — andrews, Feb 23 '17 at 10:36

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

per my comment above: you don't need to add ' explicitly to every cell value being inserted.

Instead, you should remove the leading ' character from the INSERT .. VALUES (...) statement and change your connection string by adding IMEX=0 or IMEX=2:

oleDbConnection = new System.Data.OleDb.OleDbConnection(
            "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            fileFullPath +  
            ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=0' ");

See What is the default value of IMEX in OLEDB? question for some additional IMEX discussion.

Also there is the mentioned MSFT KB article related to IMEX. From that article possible settings of IMEX are:

0 is Export mode - use for writing-to/insertion-into Excel file
1 is Import mode - use for reading from Excel file
2 is Linked mode (full update capabilities)

Note, the original full MSFT doc describing full IMEX behavior is still to be found.

edited May 23 '17 at 11:53

Community

1
1

answered Feb 23 '17 at 10:31

andrews

2,173
2
16
29

Thank, this helped. One note that this still is not working with 12.0 version, only with the old one, so I have to use this type of string `provider=Microsoft.Jet.OLEDB.4.0;Data Source=...;Extended Properties='Excel 8.0;HDR=YES;IMEX=0'` – Serbin Feb 23 '17 at 11:08
@Serbin hmm, it should be working with v12 too. Try experimenting with IMEX a bit more, maybe set it to 2. However, I remember to get the proper type for each column, I have also created a hidden row just underneath my header row and inserted there dummy strings for string column and 0 for INT columns. Then I got everything working correctly with v12 and IMEX=0. Could you pls also add `export-to-excel` tag to your question? Thanks. – andrews Feb 23 '17 at 11:16

score 0 · Answer 2 · answered Feb 23 '17 at 20:13

0

It sounds like you want to do the same thing that Format=Text does.

formatRange.NumberFormat = "@";

This sets the format of the specified range to text format.

answered Feb 23 '17 at 20:13

ASH

20,759
19
87
200

Add apostrophe character to Excel export with OleDB connection

2 Answers2

Linked