Powershell在Excel单元格中使用多行文字工作
我有一个相当简单的请求(对我来说这是一项非常艰巨的任务tbh)。Powershell在Excel单元格中使用多行文字工作
我有两个CSV文件,我想要转换为Excel,因此这两个CSV文件中的每一个将占用一张纸。 到目前为止,我已经成功了,但我想要纠正这个小问题。
之一的CSV细胞包含多个文本行,像这样:
这是第1项
这是进入2
我想有这两个条目需要进口到Excel单元格中以同样的方式是在CSV,但是当我检查我的Excel文件,第二个项目被导入到下一行:
行1个小区1 - 这是进入1
第2行第1行 - 这是条目2
我不知道我是否应该使用.NET类worksheet.UsedRange.EntireRow或worksheet.UsedRange.EntireColumn或其他。 我正在检查MSDN,但由于我仍是一个noob,我找不到任何东西。
这是我的代码示例:
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工作簿我的剧本之一,我已经成功地使用了下面的方法:
- 导入CSV拥有自营CSV
- 将其转换为一个多维数组
:$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()
- 写范围对象
。例如数组值2,看起来像这样替换代码
用这个代替:
$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
对于工作,你还需要我用一个对象转换为一个多维数组(基于关闭一个贴here但稍作修改)功能:
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
}
}
再次问候。乔恩,我已经尝试过你的解决方案,它的工作:)谢谢你,你已经救了我很可能几个小时与我的头撞墙。 我得到了我想要的,当我将它移动到Excel中时,CSV文件中的多行文本保留在一个单元格中。 –
非常好!我很高兴,当我尝试自动化Excel任务时,我的痛苦时间已经让其他人受益。 :) –
嗯,好像直接在Excel中打开CSV时,你可以用引号括整个进入和Excel将识别新行作为同一项目的一部分像(http://*.com/questions/566052/can -you编码-CR-LF-在 - 进入 - CSV文件)。因此,您可以预处理CSV并将条目换成引号。但即使如此,使用QueryTables的方法也无法正确识别。 Excel自己的数据导入工具 - 我唯一可以实现的方法是将.csv作为Excel中的新工作簿打开。 – Mikegrann
我可以想出的最佳解决方案:对所有数据进行预处理(例如用“,”替换),然后用'Workbooks.Open'单独打开所有.csv文件。全部打开后,通过从所有工作簿复制到主工作簿来合并数据。 – Mikegrann
否则使用特殊代码预处理数据以替换换行符的实例,以便excel将整个事件视为一个条目,然后在导入后可以后处理将此代码替换回换行符。 – Mikegrann