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

  • Question

  •  

    hi,

     

    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;

     

    'GRRRR

     

    instead of

     

    GRRRR

     

    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.

     

    GrahamO

    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:

    http://www.eggheadcafe.com/software/aspnet/29273491/how-can-i-remove-hidden-a.aspx

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

    http://www.knowdotnet.com/articles/datasetmerge.html


    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
    Moderator