none
Update XLS file without using header names RRS feed

  • Question

  • Hello all,

    I have datagridview, where I am loading my XLS file.

    Then I just run through it and I am using something like:

    cmd.CommandText = "UPDATE [File$A1:J3000] SET COLUMN_WITH_VALUE = 'VALUE' WHERE COLUMN_WITH_CODE = VALUE_FOR_CODE;
    cmd.ExecuteNonQuery();

    It works fine when I have column headers...

    But I also have some files without column names. Just data. So my question is how I can update let's say third column if value at first column meet some criteria.

    Sunday, October 15, 2017 11:33 AM

All replies

  • i handled Excel like this long time ago, so my memory is not so correct though, it has "Hdr=No" options within it.

    But, using Excel without header is somewhat dangerous. It cut out your information and make it as header (you lose the first line) OR it adds some stuid wordings like "F1", "F2".. as heading so you have needless information in your file.

    this is only from my memory, some nice guy will tell you the better solution. Good Luck





    Sunday, October 15, 2017 2:19 PM
  • Hello Vasqez21,

    For Given:


    You could update the value from excel file as below.

      string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\Test\123.xlsx; Extended Properties='Excel 8.0; HDR=no;'";
                OleDbConnection connExcel = new OleDbConnection(strConn);
                connExcel.Open();
    
                OleDbCommand cmd = new OleDbCommand("UPDATE [Sheet1$] SET F1 = 13, F2 = 'NeilNeil' where F3 =22051", connExcel);
                int result = cmd.ExecuteNonQuery();
                Console.WriteLine(result);
               connExcel.Close();  

    Sincerely,
    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 16, 2017 11:35 AM
    Moderator
  • I have not tested yet though, "Excel 8.0" part, is it correct ?

    Monday, October 23, 2017 2:35 PM
  • Hello Using_Word_Everydai,

    The following is correspondence between office and excel version.

    • office97 : 8.0
    • officeXP(2002) : 10.0
    • office2007: 12.0

    I have tested  on my side, "Excel 8.0" works well including "Excel 12.0".For some basic operation it seems that doesn't have strict version limited.

    Sincerely,
    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 25, 2017 12:14 PM
    Moderator
  • Hooo, that is surprising tip. Thanks.

    I've never imagine like this.

    regards

    Wednesday, October 25, 2017 10:20 PM