Add apostrophe character to Excel export with OleD

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.

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

Answer 2

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.