
CODE: [Copy to clipboard][ Last edited by qinchun36 on 2010-3-30 at 16:17 ]Dim xls1, xls2, valMap, fso, excel, wbs, sht1, sht2, i, n, result, resultString
xls1 = "C:\Documents and Settings\issuser\桌面\a.xls"
xls2 = "C:\Documents and Settings\issuser\桌面\b.xls"
result = "不同的地方.txt"
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set valMap = WScript.CreateObject("Scripting.Dictionary")
With valMap
.Add "标准无保留意见", 0
.Add "带强调事项段的无保留意见", 1
.Add "无法表示意见", 2
.Add "保留意见", 3
.Add "否定意见", 4
.Add "未披露审计意见类型", 5
.Add "--", 6
End With
Set excel = WScript.CreateObject("Excel.Application")
Set wbs = excel.Application.Workbooks
wbs.Open xls1
wbs.Open xls2
Set sht1 = wbs.Item(1).Sheets(1)
Set sht2 = wbs.Item(2).Sheets(1)
i = 1
n = 0
resultString = "A" & vbTab & "B" & vbTab & "C" & vbTab & "D"
Do
If a(i, 3) <> b(i, 3) Then
resultString = resultString & vbNewLine & _
a(i, 1) & vbTab & a(i, 2) & vbTab & c(a(i, 3)) & vbTab & c(b(i, 3))
n = n + 1
End If
i = i + 1
Loop Until sht1.Cells(i, 3) = ""
resultString = "总共有 " & n & " 项不同。" & vbNewLine & vbNewLine & resultString
fso.CreateTextFile(result, True).Write resultString
excel.Quit
MsgBox "比较结果保存到了 " & vbCrLf & vbCrLf & fso.GetFile(result).Path, 64, "完成"
Function a(i, j)
a = sht1.Cells(i, j).Value
End Function
Function b(i, j)
b = sht2.Cells(i, j).Value
End Function
Function c(s)
Dim temp
temp = valMap(s)
If temp = "" Then temp = s
c = temp
End Function
| Quote: | |
|
| Quote: | |
|
附件 1:2000-2001.rar (2010-3-30 14:50, 40.73 K,下载次数: 6)
附件 1:excel_sort.GIF (2010-3-30 16:09, 17.75 K)
![]()
| Quote: | |
|
CODE: [Copy to clipboard]将两个xls文件用excel 打开,复制有效内容到2000.txt和2001.txt中。@echo off&setlocal enabledelayedexpansion
for /f "tokens=1-3 delims= " %%a in (2000.txt) do set "%%a%%b=%%c"
for /f "tokens=1-3 delims= " %%d in (2001.txt) do (
if not "!%%d%%e!"=="%%f" (set/a n+=1
call :i !%%d%%e!
set d=!i!
call :i %%f
set f=!i!
echo %%d %%e !d! !f!))>>输出文件.txt
echo 总共有 %n% 不同。>>输出文件.txt
exit
:i
set i=%1
set i=%i:标准无保留意见=0%
set i=%i:带强调事项段的无保留意见=1%
set i=%i:无法表示意见=2%
set i=%i:保留意见=3%
set i=%i:否定意见=4%
set i=%i:未披露审计意见类型=5%
set i=%i:--=6%
CODE: [Copy to clipboard]就可以生成。findstr /x /g:2000.txt 2001.txt >temp.txt
CODE: [Copy to clipboard]如果同时需要按代码陪列顺序,则可以:@echo off&setlocal enabledelayedexpansion
findstr /x /g:2000.txt 2001.txt >temp.txt
for /f "delims=" %%i in (temp.txt) do (set i=%%i
set i=!i:标准无保留意见=0!
set i=!i:带强调事项段的无保留意见=1!
set i=!i:无法表示意见=2!
set i=!i:保留意见=3!
set i=!i:否定意见=4!
set i=!i:未披露审计意见类型=5!
set i=!i:--=6!
echo !i!)>>相同.txt
del /q temp.txt
CODE: [Copy to clipboard]注意,批脚本中出现的连续空格实际是一个制表符。@echo off&setlocal enabledelayedexpansion
findstr /x /g:2000.txt 2001.txt >temp.txt
for /f "tokens=1-3 delims= " %%a in (temp.txt) do (set i=%%c
set i=!i:标准无保留意见=0!
set i=!i:带强调事项段的无保留意见=1!
set i=!i:无法表示意见=2!
set i=!i:保留意见=3!
set i=!i:否定意见=4!
set i=!i:未披露审计意见类型=5!
set i=!i:--=6!
set "hanye_!i!%%a=%%b")
for /f "tokens=1,2 delims==" %%i in ('set^|find /i "hanye_"') do (set i=%%i
echo !i:~7! %%j !i:~6,1!)>>相同.txt
del /q temp.txt
| 欢迎光临 中国DOS联盟论坛 (http://cndos.fam.cx/forum/) | Powered by Discuz! 2.5 |