将数据从Excel工作表保存到sql数据库... [英] save data from excel sheet to sql database ...

问题描述

任何人都可以帮助...我有一个Excel工作表，我想将数据从Excel工作表保存到sql数据表...该怎么做?

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

推荐答案

确保您在SQL Server中有一个目标表来接受数据.
使用excel interop打开excel工作簿
将数据解析为一个集合
创建与数据库的连接
如果您的集合是数据表，请使用bulkcopy将数据加载到目标表中
或
通过sql连接将集合中的每个记录写入表中

有很多可用的示例！

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!

引用
如何将数据从Excel导入SQL Server [

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

首先，Excel工作表数据存储在数据网格中.当您将Excel工作表数据存储在datagrid中时，然后将数据插入到SQL数据库中.
例如，此代码将Excel工作表数据存储在datagried中.

在这里使用两个用户定义功能.
(1)ExcelFileBrowse()和
(2)字符串[] GetExcelSheetNames(字符串excelFile)

说明功能:

第一个功能使用您的excel文件在硬盘上的位置.
第二次使用获得excel工作表名称，例如sheet1，sheet2
最后使用ChkBoxList_ItemCheck:您已在datagrid中选择了此Excel工作表数据存储.

在这里使用:一个按钮，一个复选框列表和一个数据网格.

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;
       }

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

最终将数据插入到SQL数据库中

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

Finally Data Insert into SQL database

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

这篇关于将数据从Excel工作表保存到sql数据库...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！