You can insert bulk data into Excel from a CSV, a database or an array. Post in the Excel developers forum to find out how to program Excel with bulk data.
Her is an example for bulk inserting:
function Import-ExcelSheet{
param(
[Parameter(
Mandatory=$true
)][string]$FileName,
[string]$WorksheetName
)
$excel=New-Object -ComObject Excel.Application
$wb=$excel.workbooks.open($FileName)
if($WorksheetName){
if(-not($sheet=$wb.Sheets.Item($WorksheetName))){
throw "Unable to open worksheet $WorksheetName"
exit
}
}else{
Write-Warning 'Defaulting to the first worksheet in workbook.'
$sheet=$wb.ActiveSheet
}
$msg=('Worksheet {0} contains {1} columns and {2} lines' -f $sheet.Name,$sheet.UsedRange.Columns.Count,$sheet.UsedRange.Rows.Count)
Write-Verbose $msg
$fields=[ordered]@{}
for ($column = 1; $column -le $sheet.UsedRange.Columns.Count; $column ++) {
if($fieldName=$sheet.Cells.Item.Invoke(1, $column).Value2){
}else{
$fieldName='Column' + $column.ToString()
}
$fields.Add($fieldName,$null)
}
$keys=[array]$fields.Keys
for($row=2;$row -le $sheet.UsedRange.Rows.Count;$row++){
for ($i=0;$i -lt $sheet.UsedRange.Columns.Count;$i++){
$fields[$keys[$i]]=$sheet.Cells.Item.Invoke($row, $i+1).Value2
}
New-Object PSCustomObject -Property $fields
}
$wb.Close()
$excel.Quit()
while([System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet)){}
while([System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)){}
while([System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)){}
}
Import-ExcelSheet c:\scripts\test.xls