search

EvotecIT / PSWriteExcel

PSWriteExcel is very basic (at the moment) PowerShell module to create Microsoft Excel workbooks without Microsoft Excel installed.
61 stars 12 forks source link

ConvertTo-ScriptBlock and Convert-FromScriptblock are not present #11

Closed ioamnesia closed 3 years ago

ioamnesia commented 3 years ago

I did my best to reverse engineer and create my own functions which worked, but these functions need to be included somehow or the whole thing doesn't work

PrzemyslawKlys commented 3 years ago

Well, those functions do exists just not in PSWriteExcel. If you would just open PSWriteExcel.psd1 you would notice

    RequiredModules      = @(@{
            ModuleVersion = '0.0.180'
            ModuleName    = 'PSSharedGoods'
            Guid          = 'ee272aa8-baaa-4edf-9f45-b6d6f7d844fe'
        })

Of course this is only needed if you intend to use PSExcel in development form. If you dont' intend to change code I would advise:

Install-Module PSWriteExcel

Which will install "optimized/merged" version of PSwriteExcel from PSGallery and you won't have to worry about anything missing.

https://www.powershellgallery.com/profiles/Przemyslaw.Klys

All those modules are just one install away.

ioamnesia commented 3 years ago

That's odd, because I installed from PSGallery via the Install-Module PSWriteExcel command. I'm going to do an uninstall and try again and see if it repros

ioamnesia commented 3 years ago

Fresh install of version 0.1.11 from psgallery and there's no RequiredModules in the psd1:

@{
    AliasesToExport      = @('Set-ExcelTranslateFromR1C1', 'Set-ExcelTranslateToR1C1', 'Set-ExcelWorkSheetCellStyleFont')
    Author               = 'Przemyslaw Klys'
    CmdletsToExport      = @()
    CompanyName          = 'Evotec'
    CompatiblePSEditions = @('Desktop', 'Core')
    Copyright            = '(c) 2011 - 2020 Przemyslaw Klys @ Evotec. All rights reserved.'
    Description          = 'Little project to create Excel files without Microsoft Excel being installed.'
    FunctionsToExport    = @('Add-ExcelWorkSheet', 'Add-ExcelWorkSheetCell', 'Add-ExcelWorksheetData', 'ConvertFrom-Excel', 'ConvertTo-Excel', 'Excel', 'WorkbookProperties', 'Find-ExcelDocumentText', 'Get-ExcelDocument', 'Get-ExcelProperties', 'Get-ExcelTranslateFromR1C1', 'Get-ExcelTranslateToR1C1', 'Get-ExcelWorkSheet', 'Get-ExcelWorkSheetCell', 'Get-ExcelWorkSheetData', 'New-ExcelDocument', 'Remove-ExcelWorksheet', 'Request-ExcelWorkSheetCalculation', 'Save-ExcelDocument', 'Set-ExcelProperties', 'Set-ExcelWorksheetAutoFilter', 'Set-ExcelWorksheetAutoFit', 'Set-ExcelWorkSheetCellStyle', 'Set-ExcelWorkSheetFreezePane', 'Set-ExcelWorkSheetTableStyle', 'Worksheet')
    GUID                 = '82232c6a-27f1-435d-a496-929f7221334b'
    ModuleVersion        = '0.1.11'
    PowerShellVersion    = '5.1'
    PrivateData          = @{
        PSData = @{
            Tags       = @('Excel', 'ConvertTo-Excel', 'ExportExcel', 'macOS', 'linux', 'windows')
            ProjectUri = 'https://github.com/EvotecIT/PSWriteExcel'
            IconUri    = 'https://evotec.xyz/wp-content/uploads/2018/10/PSWriteExcel.png'
        }
    }
    RootModule           = 'PSWriteExcel.psm1'
    ScriptsToProcess     = @()
}
# SIG # Begin signature block
# MIIgQAYJKoZIhvcNAQcCoIIgMTCCIC0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# SIG # End signature block
ioamnesia commented 3 years ago

After running Install-Module for all your releases, it looks like only 0.1.4 includes PSSharedGoods when installing from the gallery.

