将Excel文件数据导入数据到SQL数据库中

存储过程/*
将Excel文件数据导入数据到SQL数据库中
导出的表存在则直接插入数据，如果不存在则创建表
*/

CREATE PROCEDURE dbo.ImportExcel
--declare
@path nvarchar(1000),       --文件存放目录
@fname nvarchar(250),      --文件名
@sheetname varchar(250),     --要导入的工作表名
@strGetFields varchar(1000) = '*',   --要导入的字段名称
@strWhere  varchar(1500) = '',   --导入时合法条件 
@strWhere2 varchar(1500) = '',   --查找不符合导入数据的条件
@table varchar(250),    --要导入的表名
@rowCount int,     --要导入的记录集的数量
@ret int output,
@retRow int output

as
set nocount on
declare @constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
declare @factcount int

--select @path='C:/Documents and Settings/Administrator/桌面'
--select @table='aa'
--select @fname='stockoutimei'
--select @sheetname='Sheet1'

--参数检测
set @path=rtrim(@path)
if right(@path,1)<>'/'
 begin
  set @path=@path+'/'
 end
set @fname=@fname+'.xls'

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@path+@fname+''','+@sheetname+'$)'

--检查要导入的数据数目和Excel内能导入的数据数目是否相等
--exec('select '+ @factcount +'=count(*) from '+ @sql +' where 1>0'+ @strWhere +'')
create table #temptab (rcount int)
exec('insert into #temptab select count(*) as rcount from '+ @sql +' where 1>0'+ @strWhere +'')

select @factcount=rcount from #temptab
drop table #temptab

if @factcount<>@rowCount
 begin
  --导入数量和实际数量不等
  --返回不符合条件的记录
  set @ret=-1  --不能执行导入
  --exec('select * from '+ @sql + ' where 1>0'+ @strWhere2 +'')
 end
else
 begin
  --导入数据
  --判断要接收数据的表是否存在
  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@table+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   begin
    --print('insert into '+@table+' select ClientName, IMEI, OutTime, ProductName, ReceiveAddr from '+@sql+' where IMEI<>''''')
    exec('insert into '+@table+'('+ @strGetFields +') select '+ @strGetFields +' from '+@sql+' where 1>0'+ @strWhere +'')
    --SET ANSI_NULLS ON
    --SET ANSI_WARNINGS ON
    set @ret=1  --执行成功
   end
  else
   begin
    set @ret=0  --没有表
   end
 end

set @retRow=@factcount --返回Excel文件中实际可用行数
return @factcount

GO

 

.VB后台文件

