使用PowerShell将DataTable导出到Excel [英] Export DataTable to Excel using PowerShell

问题描述

我有一个从TableName和ColumnName-Array的简单输入中创建 System.Data.Datatables 的功能.填写完表格后，我想将它们添加到数据集中并将其导出到excel文档中.

下面的代码可以做到这一点，但是在导出到excel的过程中，我发现一点点都不明显.每个表都必须导出到一个csv中，然后重新导入到excel中.

是否有更好的更清洁的方法直接在Excel中使用DataTables?

 函数MakeTable($ TableName，$ ColumnArray){$ btab =新对象System.Data.DataTable("$ TableName")foreach($ ColumnArray中的$ Col){$ MCol =新对象System.Data.DataColumn $ Col;$ btab.Columns.Add($ MCol)}返回，$ btab}函数DataSetToExcel($ Ds，$ workdirectory){$ excel =新对象-ComObject excel.application$ workbook = $ excel.Workbooks.Add(1)$ i = 0for($ DsIndex = 0; $ DsIndex -lt $ ds.Tables.Count; $ DsIndex ++){$ Table = $ ds.tables [$ Dsindex]if($ Dsindex -ne 0){$ workbook.worksheets.Add()|虚无#Errtellt neues Arbeitsblatt}$ Table |Export-Csv"$ workdirectory \ input.csv"-编码UTF8 -NoTypeInformation -Force-分隔符;"$ inputCSV ="C:\ Temp \ Test \ input.csv"$ worksheet = $ workbook.worksheets.Item(1)$ worksheet.Name = $ Table.TableName$ TxtConnector =("TEXT;" + $ inputCSV)$ Connector = $ worksheet.QueryTables.add($ TxtConnector，$ worksheet.Range("A1"))$ query = $ worksheet.QueryTables.item($ Connector.name)$ query.TextFileOtherDelimiter = $ Excel.Application.International(5)$ query.TextFileParseType = 1$ query.AdjustColumnWidth = 1$ query.Refresh()|空空$ query.Delete()}$ outputXLSX ="$ workdirectory \ output.xlsx"$ Workbook.SaveAs($ outputXLSX，51)$ excel.Quit()}函数MakeTestTable($ TableName){$ TestTable = MakeTable $ TableName @("A"，"B")for($ i = 0; $ i -lt 10; $ i ++){$ aRow = $ TestTable.NewRow()$ aRow ["A"] =(10- $ i).ToString()$ aRow ["B"] = $ i.ToString()$ TestTable.Rows.Add($ aRow)}return，$ TestTable}$ db =新对象System.Data.DataSetfor($ cx = 0; $ cx -lt 10; $ cx ++){$ tab1 = MakeTestTable"$ cx"$ db.Tables.Add($ tab1)}DataSetToExcel $ db"C:\ Temp \ Test" 

解决方案

在Github上查看我的PowerShell Excel模块

I have a function to create System.Data.Datatables from a simple input of TableName and ColumnName-Array. After filling the tables I want to add them to a dataset and export this one into an excel document.

The code below does that, however in the export to excel there is one bit that I find a bit unelegant. Every table will have to be exported into a csv and then reimported to excel.

Is there a better cleaner way to use DataTables directly in Excel?

Function MakeTable ($TableName, $ColumnArray)
{
    $btab = New-Object System.Data.DataTable("$TableName")
    foreach($Col in $ColumnArray)
    {
        $MCol = New-Object System.Data.DataColumn $Col;
        $btab.Columns.Add($MCol)
    }
    return , $btab
}

function DataSetToExcel ($Ds, $workdirectory)
{
    $excel = New-Object -ComObject excel.application 
    $workbook = $excel.Workbooks.Add(1)
    $i = 0
    for($DsIndex=0;$DsIndex -lt $ds.Tables.Count;$DsIndex++)
    {
        $Table = $ds.tables[$Dsindex]
        if($Dsindex -ne 0)
        {
            $workbook.worksheets.Add() | Out-Null #Erstellt neues Arbeitsblatt
        }
        $Table | Export-Csv "$workdirectory\input.csv" -Encoding UTF8 -NoTypeInformation -Force -Delimiter ";"
        $inputCSV = "C:\Temp\Test\input.csv"
        $worksheet = $workbook.worksheets.Item(1)
        $worksheet.Name = $Table.TableName
        $TxtConnector = ("TEXT;" + $inputCSV)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFileOtherDelimiter = $Excel.Application.International(5)
        $query.TextFileParseType  = 1
        $query.AdjustColumnWidth = 1
        $query.Refresh() | Out-Null
        $query.Delete()
    }
    $outputXLSX = "$workdirectory\output.xlsx"
    $Workbook.SaveAs($outputXLSX,51)
    $excel.Quit()
}

function MakeTestTable ($TableName)
{
    $TestTable = MakeTable $TableName @("A","B")
    for($i=0;$i -lt 10; $i++)
    {
        $aRow = $TestTable.NewRow()
        $aRow["A"] = (10-$i).ToString()
        $aRow["B"] = $i.ToString()
        $TestTable.Rows.Add($aRow)
    }
    return , $TestTable
}

$db = New-Object System.Data.DataSet
for($cx=0;$cx -lt 10;$cx++)
{
    $tab1 = MakeTestTable "$cx"    
    $db.Tables.Add($tab1)
}
DataSetToExcel $db "C:\Temp\Test"

解决方案

Check out my PowerShell Excel Module on Github. You can also grab it from the PowerShell Gallery.

It also works directly with CSV format using Import-Csv or ConvertFrom-Csv (basically any PowerShell object array).

function New-Person {

    param($First,$Last)

    $row=$dataTable.NewRow()
    $row["First"]=$First
    $row["Last"]=$Last
    $dataTable.Rows.Add($row)
}

$dataTable = New-Object System.Data.DataTable("Test")
$dataTable.Columns.Add((New-Object System.Data.DataColumn "First"))
$dataTable.Columns.Add((New-Object System.Data.DataColumn "Last"))

New-Person John Doe
New-Person Tom Doe
New-Person Jane Doe
New-Person Mary Doe

$dataTable | 
    Select First, Last | 
    Export-Excel c:\temp\people.xlsx -AutoSize -Show

这篇关于使用PowerShell将DataTable导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！