Click here to Skip to main content
11,642,381 members (66,221 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET Excel Gridview
How to import excel file to grid view in asp.net using c# AND save grid view to database sql server 2005.
Posted 26-Oct-12 4:18am
Comments
pramodhegde88 at 26-Oct-12 11:36am
   
This link might help you.
http://www.codeproject.com/Tips/370844/SQL-Import-Data-from-Excel
Sandeep Mewara at 26-Oct-12 16:18pm
   
Tried anything?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You can get data from Excel like database using System.Data.OleDb;
using System.Data.OleDb;
public DataSet ImportFromExcel(string file)
{
    // Create new dataset
    DataSet ds = new DataSet();
 
    // -- Start of Constructing OLEDB connection string to Excel file
    Dictionary<string, string> props = new Dictionary<string, string>();
 
    // For Excel 2007/2010
    if (file.EndsWith(".xlsx"))
    {
        props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
        props["Extended Properties"] = "Excel 12.0 XML";
    }
    // For Excel 2003 and older
    else if (file.EndsWith(".xls"))
    {
        props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
        props["Extended Properties"] = "Excel 8.0";
    }
    else
        return null;
 
    props["Data Source"] = file;
 
    StringBuilder sb = new StringBuilder();
 
    foreach (KeyValuePair<string, string> prop in props)
    {
        sb.Append(prop.Key);
        sb.Append('=');
        sb.Append(prop.Value);
        sb.Append(';');
    }
 
    string connectionString = sb.ToString();
    // -- End of Constructing OLEDB connection string to Excel file

    // Connecting to Excel File
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;
 
        // Get all Sheets in Excel File
        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
        // Loop through all Sheets to get data
        foreach (DataRow dr in dtSheet.Rows)
        {
            string sheetName = dr["TABLE_NAME"].ToString();
 
            // Get all rows from the Sheet
            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
 
            DataTable dt = new DataTable();
            dt.TableName = sheetName.Replace("$", string.Empty);
 
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
 
            // Add table into DataSet
            ds.Tables.Add(dt);
        }
 
        cmd = null;
        conn.Close();
    }
 
    return ds;
}
Then, you can dump the data into GridView like this:
void LoadDataIntoGridView()
{
    string file = "C:\\MyExcel.xlsx";
    System.Data.DataSet ds = ImportFromExcel(file);
    foreach (System.Data.DataTable dt in ds.Tables)
    {
        GridView gv = new GridView();
        gv.DataSource = dt;
        gv.AutoGenerateColumns = true;
        gv.DataBind();
        Panel1.Controls.Add(gv);
    }
}
About inserting data into SQL database. You need to learn SQL database.
You will not be able to work properly with SQL database without knowing the basic of it:
- How to connect to a SQL database
- How to CREATE DATABASE and CREATE TABLE
- How to perform the basic operations of SELECT, INSERT, UPDATE, DELETE

Example of CREATE TABLE:
CREATE TABLE `department`(
   id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   name varchar(50) NOT NULL,
   dateformation datetime NULL
)
Example of INSERT:
INSERT INTO department(id,name,dateformation)VALUES(1,'Sales','2012-10-27 09:00');
Tutorials of SQL database can be easily found every where.
  Permalink  
v7
Comments
lovejaygore at 26-Oct-12 14:04pm
   
Thank u for ur response. But I already have this code I want code for saving gridview in database.
adriancs at 26-Oct-12 23:13pm
   
I have updated my answer.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Check this link out.

Export Gridview Data to Excel in ASP.NET[^]
it might be helpful
  Permalink  
Comments
lovejaygore at 26-Oct-12 14:05pm
   
I want to import excel data into gridview and save that gridview to database. Thank u.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

1. Similar to how you populate your gridview from other data sources. Only difference is that you need to use oledb connection here.

Excel ---> read with oledb connection--> store in a dataset (ds.fill()) --> gridview.Source = ds;


2. save dataset ds to your Database.


Using my smartphone cant provide code. Smile | :) Apologies.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.150731.1 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100