PowerShell将客户端Excel导入远程SqlServer

 1 cls
 2 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection     #聲明一個SqlConnection對象
 3 $SqlConnection.ConnectionString = "Server=192.168.0.1;Database=ccc;user=aa;pwd=bb"    #指明SqlConnection對象的連接字符串
 4 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand    #聲明一個SqlCommand對象                                            
 5 $SqlCmd.Connection = $SqlConnection
 6 $excelApp = New-Object -COM Excel.Application
 7 $file = (dir C:\Users\xqp\Desktop\Book1.xls).FullName
 8 $book = $excelApp.Workbooks.Open($file)
 9 $sheet = $book.Worksheets.Item(1)
10 $row = 2
11 while($true)
12 {
13     if(!$sheet.Cells.Item($row,1).Value2)
14     {
15         break;
16     }    
17       $SqlCmd.CommandText= "INSERT INTO [ExpSell].[dbo].[Pay_Detail]
18            ([FyNumber]
19            ,[BankNumber]
20            ,[UserName]
21            ,[Dept]
22            ,[FyPosition]
23            ,[IdentityNumber]
24            ,[Pay_HeDing]
25            ,[Pay_BiLv]
26            ,[Award_ChaoChan]
27            ,[Award_KaoHe]
28            ,[Award_Other1]
29            ,[Award_BuTieChanLiang]
30            ,[Award_BuTieCheFang]
31            ,[Award_BaoMi]
32            ,[Award_JiaBan]
33            ,[Award_BuShangYue]
34            ,[Award_QueQing]
35            ,[Award_Other2]
36            ,[Award_ManQing]
37            ,[Award_GaoWen]
38            ,[Pay_YingFa]
39            ,[Insurance_YangLao]
40            ,[Insurance_ShiYe]
41            ,[Insurance_YiLiao]
42            ,[Pay_YingShui]
43            ,[Tax]
44            ,[Tax_Fin]
45            ,[Tax_Fang]
46            ,[Pay_ShiFa]
47            ,[MonthDay]
48            ,[ShiTang]
49            ,[gongji])
50      VALUES
51            ('$($sheet.Cells.Item($row,1).Value2)'
52            ,'$($sheet.Cells.Item($row,2).Value2)'
53            ,'$($sheet.Cells.Item($row,3).Value2)'
54            ,'$($sheet.Cells.Item($row,4).Value2)'
55            ,'$($sheet.Cells.Item($row,5).Value2)'
56            ,'$($sheet.Cells.Item($row,6).Value2)'
57            ,'$($sheet.Cells.Item($row,7).Value2)'
58            ,'$($sheet.Cells.Item($row,8).Value2)'
59            ,'$($sheet.Cells.Item($row,9).Value2)'
60            ,'$($sheet.Cells.Item($row,10).Value2)'
61            ,'$($sheet.Cells.Item($row,11).Value2)'
62            ,'$($sheet.Cells.Item($row,12).Value2)'
63            ,'$($sheet.Cells.Item($row,13).Value2)'
64            ,'$($sheet.Cells.Item($row,14).Value2)'
65            ,'$($sheet.Cells.Item($row,15).Value2)'
66            ,'$($sheet.Cells.Item($row,16).Value2)'
67            ,'$($sheet.Cells.Item($row,17).Value2)'
68            ,'$($sheet.Cells.Item($row,18).Value2)'
69            ,'$($sheet.Cells.Item($row,19).Value2)'
70            ,'$($sheet.Cells.Item($row,20).Value2)'
71            ,'$($sheet.Cells.Item($row,21).Value2)'
72            ,'$($sheet.Cells.Item($row,22).Value2)'
73            ,'$($sheet.Cells.Item($row,23).Value2)'
74            ,'$($sheet.Cells.Item($row,24).Value2)'
75            ,'$($sheet.Cells.Item($row,25).Value2)'
76            ,'$($sheet.Cells.Item($row,26).Value2)'
77            ,'$($sheet.Cells.Item($row,27).Value2)'
78            ,'$($sheet.Cells.Item($row,28).Value2)'
79            ,'$($sheet.Cells.Item($row,29).Value2)'
80            ,getdate()
81            ,'$($sheet.Cells.Item($row,30).Value2)'
82            ,'$($sheet.Cells.Item($row,32).Value2)')"
83     
84     $SqlConnection.Open();
85     $intRezult=$SqlCmd.ExecuteNonQuery();
86     $SqlConnection.Close();
87     "工号：$($sheet.Cells.Item($row,1).Value2),   保存成功！"
88     $row++
89 }
90 
91 $excelApp.Quit()
92 $book = $null
93 $sheet = $null
94 $excelApp = $null
95 [GC]::Collect()