将数据从excel中导入到access

c# 代码

        private void button1_Click(object sender, EventArgs e)
        {
            // Create Access datatable
            if (File.Exists(@"d:\book.mdb"))
            {
                File.Delete(@"d:\book.mdb");
            }

            Microsoft.Office.Interop.Access.Application _accessData;
            _accessData = new Microsoft.Office.Interop.Access.Application();
            _accessData.Visible = false;
            _accessData.NewCurrentDatabase(@"d:\book.mdb", Microsoft.Office.Interop.Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000);
            _accessData.CloseCurrentDatabase();
            _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
            _accessData = null;

            string _filename = @"d:\Book.xls";
            string _conn;
            _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + _filename + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

            OleDbConnection _connection = new OleDbConnection(_conn);

            OleDbCommand _command = new OleDbCommand();
            _command.Connection = _connection;

            try
            {
                _command.CommandText = @"SELECT * INTO [MS Access;Database=d:\book.mdb].[Sheet1] FROM [Sheet1$]";
                _connection.Open();
                _command.ExecuteNonQuery();
                _connection.Close();
                MessageBox.Show("The import is complete!");
            }

            catch (Exception exc)
            {
                MessageBox.Show("Import Failed, correct Column name in the sheet!" + "\nError message:\n" + exc.Message);
            }

        }

View Code

VB.NET 代码 

Imports System.IO
Imports Microsoft.Office.Interop
Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' delete the file with the same and create a new access file
        If File.Exists("d:\book.mdb") Then
            File.Delete("d:\book.mdb")
        End If

        Dim _accessData As Access.Application
        _accessData = New Access.Application()
        _accessData.Visible = False
        _accessData.NewCurrentDatabase("d:\book.mdb", Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000, , , )

        _accessData.CloseCurrentDatabase()
        _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
        _accessData = Nothing

        ' initialize the connect string
        Dim _filename As String = "d:\Book.xls"
        Dim _conn As String
        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

        Dim _connection As OleDbConnection = New OleDbConnection(_conn)

        'Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into Book.mdb.
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = _connection

        Try
            _command.CommandText = "SELECT * INTO [MS Access;Database=d:\Book.mdb].[Sheet1] FROM [Sheet1$]"
            _connection.Open()
            _command.ExecuteNonQuery()
            _connection.Close()
            MessageBox.Show("The import is complete!")
        Catch e1 As Exception
            MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
        End Try

    End Sub

End Class

View Code

 

相关错误解决办法：
http://social.msdn.microsoft.com/Forums/en-US/winforms/thread/2b7d388d-907d-4d0b-b46f-cacc9290c1b3

转载于:https://www.cnblogs.com/guaiguai9/archive/2010/05/27/1745414.html