一个打开EXCEL，并导数据到SQL的例子 (VB)

一个打开EXCEL，并导数据到SQL的例子

Function   ExcelImp()   As   String
                Dim   cnExcel   As   New   OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data   Source="   &   Application.StartupPath   &   "/ExcelImp/"   &   CType(Me.Tag,   Hemp.Base.FormParam).Tag.ToString   &   ".xls;Extended   Properties=""Excel   8.0;HDR=Yes;IMEX=1""")
                Dim   strSQL   As   String
                Dim   ImpCount   As   Integer   =   0

                strSQL   =   "select   *   from   [Data$]"
                Dim   dr   As   OleDb.OleDbDataReader
                Dim   cmd   As   New   OleDb.OleDbCommand(strSQL,   cnExcel)
                cnExcel.Open()
                dr   =   cmd.ExecuteReader

                Dim   ts   As   SqlClient.SqlTransaction
                Dim   cn   As   New   SqlClient.SqlConnection(Hemp.Base.UserInfo.StrConn)
                cn.Open()
                ts   =   cn.BeginTransaction
                Try

                        '添加当前数据
                        'HE   2006-09-04   生成插入HY_HuaYan表的SQL语句-由联创佳浩SQLBuild   V1.0生成
                        Dim   node   As   Xml.XmlNode
                        Dim   strCol,   strVal   As   String

                        While   dr.Read               '读EXCEL数据  
                                If   dr(o.PrimaryKeyName).ToString   <>   ""   Then   '判断取到行主键是否为空
                                        ImpCount   +=   1

                                        strCol   =   ""
                                        strVal   =   ""

                                        '如果设置了单据类型，则在保存是将单据类型保存到表中
                                        If   o.DJLX   <>   ""   Then           '临时保存在本地类型
                                                o.setValue("DJLX",   o.DJLX)
                                        End   If

                                        For   Each   node   In   docGUI("root")("data").ChildNodes
                                                If   node.Attributes("db").InnerText   =   1   Then
                                                        strCol   &=   node.Name.ToString   &   ","
                                                        Select   Case   node.Attributes("type").InnerText
                                                                Case   "C"
                                                                        strVal   &=   "'"   &   dr(node.Name).ToString   &   "',"
                                                                Case   "D"
                                                                        If   dr(node.Name).ToString   =   ""   Then
                                                                                strVal   &=   "NULL,"
                                                                        Else
                                                                                strVal   &=   "'"   &   Format(CType(dr(node.Name).ToString,   Date),   "yyyy-MM-dd")   &   "',"
                                                                                '''''   ORACLE   strVal   &=   "to_date('"   &   Format(CType(node.Attributes("value").InnerText,   Date),   "yyyy-MM-dd")   &   "','yyyy-mm-dd'),"
                                                                        End   If
                                                                Case   "N",   "P",   "M",   "I"
                                                                        If   dr(node.Name).ToString   =   ""   Then
                                                                                If   node.Name   =   "SHBS"   Or   node.Name   =   "DJLX"   Or   node.Name   =   "DJBS"   Or   node.Name   =   "DEL"   Or   node.Name   =   "DJZT"   Then
                                                                                        strVal   &=   "0,"
                                                                                Else
                                                                                        strVal   &=   "NULL,"
                                                                                End   If
                                                                        Else
                                                                                strVal   &=   dr(node.Name).ToString.Replace(",",   "").Replace("￥",   "")   &   ","
                                                                        End   If
                                                                Case   Else
                                                                        strVal   &=   "'"   &   dr(node.Name).ToString   &   "',"
                                                        End   Select
                                                End   If
                                        Next

                                        strSQL   =   "   Insert   into   "   &   o.TableName   &   "("
                                        strSQL   &=   Hemp.StringUtil.DelLastComma(strCol)   &   ")   values("
                                        strSQL   &=   Hemp.StringUtil.DelLastComma(strVal)   &   ")"

                                        Hemp.SqlHelper.ExecuteNonQuery(ts,   CommandType.Text,   strSQL)
                                End   If
                        End   While   '读EXCEL数据  
                        dr.Close()
                        ts.Commit()
                        cn.Close()
                        cn.Dispose()
                        ts.Dispose()

                        cnExcel.Close()
                        cnExcel.Dispose()

                        Return   ImpCount.ToString
                Catch   ex   As   Exception
                        ts.Rollback()
                        cn.Close()
                        ts.Dispose()
                        cnExcel.Close()
                        cnExcel.Dispose()
                        Return   "-1:"   &   ImpCount.ToString   &   ex.Message
                End   Try
        End   Function