insert into EXCEL sheet... apostrohope's RRS feed

  • Question




    I am in awe of how cool the ADO stuff works with excel. Right now I'm in the middle of coding up a class that;


    1) creates a connection to an excel file with a connection string like this;


    // works fine

    string connexionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Results.xls;Extended Properties= ""Excel 8.0;HDR=YES;""";




    2) Then it creates some worksheets (no problem) using a commmand like this.


    // works fine

    worksheetCreationCommands.Add("CREATE TABLE [Errors] ( IDINTEGER, ErrorMessage VARCHAR(100))");



    3) Then I try to insert into the table/excel workshhet. I see that the data is getting inserted ok, but each data is preceded by an apostrophe. so after calls to this;


    command.CommandText = "INSERT INTO [Errors$] (ID, ErrorMessage) VALUES('123123', 'GRRRR')";


    I'm seeing that the excel file contains;




    instead of




    Note the leading apostrophe. I'm new to this stuff. Any ideas how I can have them removed?


    Also, is there a bulk method to get data FROM a DataTable object and have it copied directly into the excel worksheet? I can do it manually but I think there must be a quick way provided by MS.


    Sorry if these questions are basic. I'm new to all this and of course it has to be delivered by yesterday etc. etc.


    thanks a million. This stuff rocks.



    Friday, June 27, 2008 11:24 AM

All replies

  • The apostrophe is there by design. There are a number of ways to handle it though:

    There isn't really a direct method to copy a DataTable to an Excel Worksheet but you can probably use the DataSet Merge method:

    Friday, June 27, 2008 12:37 PM
  • If you need bulk copy of DataTable/DataSet or DataView into Excel files, you could try my component. I designed it specifically for this purpose and to avoid some limitations with Jet. It does not require Jet or Excel and it is 100% NET managed code.

    Tuesday, July 1, 2008 2:16 AM