Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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:
 
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();
        }
  Permalink  
Comments
Dhanashri13 at 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
{
DataRow row=data.Rows[i];
int columnCount=data.Columns.Count;
string[] columns=new string[columnCount];
for(int j=0;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
Lacy00 at 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?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
 
      string ExcelSheetName = "";
      string ExcelFileName = "";
      OleDbConnection objConn = null;
      System.Data.DataTable dt = null;
      string SheetName = "";
      string connectionString = "";
 
private void BtnImport_Click(object sender, EventArgs e)
       {
           ExcelFileBrowse();
       }
 

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);
        }
 
----------------------------------------------------
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() )
 
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 355
1 _Amy 235
2 Peter Leow 185
3 Andreas Gieriet 180
4 Dave Kreskowiak 155
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,462
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,712


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 29 Nov 2012
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