程序要求:通用性高,即对数据库中任意一个表能进行导入导出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