save data from excel sheet to sql database ...

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

Can anyone help ...i have one excel sheet i want to save data from excel sheet to sql data table ...how to do that?

Posted 28-Nov-12 20:19pm

Dhanashri13

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

azizulhoque.bd · Answer 1 · 2012-11-28T23:07:00

hi,

At first excel sheet data store in data grid. When you excel sheet data store in datagried then data insert in to SQL database.
For Example, This code excel sheet data store in datagried.

Here Use tow user define function.
(1) ExcelFileBrowse() And
(2) String[] GetExcelSheetNames(string excelFile)

Description function:

First function use where your excel file in hard disk.
and second use get excel sheet name such as sheet1 , sheet2
And finally use ChkBoxList_ItemCheck : you are selected this excel sheet data store in datagried.

Here use: One button , One Checkboxlist and One datagried.

CSS

string ExcelSheetName = "";
string ExcelFileName = "";
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string SheetName = "";
string connectionString = "";

C#

private void BtnImport_Click(object sender, EventArgs e)
       {
           ExcelFileBrowse();
       }

C#

public void ExcelFileBrowse()
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Open File Dialog";
            fdlg.InitialDirectory = @"E:\Document\PROYOJON:\";
            fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
            fdlg.FilterIndex = 2;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                ExcelFileName = fdlg.FileName;
            }
            else
            {
                return;
            }

            GetExcelSheetNames(ExcelFileName);
        }

----------------------------------------------------

C#

private String[] GetExcelSheetNames(string excelFile)
        {
            ChkBoxList.Items.Clear();
            ChkBoxList.Visible = true;
            try
            {
                connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", excelFile);
                objConn = new OleDbConnection(connectionString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    SheetName = excelSheets[i];
                    ChkBoxList.Items.Add(SheetName);
                    i++;
                }
                return excelSheets;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error = " + ex);
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

-------------------------------------------------------

private void ChkBoxList_ItemCheck(object sender, ItemCheckEventArgs e)
       {
           // Local variable
           int ListIndex;

           ChkBoxList.ItemCheck -= ChkBoxList_ItemCheck;

           for (ListIndex = 0;
                ListIndex < ChkBoxList.Items.Count;
                ListIndex++)
           {
               // Unchecked all items that is not currently selected
               if (ChkBoxList.SelectedIndex != ListIndex)
               {
                   // set item as unchecked
                   ChkBoxList.SetItemChecked(ListIndex, false);
               } // if
               else
               {
                   // set selected item as checked
                   ChkBoxList.SetItemChecked(ListIndex, true);
               }
           } // for
           ChkBoxList.ItemCheck += ChkBoxList_ItemCheck;

           ExcelSheetName = ChkBoxList.CheckedItems[0].ToString();

           string query = String.Format("select * from [{0}]", ExcelSheetName.ToString());
           OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
           DataSet dataSet = new DataSet();
           dataAdapter.Fill(dataSet);
           dgGried.DataSource = dataSet.Tables[0];

           ChkBoxList.Visible = false;
       }

---------------------------------------------

Finally Data Insert into SQL database

Insert Into TableName (Coloum1, Coloum2 )
     Values(Datagried.GetRowCellValue(i, "Coloum1").ToString(), Datagried.GetRowCellValue(i, "Coloum2").ToString() )

Mycroft Holmes · Answer 2 · 2012-11-28T20:29:00

Make sure you have a destination table in SQL Server to accept the data.
using excel interop open the excel workbook
parse the data into a collection
create a connection to the database
use bulkcopy to load the data into the destination table if your collection is a datatable
OR
write each record from the collection into the table via the sql connection

There are lots of examples of this available!

__TR__ · Answer 3 · 2012-11-28T20:32:00

Solution 2

Refer
How to import data from Excel to SQL Server[^]

Posted 28-Nov-12 20:32pm

__TR__

manpreetpawar · Answer 4 · 2012-11-28T23:58:00

dwonload Microsoft's excellibrary.dll
and use this fuction

public static void ExportToExcel(DataSet dataSet)
{
Random rdm = new Random();
string name = rdm.Next(1000000, 9999999).ToString() + ".xls";
string fileType = "application/ms-excel";
FileInfo newFile = new FileInfo(HttpContext.Current.Server.MapPath("") + name);
ExcelLibrary.DataSetHelper.CreateWorkbook(HttpContext.Current.Server.MapPath("") + name, dataSet);

string attachment = string.Format("attachment; filename={0}", "ExcelFile1.xls");
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = fileType;
HttpContext.Current.Response.WriteFile(newFile.FullName);
HttpContext.Current.Response.Flush();
newFile.Delete();
HttpContext.Current.Response.End();
}

it will covert your whole excel data in dataset

Lacy00 · Answer 5 · 2012-11-29T14:55:00

As Mycroft said, first you must make sure that you must have a table in your sql database , even it is empty. besides, your datatable must have the same structure with the original Excel file.to realize below code, you may need a third party Excel library[^]: Please see below code:

C#

private void Form1_Load(object sender, System.EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\DataTableSample.xls");
            Worksheet sheet = workbook.Worksheets[0];
            DataTable data= sheet.ExportDataTable();
            //set the string connection to be the local databasestring
            string connectionStr="";
            SqlConnection conn= new SqlConnection(connectionStr);
            for (int i=0;i<data.rows.count;i++)>
            {
                DataRow row=data.Rows[i];
                int columnCount=data.Columns.Count;
                string[] columns=new string[columnCount];
                for(int j=0;j<columncount;j++)>
                {
                    columns[j]=row[j].ToString();
                }
               // suppose there are only three columns in your datatable
                SqlCommand command=new SqlCommand ("insert into DataTableSample(column1,column2,column3) values('"+columns[0]+"','"+columns[1]+
               " ','"+columns[2]+"')");
                command.ExecuteNonQuery();     
            }
            conn.Close();
        }

save data from excel sheet to sql database ...

5 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Solution 5

Add your solution here

Preview 0