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

haichuan5121

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

haichuan5121

[img][/img]

Batcher

回复 1# haichuan5121


如果需要上传文件，可以用阿里云盘或百度网盘。

如果需要上传截图，可以找个图床，例如：
http://bbs.bathome.net/thread-60985-1-1.html

mzzyb

cmd不能实现打开和操作Excel文件吧~~只能用vba实现自动化~~~

haichuan5121

回复 3# Batcher


    已上附件

czjt1234

回复 5# haichuan5121


    如果你愿意付费，可以加我QQ

haichuan5121

有大神吗？顶一顶

haichuan5121

本帖最后由 haichuan5121 于 2023-5-12 16:11 编辑


有大神吗？顶一顶

TAT

用VBA就可以

terse

用PS吧 应该可以的

terse

运行前 请备份文件

<# :
@echo off
powershell -noprofile -NoLogo "iex (${%~f0} | out-string)"
pause$exit 
#>
function get-data([int]$startrow, [int]$startcol, [int]$endrow, [int]$endcol, [string]$pat, [string]$file1, [string]$file2) {
	$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
	$dwb = $excel.Workbooks.Open($file1)
	$dws = $dwb.Sheets.Item(1)
	$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
		}
	}
	$excel.DisplayAlerts = $false
	$wb.Close($false)
	$dwb.Close($true)
	$excel.Quit()
}
$path=$PWD.Path
$file1 = "$path\元数据.xlsx"
$file2 = "$path\插入内容.xlsx"
get-data -startrow 1 -startcol 2 -endrow 4 -endcol 9  -pat 1009 -file1 $file1 -file2 $file2
get-data -startrow 6 -startcol 2 -endrow 8 -endcol 9  -pat 1018 -file1 $file1 -file2 $file2
get-data -startrow 10 -startcol 2 -endrow 11 -endcol 9  -pat 1023 -file1 $file1 -file2 $file2
复制代码

加一个，换为复制对象

<# :
@echo off
powershell -noprofile -NoLogo "iex (${%~f0} | out-string)"
pause$exit 
#>
function get-data([int]$startrow, [int]$startcol, [int]$endrow, [int]$endcol, [string]$pat, [string]$file1, [string]$file2) {
	$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))
	$dwb = $excel.Workbooks.Open($file1)
	$dws = $dwb.Sheets.Item(1)
	$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)
		$sr.Copy()
		$dsr.PasteSpecial(-4104)
		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
		}
	}
	$excel.DisplayAlerts = $false
	$wb.Close($false)
	$dwb.Close($true)
	[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
}
$path=$PWD.Path
$file1 = "$path\元数据.xlsx"
$file2 = "$path\插入内容.xlsx"
get-data -startrow 1 -startcol 2 -endrow 4 -endcol 9  -pat 1009 -file1 $file1 -file2 $file2
get-data -startrow 6 -startcol 2 -endrow 8 -endcol 9  -pat 1018 -file1 $file1 -file2 $file2
get-data -startrow 10 -startcol 2 -endrow 11 -endcol 9  -pat 1023 -file1 $file1 -file2 $file2
复制代码

haichuan5121

@echo off
setlocal enabledelayedexpansion

set "file1=excel1.xlsx"
set "file2=excel2.xlsx"
set "file3=excel3.xlsx"

