使用 powershell 将数据从数据集插入到工作表中的 Excel 表格 [英] Insert data from a dataset to an Excel table in a worksheet using powershell

问题描述

我有一个包含 3 个工作表的 Excel 文件，第三个称为DataArea".此工作表有一个名为Table1"的空表，其中包含 2 列Column1"和Column2".我已经从我的 SQL 服务器数据库中检索了数据，所以我在数据集中有 2 列.我的范围是将包含在我的数据集中的信息插入到 Excel 中的那个表中，需要这样做，因为该表是第二个工作表中图形的来源.

I have an Excel file with 3 worksheets, the third is called "DataArea". This worksheet has a empty table called "Table1" with 2 columns "Column1" and "Column2". I already retrieve the data from my SQL server database, so i have 2 columns in a dataset. My scope is to insert the information contained in my dataset to that table in excel, in need to do this because because the table is the source of a graphic in the second sheet.

有没有办法做到这一点?

Is there a way to do that?

提前致谢.

推荐答案

拿这个，看评论:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel


# create dummy datatable with 2 colums
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add('Column1',[string]::empty.GetType() )
[void]$dt.Columns.Add('Column2',[string]::empty.GetType() )

#fill datatable
for( $i = 0; $i -lt 10; $i++ ) {

    $row          = $dt.NewRow()
    $row.Column1  = 'Hello' + $i.ToString()
    $row.Column2  = 'World' + $i.ToString()
    [void]$dt.Rows.Add( $row )
}

# to move a datatable to excel, it must be an n-dimensional array of objects
# move datatable to object-array:

$excelArray = New-Object 'object[,]' $dt.Rows.Count, $dt.Columns.Count

for( $i = 0; $i -lt $dt.Rows.Count; $i++ ) {
    $excelArray[$i, 0] = $dt.Rows[$i].Column1
    $excelArray[$i, 1] = $dt.Rows[$i].Column2
}

# open excel file
$excelFile = 'D:\test\emptySheet.xlsx' 

$excel                = New-Object -ComObject Excel.Application
$excel.Visible        = $true
$excel.ScreenUpdating = $true

$workbook  = $excel.Workbooks.Open( $excelFile ,$null, $false )

$ws        = $workbook.WorkSheets.item(1) 

[void]$ws.Activate()

# select range
$range = $ws.Range('A1', ([char](64 + $dt.Columns.Count)).ToString() + ($dt.Rows.Count).ToString() ) 

# copy aray to excel range
$range.Value2 = $excelArray

# save and cleanup
[void]$workbook.Save()
[void]$workbook.Close()
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

这篇关于使用 powershell 将数据从数据集插入到工作表中的 Excel 表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！