VBNet - Форум - Прошу Помощи с экспортом в excel

Вопрос: Прошу Помощи с экспортом в excel Добавлено: 19.09.05 14:17

Автор вопроса: Роман

Здравствуйте!
Спасибо большое Павлу и danser за предоставленную информацию.
Начал создавать экспорт базы данных Access в Excel через созданную мной
програму написанную на Visual Basic.NET
Но после написания кода не чего не происходит, не могли бы вы мне помочь
найти ошибку,а то понять не могу в чем выражается ошибка.
Суть такова, что при нажатии на radioButton1 и кнопки создать,должен
произойти экспорт базы в excel.
В Базе находится всего одна таблица Spravochnik
Далее в этом же окне хочу создать импорт из excel который будет
происходить при нажатии на radiobutton2.

Привожу код программы:

Imports System.Data.OleDb
Public Class servis
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form
Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New
System.Resources.ResourceManager(GetType(servis))
        Me.GroupBox1 = New System.Windows.Forms.GroupBox()
        Me.RadioButton1 = New System.Windows.Forms.RadioButton()
        Me.RadioButton2 = New System.Windows.Forms.RadioButton()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.Button2 = New System.Windows.Forms.Button()
        Me.GroupBox1.SuspendLayout()
        Me.SuspendLayout()
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.AddRange(New
System.Windows.Forms.Control() {Me.RadioButton1, Me.RadioButton2,
Me.Button1, Me.Button2})
        Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.GroupBox1.Size = New System.Drawing.Size(256, 176)
        Me.GroupBox1.TabIndex = 0
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "Настройки Экспорта"
        '
        'RadioButton1
        '
        Me.RadioButton1.Location = New System.Drawing.Point(24, 72)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.Size = New System.Drawing.Size(200, 24)
        Me.RadioButton1.TabIndex = 5
        Me.RadioButton1.Text = "RadioButton6"
        '
        'RadioButton2
        '
        Me.RadioButton2.Location = New System.Drawing.Point(24, 24)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(200, 24)
        Me.RadioButton2.TabIndex = 4
        Me.RadioButton2.Text = "RadioButton5"
        '
        'Button1
        '
        Me.Button1.DialogResult = System.Windows.Forms.DialogResult.OK
        Me.Button1.Location = New System.Drawing.Point(16, 128)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Создать"
        '
        'Button2
        '
        Me.Button2.DialogResult =
System.Windows.Forms.DialogResult.Cancel
        Me.Button2.Location = New System.Drawing.Point(136, 128)
        Me.Button2.Name = "Button2"
        Me.Button2.TabIndex = 1
        Me.Button2.Text = "Отмена"
        '
        'servis
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(272, 197)
        Me.Controls.AddRange(New System.Windows.Forms.Control()
{Me.GroupBox1})
        Me.Icon = CType(resources.GetObject("$this.Icon"),
System.Drawing.Icon)
        Me.Name = "servis"
        Me.Text = "Настройки Экспорта и Импорта"
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=C:\Program Files\Home
Cotalog\Cotalog\Data\HomeCotalog.xls;" & _
                   "Extended Properties=""Excel 8.0;HDR=YES"""

    Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=C:\Program Files\Home
Cotalog\Cotalog\Data\HomeCotalog1.xls;" & _
                   "Extended Properties=""Excel 8.0;HDR=YES"""

    Private m_sCotalog = _
          "C:\Program Files\Home
Cotalog\Cotalog\Data\Cotalog.mdb;Mode=Share D" & _
        "eny None;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry " & _
        "Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine
Type=5;Jet OLEDB:Databas" & _
        "e Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Trans" & _
        "actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
System Database=Fa" & _
        "lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy
Locale on Compact=Fals" & _
        "e;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False"

    Private m_sAction As String

    Private Sub servis_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        RadioButton2.Text = "Импорт из Excel"
        RadioButton1.Text = "Экспорт в Excel"
        Button1.Text = "Создать"
    End Sub

    Private Sub RadioButtons_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles RadioButton1.Click
        m_sAction = sender.Text 'Store the text for the selected radio
button
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Try
            ' Call the associated routine to add/update/modify the
workbook.
            Select Case m_sAction
                Case "Экспортировать в Excel" : Use_External_Source()
            End Select

        Catch ex As OleDbException
            Dim er As OleDbError
            For Each er In ex.Errors
                MsgBox(er.Message)
            Next
        Catch ex2 As System.InvalidOperationException
            MsgBox(ex2.Message)
        End Try

    End Sub

    Public Sub Use_External_Source()

        ' Open a connection to the sample Northwind Access database.
        Dim conn As New System.Data.OleDb.OleDbConnection( _
              "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\Program Files\Home
Cotalog\Cotalog\Data\Cotalog.mdb;Mode=Share D" & _
        "eny None;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry " & _
        "Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine
Type=5;Jet OLEDB:Databas" & _
        "e Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Trans" & _
        "actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
System Database=Fa" & _
        "lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy
Locale on Compact=Fals" & _
        "e;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False")
        conn.Open()
        Dim cmd As New System.Data.OleDb.OleDbCommand()
        cmd.Connection = conn

        cmd.CommandText = "INSERT INTO [Spravochnik$] IN 'C:\Program
Files\Home Cotalog\Cotalog\Data\HomeCotalog.xls' 'Excel 8.0;'" & _
           "SELECT ([N Порядковый Номер], [№ Книги], [№ Полки], Автор,
[Год Выпуска], Комментарий, Жанр, [Название Книги], [Название Рассказа],
Примечание1, Содержание, Примечание2) FROM Spravochnik"
        cmd.ExecuteNonQuery()

        cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\Program
Files\Home Cotalog\Cotalog\Data\HomeCotalog2.xls].[Spravochnik]" & _
                        "([N Порядковый Номер], [№ Книги], [№ Полки],
Автор, [Год Выпуска], Комментарий, Жанр, [Название Книги], [Название
Рассказа], Примечание1, Содержание, Примечание2)FROM [Spravochnik]"
        cmd.ExecuteNonQuery()

        conn.Close()

    End Sub
End Class

Ответить

Ответы Всего ответов: 0

Нет ответов

Страница: