通过power shell将excel导入到数据库

#第二版，增加了导入结果提示，代替了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()