Send-SqlDataToExcel.ps1

[canhsyn]

Hi,

I want to add multiple tables to a single excel sheet and then, filter and edit the format. But there is a problem in the filtering stage. Only the last table is being filtered.
Thank you for your help in this matter.

Send-SQLDataToExcel -MSSQLSERVER -Connection $serverName -SQL $query16first -Database $DBNameSL -Path $newFilename -WorkSheetname "F" -AutoSize -AutoFilter -StartRow 6 -StartColumn 1
Send-SQLDataToExcel -MSSQLSERVER -Connection $serverName -SQL $query16second -Database $DBNameSL -Path $newFilename -WorkSheetname "F" -AutoSize -AutoFilter -StartRow 6 -StartColumn 6
Send-SQLDataToExcel -MSSQLSERVER -Connection $serverName -SQL $query16third -Database $DBNameSL -Path $newFilename -WorkSheetname "F" -AutoSize -AutoFilter -StartRow 6 -StartColumn 11
Send-SQLDataToExcel -MSSQLSERVER -Connection $serverName -SQL $query16fourth -Database $DBNameSL -Path $newFilename -WorkSheetname "F" -AutoSize -AutoFilter -StartRow 6 -StartColumn 16
Send-SQLDataToExcel -MSSQLSERVER -Connection $serverName -SQL $query16fifth -Database $DBNameSL -Path $newFilename -WorkSheetname "F" -AutoSize -AutoFilter -StartRow 6 -StartColumn 21

[ili101]

I think this cannot be done in excel, can you do it in Microsoft Excel?
You can use -TableName instead of -AutoFilter

[canhsyn]

Thanks. I'm creating too many excel files using the importexcel module. So i need to do this work with using the script. I tried -TableName but it doesn't work.

[ili101]

Sorry I wasn't clear, I didn't mean you do it manually I meant that as far as I know this cannot be done even in Microsoft Excel or any other Excel client as this is not supported by the xlsx format.
What's the problem with -TableName ?

[dfinke]

TableName

I didn't look to see if/how tablename is passed from Send-SQLDataToExcel.

One challenge with tables is, as you filter them the other tables that are close will also change size because Excel adjusts the visible rows .

$xlfile="$env:TEMP\testTable.xlsx"
rm $xlfile -ErrorAction SilentlyContinue

$d1=convertfrom-csv @"
Id,Item,Sold
1,Apple,100
2,Pear,200
3,Grape,150
"@

$d2=convertfrom-csv @"
Id,Region,Item,Sold
1,East,Nails,100
2,North,Hammer,200
3,South,Bolts,150
4,East,Nails,100
5,North,Hammer,200
6,South,Bolts,150
"@

$d2 | Export-Excel $xlfile -TableName Hardware -StartColumn 5
$d1 | Export-Excel $xlfile -TableName Fruit -show

[ili101]

