Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for readexcel.ps

(view source code of readexcel.ps as plain text) 

  1. param (
  2. 	[parameter( Mandatory = $False, ValueFromPipeline = $True )]
  3. 	[string] $excelfile,
  4. 	[switch] $h,
  5. 	[parameter( ValueFromRemainingArguments = $true )]
  6. 	[object] $invalidArgs
  7. )
  8.  
  9. if ( $h -or $invalidArgs -or $excelfile.Contains( "?" ) -or ![System.IO.File]::Exists( $excelfile ) ) {
  10. 	if ( ![string]::IsNullOrWhiteSpace( $excelfile ) -and !$excelfile.Contains( "?" ) -and ![System.IO.File]::Exists( $excelfile ) ) {
  11. 		Write-Host
  12. 		Write-Host "ERROR:   " -ForegroundColor Red -NoNewline
  13. 		Write-Host ( "Excel file `"{0}`" not found" -f $excelfile )
  14. 	} elseif ( $h -or [bool] $invalidArgs ) {
  15. 		Write-Host
  16. 		Write-Host "ERROR:   " -ForegroundColor Red -NoNewline
  17. 		Write-Host "Too many argument(s)"
  18. 	}
  19. 	Write-Host
  20. 	Write-Host "ReadExcel.ps1,  Version 1.00"
  21. 	Write-Host "Read an Excel file and show all cells for all sheets"
  22. 	Write-Host
  23. 	Write-Host "Usage:   " -NoNewline
  24. 	Write-Host "./ReadExcel.ps1  excelfile" -ForegroundColor White
  25. 	Write-Host
  26. 	Write-Host "Where:   " -NoNewline
  27. 	Write-Host "excelfile   " -ForegroundColor White -NoNewline
  28. 	Write-Host "is the Excel file to be read"
  29. 	Write-Host
  30. 	Write-Host "Notes:   This script requires Excel."
  31. 	Write-Host "         This script " -NoNewline
  32. 	Write-Host "may " -ForegroundColor White -NoNewline
  33. 	Write-Host "sometimes leave Excel open after running."
  34. 	Write-Host "         Return code (`"errorlevel`") 1 in case of errors, otherwise 0."
  35. 	Write-Host
  36. 	Write-Host "Credits: Based on article by François-Xavier Cat"
  37. 	Write-Host "         https://lazywinadmin.com/2014/03" -ForegroundColor Darkgray
  38. 	Write-Host "         /powershell-read-excel-file-using-com.html" -ForegroundColor Darkgray
  39. 	Write-Host
  40. 	Write-Host "Written by Rob van der Woude"
  41. 	Write-Host "https://www.robvanderwoude.com"
  42.  
  43. 	Exit 1
  44. }
  45.  
  46. $objExcel = New-Object -ComObject Excel.Application
  47. $workbook = $objExcel.Workbooks.Open( $excelfile )
  48. $workbook.Sheets | ForEach-Object {
  49. 	if ( ![string]::IsNullOrEmpty( $_.Range( "A1" ).Text ) ) {
  50. 		Write-Host $_.Name # sheet name
  51. 		Write-Host ( "=" * $_.Name.Length ) # underline sheet name
  52. 		for ( $row = 1; $row -le $_.UsedRange.Rows.Count; $row++ ) {
  53. 			for ( $column = 1; $column -le $_.UsedRange.Columns.Count ; $column++ ) {
  54. 				Write-Host ( "({0}{1})`t{2}" -f ( [char] ( $column + 64 ) ), $row, $_.Columns.Item( $column ).Rows.Item( $row ).Text )
  55. 			}
  56. 			Write-Host
  57. 		}
  58. 	}
  59. }
  60.  
  61. [void] $workbook.Close( )
  62. [void] $objExcel.Quit( )
  63.  

page last modified: 2024-04-16; loaded in 0.0080 seconds