You must Sign In to post a response.
  • Category: ASP.NET

    Insert Excel to Sql Database for asp.net

    Insert Excel to Sql Database for asp.net

    coding plzzzz
  • #240204
    http://support.microsoft.com/kb/319951/EN-US/ - It may help you

  • #240206
    you can insert values from excel to sql

    first you have to store excel data in datatable,
    you can store data in datatable as

    string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
    datatable dt=new datatable();
    Oledbconnection con=new oledbconnection(constring);
    oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
    da.fill(dt);

    now you can store datatable data into sql using for/while loop.

    Regards,
    Sujit Kumar

  • #240229
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69818


    // Connection String to Excel Workbook
    string excelConnectionString = @"Provider=Microsoft
    .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
    Properties=""Excel 8.0;HDR=YES;""";

    // Create Connection to Excel Workbook
    using (OleDbConnection connection =
    new OleDbConnection(excelConnectionString))
    {
    OleDbCommand command = new OleDbCommand
    ("Select ID,Data FROM [Data$]", connection);

    connection.Open();

    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader())
    {
    // SQL Server Connection String
    string sqlConnectionString = "Data Source=.;
    Initial Catalog=Test;Integrated Security=True";

    // Bulk Copy to SQL Server
    using (SqlBulkCopy bulkCopy =
    new SqlBulkCopy(sqlConnectionString))
    {
    bulkCopy.DestinationTableName = "ExcelData";
    bulkCopy.WriteToServer(dr);
    }
    }
    }

  • #240237
    hi muthu,

    check this one:
    excel to sql and vice versa:

    Bind/Export Gridview data to EXCEL file---- ON button click:

    protected void btnsubmit_Click(object sender, EventArgs e)
    {

    grdsqldata.PageSize = Convert.ToInt32(ViewState["rows"]);
    grdsqldata.DataBind();
    GridViewExportUtil.Export("Book1.xls",this.grdsqldata);

    }


    public static void Export(string fileName, GridView grd)
    {
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader(
    "content-disposition", string.Format("attachment; filename={0}", fileName));
    HttpContext.Current.Response.ContentType = "application/ms-excel";

    using (StringWriter sw = new StringWriter())
    {
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
    // Create a form to contain the grid

    Table table = new Table();
    table.GridLines = grd.GridLines;

    // add the header row to the table

    if (grd.HeaderRow != null)
    {
    //GridViewExportUtil.PrepareControlForExport(grd.HeaderRow);
    table.Rows.Add(grd.HeaderRow);
    table.GridLines = grd.GridLines;
    table.BackColor = System.Drawing.Color.Beige;
    }

    // add each of the data rows to the table
    foreach (GridViewRow row in grd.Rows)
    {
    //GridViewExportUtil.PrepareControlForExport(row);
    table.Rows.Add(row);
    }

    // add the footer row to the table
    if (grd.FooterRow != null)
    {
    table.GridLines = grd.GridLines;
    //GridViewExportUtil.PrepareControlForExport(grd.FooterRow);
    table.Rows.Add(grd.FooterRow);
    }

    // render the table into the htmlwriter

    table.RenderControl(htw);

    // render the htmlwriter into the response
    HttpContext.Current.Response.Write(sw.ToString());
    HttpContext.Current.Response.End();
    }
    }
    }


    Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
    Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

    // Connection String to Excel Workbook
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

    // Create Connection to Excel Workbook
    using (OleDbConnection connection =
    new OleDbConnection(excelConnectionString))
    {
    OleDbCommand command = new OleDbCommand
    ("Select ID,Data FROM [Data$]", connection);

    connection.Open();

    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader())
    {
    // SQL Server Connection String
    string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

    // Bulk Copy to SQL Server
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    {
    bulkCopy.DestinationTableName = "Sqltablename";
    bulkCopy.WriteToServer(dr);
    }
    }
    }

  • #285805
    First execute this procedure

    EXEC sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    EXEC sp_configure ‘Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE;

    GO

    After executing procedure then write the query


    Insert into dbo.tableName
    select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:\contact.xls',
    'select * from [Sheet1$]‘)

    Regards,
    Sujit Kumar


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.