代码有删减,流程没多大问题
cls;
$T="d:/xxxx.xlsx"
Get-Date -Format yyyyMMdd-hhmm-ss-fff
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try{
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
$cmd.Dispose()
}
catch{}
return $conn
}
function WriteMySQLQuery($conn, [string]$query) {
$command = $conn.CreateCommand()
$command.CommandText = $query
$RowsInserted = $command.ExecuteNonQuery()
$command.Dispose()
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}
$MySqlCon = ConnectMySQL -user user1 -pass 123456 -MySQLHost 217.0.0.1 -database sre
$Application = New-Object -ComObject "Excel.Application";
$Application.displayAlerts=$false;
$Application.visible= $false;
$Workbook=$Application.Workbooks.open($xxx);
$Sheet=$Workbook.worksheets.item(1);
$Cel=$Sheet.cells;
$Rowmax = $Sheet.UsedRange.Rows.Count;
$Columnmax = $Sheet.UsedRange.columns.Count;
if ($Rowmax -gt 1)
{
for($R=2;$R -le $Rowmax;$R++)
{
if ( ($Cel.item($R,1).value2) -like "Remark*" )
{
$Remark= $Cel.item($R,2).value2
}
if ( ($Cel.item($R,1).value2) -like "Date*" )
{
$Dates= $Cel.item($R,2).Text
$Bys= $Cel.item($R,6).value2
}
}
for($R=1;$R -le $Rowmax;$R++)
{
$R
if ((($Cel.item($R,1).value2).Length -gt 0 ) -and (($Cel.item($R,2).value2).Length -gt 0) )
{
if (($Cel.item($R,1).value2).Length -gt 0)
{
$SysType=$Cel.item($R,1).value2
}
for($C=2;$C -le $Columnmax;$C++)
{
$Text=$Text + ",'" + $Cel.item($R,$C).value2 + "'"
# $Text=$Text + ",'" + $C + "'"
}
$Text= "(" + $Text + ",'" + $Dates + "','" + $Remark + "','" + $Bys + "','','','','','',now(),now())"
$query = 'INSERT FEA VALUES ' +$Text
$Rows = WriteMySQLQuery $MySqlCon $query
}
}
}
$Workbook.close()
$Sheet = $Null
$Workbook = $Null
$Application = $null
Get-Date -Format yyyyMMdd-hhmm-ss-fff
$conn.Close()
[GC]::Collect()