通过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()

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181031G1QSNR00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券