Send-SQLDataToExcel.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 |
Function Send-SQLDataToExcel { <# .Synopsis Runs a SQL query and inserts the results into an ExcelSheet, more efficiently than sending it via Export-Excel .Description This command takes either an object representing a session with a SQL server or ODBC database, or a connection String to make one. It the runs a SQL command, and inserts the rows of data returned into a worksheet. It takes most of the parameters of Export-Excel, but it is more efficient than getting dataRows and piping them into Export-Excel, data-rows have additional properties which need to be stripped off. .Example C:\> Send-SQLDataToExcel -MsSQLserver -Connection localhost -SQL "select name,type,type_desc from [master].[sys].[all_objects]" -Path .\temp.xlsx -WorkSheetname master -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow Connects to the local SQL server and selects 3 columns from [Sys].[all_objects] and exports then to a sheet named master with some basic header manager .Example C:\> $SQL="SELECT top 25 DriverName, Count(RaceDate) as Races, Count(Win) as Wins, Count(Pole) as Poles, Count(FastestLap) as Fastlaps FROM Results GROUP BY DriverName ORDER BY (count(win)) DESC" C:\> $Connection = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=790;ReadOnly=0;Dbq=C:\users\James\Documents\f1Results.xlsx;' C:\> Send-SQLDataToExcel -Connection $connection -SQL $sql -path .\demo4.xlsx -WorkSheetname "Winners" -AutoSize -AutoNameRange This declares a SQL statement and creates an ODBC connection string to read from an Excel file, it then runs the statement and outputs the resulting data to a new spreadsheet. .Example C:\> Send-SQLDataToExcel -path .\demo4.xlsx -WorkSheetname "LR" -Connection "DSN=LR" -sql "SELECT name AS CollectionName FROM AgLibraryCollection Collection ORDER BY CollectionName" This example uses an Existing ODBC datasource name "LR" which maps to an adobe lightroom database and gets a list of collection names into a worksheet #> param ( #Database connection string; either DSN=ODBC_Data_Source_Name, a full odbc or SQL Connection string, or the name of a SQL server [Parameter(ParameterSetName="SQLConnection", Mandatory=$true)] [Parameter(ParameterSetName="ODBCConnection",Mandatory=$true)] $Connection, #A pre-existing database session object [Parameter(ParameterSetName="ExistingSession",Mandatory=$true)] [System.Data.Common.DbConnection]$Session, #Specifies the connection string is for SQL server not ODBC [Parameter(ParameterSetName="SQLConnection",Mandatory=$true)] [switch]$MsSQLserver, #Switches to a specific database on a SQL server [Parameter(ParameterSetName="SQLConnection")] [String]$DataBase, #The SQL query to run [Parameter(Mandatory=$true)] [string]$SQL, $Path, [String]$WorkSheetname = 'Sheet1', [Switch]$KillExcel, #If Specified, open the file created. [Switch]$Show, [String]$Title, [OfficeOpenXml.Style.ExcelFillStyle]$TitleFillPattern = 'None', [Switch]$TitleBold, [Int]$TitleSize = 22, [System.Drawing.Color]$TitleBackgroundColor, [String]$Password, [String[]]$PivotRows, [String[]]$PivotColumns, $PivotData, [Switch]$PivotDataToColumn, [Hashtable]$PivotTableDefinition, [Switch]$IncludePivotChart, [OfficeOpenXml.Drawing.Chart.eChartType]$ChartType = 'Pie', [Switch]$NoLegend, [Switch]$ShowCategory, [Switch]$ShowPercent, [Switch]$AutoSize, [Switch]$FreezeTopRow, [Switch]$FreezeFirstColumn, [Switch]$FreezeTopRowFirstColumn, [Int[]]$FreezePane, [Switch]$AutoFilter, [Switch]$BoldTopRow, [Switch]$NoHeader, [String]$RangeName, [String]$TableName, [OfficeOpenXml.Table.TableStyles]$TableStyle = 'Medium6', [Object[]]$ExcelChartDefinition, [Switch]$AutoNameRange, [Object[]]$ConditionalFormat, [Object[]]$ConditionalText, [ScriptBlock]$CellStyleSB, [Int]$StartRow = 1, [Int]$StartColumn = 1, #If Specified, return an ExcelPackage object to allow further work to be done on the file. [Switch]$Passthru ) if ($KillExcel) { Get-Process excel -ErrorAction Ignore | Stop-Process while (Get-Process excel -ErrorAction Ignore) {} } #We were either given a session object or a connection string (with, optionally a MSSQLServer parameter) # If we got -MSSQLServer, create a SQL connection, if we didn't but we got -Connection create an ODBC connection if ($MsSQLserver) { if ($connection -notmatch "=") {$Connection = "server=$Connection;trusted_connection=true;timeout=60"} $Session = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $Connection if ($Session.State -ne 'Open') {$session.Open()} if ($DataBase) {$Session.ChangeDatabase($DataBase) } } elseif ($Connection) { $Session = New-Object -TypeName System.Data.Odbc.OdbcConnection -ArgumentList $Connection ; $Session.ConnectionTimeout = 30 } #A session was either passed in or just created. If it's a SQL one make a SQL DataAdapter, otherwise make an ODBC one if ($Session.gettype().name -match "SqlConnection") { $dataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList ( New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $sql, $Session) } else { $dataAdapter = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter -ArgumentList ( New-Object -TypeName System.Data.Odbc.OdbcCommand -ArgumentList $sql, $Session ) } #Both adapter types output the same kind of table, create one and fill it from the adapter $dataTable = New-Object -TypeName System.Data.DataTable $rowCount = $dataAdapter.fill($dataTable) Write-Verbose "Query returned $rowcount row(s)" #ExportExcel user a -NoHeader parameter so that's what we use here, but needs to be the other way around. $PrintHeaders = -not $NoHeader if ($Title) {$r = $StartRow +1 } else {$r = $StartRow} #Get our Excel sheet and fill it with the data $excelPackage = Export-Excel -Path $Path -WorkSheetname $WorkSheetname -PassThru $excelPackage.Workbook.Worksheets[$WorkSheetname].Cells[$r,$StartColumn].LoadFromDataTable($dataTable, $PrintHeaders ) | Out-Null #Call export-excel with any parameters which don't relate to the SQL query "Connection", "Database" , "Session", "MsSQLserver", "Destination" , "sql" ,"Path" | foreach-object {$null = $PSBoundParameters.Remove($_) } Export-Excel -ExcelPackage $excelPackage @PSBoundParameters #If we were not passed a session close the session we created. if ($Connection) {$Session.close() } } |