Trying Version 0.1.3
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.3.
Trying Version 0.1.4
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Performing the operation "Install-Module" on target "Version '0.1.4' of module 'PSWriteExcel'".
VERBOSE: The installation scope is specified to be 'CurrentUser'.
VERBOSE: The specified module will be installed in 'C:\Users\mrowland\OneDrive - BitTitan\Documents\WindowsPowerShell\Modules'.
VERBOSE: The specified Location is 'NuGet' and PackageManagementProvider is 'NuGet'.
VERBOSE: Downloading module 'PSWriteExcel' with version '0.1.4' from the repository 'https://www.powershellgallery.com/api/v2'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSWriteExcel'' for ''.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSSharedGoods'' for ''.
VERBOSE: Package 'PSWriteColor' is already installed.
VERBOSE: Package 'Connectimo' is already installed.
VERBOSE: Package 'PSWriteColor' is already installed.
VERBOSE: Package 'Connectimo' is already installed.
VERBOSE: InstallPackage' - name='PSSharedGoods', version='0.0.188',destination='C:\Users\mrowland\AppData\Local\Temp\863946783'
VERBOSE: DownloadPackage' - name='PSSharedGoods', version='0.0.188',destination='C:\Users\mrowland\AppData\Local\Temp\863946783\PSSharedGoods.0.0.188\PSSharedGoods.0.0.188.nupkg', uri='https://www.powershellgallery.com/api/v2/package/PSSharedGoods/0.0.188'
VERBOSE: Downloading 'https://www.powershellgallery.com/api/v2/package/PSSharedGoods/0.0.188'.
VERBOSE: Completed downloading 'https://www.powershellgallery.com/api/v2/package/PSSharedGoods/0.0.188'.
VERBOSE: Completed downloading 'PSSharedGoods'.
VERBOSE: Hash for package 'PSSharedGoods' does not match hash provided from the server.
VERBOSE: InstallPackageLocal' - name='PSSharedGoods', version='0.0.188',destination='C:\Users\mrowland\AppData\Local\Temp\863946783'
VERBOSE: InstallPackage' - name='PSWriteExcel', version='0.1.4',destination='C:\Users\mrowland\AppData\Local\Temp\863946783'
VERBOSE: DownloadPackage' - name='PSWriteExcel', version='0.1.4',destination='C:\Users\mrowland\AppData\Local\Temp\863946783\PSWriteExcel.0.1.4\PSWriteExcel.0.1.4.nupkg', uri='https://www.powershellgallery.com/api/v2/package/PSWriteExcel/0.1.4'
VERBOSE: Downloading 'https://www.powershellgallery.com/api/v2/package/PSWriteExcel/0.1.4'.
VERBOSE: Completed downloading 'https://www.powershellgallery.com/api/v2/package/PSWriteExcel/0.1.4'.
VERBOSE: Completed downloading 'PSWriteExcel'.
VERBOSE: Hash for package 'PSWriteExcel' does not match hash provided from the server.
VERBOSE: InstallPackageLocal' - name='PSWriteExcel', version='0.1.4',destination='C:\Users\mrowland\AppData\Local\Temp\863946783'
**VERBOSE: Validating the 'PSSharedGoods' module contents under 'C:\Users\mrowland\AppData\Local\Temp\863946783\PSSharedGoods.0.0.188' path.
VERBOSE: Test-ModuleManifest successfully validated the module manifest file 'C:\Users\mrowland\AppData\Local\Temp\863946783\PSSharedGoods.0.0.188'.
VERBOSE: Validating the authenticode signature and publisher of the catalog file or module manifest file of the module 'PSSharedGoods'.
VERBOSE: Skipping the Publisher check for the version '0.0.188' of module 'PSSharedGoods'.
VERBOSE: Checking for possible command collisions for the module 'PSSharedGoods' commands.
PackageManagement\Install-Package : The following commands are already available on this system:'ConvertFrom-Html'. This module 'PSSharedGoods' may override the existing commands. If you still want to install this module 'PSSharedGoods', use -AllowClobber parameter.**
At C:\Users\mrowland\OneDrive - BitTitan\Documents\WindowsPowerShell\Modules\PowerShellGet\2.2.4.1\PSModule.psm1:9709 char:34
+ ... talledPackages = PackageManagement\Install-Package @PSBoundParameters
+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package], Exception
    + FullyQualifiedErrorId : CommandAlreadyAvailable,Validate-ModuleCommandAlreadyAvailable,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage

