xlsx

1.0

xlsx.psm1

<#
 .Synopsis
 xlsx file read
  
 .Description
  Functions to read a xlsx file and converting a csv to xlsx
 
 .Parameter Xlsx-Get-TableNames
 .Parameter Xlsx-Get-TableData
 .Parameter Xlsx-Convert-from-Csv
#>

Set-Variable Global:xlsx_OleDbEnumerator -value (([System.Data.OleDb.OleDbEnumerator]::new()).GetElements() | ? { $_.SOURCES_NAME -match "Microsoft.ACE.OleDB" }) -option ReadOnly -Force
if ($global:xlsx_OleDbEnumerator -is [System.Array]){
  Set-Variable global:xlsx_sourceName -value $xlsx_OleDbEnumerator[0].SOURCES_NAME -option ReadOnly -Force
} else {
  Set-Variable global:xlsx_sourceName -value $xlsx_OleDbEnumerator.SOURCES_NAME -option ReadOnly -Force
}

Set-Variable global:Xlsx_Provider -value "Provider=$($global:xlsx_SourceName);" -option ReadOnly -Force
Set-Variable global:Xlsx_Data -value "Data Source='`{0`}';" -option ReadOnly -Force
Set-Variable global:xlsx_ExtProp -value "Extended Properties=`"Excel 12.0 xml;HDR=Yes;`"" -Option ReadOnly -Force
Set-Variable global:xlsx_ExtProp_IMEX -value "Extended Properties=`"Excel 12.0 xml;HDR=Yes;IMEX=`{0`};`"" -Option ReadOnly -Force

