The Wayback Machine - https://web.archive.org/web/20201011235214/https://github.com/dfinke/ImportExcel/issues/46
Skip to content

/ ImportExcel

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Export-Excel generates corrupt Excel file #46

Closed
DarkLite1 opened this issue Oct 16, 2015 · 2 comments
Closed

Export-Excel generates corrupt Excel file #46

DarkLite1 opened this issue Oct 16, 2015 · 2 comments
Labels
enhancement

Comments

@DarkLite1
Copy link
Contributor

@DarkLite1 DarkLite1 commented Oct 16, 2015

Good morning Doug, I'm still an active user of your function (and still a big fan :) ) but I wanted to warn you about a small bug I recently encountered.

This code works flawlessly and generates an Excel sheet with a title:

$ExcelParams = @{
    Path = 'S:\Folder\File.xlsx'
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
    Title = 'My title'
}
Get-Process | where Name -EQ 'NonExisting' | Export-Excel @ExcelParams

This code, without the Title parameter also generates an Excel sheet but when opening it it prompts to inform the user something is wrong:

$ExcelParams = @{
    Path = 'S:\Folder\File.xlsx'
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
}
Get-Process | where Name -EQ 'NonExisting' | Export-Excel @ExcelParams

image

In my script I have a lot of stuff to export that can be $Null or really have some content.
Example

$ExcelParams = @{
    Path         = $ExcelFile
    AutoSize     = $true
    FreezeTopRow = $true
}
$LogonScriptNotExisting | Export-Excel @ExcelParams -TableName 'Users with non existing logon script' -WorkSheetname 'Users with non existing logon script' -Title 'Users with non existing logon script'
$LogonScriptBlank | Export-Excel @ExcelParams -TableName 'Logon script blank' -WorkSheetname 'Logon script blank'
$TypeOfAcccountBlank | Export-Excel @ExcelParams -TableName 'Type of account blank' -WorkSheetname 'Type of account blank'
$TSUserProfileNotExisting | Export-Excel @ExcelParams -TableName 'TS Profile non existing' -WorkSheetname 'TS Profile non existing'
$DisplayNameStandard | Export-Excel @ExcelParams -TableName 'DisplayName standard not followed' -WorkSheetname 'DisplayName standard not followed'
$DisplayNameCnName | Export-Excel @ExcelParams -TableName 'DisplayName NE CommonName' -WorkSheetname 'DisplayName NE CommonName'
$Description | Export-Excel @ExcelParams -TableName 'Description empty' -WorkSheetname 'Description empty'
$HomeFolderGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc home folder' -WorkSheetname 'Grouphc home folder'
$TSUserProfileGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc TS Profile' -WorkSheetname 'Grouphc TS Profile'
$TSHomeDirectoryGrouphc | Export-Excel @ExcelParams -TableName 'Grouphc TS Home drive' -WorkSheetname 'Grouphc TS Home drive'
$InactiveUser | Export-Excel @ExcelParams -TableName 'Inactive users' -WorkSheetname 'Inactive users'
$InactiveComputer | Export-Excel @ExcelParams -TableName 'Inactive computers' -WorkSheetname 'Inactive computers'
$GroupsNoPlaceholder | Export-Excel @ExcelParams -TableName 'ROL Groups without belsrvc' -WorkSheetname 'ROL Groups without belsrvc'
$UsersWithoutOCS | Export-Excel @ExcelParams -TableName 'Users without OCS' -WorkSheetname 'Users without OCS'

As an extra bonus, it would be great if the function just didn't generate a worksheet at all when there is no content. And also didn't throw an error, because some of these variables can be empty. Otherwise we have to do the following for every line above, which makes the script unnecessary long :

if ($UsersWithoutOCS) {
    $UsersWithoutOCS | Export-Excel @ExcelParams -TableName 'Users without OCS' -WorkSheetname 'Users without OCS'
}
#...

This also generates an corrupt Excel file with mixed up headers:

$ExcelParams = @{
    Path = $ExcelFile
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
    Title = 'My title'
}
Get-Process | where Name -EQ 'explorer' | Export-Excel @ExcelParams

image

Thank you for your help and time. It's still one of my favorite functions.
@dfinke dfinke added the enhancement label Oct 16, 2015
@dfinke dfinke closed this in a009afb Oct 16, 2015
@DarkLite1
Copy link
Contributor Author

@DarkLite1 DarkLite1 commented Oct 19, 2015

Great work Doug, thank you very much!

One small caveat, is it possible to have a switch like say 'NoOutput'? So when there is no output, no Excel sheet or worksheet is created? We are now left with an empty worksheet which is a bit silly sometimes.

Thank you again for your great work. I really appreciate it.
@DarkLite1
Copy link
Contributor Author

@DarkLite1 DarkLite1 commented Oct 19, 2015

After some testing, this is still not resolved as it still generates a corrupt Excel:

$ExcelParams = @{
    Path = $ExcelFile
    AutoSize = $true
    FreezeTopRow = $true
    TableName = 'Test'
    WorkSheetname = 'Test sheet'
    Title = 'My title'
}
Get-Process | where Name -EQ 'explorer' | Export-Excel @ExcelParams
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement
Projects
None yet
Milestone
No milestone
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
@dfinke @DarkLite1
You can’t perform that action at this time.