'执行DataGrid事件 - 导入/删除IMEI
    Private Sub DgSHOW_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DgSHOW.ItemCommand
        Dim ret As Integer
        Dim strNotice As String
        Dim fname As String = e.Item.Cells(2).Text
        Dim lstrFileFolder As String = Server.MapPath("../InIMEI").ToString()

       '//数据准备
        '-------------------------------
        Me.ImportObj.path = lstrFileFolder
        Me.ImportObj.fname = fname
        Me.ImportObj.sheetname = "Sheet1"
        Me.ImportObj.strGetFields = "Date_Code,Bar_Code,Start_Date,End_Date"
        Me.ImportObj.table = "BarCode_DateCode"
        Me.ImportObj.IMEIfield = "Bar_Code"
        Me.ImportObj.strWhere = " AND Date_Code IS NOT NULL AND Bar_Code IS NOT NULL"
        Me.ImportObj.strWhere2 = " AND (Date_Code IS NULL OR Bar_Code IS NULL)"
        Me.ImportObj.SN = e.Item.Cells(0).Text
        '-------------------------------

        If (e.CommandName = "Import") Then  ' Excute Import IMEI
            '=============================================
            '///<summary> Excute Import IMEI </summary>///
            '=============================================   
            Me.ImportObj.rowCount = CType(e.Item.FindControl("txtCount"), HtmlInputText).Value

            If InStrRev(fname.ToUpper, ".XLS") = 0 Then
                Response.Write("<script>alert('文件格式不对，不能导入！');</script>")
                Exit Sub
            End If

           '调用判断方法
            ret = Me.ImportObj.JudgeExcel()
            If ret <> 0 Then
                If ret = -1 Then
                    strNotice = "请查证要导入的Excel中Sheet1表是否存在！"
                ElseIf ret = -2 Then
                    strNotice = "Excel中字段个数不符，请确认后再导入！"
                ElseIf ret = -3 Then
                    strNotice = "Excel中字段名称不符，请确认后再导入！"
                End If
                Response.Write("<script language='javascript'>alert('" & strNotice & "');</script>")
                Exit Sub
            End If

           '执行导入
            ret = Me.ImportObj.ImportBarCode()
            If ret = 1 Then
                strNotice = "导入成功！"
            ElseIf ret = 0 Then
                strNotice = "要导入接收数据的表不存在！"
            ElseIf ret <> -321 Then '非系统执行错误
                strNotice = "/n导入数量和实际能够导入数量【" & ret / 100 & "】行不等/n/n 并请确认要导入的Excel的数据格式为文本格式！"
            End If
            Response.Write("<script language='javascript'>alert('" & strNotice & "');</script>")
            Me.DGBind()

        ElseIf (e.CommandName = "Delete") Then ' Excute Delete IMEI
            '=============================================
            '///<summary> Excute Delete IMEI </summary>///
            '=============================================

            If File.Exists(lstrFileFolder & "/" & fname) = False Then
                Response.Write("<script language='javascript'>alert('未能找到数据文件" & fname & "！');</script>")
                Exit Sub
            End If

            '调用判断方法
            ret = Me.ImportObj.JudgeExcel()
            If ret <> 0 And ret <> -321 Then
                If ret = -1 Then
                    strNotice = "请查证Excel中Sheet1表是否存在！"
                ElseIf ret = -2 Then
                    strNotice = "Excel中字段个数不符，请确认！"
                ElseIf ret = -3 Then
                    strNotice = "Excel中字段名称不符，请确认！"
                End If
                Response.Write("<script language='javascript'>alert('" & strNotice & "');</script>")
                Exit Sub
            End If

           '执行条码删除
            ret = Me.ImportObj.DelBarCode()
            If ret = 1 Then
                Me.DGBind()
            End If
        End If
    End Sub

 

.Class VB后台类

