PoshCode Archive  Artifact [3fb1e51841]

Artifact 3fb1e518412e68e67b1f027b730272e7604e1a2ec3d000856b2a13a3e3eb254e:

• File Excel-LoadFile.ps1 — part of check-in [3c37a6fe43] at 2018-06-10 13:25:08 on branch trunk — Load an Excel document into an Excel COM object for processing as input. These functions can be modified to start reading the data at a specified row count. The data/headers do not need be on the first row. (user: Vidrine size: 2034)

# encoding: ascii
# api: powershell
# title: Excel-LoadFile
# description: Load an Excel document into an Excel COM object for processing as input. These functions can be modified to start reading the data at a specified row count.  The data/headers do not need be on the first row.
# version: 0.1
# type: function
# author: Vidrine
# license: CC0
# function: Excel-LoadFile
# x-poshcode-id: 3402
# x-archived: 2017-05-22T03:17:40
# x-published: 2012-05-07T14:44:00
#
# Additionally, the headers can be queried based on the beginning row information, and can then be referenced by name, instead of number.
# Also, the Excel-CloseFile will terminate all Excel processes running.  This is to free the Excel input file from memory to allow for it to be moved/edited/touched by another process/function.
#
# $Excel = New-Object -ComObject Excel.Application

Function Excel-LoadFile {
	Param (
		$SourceFile,
		$ExcelObject
	)
	
	#$excel.visible = $true # Makes Excel document visible on the screen
	$Workbook   = $ExcelObject.Workbooks.Open($SourceFile)
	$Worksheets = $Workbook.Worksheets
	$Worksheet  = $Workbook.Worksheets.Item(1)
	
	return $Worksheet		
}

Function Excel-RowCount {
	Param ($Worksheet)
	
	$range   = $Worksheet.UsedRange
	$rows    = $range.Rows.Count
	$rows    = $rows - 2
	
	return $rows
}

Function Excel-ColumnCount {
	Param ($Worksheet)
	
	$range   = $Worksheet.UsedRange
	$columns = $range.Columns.Count
		
	return $columns
}

Function Excel-ReadHeader {
    Param ($Worksheet)
	
    $Headers =@{}
    $column = 1
    Do {
        $Header = $Worksheet.cells.item(3,$column).text
        If ($Header) {
            $Headers.add($Header, $column)
            $column++
        }
    } until (!$Header)
    $Headers
}

Function Excel-CloseFile {
	Param($ExcelObject)
	
	$ExcelProcess = Get-Process Excel
	$ExcelProcess | ForEach { Stop-Process ( $_.id ) }
	[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) | Out-Null
}