Excel导入数据到数据库(Sql2005 ,Access)

1.在Sql2005创建对应的表"Roll"

2.应用以下这段代码

string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
            using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
            {
                OleDbCommand cmd = new OleDbCommand("select * FROM [Sheet1$]", conn);
                conn.Open();
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
                    // Bulk Copy to SQL Server 
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "Roll";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }

3.Excel导入、导出数据到access,使用Com组件

    public static void ExcelImportDB()
        {
            OleDbConnection conExcel = new OleDbConnection();
            try
            {
                ApplicationClass access = new ApplicationClass();

                access.Visible = false;
                access.OpenCurrentDatabase(Settings.Default.DBPath, true, "");

                OpenFileDialog openFile = new OpenFileDialog();
                openFile.Filter = ("Excel 文件(*.xls)|*.xls");

                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    access.DoCmd.TransferSpreadsheet(AcDataTransferType.acImport, AcSpreadSheetType.acSpreadsheetTypeExcel12, "Intergral", openFile.FileName, true, null, null);

                    access.CloseCurrentDatabase();
                    access.DoCmd.Quit(AcQuitOption.acQuitSaveAll);

                    Marshal.ReleaseComObject(access);

                    access = null;

                    System.Windows.Forms.MessageBox.Show("导入数据成功", "导入数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.ToString());
            }
            finally
            {
                conExcel.Close();
            }
        }

        public static void DBExportExcel()
        {
            try
            {
                ApplicationClass access = new ApplicationClass();

                access.Visible = false;
                access.OpenCurrentDatabase(Settings.Default.DBPath, false, "");

                SaveFileDialog saveFile = new SaveFileDialog();
                saveFile.Filter = ("Excel 文件(*.xls)|*.xls");
                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    access.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel9, "Intergral", saveFile.FileName, true, null, null);

                    access.CloseCurrentDatabase();
                    access.DoCmd.Quit(AcQuitOption.acQuitSaveNone);

                    Marshal.ReleaseComObject(access);

                    access = null;

                    MessageBox.Show("导出数据成功", "导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }