LeVeilleur.net

Hello,

Voici une fonction powershell vous permettant de regrouper/merge de tous les fichiers portant une extension particulière.

$source="C:\"
$extenstion ="*.csv"
$outputFile = "MyMergeFile.csv"
$blockTerminator = "ENDOFFILE"

function mergeFile ($source, $extenstion, $outputFile, $blockTerminator)
{

[System.IO.DirectoryInfo]$directoryInfo = New-Object System.IO.DirectoryInfo($source);
$rgFiles = $directoryInfo.GetFiles($extenstion);
$builder = New-Object System.Text.StringBuilder;

foreach ($fileInfo in $rgFiles)
	{
	[System.IO.FileStream]$fReader = $fileInfo.OpenRead();
	if (-not ($fileInfo -eq $null))
		{
		write $fileInfo.Name;
		$reader = New-Object System.IO.StreamReader($fReader);
		$builder.AppendLine($reader.ReadToEnd());
		$builder.AppendLine($blockTerminator);
		}
	}
if (-NOT $source.EndsWith('\'))
	{
 	$source = $source + '\';
	}

$outputFile = $source + $outputFile;
[System.IO.FileStream]$fWriter = New-Object System.IO.FileStream($outputFile, [System.IO.FileMode]::OpenOrCreate);
$writer = New-Object System.IO.StreamWriter($fWriter);
$writer.Write($builder.ToString());
$writer.Flush();
$writer.Close();
}

mergeFile $source $extenstion $outputFile $blockTerminator

Voici quelques bouts de code pour controler Microsoft Excel à partir de PowerShell

#Creation of the xls Sheet

$a = New-Object -comobject Excel.Application
$a.visible = $True

$MyWorkBook = $a.Workbooks.Add()
$MyWorksheets = $MyWorkBook.Worksheets.Item(1)
$i=1;

$MyWorksheets.Cells.Item(1,$i++) = "Server Name"
$MyWorksheets.Cells.Item(1,$i++) = "Host"
$MyWorksheets.Cells.Item(1,$i++) = "Cluster"

#Excel Sheet Format
$d = $MyWorksheets.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$MyWorksheets.Cells.Item($intRow,$i++) = "OK"
$MyWorksheets.Cells.Item($intRow,$i-1).Interior.ColorIndex = 4

#Save the XLS page in CSV
$FileFormat=[Microsoft.Office.Interop.Excel.xlFileFormat]::xlCsv
if(test-path "$FileLocation\BocadaExport.csv"){Remove-Item "$FileLocation\BocadaExport.csv"}
$MyWorksheets.SaveAs("$FileLocation\BocadaExport.csv",$FileFormat)

#Quit XLS
$excel.displayalerts = $False
$excel.quit()