Click here to Skip to main content
14,836,740 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

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!
   
   
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() )
   
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
   
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();
        }
   
Comments
Dhanashri13 5-Dec-12 5:50am
   
thanks for help..
but in this code

Collapse | Copy Code
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();
}

error occur=
Server Error in '/ecom' Application.
Format of the initialization string does not conform to specification starting at index 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Source Error:


Line 52: //set the string connection to be the local databasestring
Line 53: string connectionStr = "ConnectionString";
Line 54: SqlConnection conn= new SqlConnection(connectionStr);
Line 55: for (int i=0;i
[no name] 11-Dec-12 21:03pm
   
are you missing to add the reference, or are you correctly set the string connection to be the local database string?

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