Powershell在Excel单元格中使用多行文本 [英] Powershell work with multiline text in Excel cell

问题描述

我有一个相当简单的请求（对我来说这是相当艰巨的任务tbh）。

I have a fairly simple request (for me it is quite tough task tbh).

我有两个CSV文件，我想转换为Excel，这两个CSV文件将分别占用一张。

I have two CSV files which I want to convert to Excel so each of these two CSV files would occupy one sheet each. So far, I have made it work, but I have this small thing I want to correct.

CSV中的一个单元格包含多个文本行，这样的：

One of the cells in CSV contains multiple text lines, something like this:

这是条目1

这是条目2

我想要将这两个条目导入到Excel单元格中，就像在CSV中一样，但是当我检查我的Excel文件时，第二个条目被导入到下一行：

I would like to have these two entries to be imported into Excel cell the same way it is in CSV, but when I check my Excel file, the second entry is imported into next row:

行1 Cell1 - 这是条目1

Row 1 Cell1 - This is entry 1

行2 Cell1 - 这是条目2

Row 2 Cell1 - This is entry 2

我不知道我是否应该使用.NET类worksheet.UsedRange.EntireRow或worksheet.UsedRange.EntireColumn或其他。
我正在检查MSDN，但由于我还是一个noob，我找不到任何东西。

I don't know if I should work with .NET class worksheet.UsedRange.EntireRow or worksheet.UsedRange.EntireColumn or something else. I was checking MSDN, but since I am still a noob, I couldn't find anything.

这是我的代码示例：

Function Merge-CSVFiles
{
Param(
$CSVPath = ".\Reports", 
$XLOutput=".\final_final_report.xlsx"
)

$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Excel.sheetsInNewWorkbook = $csvFiles.Count
$workbooks = $excel.Workbooks.Add()
$CSVSheet = 1

Foreach ($CSV in $Csvfiles)

{
$worksheets = $workbooks.worksheets
$CSVFullPath = $CSV.FullName
$SheetName = ($CSV.name -split "\.")[0]
$worksheet = $worksheets.Item($CSVSheet)
$worksheet.Name = $SheetName
$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")
$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.QueryTables.item($Connector.name).delete()
$worksheet.UsedRange.EntireColumn.AutoFit()
$CSVSheet++

}

$workbooks.SaveAs($XLOutput,51)
$workbooks.Saved = $true
$workbooks.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

推荐答案

我不知道为什么你使用的方法，但我能够在我的系统以及。我在我的一个脚本中做一些类似的操作，构建一个大的Excel工作簿，我已经成功使用了以下方法：

I am not sure why the method you are using does that but I was able to reproduce on my system as well. I do some similar operations in one of my scripts that builds a large Excel workbook and I have used the following method with success:

1. 导入CSV with Import-CSV


2. 将它转换为多维数组


3. 将数组写入范围对象的Value2

例如，替换以下代码：

$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")
$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.QueryTables.item($Connector.name).delete()

改为：

$CsvContents = Import-Csv $CSVFullPath
$MultiArray = (ConvertTo-MultiArray $CsvContents -Headers).Value
$StartRowNum = 1
$StartColumnNum = 1
$EndRowNum = $CsvContents.Count + 1
$EndColumnNum = ($CsvContents | Get-Member | Where-Object { $_.MemberType -eq 'NoteProperty' }).Count
$Range = $worksheet.Range($worksheet.Cells($StartRowNum, $StartColumnNum), $worksheet.Cells($EndRowNum, $EndColumnNum))
$Range.Value2 = $MultiArray

需要用于将对象转换为多维数组的函数（基于这里，但稍作修改）：

For that to work you will also need the function I use for converting an object to a multi-dimensional array (based off the one posted here but with some slight modifications):

function ConvertTo-MultiArray
{
    param (
        $InputObject,
        [switch]$Headers = $false
    )
    begin
    {
        $Objects = @()
        [ref]$Array = [ref]$null
    }
    process
    {
        $Objects += $InputObject
    }
    end
    {
        $Properties = $Objects[0].PSObject.Properties | ForEach-Object{ $_.Name }
        $Array.Value = New-Object 'object[,]' ($Objects.Count + 1), $Properties.Count
        $ColumnNumber = 0
        if ($Headers)
        {
            $Properties | ForEach-Object{
                $Array.Value[0, $ColumnNumber] = $_.ToString()
                $ColumnNumber++
            }
            $RowNumber = 1
        }
        else
        {
            $RowNumber = 0
        }
        $Objects | ForEach-Object{
            $Item = $_
            $ColumnNumber = 0
            $Properties | ForEach-Object{
                if ($Item.($_) -eq $null)
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = ""
                }
                else
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = $Item.($_).ToString()
                }
                $ColumnNumber++
            }
            $RowNumber++
        }
        $Array
    }
}

这篇关于Powershell在Excel单元格中使用多行文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！