You must Sign In to post a response.
  • Category: SQL Server

    Get excel sheets using sql query

    How to get all excel sheet name using open row set ..

    What is the query .?
  • #768173
    What's your specific requirement? following links may help

    http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database
    http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Worksheet-Name-Rename-Excel-Sheet-and-Set-Tab-Color.html

  • #768179
    You can use OLEDB object to read excel file, with that technique you can query on dataset, see below method
    There are a couple of ways to refer to an Excel table:
    1.Using sheet name: With the help of sheet name, you can refer to Excel data, you need to use '$' with sheet name, e.g. Select * from [Sheet1$]
    2.Using Range: We can use Range to read Excel tables. It should have specific address to read, e.g. Select * from [Sheet1$B1:D10]
    Here $ indicates the EXCEL table/sheet already exists in workbook, if you want to create a New workbook/sheet, then do not use $, look at the sample below:

    // Connect EXCEL sheet with OLEDB using connection string
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
    conn.Open();
    OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
    ("select * from [Sheet1$]", conn);
    DataSet excelDataSet = new DataSet();
    objDA.Fill(excelDataSet);
    dataGridView1.DataSource = excelDataSet.Tables[0];
    }

    //In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
    // with the help of data adaptor we can load records in dataset

    //write data in EXCEL sheet (Insert data)
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
    try
    {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange)
    VALUES ('DEC','40','60','80');";
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    //exception here
    }
    finally
    {
    conn.Close();
    conn.Dispose();
    }
    }

    //update data in EXCEL sheet (update data)
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
    try
    {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    //exception here
    }
    finally
    {
    conn.Close();
    conn.Dispose();
    }
    }

    check below link
    http://www.codeproject.com/Articles/1088970/Read-Write-Excel-file-with-OLEDB-in-Csharp-without

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768434
    This is the query

    select *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=D:\excel.xlsx' ,
    'SELECT * FROM [sheetname$]');


Sign In to post your comments