MVC将数据从Excel加载到SQLServer [英] MVC Load data from Excel to SQLServer
问题描述
这是因为在Excel文件中,我通常有大约7000行,加载时间太长(当然我按行排列)。
以下是分析的代码。
Dim varExcel As Object = CreateObject(Excel.Application)
Dim varBook作为Object = varExcel.Workbooks.Open(varFileName,True,True,,)
Dim varSheet As Object = varExcel.Worksheets(1)
Dim varArray As Object = varSheet.Range (A1:A65000)值
varCount = 1
对于变量As Integer = 2到varArray.GetUpperBound(0)
如果不是String.IsNullOrEmpty(varArray(vari,1))然后
varCount = varCount + 1
Else
退出
结束如果
下一个
尝试
varArray = Nothing
varArray = varSheet.Range(A1:L& varCount).Value
对于变量As Integer = 2到varArray.GetUpperBound(0)
varCount = vari
Dim varSec = funGetSec(mytable)
Dim varTemp A s新的TEMPORAL
varTemp.ID_TEMPORAL = varSec.NUMERO_SECUENCIAL
如果不是varArray(变量,1)是Nothing然后varTemp.CEDSOC = varArray(变量,1).ToString.Trim.ToUpper
如果var var(),var ).ToString.Trim.ToUpper
如果不是varArray(变量,4)不是,那么varTemp.DF_FCR = varArray(vari,4).ToString.Trim.ToUpper
如果不是varArray(如果IsNumeric(varArray(变量,5))然后varTemp.DFTOVA = CDec(varArray(vari,5))
如果NotvarArray(变量,6)变量,6))然后varTemp.DFINTE = CDec(varArray(变量,6))
如果不是varArray(变量,7)Is Nothing然后如果IsNumeric(varArray(变量,7))然后varTemp.K_FALTANTE0 = CDec (varArray(vari,7))
如果不是varArray(变量,8)是Nothing然后如果IsNumeric(varArray(vari, 8))然后varTemp.CARDIA = CLng(varArray(vari,8))
如果不是varArray(变量,9)Is Nothing然后如果IsNumeric(varArray(变量,9))然后varTemp.DFNUVA = CLng(varArray (变量,9))
如果不是varArray(vari,10)Is Nothing然后如果IsNumeric(varArray(vari,10))然后varTemp.BFBECD = CLng(varArray(vari,10))
如果不是varArray(变量,11)不是,那么varTemp.BFBPTX = varArray(变量,11).ToString.Trim.ToUpper
如果不是varArray(变量,12)是Nothing然后varTemp.A0BFTX = varArray (变量,12).ToString.Trim.ToUpper
varTemp.BFA0CD = CLng(66)
varTemp.FECHA = CDate(Now.Date)
varEntidades.AddToTEMPORALSet(varTemp )
varEntidades.SaveChanges()
下一个
varExcel.DisplayAlerts = False
varBook.Close(SaveChanges:= False)
varExcel.Quit()
varSheet = Nothing
varBook = Nothing
varExcel = Nothing
'Exec存储过程'
Dim varResp As Lon g
Dim varABC As ABC = GetABC(parIDABC)
如果不是varEntidades.spABC(parIDABC,varResp,varErrMensaje)然后
如果不是varEntidades.spErrorABC(varErrMensaje)然后抛出新的ArgumentException(varErrMensaje)
抛出新的ArgumentException(varErrMensaje)
如果
Catch ex As Exception
varExcel.DisplayAlerts = False
varBook.Close(SaveChanges := False)
varExcel.Quit()
varSheet = Nothing
varBook = Nothing
varExcel = Nothing
如果不是varEntidades.spErrorABC(varErrMensaje)然后抛出新的ArgumentException(varErrMensaje)
抛出新的ArgumentException(ex.Message& - 查看Excel文件 - 行号:& varCount)
结束尝试
返回东西
我希望这个
您可以在Excel或SQl服务器连接上使用Excel或SQL Server参考,例如:
SELECT Col1,Col2,Col3 INTO
[ODBC; Description = TEST; DRIVER = SQL Server ; SERVER = Some\Instance; Trusted_Connection = Yes; DATABASE = test] .TableZ FROM [Sheet1 $]
您可能需要在SQL Server上启用即席查询,如果您使用ACE,可能会有其他注意事项:
SELECT * INTO newx
FROM OPENROWSET 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR = YES; IMEX = 1;数据库= c:\docs\testdata.xls',
'SELECT * FROM [Sheet2 $]');
I have a code that loads data from Excel into SQLServer table. It works fine, but I need to know if there's a best way to do it, I mean do the process faster. This is because in Excel file I usually have around 7000 rows and the time loading is too long (of course I'm loading row by row).
Here's the code to your analyze.
Dim varExcel As Object = CreateObject("Excel.Application")
Dim varBook As Object = varExcel.Workbooks.Open(varFileName, True, True, , "")
Dim varSheet As Object = varExcel.Worksheets(1)
Dim varArray As Object = varSheet.Range("A1:A65000").Value
varCount = 1
For vari As Integer = 2 To varArray.GetUpperBound(0)
If Not String.IsNullOrEmpty(varArray(vari, 1)) Then
varCount = varCount + 1
Else
Exit For
End If
Next
Try
varArray = Nothing
varArray = varSheet.Range("A1:L" & varCount).Value
For vari As Integer = 2 To varArray.GetUpperBound(0)
varCount = vari
Dim varSec = funGetSec("mytable")
Dim varTemp As New TEMPORAL
varTemp.ID_TEMPORAL = varSec.NUMERO_SECUENCIAL
If Not varArray(vari, 1) Is Nothing Then varTemp.CEDSOC = varArray(vari, 1).ToString.Trim.ToUpper
If Not varMatrizDatos(vari, 2) Is Nothing Then varTemp.DFCOPR = varArray(vari,2).ToString.Trim.ToUpper
If Not varArray(vari, 3) Is Nothing Then varTemp.NOMSOC = varArray(vari, 3).ToString.Trim.ToUpper
If Not varArray(vari, 4) Is Nothing Then varTemp.DF_FCR = varArray(vari, 4).ToString.Trim.ToUpper
If Not varArray(vari, 5) Is Nothing Then If IsNumeric(varArray(vari, 5)) Then varTemp.DFTOVA = CDec(varArray(vari, 5))
If NotvarArray(vari, 6) Is Nothing Then If IsNumeric(varArray(vari, 6)) Then varTemp.DFINTE = CDec(varArray(vari, 6))
If Not varArray(vari, 7) Is Nothing Then If IsNumeric(varArray(vari, 7)) Then varTemp.K_FALTANTE0 = CDec(varArray(vari, 7))
If Not varArray(vari, 8) Is Nothing Then If IsNumeric(varArray(vari, 8)) Then varTemp.CARDIA = CLng(varArray(vari, 8))
If Not varArray(vari, 9) Is Nothing Then If IsNumeric(varArray(vari, 9)) Then varTemp.DFNUVA = CLng(varArray(vari, 9))
If Not varArray(vari, 10) Is Nothing Then If IsNumeric(varArray(vari, 10)) Then varTemp.BFBECD = CLng(varArray(vari, 10))
If Not varArray(vari, 11) Is Nothing Then varTemp.BFBPTX = varArray(vari, 11).ToString.Trim.ToUpper
If Not varArray(vari, 12) Is Nothing Then varTemp.A0BFTX = varArray(vari, 12).ToString.Trim.ToUpper
varTemp.BFA0CD = CLng(66)
varTemp.FECHA = CDate(Now.Date)
varEntidades.AddToTEMPORALSet(varTemp)
varEntidades.SaveChanges()
Next
varExcel.DisplayAlerts = False
varBook.Close(SaveChanges:=False)
varExcel.Quit()
varSheet = Nothing
varBook = Nothing
varExcel = Nothing
' Exec Stored Procedure '
Dim varResp As Long
Dim varABC As ABC = GetABC(parIDABC)
If Not varEntidades.spABC(parIDABC, varResp, varErrMensaje) Then
If Not varEntidades.spErrorABC(varErrMensaje) Then Throw New ArgumentException(varErrMensaje)
Throw New ArgumentException(varErrMensaje)
End If
Catch ex As Exception
varExcel.DisplayAlerts = False
varBook.Close(SaveChanges:=False)
varExcel.Quit()
varSheet = Nothing
varBook = Nothing
varExcel = Nothing
If Not varEntidades.spErrorABC(varErrMensaje) Then Throw New ArgumentException(varErrMensaje)
Throw New ArgumentException(ex.Message & " - Review Excel file - row number: " & varCount)
End Try
Return Something
I hope this help someone else too.
You can use either an Excel or SQL Server reference in-line on an Excel or SQl Server connection, for example:
SELECT Col1, Col2, Col3 INTO
[ODBC;Description=TEST;DRIVER=SQL Server;SERVER=Some\Instance;Trusted_Connection=Yes;DATABASE=test].TableZ FROM [Sheet1$]"
You will probably need to enable ad hoc queries on SQL Server and if you are using ACE there may be some other considerations:
SELECT * INTO newx
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;IMEX=1;Database=c:\docs\testdata.xls',
'SELECT * FROM [Sheet2$]');
这篇关于MVC将数据从Excel加载到SQLServer的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!