#第二版,增加了导入结果提示,代替了sql报错提示
cls
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection #聲明一個SqlConnection對象
$SqlConnection.ConnectionString = "Server=172.18.32.116;Database=xfz_zhwx;user=test;pwd=123" #指明SqlConnection對象的連接字符串
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #聲明一個SqlCommand對象
$SqlCmd.Connection = $SqlConnection
$excelApp = New-Object -COM Excel.Application
$file = (dir C:\11.xlsx).FullName
$book = $excelApp.Workbooks.Open($file)
$sheet = $book.Worksheets.Item(1)
$row = 2
$count=1
while($true)
{
if(!$sheet.Cells.Item($row,1).Value2)
{
break;
}
$SqlCmd.CommandText= "INSERT INTO [xfz_zhwx].[dbo].[jx_grade1]
([njdm]
,[njmc]
,[rxnf]
,[mark]
,[jwdm])
VALUES
('$($sheet.Cells.Item($row,1).Value2)'
,'$($sheet.Cells.Item($row,2).Value2)'
,'$($sheet.Cells.Item($row,3).Value2)'
,'$($sheet.Cells.Item($row,4).Value2)'
,'$($sheet.Cells.Item($row,5).Value2)')"
try
{
$SqlConnection.Open();
$intRezult=$SqlCmd.ExecuteNonQuery();
$SqlConnection.Close();
$sheet.Cells.Item($row,1).Value2='导入成功!';
$count.toString()+' 条导入成功:'+$($sheet.Cells.Item($row,3).Value2);
}
catch
{
$count.toString()+' 条导入失败:'+$($sheet.Cells.Item($row,3).Value2);
}
$count++
$row++
}
$excelApp.Quit()
$book = $null
$sheet = $null
$excelApp = $null
[GC]::Collect()
--------------------------------------------------------
#第一版,导入失败是sql提示
--------------------------------------------------------
cls
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection #聲明一個SqlConnection對象
$SqlConnection.ConnectionString = "Server=172.18.32.116;Database=xfz_zhwx;user=test;pwd=123" #指明SqlConnection對象的連接字符串,仅需将数据库用户授予某个表的操作权限以保障安全
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #聲明一個SqlCommand對象
$SqlCmd.Connection = $SqlConnection
$excelApp = New-Object -COM Excel.Application
$file = (dir C:\11.xlsx).FullName
$book = $excelApp.Workbooks.Open($file)
$sheet = $book.Worksheets.Item(1)
$row = 2
while($true)
{
if(!$sheet.Cells.Item($row,1).Value2)
{
break;
}
$SqlCmd.CommandText= "INSERT INTO [xfz_zhwx].[dbo].[jx_grade1]
([njdm]
,[njmc]
,[rxnf]
,[mark]
,[jwdm])
VALUES
('$($sheet.Cells.Item($row,1).Value2)'
,'$($sheet.Cells.Item($row,2).Value2)'
,'$($sheet.Cells.Item($row,3).Value2)'
,'$($sheet.Cells.Item($row,4).Value2)'
,'$($sheet.Cells.Item($row,5).Value2)')"
$SqlConnection.Open();
$intRezult=$SqlCmd.ExecuteNonQuery();
$SqlConnection.Close();
"工号:$($sheet.Cells.Item($row,1).Value2), 保存成功!"
$row++
}
$excelApp.Quit()
$book = $null
$sheet = $null
$excelApp = $null
[GC]::Collect()
领取专属 10元无门槛券
私享最新 技术干货