[文本处理] bat读取文本内指定列数据并保存excel里

$fileTxt = "1.txt"
$fileExcel = "1.xlsx"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.displayAlerts = $true
$wb = $excel.Workbooks.add()

$filec = Get-Content -Path $fileTxt
$wb.sheets(1).columns(1).numberformatlocal= "general"

$c = 0,1,4,6,8

for ( $i = 1 ; $i -lt $filec.Count ; $i++ )
{ 
	$s = $fileC[$i] -replace "\s+",","
	$sa = ("," + $s).Split(",")
	for ( $j = 1 ; $j -lt $c.count ; $j++ )
	{
		$wb.sheets(1).cells($i,$j)=$sa[$c[$j]]
	}
}

$wb.Saveas($fileExcel)
$excel.Quit()
$excel = $null
[GC]::Collect()
复制代码

$sourPath = "D:\1"
$destPath = "D:\2"

$sourText = get-childitem -path $sourPath "*.txt"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.displayAlerts = $true
$c = 0,1,4,6,8

foreach ( $f in $sourText )
{
    $filec = Get-Content -Path $f.fullname
    $wb = $excel.Workbooks.add()
    $wb.sheets(1).Columns("A:D").numberformatlocal= "general"

    for ( $i = 1 ; $i -lt $filec.Count ; $i++ )
    { 
        $s = $fileC[$i] -replace "\s+",","
        $sa = ("," + $s).Split(",")
        for ( $j = 1 ; $j -lt $c.count ; $j++ )
        {
            $wb.sheets(1).cells($i,$j)=$sa[$c[$j]]
        }
    }

    $wb.Saveas($sourPath + $f.basename + ".xlsx")
    $excel.Quit()
}
$excel = $null
[GC]::Collect()
复制代码