在Excel和Oracle数据库之间导入或导出数据

Question

我使用SQLTools 1.5来编写Oracle SQL脚本。我想将Excel文件中的数据导入Oracle数据库。我该怎么做呢？

另外，如何将Oracle数据库中的数据导出到Excel文件？

Answer 1

SQL Tools支持将Excel文件中的数据导入Oracle。您可以使用Oracle的SQL Developer将数据从Excel文件导入数据库。

Jeff Smith在他的blog post中解释了如何做到这一点

• 右键单击表->导入数据
• 选择您的文件
• 注意标题！您的Excel文件是否具有列标题？我们是否希望将它们视为表中的一行？可能不会。默认选项会处理此问题。
• 选择要成为imported
• Tell SQL Developer的Excel列电子表格中的哪些列与Oracle表中的哪些列相匹配。
• 点击了“验证”按钮。修复任何看起来正确的mistakes.
• Everything！单击‘Finish’button.
• Verify导入查看您的新表data

您也可以在SQL Developer中执行相反的操作，只需右键单击表并选择Export。

Answer 2

检查这个，它可能会有帮助

WebUtil:如何将Excel文件读入Oracle Form

https://sites.google.com/site/craigsoraclestuff/oracle-forms-webutil/read-excel-into-forms

Answer 3

Below sniplet works...

Imports Oracle.DataAccess.Client
Imports System.Configuration
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

    Sub Main()
        Dim dt As New DataTable
        Dim connString = ConfigurationManager.ConnectionStrings("oraConnectionString").ConnectionString
        Using conn As New OracleConnection(connString)
            Dim cmd As New OracleCommand
            cmd.Connection = conn
            cmd.CommandText = "select Doc_name,Doc_id from Document;"
            conn.Open()
            Using oda As New OracleDataAdapter(cmd.CommandText, conn)
                oda.Fill(dt)
            End Using
            conn.Close()
        End Using
        'exportTOExcel
        savetoXcel(dt, dt.Rows.Count)

    End Sub

    Private Sub savetoXcel(dt As DataTable, rows As Int64)
        Dim appXL As Excel.Application
        Dim wbXl As Excel.Workbook
        Dim shXL As Excel.Worksheet
        Dim raXL As Excel.Range
        ' Start Excel and get Application object.
        appXL = CreateObject("Excel.Application")
        appXL.Visible = True
        ' Add a new workbook.
        wbXl = appXL.Workbooks.Add
        shXL = wbXl.ActiveSheet
        ' Add table headers going cell by cell.
        shXL.Cells(1, 1).Value = "Document ID"
        shXL.Cells(1, 2).Value = "Document Name"
        Console.WriteLine("rows:" + rows.ToString)
        ' Format A1:D1 as bold, vertical alignment = center.
        With shXL.Range("A1", "B1")
            .Font.Bold = True
            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        End With
        ' Create an array to set multiple values at once.
        Dim students(rows, 2) As String
        Dim i As Int32 = 0
        For Each Dr As DataRow In dt.Rows
            students(i, 0) = Dr.Item(0)
            students(i, 1) = Dr.Item(1)
            i = i + 1
        Next

        Dim BRange As String = "B" + rows.ToString
        'Console.WriteLine("BRange:" + BRange)
        shXL.Range("A2", BRange).Value = students

        'AutoFit columns A:D.
        raXL = shXL.Range("A1", "B1")
        raXL.EntireColumn.AutoFit()
        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        appXL.Visible = True
        appXL.UserControl = True
        'Export to excel
        wbXl.SaveAs(Filename:="d:\Db_report.xls", FileFormat:=50, ReadOnlyRecommended:=True)
        ' Release object references.
        raXL = Nothing
        shXL = Nothing
        wbXl = Nothing
        appXL.Quit()
        appXL = Nothing
        Exit Sub

    End Sub
End Module