Excel 导入/导出Oracle 数据库

来源：互联网发布：4glte是什么网络制式编辑：程序博客网时间：2024/05/21 04:41

程序要求:通用性高,即对数据库中任意一个表能进行导入导出EXCEL.

程序环境:vs2008 ,vb.net,Oracle 9i

1.根据表名获取正确的查询语句,因为ORACLE中时间类型的字段直接用SELECT ＊ FROM TABLENAME 会出错,所以需要对 TIMESTAMP(0) WITH TIME ZONE 类型字段格式化.

''' <summary>
    ''' get all colums name from table
    ''' </summary>
    ''' <param name="tbname"></param>
    ''' <param name="tb"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getColumsName(ByVal tbname As String, ByRef tb As DataTable) As Boolean
        Dim Sql As String = " SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :TABLE_NAME"
        Dim params As OracleParameter() = New OracleParameter() {New OracleParameter("TABLE_NAME", tbname)}
        Try
            clsOraDB = New clsOraClienDb()
            clsOraDB.ConnectionString = strDBConn
            clsOraDB.Open()
            If Not clsOraDB.FillDataTable(Sql, tb, params) Then
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            clsOraDB.Close()
            clsOraDB = Nothing
        End Try
        Return True
    End Function
    ''' <summary>
    ''' get all colums data from database
    ''' </summary>
    ''' <param name="tbname"></param>
    ''' <param name="tb"></param>
    ''' <param name="TIMEZONE"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getTable(ByVal tbname As String, ByRef tb As DataTable, ByVal TIMEZONE As String) As Boolean
        Dim dt As DataTable = New DataTable()
        If Not getColumsName(tbname, dt) Then
            Return False
        End If
        If dt.Rows.Count = 0 Then
            Return False
        End If
        Dim sql As String = "SELECT "
        For i As Integer = 0 To dt.Rows.Count - 1
            If i = dt.Rows.Count - 1 Then
                If dt.Rows(i).Item("DATA_TYPE").ToString.ToUpper() = "TIMESTAMP(0) WITH TIME ZONE" Then
                    sql = sql & "BS_TO_CHAR_DATETIME(" & dt.Rows(i).Item("COLUMN_NAME").ToString & ",'" & TIMEZONE & "')  " & dt.Rows(i).Item("COLUMN_NAME").ToString
                Else
                    sql = sql & dt.Rows(i).Item("COLUMN_NAME").ToString
                End If
            Else
                If dt.Rows(i).Item("DATA_TYPE").ToString.ToUpper() = "TIMESTAMP(0) WITH TIME ZONE" Then
                    sql = sql & "BS_TO_CHAR_DATETIME(" & dt.Rows(i).Item("COLUMN_NAME").ToString & ",'" & TIMEZONE & "')  " & dt.Rows(i).Item("COLUMN_NAME").ToString & ","
                Else
                    sql = sql & dt.Rows(i).Item("COLUMN_NAME").ToString & ","
                End If
            End If
        Next
        sql = sql & " from " & tbname
        Try
            clsOraDB = New clsOraClienDb()
            clsOraDB.ConnectionString = strDBConn
            clsOraDB.Open()
            If Not clsOraDB.FillDataTable(sql, tb) Then
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            clsOraDB.Close()
            clsOraDB = Nothing
        End Try
        Return True
    End Function

上面两个函数会返回数据中的数据,以下两个函数会读取EXCEL数据,并更新EXCEL数据到数据表

''' <summary>
    ''' make a excel file on the server by datatable
    ''' </summary>
    ''' <param name="filename"></param>
    ''' <param name="tbname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MakeExcelTable(ByVal filename As String, ByVal tbname As String) As DataTable
        'declare excel content object ----------------------------------------------------
        Dim xlApp As New Excel.Application()
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Sheets
        Dim dt As DataTable = New DataTable()
        dt.TableName = tbname
        If xlApp.Equals(Nothing) Then
            Return Nothing
        End If
        'xlApp.Visible = True
        Dim oMissing As Object = System.Reflection.Missing.Value
        Dim worksheet As Excel.Worksheet
        Try
            xlBook = xlApp.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)
            xlSheet = xlBook.Worksheets
            worksheet = CType(xlSheet.Item(1), Excel.Worksheet)
            'xlSheet = DirectCast(xlBook.Worksheets(1), Excel.Worksheet)
            If worksheet.Equals(Nothing) Then
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try
        'declare excel content object end ---------------------------------------------------
        'take excel data into datatable -----------------------------------------------------
        Dim cellcontent As String = String.Empty
        Dim rowcount, colcount, rowindex, colindex As Integer
        rowcount = worksheet.UsedRange.Rows.Count
        colcount = worksheet.UsedRange.Columns.Count
        Dim range As Excel.Range
        Try
            'excel row 
            For rowindex = 1 To rowcount
                Dim dtrow As DataRow = dt.NewRow()
                'excel colum
                For colindex = 1 To colcount
                    range = worksheet.Cells(rowindex, colindex)
                    If range.Value Is Nothing Then
                        cellcontent = String.Empty
                    Else
                        cellcontent = range.Value
                    End If
                    If rowindex = 1 Then
                        dt.Columns.Add(cellcontent)
                    Else
                        Try
                            dtrow(colindex - 1) = cellcontent
                        Catch ex As Exception
                            Return Nothing
                        End Try
                    End If
                Next
                If rowindex > 1 Then
                    dt.Rows.Add(dtrow)
                End If
            Next
            'take excel data into datatable en -----------------------------------------------------
        Catch ex As Exception
            Return Nothing
        Finally
            'release resource -----------------------------------
            xlBook.Close(False, oMissing, oMissing)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
            xlBook = Nothing
            xlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
            xlApp = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
            'release resource end-----------------------------------
        End Try
        Return dt
    End Function
