Click here to Skip to main content
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 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  
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.

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

  Print Answers RSS
0 OriginalGriff 373
1 Sergey Alexandrovich Kryukov 197
2 Richard Deeming 160
3 PhilLenoir 120
4 RyanDev 115
0 Sergey Alexandrovich Kryukov 6,405
1 OriginalGriff 5,798
2 CPallini 2,473
3 Richard MacCutchan 1,647
4 Abhinav S 1,530


Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2014
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