Tested it now -TableName works in Send-SQLDataToExcel. It is passed to Export-Excel.
It have a bug however that if the order of adding the tables is not from left to right and up to down it will fail (As Export-Excel don't know what is the correct end position of the last added table)

$newFilename = "C:\Users\illym\Desktop\Temp\Test.xlsx"
$Date = Get-Date
$Time = [TimeSpan]::FromHours(16)
$DataTable = [Data.DataTable]::new('Test')
$null = $DataTable.Columns.Add('IDD', [Int32])
$null = $DataTable.Columns.Add('Name')
$null = $DataTable.Columns.Add('Date', [DateTime])
$null = $DataTable.Columns.Add('Time', [TimeSpan])
$DataTable.Rows.Add(8, 'Test', $Date, $Time)

Send-SQLDataToExcel -DataTable $DataTable -Path $newFilename -WorkSheetname "F" -AutoSize -TableName bbb -StartRow 6 -StartColumn 6
Send-SQLDataToExcel -DataTable $DataTable -Path $newFilename -WorkSheetname "F" -AutoSize -TableName AAA -StartRow 6 -StartColumn 1

Exception calling "Save" with "0" argument(s): "Error saving file C:\Users\illym\Desktop\Temp\Test.xlsx"
At D:\ILI\Profile\Documents\WindowsPowerShell\Modules\ImportExcel\5.4.4\Export-Excel.ps1:1060 char:30
+             else           { $pkg.Save() }
+                              ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

But this problem dose not exist in my fork https://github.com/ili101/ImportExcel as Export-Excel is the one that adds the data so it know exactly what to apply the format on.

Regarding filtering neighboring Tables yes it can be confusing, this is probably why they limited
AutoFilter to only one region per Sheet, but that's how it works...

[jhoneill]

@ili101 I would be surprised if it "knows exactly what to apply the format on", because the data is written to sheet, and then export-Excel is called, and because that call is applying tables etc without adding data first it applies things to the whole sheet. This will break things.
I will have a look at (a) Putting a check for that into Export-Excel and (b) adding the tables in Send-SQLData, I had not considered the case of both adding multiple sets of data on the same page AND using tables. You can do either but not both

[jhoneill]

I've moved the creation of ranges and tables into Send-SQLDataToExcel , and it seems to work better. Hopefully @dfinke will include this in the next merge.

[ili101]

@dfinke Can you approve my pull request please? #298
It not only fixes this problem but meany more problems in Send-SQLDataToExcel by moving the DataTable headlining into Export-Excel.
The fix by @jhoneill fix this problem but not all the other ones fixed in my pull
I don't think that duplicating all the relevant code form Export-Excel into Send-SQLDataToExcel is a good maintainable solution, and if you want to go this route you need all the code from Add-ExcelTable from Export-Excel in Send-SQLDataToExcel to not just what @jhoneill added.

[dfinke]

@jhoneill Thoughts?

[jhoneill]

@ili101 the calls to Add-ExcelTable And Add-ExcelName have moved into Send-SQLData … very little duplication, just moved where the range is determined. That was the idea behind making them separate functions ...
@dfinke I've had a look at the PR. New parameters which aren't explained and no explanatory examples means reading the code to figure out what it does. Plus the changes were made to a version several revisions behind where we are now, so a side by side diff shows differences which aren't part of this change. The "only look look at the first X rows to autosize" is smart. You could have tens of thousands of rows, why have EPPlus render all the cells to get the widths when the first pageful is a reasonable approximation ?
The idea of being able to put a table into sheet using export-excel is OK , although the function is already too complicated, but there's more "special case" handling of tables than I'd like. It doesn't replace what I've done with Send-SQLData because the starting point is a datatable, not a sql query, and that really has to stay separate - we can't have SQL queries and connection strings going into Export-Excel.
If it goes in, there are some things which need to be fixed. The help text for a start, but also there's some stuff using named styles in a way which I think will cause incorrect formatting - basically instead of providing a string which EPPlus converts to built in styles 22 and 46, which are displayed in local formats, it creates named styles containing the litteral text, which - I think - hard codes them to least-significant-in-the-middle. There's also a problem of piping more than one datatable in, I think if you do that you'll get tables overwriting each other and attempts to recreate the same name. When I read some of it I thought I saw some problems with particular combinations of parameters and I'd want to test it further.

Sorry , I'm grumpy and need to get to some sleep. That probably reads as being over-critical of someone elses work, and there are bits I haven't examined at all. But those are my thoughts for the moment.

[ili101]

@jhoneill All the changes and parameters are documented here https://github.com/ili101/ImportExcel/blob/master/CHANGELOG.md and I added some examples here https://github.com/ili101/ImportExcel/blob/master/Examples/DataTable/DataTableAndAutoSizePerformance.ps1 (And there are the tests add). If you think some more or a different type of explanation is needed I can add it. (I will add the ".PARAMETER" parts I forgot)

I already updated the code from master twice (As I use it in my environment) which is why I appreciate it if it will get merged so I wouldn't need to do it any more. If there is an option to get it merged I will happily update it again.

It doesn't replace what I've done with Send-SQLData because the starting point is a datatable, not a sql query, and that really has to stay separate - we can't have SQL queries and connection strings going into Export-Excel.

I totally agree the changes do not replace Send-SQLData the changes only move the DataTable headlining from Send-SQLData to Export-Excel so Send-SQLData do the SQL stuff and Export-Excel handle the Exporting of the resorting data to Excel, DataTables are not exclusive to SQL so in my opinion it is more appropriate this way.

basically instead of providing a string which EPPlus converts to built in styles 22 and 46, which are displayed in local formats, it creates named styles containing the litteral text, which - I think - hard codes them to least-significant-in-the-middle.

I'm not sure what are you referring to in here, the correct version of Export-Excel uses this code in "function Add-CellValue" $TargetCell.Style.Numberformat.Format = 'm/d/yy h:mm' and the function I used here Set-ExcelColumn -Worksheet $ws -Column $ColumnIndex -NumberFormat 'Date-Time' -StartRow $ExcelRange.Start.Row was not added by me and dose the same thing. and it is displayed in local time format.

piping more than one datatable. Do we want to support this? What will we want it to do in this case?

Looking foreword to your feedback, so I will know what to change.

Edit: Regarding what I said on duplicate code, I was wrong regarding Add-ExcelTable, I didn't noticed you used it. but there are other parts from Export-Excel that don't work like -ReturnRange -IncludePivotTable -AutoSize (and maybe more) as they all use the wrong range.
So the solutions I can think of are:

1. Duplicated code to Send-SQLData (Bad idea).
2. Put all the End {...} and Begin {...} parts of Export-Excel in functions and use it in both places, something like:
   function Export-Excel { Begin {Invoke-Begin; function Add-CellValue {...} } Process{[The Data code]} End {Invoke-End } }
function Send-SQLDataToExcel { Begin {Invoke-Begin; [SQL Stuff]} Process{[The DataTable code]} End {Invoke-End } }
3. Handel the DataTable in Export-Excel like I did.

[jhoneill]

The solutions.
Send-SQLData ... I have a module which a few people used named GetSQL. It connects to ODBC or SQL server and sends any SQL and returns the result. It holds the connection open, so once you've connected you can just do get-sql $statement it also helps compose lines of SQL (argument completers mean you can do -Table -Select -where ). Get-SQL ... | Export-Excel is slow with half a million cells. The whole thing about the export method is to try to anything in but not worry about speed. I made SendSQL-Data from part of Get-SQL, ending with range.LoadFromDataTable() then applied date formating, then I copied the parameters of Export-Excel and so it could do all the things that export-excel can do.
I didn't think about things like sending a query to an existing sheet and applying tables and ranges.
Making a seperate call to export-excel to apply things to the data in the sheet, is going to end badly when the data only part of the sheet.
Putting the calls to add a table or a name into Send-SQLData means it will work properly when adding data to a sheet. But as you say it will return the whole range used by the sheet that's by design etc.
Doing that I have broken something else...
Skype-for-business call records are split between servers and you might want to run the same query the first server, then insert without headers from each of the others below the existing data. But before I could apply a table / range on the last insert and it covered the whole set. That's OK because I can call export-excel with those parameters.
I don't mind documenting that if you want a single command to connect to SQL Server, send a query, and make a pivot chart from them, then you need to put the data on its on page! So Send-SqlData is a lot of compromises, and I think they're OK.
You've solved the ranges problem by having Export-Excel know the range of data to work on, by doing the insert itself.

Overnight I remembered something about the behaviour of a datatable with the pipeline. You can't pipe a whole table - when you try you get its rows. You can't return a datatable via the Pipeline either (you get rows), I remember changing Get-SQL so it could put the table into a variable to work round this. So that question goes away ... I think.

There's another issue with Export-Excel and I keep avoiding it. It has a targetData parameter where other commands would have input object which can be multiple rows. You can't do $p = ps ; export-excel $p it has to be $p | export-Excel ; I think we should allow a multi-row item to be passed as the target data and handle datatables properly. But I'll defer to @dfinke on this.

The styles thing. Excel has some built in styles which are referred to by number. If you choose short date format it will change how the date is displayed when the localization changes. If you tell EPPlus to use 'm/d/yy h:mm' it is converted to "Format 22" which will always be localized. So on my machine setting what looks like US format (least significant in the middle) displays in European (least significant first). And if I change the culture of my machine to japanese the date be displayed as least significant last.
Creating named styles to mirror the numbered ones is redundant, and I don't know if it says "this name is an alias for format-22" [good] or "this name is fixed as 'm/d/yy'" [bad]. So I'd get rid of that.

I still haven't worked my way through what happens with the combinations of -table -tableName and
-tableStyle :-)