''' <summary>
    ''' Import data from excel to database for excel
    ''' </summary>
    ''' <param name="filename"></param>
    ''' <param name="tbname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function readExcel(ByVal filename As String, ByVal tbname As String, ByRef erds As DataSet, ByVal timezone As String) As Boolean
        'get dataset from excel
        '---------------------------------------------------------------------------
        Dim xlsds As New DataSet()
        Dim xldt As DataTable = Nothing
        Try
            xldt = MakeExcelTable(filename, tbname)
            xlsds.Tables.Add(xldt)
        Catch ex As Exception
            Return False
        End Try
        '------------------------------------------------------------------------
        'GET DATASET FROM DATABASE
        '------------------------------------------------------------------------------
        Dim dbds As DataSet = New DataSet()
        Dim sqlconn As OracleConnection = New OracleConnection(strDBConn)
        Dim lsSQL As String = getSql(tbname, timezone)
        If lsSQL = "" Then
            Return False
        End If
        Dim dbcmd As OracleCommand = New OracleCommand(lsSQL, sqlconn)
        Dim dbda As OracleDataAdapter = New OracleDataAdapter(dbcmd)
        dbda.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Try
            dbda.Fill(dbds, tbname)
        Catch ex As Exception
            Return False
        Finally
            sqlconn.Close()
            sqlconn = Nothing
        End Try
        '---------------------------------------------------------------------------------
        'create a  new dataset whose structure is as same as dbdataset
        '----------------------------------------------------------------------
        Dim newds As DataSet = New DataSet()
        newds = dbds.Clone()
        erds = xlsds.Clone()
        'create a table for contain error rows
        '-------------------------------------------------------------------------
        'COMPARE THE COLOUMS BETWEEN DATABASE TABLE AND EXCELd
        Dim a As Integer = xlsds.Tables(tbname).Columns.Count
        Dim b As Integer = dbds.Tables(tbname).Columns.Count
        If a <> b Then
            Return False
        End If
        'APPEND DATAS OF EXCEL 
        '------------------------------------------------------------------------
        Dim xlsrow As DataRow
        Dim colnums As Integer = dbds.Tables(tbname).Columns.Count
        For Each xlsrow In xlsds.Tables(tbname).Rows
            Dim colindex As Integer = 0
            Dim isrownull As Integer = 0
            Dim newrow As DataRow = newds.Tables(tbname).NewRow
            Try
                For colindex = 0 To colnums - 1
              '测试列的长度与类型
                    Dim maxleng As Integer = dbds.Tables(tbname).Columns(colindex).MaxLength
                    Dim name As String = dbds.Tables(tbname).Columns(colindex).ColumnName
'此处是我还未解决的问题,无法测出TIMESTAMP(0) WITH TIME ZONE  类型的列 的DATATYPE,显示的为STRING类型，郁闷啊,长度确是4000,因此我在这里测试时勉强用4000作为判断是不是时间类型的条件,其实这是不科学的.
                    If maxleng = 4000 Then
                        Dim s As String = dbds.Tables(tbname).Columns(colindex).DateTimeMode
以下我想格式化时间列,但还是没效果,这样的字符串在ORACLE中必须格式化,对ORACLE我不太熟悉,因此不知道有什么方法可以解决这个问题
                        newrow.Item(colindex) = Date.Parse(xlsrow.Item(colindex).ToString).ToString("yyyy/MM/dd HH:mm:ss ") & timezone
                       ' newrow.Item(colindex) = System.DateTime.Now
                    Else
                        If xlsrow.Item(colindex).ToString = "" Then
                            newrow.Item(colindex) = DBNull.Value
                        Else
                            newrow.Item(colindex) = xlsrow.Item(colindex)
                        End If
                    End If
                    isrownull = isrownull + Len(xlsrow(colindex).ToString.Trim)
                Next
                If isrownull <> 0 Then
                    newds.Tables(tbname).Rows.Add(newrow)
                Else
                    Call CollectError(colnums, xlsrow, erds, tbname)
                    newrow.Delete()
                End If
            Catch ex As Exception
                Call CollectError(colnums, xlsrow, erds, tbname)
                newrow.Delete()
            End Try
        Next
        '-----------------------------------------------------------------------------
        'merge newds to dbds,it's very important.
        '-------------------------------
        dbds.Merge(newds, False)
        newds = Nothing
        xlsds = Nothing
        '-------------------------------------------
        'update datas
        '-----------------------------------------------------------------------
        Dim objBuilder As OracleCommandBuilder = New OracleCommandBuilder(dbda)
        dbda.UpdateCommand = objBuilder.GetUpdateCommand
        dbda.InsertCommand = objBuilder.GetInsertCommand
        Dim k As Integer = 0
        Dim J As Integer = dbds.Tables(tbname).Rows.Count
        Try
            'dbda.ContinueUpdateOnError = True
'对时间类型的字段更新,我现在依然没有解决问题,在调用UPDATE函数的时候他自己生成的字句在ORACLE中调用的UPDATE语句可能采用不同的时间函数,这让我很郁闷,我试图在合并数据集之前对时间类型字段进行格式化,但很让我失望这并没效果.我觉得一定还有其他的方式可以将时间类型的字段通过UPDATE函数更新,希望友人赐教.
            k = dbda.Update(dbds.Tables(tbname).Select(Nothing, Nothing, DataViewRowState.CurrentRows))
        Catch ex As Exception
            Return False
        Finally
            dbda = Nothing
            dbds = Nothing
        End Try
        '-------------------------------------------------------------------------
        Return True
    End Function

对于下载,我做的比较简单,这个并不难.DOWNLODEXL函数是放在.aspx.vb中的

    ''' <summary>
    ''' function about download excel file  
    ''' </summary>
    ''' <param name="filename"></param>
    ''' <param name="tbname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DownloadExl(ByVal filename As String, ByVal tbname As String) As Boolean
        Response.Clear()
        Response.Buffer = True
        'Response.AddHeader("content-disposition", "attachment;filename= DOA BGA Report_" & Format(Now(), "yyyyMMdd") & ".xls")
        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8))
        Response.ContentType = "application/vnd.ms-excel"
        Response.ContentEncoding = System.Text.Encoding.UTF8
        Response.Charset = ""
        Me.EnableViewState = False
        Dim first As Boolean = True
        ' ''Set the content type to Excel.
        ''response.ContentType = "application/octet-stream"
        ''response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8))
        Dim stringWriter As New StringWriter()
        Dim htmlWriter As HtmlTextWriter = New HtmlTextWriter(stringWriter)
        Dim Table As New DataTable()
        Dim gvExcel As DataGrid = New DataGrid()
        Try
            'If Not m_BL.getTable(tbname, Table) Then
            '    Return False
            'End If
            If Not m_BL.getTable(tbname, Table, UserInfo.TimeZoneRegion) Then
                Return False
            End If
            gvExcel.DataSource = Table
            gvExcel.DataBind()
        Catch ex As Exception
            Return False
        End Try
        Try
            gvExcel.RenderControl(htmlWriter)
            Response.Write(stringWriter.ToString())
            Response.End()
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function