for %%f in ("%file1%" "%file2%" "%file3%") do (
    echo Processing "%%~nxf"...
    set "foundA=false"
    set "foundB=false"
    for /f "tokens=1,4 delims=," %%a in ('type "%%~f" ^| findstr /r /c:",[ABCD]$"') do (
        if "%%b"=="A" (
            set "foundA=true"
            set "insertRow=%%a"
        ) else if "%%b"=="B" (
            set "foundB=true"
            set "replaceCell=%%a"
        )
    )
    if !foundA! (
        echo Inserting rows in "%%~nxf"...
        powershell -Command "$excel = New-Object -ComObject Excel.Application; $workbook = $excel.Workbooks.Open('%%~ff'); $worksheet = $workbook.Worksheets.Item(1); $row = $worksheet.Cells.Item($insertRow).EntireRow; $row.Insert(); $worksheet.Cells.Item($insertRow+1,1).Value = 'New row 1'; $worksheet.Cells.Item($insertRow+2,1).Value = 'New row 2'; $worksheet.Cells.Item($insertRow+3,1).Value = 'New row 3'; $workbook.Save(); $workbook.Close(); $excel.Quit();"
    )
    if !foundB! (
        echo Replacing cell in "%%~nxf"...
        powershell -Command "$excel = New-Object -ComObject Excel.Application; $workbook = $excel.Workbooks.Open('%%~ff'); $worksheet = $workbook.Worksheets.Item(1); $cell = $worksheet.Cells.Item(!replaceCell!); $cell.Value = 'New value'; $workbook.Save(); $workbook.Close(); $excel.Quit();"
    )
)
这个怎么改

terse

$insertRow !insertRow!

haichuan5121

回复 12# haichuan5121
。另外批次文件插入怎么修改呀！现单文件可以修改；如果EXCEL 里面俩Sheet的话，怎么插入到第二个Sheet
是不是这个代码对XLS 文件不适用啊 怎么修改能兼容XLS文件的；如果不想替换4）填充后B列空白位置填充为上一行数据 这个功能怎么修改啊

haichuan5121

回复 11# terse


   大神怎么批量处理EXCEL；咱代码能单个插入，另外如果EXCEL是多Sheet组成怎么插到第二个sheet
$dws = $dwb.Sheets.Item(1)改成$dws = $dwb.Sheets.Item(2)？  是这样的 可以了。。。


第 1 行的 <# 和第 20 行的 #> 是注释的开始和结束标记，表示这段代码是一个注释块。在代码执行时，这个注释块会被忽略掉。这种写法可以让这段代码既可以作为注释，又可以作为 PowerShell 脚本来执行。

第 2 行的 @echo off 表示关闭命令行窗口的回显功能。

第 3 行的 powershell -noprofile -NoLogo "iex (${%~f0} | out-string)" 表示在 PowerShell 中执行代码。其中，-noprofile 表示不加载 PowerShell 的配置文件；-NoLogo 表示不显示 PowerShell 的标志；${%~f0} 表示获取当前脚本的完整路径；out-string 表示将输出转换为字符串；iex 表示执行字符串作为 PowerShell 脚本。

第 4 行的 pause 表示在代码执行完毕后暂停，等待用户按下任意键继续。

第 5 行的 $exit 表示退出 PowerShell。

第 7~28 行是一个名为 get-data 的函数，用来查找指定内容并插入数据。该函数接受 7 个参数：$startrow、$startcol、$endrow、$endcol、$pat、$file1 和 $file2。其中，$startrow、$startcol、$endrow 和 $endcol 表示要查找的单元格范围；$pat 表示要查找的内容；$file1 表示要插入数据的 Excel 文件；$file2 表示要查找内容的 Excel 文件。

第 9~11 行创建一个 Excel 应用程序对象，并将其设置为不可见。

第 12~13 行打开要查找内容的 Excel 文件，并获取第一个工作表对象。

第 14 行使用 Range 方法获取指定的单元格范围。

第 15~16 行打开要插入数据的 Excel 文件，并获取第一个工作表对象。

第 17~18 行使用 UsedRange 属性获取已使用的单元格范围，并在其中查找指定内容。

第 19~27 行根据查找到的单元格位置，插入指定行数的数据。

第 29~32 行设置 Excel 的一些选项，包括关闭警告对话框和关闭工作簿时不保存更改。

第 33~34 表示释放 Com 对象，以释放 Excel 应用程序的资源。

第 36~38 表示调用 get-data 函数三次，分别查找不同的单元格范围，并在另一个 Excel 文件中插入指定行数的数据。

需要注意的是，这段代码使用了 PowerShell 的 COM 对象来操作 Excel 文件，因此需要在运行之前确保计算机上已安装 Microsoft Office。此外，如果你的计算机上安装的是 64 位的 Office，则需要使用 64 位的 PowerShell 来运行此脚本。