Copy a Varible into a excel worksheet

i have created a varible using name $MnSH += new-object psobject -property @{.     it has 6 columns and some 10,000+ rows.

I have an excell spreadsheet that i update monthly with this report and i already add a new sheet to it.  Then copy the data in one piece at a time.   I am looking for a way to just paste the entire $MnSH into this sheet so that it will not take so long  

Dclavet

"
September 3rd, 2015 11:18am

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

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 11:28am

Here is an example with OleDb from a CSV.

Param(
    $excelFile='update_tester.xlsx',
    $csvFile='names.csv'
)

$conBase='Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0;HDR=YES";Persist Security Info=False'
$connectionString=$conBase -f $filename
$conn = new-object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()

$cmd = $conn.CreateCommand()
$items=Import-Csv items.csv

# we are going to update phones
foreach ($item in $items) {
    $cmd.CommandText = 'UPDATE [Sheet1$] SET Phone=[{0}] where Name=[{1}]' -f $item.Phone, $item.Name
    $cmd.ExecuteNonQuery()
}


You can use similar methods to update from and array.

September 3rd, 2015 11:38am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics