cls

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection     #聲明一個SqlConnection對象
$SqlConnection.ConnectionString = "Server=192.168.1.1;Database=sa;user=sa;pwd=123"    #指明SqlConnection對象的連接字符串
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand    #聲明一個SqlCommand對象                                            
$SqlCmd.Connection = $SqlConnection
$excelApp = New-Object -COM Excel.Application
$file = (dir D:\express.xlsx).FullName
$book = $excelApp.Workbooks.Open($file)
$sheet = $book.Worksheets.Item(1)

        $SqlCmd.CommandText="
        INSERT INTO [AppData].[dbo].[ExpressCheckHeader]
               ([CheckId]
               ,[UpdateName]
               ,[UpdateTime]
               ,[HeaderStatus]
               ,[ExpressCompany])
         VALUES
               ('$($sheet.Cells.Item(2,1).Value2)'
               ,'$($sheet.Cells.Item(2,2).Value2)'
               ,'$($sheet.Cells.Item(2,3).Value2)'
               ,0
               ,'$($sheet.Cells.Item(2,4).Value2)')"
              
    try{
        $SqlConnection.Open();
        $intRezult=$SqlCmd.ExecuteNonQuery();
        $SqlConnection.Close();
        $sheet.Cells.Item(2,9).Value2='表头导入成功！';
        "------------------------------------------------------"
        "表头导入成功！"
        "------------------------------------------------------"
    }
    catch
    {
        "表头保存失败!";
        "------------------------------------------------------"
        return;
    }           
           
$row = 5
$count=1 
while($true)
{
    if(!$sheet.Cells.Item($row,1).Value2)
    {
        break;
    } 
      $SqlCmd.CommandText=
      "INSERT INTO [AppData].[dbo].[ExpressCheckLine]
           ([CheckId]
           ,[SendDate]
           ,[ExpressID]
           ,[SendToAddress]
           ,[ExpContent]
           ,[ExpWeight]
           ,[ExpMoney]
           ,[Customer]
           ,[ExpStatus])
     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)'
           ,$($sheet.Cells.Item($row,6).Value2)
           ,$($sheet.Cells.Item($row,7).Value2)
           ,'$($sheet.Cells.Item($row,8).Value2)'
           ,0)"
    
    try{
        $SqlConnection.Open();
        $intRezult=$SqlCmd.ExecuteNonQuery();
        $SqlConnection.Close();
        $sheet.Cells.Item($row,9).Value2='导入成功！';
        $count.toString()+' 条保存成功,条码号：'+$($sheet.Cells.Item($row,3).Value2);
    }
    catch
    {
        $count.toString()+' 条保存失败,条码号：'+$($sheet.Cells.Item($row,3).Value2);
    }
    $count++
    $row++
}
"------------------------------------------------------"
"导入操作完成！请查看导入EXCEL文件！"
"------------------------------------------------------"

$book.Save()
$book.Close()
$excelApp.Quit()
$book = $null
$sheet = $null
$excelApp = $null
[GC]::Collect()