[ili101]

@jhoneill Thank you, you should read my notes to save you some time https://github.com/ili101/ImportExcel/blob/master/CHANGELOG.md
As I mention there you can't pipe a DataTable from variable normally, you have to ether use the param as you mention Export-Excel -TargetData $DataTable or add a coma , $DataTable | Export-Excel and for the user that make the mistake of piping it I added the warning as I mention there also. Surprisingly piping from function to function also works:

$newFilename = "C:\Users\illym\Desktop\Temp\Test.xlsx"

function Get-Data
{
    param (
    )
    $Date = Get-Date
    $Time = [TimeSpan]::FromHours(16)
    $DataTable = [Data.DataTable]::new('Test')
    $null = $DataTable.Columns.Add('IDD', [Int32])
    $null = $DataTable.Columns.Add('Name')
    $null = $DataTable.Columns.Add('Date', [DateTime])
    $null = $DataTable.Columns.Add('Time', [TimeSpan])
    $null = $DataTable.Rows.Add(8, 'Test', $Date, $Time)
    $null = $DataTable.Rows.Add(9, 'Test2', $Date, $Time)
    , $DataTable
}
$Data = Get-Data

# This will not work correctly, it will throw you the warning I made.
$Data | Export-Excel -Path $newFilename

# This 3 works.
Export-Excel -Path $newFilename -TargetData $Data
, $Data | Export-Excel -Path $newFilename
Get-Data | Export-Excel -Path $newFilename

Regarding the non DataTable problem you mention $p = ps ; export-excel $p that can be done by adding one line of a foreach in the beginning of Process
Process{ foreach ($item in $TargetData) {[The normal code]} } that will allow piping and using the parameter with the same result. But even in the native PowerShell cmdlets there is no consensus of how to handle -InputObject for example Out-File -InputObject $Data is the same as $Data | Out-File but Export-Csv -InputObject $Data is different from $Data | Export-Csv so yes I don't know what @dfinke want to go with.