[文本处理] 求助Excel 批量查找插入数据

haichuan5121

各位大哥大神们
            求教如何根据制定单元格内容批量插入指定数据行；
表格行数不一定，但C列每行都是唯一值
1）假如查找1009后在其后插入条件区黄色部分
2）假如查找1018后在其后插入条件区褐色部分
3）假如查找1023后在其后插入条件区紫色部分
4）填充后B列空白位置填充为上一行数据
[url=链接：https://pan.baidu.com/s/13nJ40KCHEVbxsmCvJ6ONlQ?pwd=8888  提取码：8888]附件[/url]

terse

回复 30# haichuan5121
能处理就好，就是觉得有点乱，还不如直接让ps在函数外面处理好；数组在函数外面循环，然后传进函数是两个文件加关键词及行列数；
行列数从文件的单元表格的背景色来获取要增加代码，行列固定的话不如直接按数组一次性传进函数，你现在是一个文件替换三次的话，是不是要传三次；
当然，怎样方便怎样来吧

haichuan5121

回复 29# terse


    不好意思 24楼执行是对的；插入三遍是因为Files1文件没有清空，里面有三遍路径导致的；

terse

没有搜索到关键词 就不执行下面的

if (!$found) {
    Write-Host "Pattern $pat not found in any file."
} else {
    $excel.DisplayAlerts = $false
    $wb.Close($false)
    $excel.Quit()
    [Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
}
复制代码

terse

回复 27# haichuan5121

晕了，以为你就只要操作一次文件插入，先说说加的$found意图是什么

haichuan5121

回复 26# terse


    效果一样的呢；还是只插入一个excel后面的就不执行了；三遍插入的原因是在 PowerShell 脚本中使用了两个嵌套的 foreach 循环，这样会导致对每个文件都进行一次插入操作。如果需要对每个文件进行一次插入操作，可以将 foreach 循环改为 if 判断这个怎么修改

terse

回复 25# haichuan5121
这样行不

    if (!$found) {
        Write-Host "Pattern $pat not found in any file."

        $excel.DisplayAlerts = $false
        $wb.Close($false)
        $excel.Quit()
        [Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        return
    }
复制代码

haichuan5121

回复 23# terse
添加判断语句后，只执行一个文件其余的就不执行了，怎么修改。。。。


    <# :

echo on & setlocal EnableDelayedExpansion

for /f "delims=" %%i in ('"dir /a/s/b/on 元数据*.*"') do (

set file=%%~fi

set file=!file:/=/!

echo !file! >> files1.txt

)
powershell -noprofile -NoLogo "iex (${%~f0} | out-string)"
pause
#>
function get-data([int]$startrow, [int]$startcol, [int]$endrow, [int]$endcol, [string]$pat, [string[]]$files1, [string[]]$files2) {
        foreach ($file2 in $files2) {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $wb = $excel.Workbooks.Open($file2)
    $ws = $wb.Sheets.Item(1)
    $sr = $ws.Range($ws.Cells.Item($startrow, $startcol), $ws.Cells.Item($endrow, $endcol))
    $sd = $sr.Value2
    $found = $false
    foreach ($file1 in $files1) {
        $dwb = $excel.Workbooks.Open($file1)
        $dws = $dwb.Sheets.Item(2)
        $fc = $dws.UsedRange.Find($pat)
        if ($fc -ne $null) {
            $found = $true
            $rown = $fc.Row
            for ($i = 0; $i -le $endrow-$startrow; $i++) {
                $dws.Rows.Item($rown + 1).EntireRow.Insert()
            }
            $dsr = $dws.Range($dws.Cells.Item($rown + 1, $startcol), $dws.Cells.Item($rown + 1 + ($endrow - $startrow), $endcol))
            $dsr = $dsr.Resize($sr.Rows.Count, $sr.Columns.Count)
            $dsr.Value2 = $sd
            $sr.Copy()
            $dsr.PasteSpecial(-4122)
            for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                # $val = $dsr.Cells.Item($i, 1).Offset(-1, 0)
                # $dsr.Cells.Item($i, 1).Value2 = $val.Value2
            }
            for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                $val = $dsr.Cells.Item($i, 4).Offset(-5, 0)
                $dsr.Cells.Item($i, 4).Value2 = $val.Value2
            }
            $dwb.Close($true)
            break
        }
    }
    if (!$found) {
        Write-Host "Pattern $pat not found in any file."
    }
    $excel.DisplayAlerts = $false
    $wb.Close($false)
    $excel.Quit()
    [Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

        }
}

$files1 = Get-Content "files1.txt"

$files2 = Get-Content "files2.txt"

get-data -startrow 1 -startcol 2 -endrow 4 -endcol 9 -pat 1004 -files1 $files1 -files2 $files2
get-data -startrow 6 -startcol 2 -endrow 8 -endcol 9 -pat 1011 -files1 $files1 -files2 $files2
get-data -startrow 10 -startcol 2 -endrow 11 -endcol 9 -pat 1013 -files1 $files1 -files2 $files2

haichuan5121

回复 21# terse
大哥 给看下 这个为啥执行了三遍插入啊 怎么修改啊？
<# :

echo on & setlocal EnableDelayedExpansion

for /f "delims=" %%i in ('"dir /a/s/b/on 元数据*.*"') do (

set file=%%~fi

set file=!file:/=/!

echo !file! >> files1.txt

)
powershell -noprofile -NoLogo "iex (${%~f0} | out-string)"
pause
#>
function get-data([int]$startrow, [int]$startcol, [int]$endrow, [int]$endcol, [string]$pat, [string[]]$files1, [string[]]$files2) {
        foreach ($file2 in $files2) {
                $excel = New-Object -ComObject Excel.Application
                $excel.Visible = $false
                $wb = $excel.Workbooks.Open($file2)
                $ws = $wb.Sheets.Item(1)
                $sr = $ws.Range($ws.Cells.Item($startrow, $startcol), $ws.Cells.Item($endrow, $endcol))
                $sd = $sr.Value2
                foreach ($file1 in $files1) {
                        $dwb = $excel.Workbooks.Open($file1)
                        $dws = $dwb.Sheets.Item(2)
                        $fc = $dws.UsedRange.Find($pat)
                        if ($fc -ne $null) {
                                $rown = $fc.Row
                                for ($i = 0; $i -le $endrow-$startrow; $i++) {
                                        $dws.Rows.Item($rown + 1).EntireRow.Insert()
                                }
                                $dsr = $dws.Range($dws.Cells.Item($rown + 1, $startcol), $dws.Cells.Item($rown + 1 + ($endrow - $startrow), $endcol))
                                $dsr = $dsr.Resize($sr.Rows.Count, $sr.Columns.Count)
                                $dsr.Value2 = $sd
                                $sr.Copy()
                                $dsr.PasteSpecial(-4122)
                                for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                                        # $val = $dsr.Cells.Item($i, 1).Offset(-1, 0)
                                        # $dsr.Cells.Item($i, 1).Value2 = $val.Value2
                                }
for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                        $val = $dsr.Cells.Item($i, 4).Offset(-5, 0)
                        $dsr.Cells.Item($i, 4).Value2 = $val.Value2
                        }
                        $dwb.Close($true)
                }
}
                $excel.DisplayAlerts = $false
                $wb.Close($false)
                $excel.Quit()
                [Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        }
}

$files1 = Get-Content "files1.txt"

$files2 = Get-Content "files2.txt"

get-data -startrow 1 -startcol 2 -endrow 4 -endcol 9 -pat 1004 -files1 $files1 -files2 $files2
get-data -startrow 6 -startcol 2 -endrow 8 -endcol 9 -pat 1011 -files1 $files1 -files2 $files2
get-data -startrow 10 -startcol 2 -endrow 11 -endcol 9 -pat 1013 -files1 $files1 -files2 $files2

terse

回复 22# haichuan5121
不把文件路径写入文本，就把文件路径写入数组，然后传函数，这样吗，要不你就用Get-Content提取文件

haichuan5121

回复 21# terse

就是原先那个代码 多文件执行前需要把每个文件路径写到Files1文本内，插入的内容写到Files2里；

怎么实现，我不需要提前写Files1 他就可以自动读取列表执行，
    C:\Users\10000\Desktop\测试p1\元数据.xlsx
    C:\Users\10000\Desktop\测试p1\元数据-副本.xlsx
    C:\Users\10000\Desktop\测试p1\元数据-副本2.xlsx

terse

这样?
$file1 = @(file1,file2,file3)
$file2 = '.\插入内容.xlsx'

haichuan5121

回复 19# terse
大神 再看下；下面的代码能完美实现多个文件的整理；但是需要提前将文件路径写到Files1和Files2里面，每行一个文件路径；
  怎么修改可以实现当前文件夹下直接提取需要执行的文件路径生成Files1，需要插入的文件就是，插入内容.xsls


    <# :
echo on
powershell -noprofile -NoLogo "iex (${%~f0} | out-string)"
pause
#>
function get-data([int]$startrow, [int]$startcol, [int]$endrow, [int]$endcol, [string]$pat, [string[]]$files1, [string[]]$files2) {
        foreach ($file2 in $files2) {
                $excel = New-Object -ComObject Excel.Application
                $excel.Visible = $false
                $wb = $excel.Workbooks.Open($file2)
                $ws = $wb.Sheets.Item(1)
                $sr = $ws.Range($ws.Cells.Item($startrow, $startcol), $ws.Cells.Item($endrow, $endcol))
                $sd = $sr.Value2
                foreach ($file1 in $files1) {
                        $dwb = $excel.Workbooks.Open($file1)
                        $dws = $dwb.Sheets.Item(2)
                        $fc = $dws.UsedRange.Find($pat)
                        if ($fc -ne $null) {
                                $rown = $fc.Row
                                for ($i = 0; $i -le $endrow-$startrow; $i++) {
                                        $dws.Rows.Item($rown + 1).EntireRow.Insert()
                                }
                                $dsr = $dws.Range($dws.Cells.Item($rown + 1, $startcol), $dws.Cells.Item($rown + 1 + ($endrow - $startrow), $endcol))
                                $dsr = $dsr.Resize($sr.Rows.Count, $sr.Columns.Count)
                                $dsr.Value2 = $sd
                                $sr.Copy()
                                $dsr.PasteSpecial(-4122)
                                for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                                        # $val = $dsr.Cells.Item($i, 1).Offset(-1, 0)
                                        # $dsr.Cells.Item($i, 1).Value2 = $val.Value2
                                }
for ($i = 1; $i -le $dsr.Rows.Count; $i++) {
                        $val = $dsr.Cells.Item($i, 4).Offset(-5, 0)
                        $dsr.Cells.Item($i, 4).Value2 = $val.Value2
                        }
                        $dwb.Close($true)
                }
}
                $excel.DisplayAlerts = $false
                $wb.Close($false)
                $excel.Quit()
                [Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        }
}

$files1 = Get-Content "files1.txt"

$files2 = Get-Content "files2.txt"

get-data -startrow 1 -startcol 2 -endrow 4 -endcol 9 -pat 1004 -files1 $files1 -files2 $files2
get-data -startrow 6 -startcol 2 -endrow 8 -endcol 9 -pat 1011 -files1 $files1 -files2 $files2
get-data -startrow 10 -startcol 2 -endrow 11 -endcol 9 -pat 1013 -files1 $files1 -files2 $files2

terse

回复 18# haichuan5121
循环调用函数试试，xls文件问题也许是版本问题， 不知你那边为什么不行，环境不清楚，不好说

haichuan5121

回复 17# terse


    1)同文件夹下数十个文件 怎么能批量插入啊，2)XLS文件怎么不能执行呢