'条码导入--判断Excel文件是否符合导入要求
    Public Function JudgeExcel() As Integer
        Dim conn As New EasyConn
        Dim strSQL As String
        Dim ret As Integer = 0 '[-1→Excel中(me._sheetname)表不存在,-2→Excel中字段个数不符,-3→Excel中字段名称不符]

        strSQL = "SELECT TOP 1 * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" & Me._path & "/" & Me._fname & "'," & Me._sheetname & "$)"

        Dim Myconn As New SqlConnection(conn.CONN_STRING)
        '------------------------------------------------------
        'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me._path & "/" & Me._fname & ";Extended Properties=Excel 8.0;"
        'Dim conn As New OleDbConnection(strConn)
        'conn.Open()
        'Dim cmd As New System.Data.OleDb.OleDbCommand
        'cmd.Connection = conn
        'cmd.CommandText = strSQL
        '------------------------------------------------------
        Try
            Dim cmd As New SqlCommand(strSQL, Myconn)
            Myconn.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            If dr.FieldCount <> 4 Then
                ret = -2
            Else
                Dim i As Integer
                For i = 0 To dr.FieldCount - 1
                    If InStrRev("," & Me._strGetFields & ",", "," & dr.GetName(i) & ",") = 0 Then
                        ret = -3
                        Return ret
                    End If
                Next
            End If

            dr.Close()
            dr = Nothing
        Catch ex As Exception
            'Throw ex
            ret = -1
        Finally
            conn.Close(Myconn)
        End Try

        Return ret
    End Function

   '导入条码
    Public Function ImportBarCode() As Integer
        Dim Conn As New EasyConn
        Dim RunObj As EasyRun
        Dim MyConn As New SqlConnection
        Dim strSQL As String
        Dim MyPara As EasyRunParameterCache
        Dim returnValue As Integer
        Dim factRow As Integer

        strSQL = "DELETE FROM " & Me._table & " WHERE " & Me._IMEIfield & " IN (SELECT CAST(" & Me._IMEIfield & " AS CHAR) AS " & Me._IMEIfield & " FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" & Me._path & "/" & Me._fname & "'," & Me._sheetname & "$))"

        MyConn = Conn.Open(Conn.CONN_STRING)    '//打开连接

        '//开始事务
        Dim MyTran As SqlTransaction = MyConn.BeginTransaction
        Try
           '------------------------------------------

            If Me._IMEIfield <> "" Then
               '导入前先删除重复的IMEI
                RunObj.ExecuteNonQuery(MyTran, CommandType.Text, strSQL)
            End If
            '------------------------------------------   

            '执行导入
            Dim NewProName As String
            NewProName = "ImportExcel"  '存储过程名

            Dim parms() As SqlParameter
            parms = MyPara.GetCachedParameterSet(Conn.CONN_STRING, NewProName)

            If parms Is Nothing Then
                parms = MyPara.GetSpParameterSet(Conn.CONN_STRING, NewProName)
                MyPara.CacheParameterSet(Conn.CONN_STRING, NewProName, parms)
            End If

            Me._fname = _fname.Replace(".xls", "")
            parms(0).Value = _path          '--文件存放目录
            parms(1).Value = _fname         '--文件名
            parms(2).Value = _sheetname     '--要导入的工作表名
            parms(3).Value = _strGetFields '--要导入的字段名称
            parms(4).Value = _strWhere      '--导入时合法条件  
            parms(5).Value = _strWhere2    '--查找不符合导入数据的条件
            parms(6).Value = _table         '--要导入的表名
            parms(7).Value = _rowCount      '--要导入的记录集的数量           
            parms(8).Value = 0              '--返回值
            parms(9).Value = 0             '--返回的Excel文件中可用行数

            RunObj.ExecuteNonQuery(MyTran, CommandType.StoredProcedure, NewProName, parms)
            returnValue = parms(8).Value
            factRow = parms(9).Value
            '------------------------------------------

           '更新状态
            If returnValue = 1 Then     '[-2→无可用数据,-1→不能导入,1→导入成功,0→没有表]
                ChangeUploadStatus(Me._SN, "1", MyTran, RunObj)
            Else
                ChangeUploadStatus(Me._SN, "2", MyTran, RunObj)
                If returnValue = -1 Then returnValue = factRow * 100 '数量不符,返回实际数量*100
            End If
           '------------------------------------------
            If factRow = 0 Then returnValue = -2 'Excel文件中可用行数为0，即无实际可用数据

            MyTran.Commit() '//提交事务
        Catch ex As Exception
            MyTran.Rollback()  '//回滚事务
            returnValue = -321 '返回-321，表示Catch到System.Excute错误
            Throw ex
        Finally
            Conn.Close(MyConn) '//关闭连接
        End Try

        Return returnValue
    End Function

    '删除条码
    Public Function DelBarCode() As Integer
        Dim Conn As New EasyConn
        Dim RunObj As EasyRun
        Dim MyConn As New SqlConnection
        Dim strSQL As String
        Dim Ret As Integer

        strSQL = "DELETE FROM " & Me._table & " WHERE " & Me._IMEIfield & " IN (SELECT CAST(" & Me._IMEIfield & " AS CHAR) AS " & Me._IMEIfield & " FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" & Me._path & "/" & Me._fname & "'," & Me._sheetname & "$))"

        MyConn = Conn.Open(Conn.CONN_STRING)   '//打开连接

        Dim MyTran As SqlTransaction = MyConn.BeginTransaction  '//开始事务
        Try
            '执行条码删除
            RunObj.ExecuteNonQuery(MyTran, CommandType.Text, strSQL)

           '更新状态
            ChangeUploadStatus(Me._SN, "3", MyTran, RunObj)

            MyTran.Commit() '//提交事务
            Ret = 1
        Catch ex As Exception
            MyTran.Rollback()  '//回滚事务
            Ret = -1
        Finally
            Conn.Close(MyConn)  '//关闭连接
        End Try

        Return Ret
    End Function

 

前台验证.aspx
     // 导入时要求必须输入将导入的记录条数
         function  checkCount(obj)
         {
            if(document.all(obj).value=='')
            {
                alert('请输入要导入的数据实际有效数量！');
                document.all(obj).focus();
                return false;
            }
            return true;
        }