Trying Version 0.1.5
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.5.
Trying Version 0.1.6
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.6.
Trying Version 0.1.7
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.7.
Trying Version 0.1.8
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.8.
Trying Version 0.1.9
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.9.
Trying Version 0.1.10
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified.  PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='PSwriteexcel'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'PSwriteexcel'.
VERBOSE: Skipping installed module PSWriteExcel 0.1.10.
PrzemyslawKlys commented 3 years ago

Ok, so there are 2 things here.

  1. It seems ConvertTo-ScriptBlock was lost somehow - need to find it/rewrite and it back to PSWriteExcel
  2. PSsharedGoods is only needed during development - as in if you would add features to PSWriteExcel

Sometime around 0.1.4, I've improved my building module called PSPublishModule which among other features it able to extract required functions from other "approved" modules and merge them to a single PSM1 as if they were native private functions.

image

It seems that during the migration of Excelimo cmdlets to PSWriteExcel I've forgotten this cmdlet.

function ConvertTo-ScriptBlock {
    param(
        [Array] $Code,
        [string[]] $Include,
        [string[]] $Exclude
    )
    if ($Include) {
        $Output = foreach ($Line in $Code) {
            foreach ($I in $Include) {
                if ($Line.StartsWith($I)) {
                    $Line
                }
            }
        }
    }
    if ($Exclude) {
        $Output = foreach ($Line in $Code) {
            $Tests = foreach ($E in $Exclude) {
                if ($Line.StartsWith($E)) {
                    $true
                }
            }
            if ($Tests -notcontains $true) {
                $Line
            }
        }
    }
    if ($Output) {
        [ScriptBlock]::Create($Output)
    }
}

I've added it back to PSWriteExcel, "combined" so if you Install-Module PSWriteExcel again everything should be in its place without the need for PSSharedGoods.

Hope it explains the whole process.

Anyway, I've never noticed lack of that cmdlet because of experimental nature of Excel/Worksheet/Workbook. It may need rewrite. I added it because I wanted to have a cooler version to use, but the most important feature for me was "Parallel" switch. 

$Process = Get-Process | Select-Object -First 5

Excel -FilePath $PSScriptRoot\"Run-Demo01.xlsx" {
    WorkbookProperties -Title 'Test'
    Worksheet -DataTable $Process -Name 'Processes'

    Worksheet -DataTable $Process -Name 'Processes Test1' -TabColor Crimson
    Worksheet -DataTable $Process -Name 'Processes Test2' -TabColor BlueViolet
    Worksheet -DataTable $Process -Name 'Processes Test3' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test43' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test5' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test6' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test7' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test7' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test9' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test10' -TabColor Aquamarine

    for ($i = 0; $i -le 500; $i++) {
        #Worksheet -DataTable $Process -Name "Processes Test $i" -TabColor BlanchedAlmond
    }

} -Verbose -Open

And with experimental Parallel it's much faster

$Process = Get-Process | Select-Object -First 5

Excel -FilePath $PSScriptRoot\"Run-Demo01.xlsx" {
    WorkbookProperties -Title 'Test'
    Worksheet -DataTable $Process -Name 'Processes'

    Worksheet -DataTable $Process -Name 'Processes Test1' -TabColor Crimson
    Worksheet -DataTable $Process -Name 'Processes Test2' -TabColor BlueViolet
    Worksheet -DataTable $Process -Name 'Processes Test3' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test43' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test5' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test6' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test7' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test7' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test9' -TabColor Aquamarine
    Worksheet -DataTable $Process -Name 'Processes Test10' -TabColor Aquamarine

    for ($i = 0; $i -le 500; $i++) {
        #Worksheet -DataTable $Process -Name "Processes Test $i" -TabColor BlanchedAlmond
    }

} -Verbose -Open -Parallel

However, I've seen it become stuck from time to time, hence Parallel may not work reliably, and converting to and from script block, editing its content on the fly and putting it back is bound to errors.

ioamnesia commented 3 years ago

Awesome. Thanks for knocking this out so quick :)

Excel is notoriously finicky about locking files when in use, so maybe the parallel problems are due to simultaneous access? Just a thought. Best of luck!

PrzemyslawKlys commented 3 years ago

I don't think it's that. More likely my runspaces code is buggy and never returns from a loop. As working with runspaces, timeouts, and other parallel processing is not really trivial it would require more debugging time which I don't have time for now.