Click here to Skip to main content
15,886,006 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Sir
I want to upload a excel File and put excel file data in database though asp.net . my data base is SQL server 2005 .please help me .
I have Search in Google but I can't got any answer.
Posted
Comments
Divya RS 3-Nov-12 3:12am    
its not a matter of excel files ,u can upload any files and store it in database , refine your sarch in google as upload and save files using asp.net, u will get lot of sources

If you need to upload your excel file to datatabase, the first step is to export excel to datatable and then, connect your datatable to sql server database see below code:
C#
//Create a workbook
Workbook workbook = new Workbook();
//Load the file
workbook.LoadFromFile("DataTableSample.xls");
//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];
//Export datatable
DataTable dataTable = sheet.ExportDataTable();

Then, you can connect your datatable to sql server.If you still not know very clear, you can see a similar question here:
Export excel to database through c#[^]
 
Share this answer
 
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Diagnostics;

Boolean IsClick = true;
byte[] Attachmnt;
ClassLibrary1.Entity objEntity = new ClassLibrary1.Entity();
Query objQuery = new Query();
string Connstr = System.Configuration.ConfigurationManager.AppSettings["Connstr"].ToString();

private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();                openFileDialog1.InitialDirectory = @"C:\";
openFileDialog1.Title = "Select ur file ";
openFileDialog1.CheckFileExists = true;
openFileDialog1.CheckPathExists = true;
openFileDialog1.DefaultExt = "doc";
openFileDialog1.Filter = "Excel file(*.xls)|*.xls";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;

openFileDialog1.ReadOnlyChecked = true;
openFileDialog1.ShowReadOnly = true;
                
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
tbUpload.Text = openFileDialog1.SafeFileName;
FilePath = openFileDialog1.FileName;
if (openFileDialog1.FileName != null)   
{
v_Filepath = FilePath;
System.IO.FileStream fs = new System.IO.FileStream(FilePath, System.IO.FileMode.Open, System.IO.FileAccess.ReadWrite);
System.IO.BinaryReader binaryReader = new System.IO.BinaryReader(fs);
long byteLength = new System.IO.FileInfo(FilePath).Length;
Attachmnt = binaryReader.ReadBytes((Int32)byteLength);
fs.Close();
fs.Dispose();
binaryReader.Close();
  }                    
 }
}   

 private void btnSubmit_Click(object sender, EventArgs e)
{            
Document objDoc = new Document();
objDoc.DocContent = Attachmnt;
objEntity.ResumeData_ = objDoc.DocContent;
objEntity.Submit();
}
  
public class Document   
{
 public int DocId { get; set; }
 public string DocName { get; set; }
 public byte[] DocContent { get; set; }
}  


namespace ClassLibrary1
{
    public class Entity
    {   
        private byte[] ResumeData;
        public byte[] ResumeData_
        {
            get { return ResumeData; }
            set { ResumeData = value; }
        }   
 string Connstr=System.Configuration.ConfigurationManager.AppSettings["Connstr"].ToString();
        public void Submit()
        {
            SqlCommand Cmd = new SqlCommand();
            SqlConnection Conn = new SqlConnection(Connstr);
            Conn.Open();
            Cmd.Connection = Conn;
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.CommandText = "SP_Insert_Joinee";
            Cmd.ExecuteNonQuery();
        }
}
}
 
Share this answer
 
v2
Get data table from excel uploaded:
public DataTable GetTable(string filename, string SheetName, string outTableName)
   {
       try
       {
           string Con = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                       @"Data Source=" + filename + ";" +
                       @"Extended Properties=" + Convert.ToChar(34).ToString() +
                       @"Excel 8.0;" + "Imex=2;" + "HDR=Yes;" + Convert.ToChar(34).ToString();
           OleDbConnection oleConn = new OleDbConnection(Con);
           oleConn.Open();
           OleDbCommand oleCmdSelect = new OleDbCommand();
           oleCmdSelect = new OleDbCommand(
                   @"SELECT * FROM ["
                   + SheetName
                   + "$" + "]", oleConn);
           OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
           oleAdapter.SelectCommand = oleCmdSelect;
           DataTable dt = new DataTable(outTableName);
           oleAdapter.FillSchema(dt, SchemaType.Source);
           oleAdapter.Fill(dt);
           oleCmdSelect.Dispose();
           oleCmdSelect = null;
           oleAdapter.Dispose();
           oleAdapter = null;
           oleConn.Dispose();
           oleConn = null;
           return dt;
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }


Then using bulk operation dataadapter insert it into sql table
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900