我有代码,可以读取一个SQL选择语句的输出到一个工作表,并保存它。
现在我正在尝试创建三个选择语句并将其导出到EXCEL中的三个工作表中。
不幸的是,代码只从第一个SELECT语句提取输出并将其存储到第一个EXCEL工作表
# SQL string with multiple SELECT statements
$SQL = "SELECT Statement 1
SELECT Statement 2
SELECT Statement 3"
# Create Excel file to save the data
if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
{
New-Item "$DirectoryToSave" -type directory | out-null
}
$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1
if ($currentWorksheet -lt 4){
$ws = $wb.Worksheets.Item($currentWorksheet)
}
else
{
$ws = $wb.Worksheets.Add()
}
$currentWorksheet += 1
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
$Excel.Columns.Item(1).Font.Bold = $true
}
$filename = "D:ScriptDatabase_stuff.xlsx"
if (test-path $filename ) { rm $filename }
$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
我怎么用谷歌搜索解决这个问题?谢谢你!
更新短期解决方案
SQL1 = "SELECT Statement 1"
SQL2 = "SELECT Statement 2"
SQL3 = "Select Statement 3"
if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
{
New-Item "$DirectoryToSave" -type directory | out-null
}
$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1
$ws = $wb.Worksheets.Item(1)
$ws.name = "GUP Download Activity"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL1)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$ws = $wb.Worksheets.Item(2)
$ws.name = "Distinct"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL2)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$ws = $wb.Worksheets.Item(3)
$ws.name = "Computers in GUP Downloads"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL3)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$filename = "D:ScriptDaily_GUP_Report.xlsx"
if (test-path $filename ) { rm $filename }
$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
你不应该替换这个吗:
$SQL = "SELECT Statement 1
SELECT Statement 2
SELECT Statement 3"
吗?:
$SQL = "SELECT Statement 1
UNION ALL
SELECT Statement 2
UNION ALL
SELECT Statement 3"
我不明白这三个工作表的问题。为什么不是一个简单的循环或一个Sub,它将把语句和工作表作为参数?
短期解决方案
SQL1 = "SELECT Statement 1"
SQL2 = "SELECT Statement 2"
SQL3 = "Select Statement 3"
if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
{
New-Item "$DirectoryToSave" -type directory | out-null
}
$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1
$ws = $wb.Worksheets.Item(1)
$ws.name = "GUP Download Activity"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL1)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$ws = $wb.Worksheets.Item(2)
$ws.name = "Distinct"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL2)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$ws = $wb.Worksheets.Item(3)
$ws.name = "Computers in GUP Downloads"
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL3)
if ($qt.Refresh()){
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
}
$filename = "D:ScriptDaily_GUP_Report.xlsx"
if (test-path $filename ) { rm $filename }
$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out