存储过程/* 将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
>
<
uc1:resetimg
id
="ResetImg2"
runat
="server"
></
uc1:resetimg
><
input
class
="SubmitButton"
id
="btnCancel"
type
="reset"
value
=" 取 消 "
name
="btnCancel"
runat
="server"
>
</
td
>
</
tr
>
</
table
>