前台页面
< asp:datagrid  id ="DgSHOW"  runat ="server"  CssClass ="DataGrid"  Width ="100%"  AutoGenerateColumns ="False" >
                                         < HeaderStyle  CssClass ="DgRowTitle_C" ></ HeaderStyle >
                                         < Columns >
                                             < asp:BoundColumn  Visible ="False"  DataField ="SN"  ReadOnly ="True" ></ asp:BoundColumn >
                                             < asp:TemplateColumn  HeaderText ="序号" >
                                                 < ItemStyle  HorizontalAlign ="Center" ></ ItemStyle >
                                                 < ItemTemplate >
                                                     <% # Container.ItemIndex + 1 %>
                                                 </ ItemTemplate >
                                             </ asp:TemplateColumn >
                                             < asp:BoundColumn  DataField ="FileName"  ReadOnly ="True"  HeaderText ="文件名" ></ asp:BoundColumn >
                                             < asp:BoundColumn  DataField ="FileSize"  ReadOnly ="True"  HeaderText ="文件大小" ></ asp:BoundColumn >
                                             < asp:BoundColumn  DataField ="UpMan"  ReadOnly ="True"  HeaderText ="上传人" ></ asp:BoundColumn >
                                             < asp:BoundColumn  DataField ="UpTime"  ReadOnly ="True"  HeaderText ="上传时间" ></ asp:BoundColumn >
                                             < asp:BoundColumn  HeaderText ="状态" ></ asp:BoundColumn >
                                             < asp:TemplateColumn  HeaderText ="确认导入数量" >
                                                 < ItemStyle  CssClass ="DgTdW" ></ ItemStyle >
                                                 < ItemTemplate >
                                                     < INPUT  class ="NoBorderW_R"  id ="txtCount"  style ="WIDTH: 100%"  onkeypress ="onlyNumeric()"
                                                        type ="text"  maxLength ="10"  runat ="server" >
                                                 </ ItemTemplate >
                                             </ asp:TemplateColumn >
                                             < asp:ButtonColumn  Text ="导入"  HeaderText ="导入"  CommandName ="Import" >
                                                 < ItemStyle  HorizontalAlign ="Center" ></ ItemStyle >
                                             </ asp:ButtonColumn >
                                             < asp:ButtonColumn  Text ="删除"  HeaderText ="删除"  CommandName ="Delete" >
                                                 < ItemStyle  HorizontalAlign ="Center" ></ ItemStyle >
                                             </ asp:ButtonColumn >
                                             < asp:TemplateColumn >
                                                 < HeaderStyle  Width ="50px" ></ HeaderStyle >
                                                 < ItemStyle  HorizontalAlign ="Center" ></ ItemStyle >
                                                 < HeaderTemplate >
                                                     < INPUT  id ="chkAll"  onclick ="return select_deselectAll(this.checked, this.id, 'chkDel')"
                                                        type ="checkbox" > 全选
                                                 </ HeaderTemplate >
                                                 < ItemTemplate >
                                                     < asp:CheckBox  id ="chkDel"  runat ="server"  ToolTip ="选中,表示要删除该文件"  Text ="删除" ></ asp:CheckBox >
                                                 </ ItemTemplate >
                                             </ asp:TemplateColumn >
                                         </ Columns >
                                     </ asp:datagrid >
                                     < table >
                                         < tr >
                                             < td  height ="1" ></ td >
                                         </ tr >
                                     </ table >
                                     < table  class ="ButtonTab"  width ="100%" >
                                         < tr >
                                             < td >< uc1:submitimg  id ="SubmitImg2"  runat ="server" ></ uc1:submitimg >< asp:button  id ="btnDel"  runat ="server"  CssClass ="SubmitButton"  Text ="确定删除"  ToolTip ="删除服务器目录中不再需要的Excel文件" ></ asp:button > &nbsp;&nbsp;&nbsp;
                                                 < uc1:resetimg  id ="ResetImg2"  runat ="server" ></ uc1:resetimg >< input  class ="SubmitButton"  id ="btnCancel"  type ="reset"  value =" 取　消 "  name ="btnCancel"
                                                    runat ="server" >
                                             </ td >
                                         </ tr >
                                     </ table >