[文件操作] Excel文本处理

@echo off
rem 保存为ansi编码
cd /d "%~dp0"
for /f "useback tokens=1* delims=," %%a in ("b.csv") do (
	set _%%a=%%b
)
(
	echo 城市,团队,小队,分队,奖品,省份,描述
	for /f "useback skip=1 delims=" %%a in ("a.csv") do (
		for /f "delims=," %%b in ("%%a") do (
			(for /f "tokens=1* delims=_=" %%c in ('set _ ^|find "%%b"') do (
				for %%e in (%%d) do (
					set str=%%e
				)
				call echo %%a,%%c,%%str%%
			)) || echo %%a
		)
	)
) > "c.csv"
pause
复制代码

@echo off & (
echo,城市	团队        小队        分队        奖品	省份	描述
for /f "skip=1 tokens=1* delims= " %%a in (a.txt) do (
	for /f "tokens=1-3 delims= " %%x in ('findstr /ic:"%%a" b.txt') do echo,%%a	%%b	%%x	%%z
))>c.txt
pause&exit/b
复制代码

Set fso=WScript.CreateObject("Scripting.Filesystemobject")
Set fd=fso.GetFolder(".")

Set oExcle = CreateObject("Excel.Application")
Set Axl = oExcle.Workbooks.Open(fd&"\A.xlsx")
Set Bxl = oExcle.Workbooks.Open(fd&"\B.xlsx")
Set Cxl = oExcle.Workbooks.Add()

Axl.Worksheets(1).Copy Cxl.Worksheets(Cxl.Worksheets.Count)
Cxl.Sheets(1).Name = "A"
Bxl.Worksheets(1).Copy Cxl.Worksheets(Cxl.Worksheets.Count)
Cxl.Sheets(2).Name = "B"

Cxl.Sheets("A").UsedRange.Copy
Cxl.Sheets("sheet1").range("A1").PasteSpecial
lastRow = Cxl.Sheets("A").Cells(Cxl.Sheets("A").Rows.Count, 1).End(-4162).Row
Cxl.Sheets("B").UsedRange.Copy
Cxl.Sheets("sheet1").range("F1").PasteSpecial
Cxl.Sheets("sheet1").columns("G").delete
Cxl.Sheets("sheet1").Range("F2").Formula2 = "=INDEX(b!A:A,MATCH(TRUE,ISNUMBER(FIND(A2,b!B:B)),0))"
Cxl.Sheets("sheet1").Range("F2:F"&lastRow).FillDown
Cxl.Sheets("sheet1").Range("G2").Formula2 = "=INDEX(b!C:C,MATCH(F2,b!A:A,0))"
Cxl.Sheets("sheet1").Range("G2:G"&lastRow).FillDown
Cxl.Sheets("sheet1").Activate
Cxl.Sheets("sheet1").Name = "汇总结果"
Cxl.SaveAs fd&"\C.xlsx"
Cxl.Close

oExcle.Quit
MsgBox "工作表汇总完毕!"
复制代码