function Xlsx-ResolvePath{
  [cmdletbinding(DefaultParameterSetName = 'Path')]
  param(
    [parameter(
        Mandatory,
        ParameterSetName = 'Path',
        Position = 0,
        ValueFromPipeline,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [SupportsWildcards()]
    [string[]]$Path,
    [parameter(
        Mandatory,
        ParameterSetName = 'LiteralPath',
        Position = 0,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [Alias('PSPath')]
    [string[]]$LiteralPath
  )
  begin{
    if($PSBoundParameters.ContainsKey('Debug')){$DebugPreference=[System.Management.Automation.ActionPreference]::Continue}
    Write-Debug $MyInvocation.MyCommand
  }
  process{
    try{
      Switch (($psBoundParameters.GetEnumerator() |? {$_.Value -and $_.key -ne "Debug"}).key){
        "Path" { 
          write-Debug "Path Selected"
          $retval=Resolve-Path $path -ErrorAction stop 
        }
        "LiteralPath" {
          Write-Debug "Literalpath Selected"
          $retval=Resolve-path $literalpath -ErrorAction stop
        }
        Default { Throw "Noting to Resolve" }
      }
    }catch [System.Management.Automation.ItemNotFoundException]{
      Write-debug "ItemNotfoundError"
      #Arriving Here Means the file you trying to access isn't there return somtingh that looks like a path for xlsx
      Switch (($psBoundParameters.GetEnumerator() |? {$_.Value -and $_.key -ne "Debug"}).key){
        "Path" {
          write-debug "ErrorHandle Path"
          write-debug (Resolve-path ".\")
          $retval=[PsCustomObject]@{"Path"=(([string](Join-Path -path (Resolve-Path -Path ".\") -ChildPath $Path)) -replace "^(.*)(?:\.xlsx)$",'$1' ) + ".xlsx"}}
       "LiteralPath" {
         write-debug "ErrorHandle LiteralPath"
         $retval=[PsCustomObject]@{"Path"=$LiteralPath}
         }
       Default { throw "Noting to Resolve" }
      }
    }catch{
      throw $_
    }finally{
      #Closeing
    }
    write-debug $retval
    return $retval
  } 
}

Function Get-XlsxTableNames{
  [cmdletbinding(DefaultParameterSetName = 'Path')]
  param(
    [parameter(
        Mandatory,
        ParameterSetName = 'Path',
        Position = 0,
        ValueFromPipeline,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [SupportsWildcards()]
    [string]$Path,
    [parameter(
        Mandatory,
        ParameterSetName = 'LiteralPath',
        Position = 0,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [Alias('PSPath')]
    [string]$LiteralPath
  )
  begin {
    if($PSBoundParameters.ContainsKey('Debug')){$DebugPreference=[System.Management.Automation.ActionPreference]::Continue}
    Write-Debug $MyInvocation.MyCommand
  }
  process {
    if($PSBoundParameters.ContainsKey('Path')){
      write-debug "Path"
      $resolvedpaths = xlsx-ResolvePath -Path $Path
    }
    if($PSBoundParameters.ContainsKey("LiteralPath")){
      Write-debug "LiteralPath"
      $resolvedpaths = xlsx-ResolvePath -LiteralPath $LiteralPath
    }
    $resolvedPaths | %{
      Write-debug $_
      $FullName=(Get-item $_).FullName
      $szConnectionString = $global:xlsx_Provider+($global:xlsx_Data -f $FullName)+$global:xlsx_ExtProp
      Write-Debug $szConnectionString
      [System.Data.Common.DbConnection]$objConn=[System.Data.OleDb.OleDbConnection]::new($szConnectionString)
      try{
        $objConn.Open()
        ($objConn.GetSchema("Tables") <#|? {$_.Table_Name -match "\$`$"}#>).Table_Name |% {
          [PSCustomObject]@{
            Path=$FullName;
            Table_Name=$_
          }
        }
      }Catch{
        $_
      }Finally{
        $objConn.Close()
      }
    }
  }
}

Function Get-XlsxTableData{
  [cmdletbinding(DefaultParameterSetName = 'Path')]
  param(
    [parameter(
        Mandatory,
        ParameterSetName = 'Path',
        Position = 0,
        ValueFromPipeline,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [SupportsWildcards()]
    [string]$Path,
    [parameter(
        Mandatory,
        ParameterSetName = 'LiteralPath',
        Position = 0,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [Alias('PSPath')]
    [string]$LiteralPath,
    [Parameter(ValueFromPipelineByPropertyName = $true)][string]$Table_Name,
    [Switch]$Noheader,
    [Switch]$IMEX
  )
  begin{
    if($PSBoundParameters.ContainsKey('Debug')){$DebugPreference=[System.Management.Automation.ActionPreference]::Continue}
    Write-Debug $MyInvocation.MyCommand
  }
  Process{
    if($PSBoundParameters.ContainsKey('Path')){
      write-debug "Path"
      $resolvedpaths = xlsx-ResolvePath -Path $Path
    }
    if($PSBoundParameters.ContainsKey("LiteralPath")){
      Write-debug "LiteralPath"
      $resolvedpaths = xlsx-ResolvePath -LiteralPath $LiteralPath
    }
    write-debug ("Path:{0}" -f ($resolvedPaths | select -ExpandProperty Path) )
    $resolvedPaths | %{
      $szConnectionString = $global:xlsx_Provider+($global:xlsx_Data -f (Get-Item -LiteralPath $_).fullName)+$global:xlsx_ExtProp
      if($NoHeader.IsPresent){$szConnectionString -replace "HDR=Yes","HDR=No"}
      if($IMEX.IsPresent){$szConnectionString -replace "`"$","IMEX=1;`""}
      Write-Debug $szConnectionString
      [System.Data.Common.DbConnection]$objConn=[System.Data.OleDb.OleDbConnection]::new($szConnectionString)
      try{
        $objConn.Open()
        [System.Data.OleDb.OleDbDataAdapter]$objDA=[System.Data.OleDb.OleDbDataAdapter]::new(("select * from [{0}]" -f $Table_Name), $objConn)
        [System.Data.DataSet]$ExcelDataSet=New-Object System.Data.DataSet
        $objDA.Fill($ExcelDataSet) | Out-Null
        #Return Xlsx-ConvertTo-PsCustomObject $ExcelDataSet
        return $ExcelDataSet
      }catch{
        $_
      }finally{
        $objconn.Close()
      }
    }
  }
}

Function ConvertTo-XlsxFromCsv{
  #https://www.vbforums.com/showthread.php?844591-VB6-Convert-CSV-to-Excel-Using-ADO
  [cmdletbinding(DefaultParameterSetName = 'Path')]
  param(
    [parameter(
        Mandatory,
        ParameterSetName  = 'Path',
        Position = 0,
        ValueFromPipeline,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [SupportsWildcards()]
    [string[]]$Path,
    [parameter(
        Mandatory,
        ParameterSetName = 'LiteralPath',
        Position = 0,
        ValueFromPipelineByPropertyName
    )]
    [ValidateNotNullOrEmpty()]
    [Alias('PSPath')]
    [string[]]$LiteralPath,
    [string]$Table_Name="Sheet1",
    [string]$DestinationPath,
    [ValidateSet("Yes","No")]$CsvHeader="Yes",
    [ValidateSet("Delimited","Delimited(x)","CSVDelimited","TabDelimited")]
    [String]$Delimited="CSVDelimited",
    [string]$Delimiter=";"
  )
  Begin{
   if($PSBoundParameters.ContainsKey('Debug')){$DebugPreference=[System.Management.Automation.ActionPreference]::Continue}
    Write-Debug $MyInvocation.MyCommand
  }
  Process{
    if($PSBoundParameters.ContainsKey('Path')){
      write-debug "Path"
      $resolvedpaths = xlsx-ResolvePath -Path $Path
    }
    if($PSBoundParameters.ContainsKey("LiteralPath")){
      Write-debug "LiteralPath"
      $resolvedpaths = xlsx-ResolvePath -LiteralPath $LiteralPath
    }
    if($CsvHeader){$sHDRProp="Yes"}else{$sHDRProp="No"}
    if($Delimited -eq "Delimited(x)"){
      $_Delimited=$Delimited -replace "x",$Delimiter
    }else{
      $_Delimited=$Delimited
    }
    Write-Debug $resolvedpaths
    $ResolvedPaths |% {
      Write-Debug ("CsvFile:{0}" -f (get-item $_).FullName)
      if($PSBoundParameters.ContainsKey("DestinationPath")){
        Write-Debug ("DestinationPath:From Function Call") 
      }else{
        $DestinationPath=("{0}.xlsx" -f (join-path -Path (Get-Item $_).directoryName -ChildPath (get-item $_).BaseName))
        Write-Debug ("Destination:{0}" -f $DestinationPath)  
      }
      $szConnectionString=("{0}Data Source={1};Extended Properties='text;HDR={2};FMT={3}'" -f $global:xlsx_Provider,(Get-item $_).DirectoryName,$CsvHeader,$_Delimited)
      Write-Debug $szConnectionString
      [System.Data.OleDb.OleDbConnection]$objConn=[System.Data.OleDb.OleDbConnection]::new()
      $objConn.ConnectionString=$szConnectionString
      try{
        $objconn.Open()
        $sSql=("Select * INTO [{0}] IN '' [Excel {1};DataBase={2}] FROM [{3}]" -f $Table_Name,"12.0 XML",$DestinationPath,((Get-Item $_).Name -replace "\.","#"))
        Write-Debug $sSql
        [System.Data.OleDb.OleDbCommand]$objCommand=[System.Data.OleDb.OleDbCommand]::new()
        $objCommand.Connection=$objConn
        $objCommand.CommandText=$sSql
        $objCommand.ExecuteNonQuery()
      }catch{
        $_.Exception
      }Finally{
        if($objConn.State){$objConn.close()}
      }
    }
  }
}

Export-ModuleMember Get-XlsxTableData
Export-ModuleMember Get-XlsxTableNames
Export-ModuleMember ConvertTo-XlsxFromcsv