#include <FileConstants.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ProgressConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <MsgBoxConstants.au3>
#include <WinAPIFiles.au3>
#include <File.au3>
#Region ### START Koda GUI section ### Form=F:\Au3Project\Excel文件批量合并器.kxf
Opt("GUIOnEventMode", 1) ;改变成 OnEvent 事件模式
Local $FormMain = GUICreate("Excel文件批量合并器", 333, 280, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton")
$Progress1 = GUICtrlCreateProgress(88, 152, 241, 17)
Local $chBox = GUICtrlCreateCheckbox("是否含有表头", 24, 24, 137, 25)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
GUICtrlSetOnEvent($chBox, "chBox")
Local $btnCls = GUICtrlCreateButton("重 置", 48, 216, 73, 25)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
GUICtrlSetOnEvent($btnCls, "btnCls")
Local $btnRun = GUICtrlCreateButton("运 行", 192, 216, 73, 25)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
GUICtrlSetOnEvent($btnRun, "btnRun")
$Label1 = GUICtrlCreateLabel("进度:", 24, 152, 52, 23)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
$Label2 = GUICtrlCreateLabel("第几张工作表?", 24, 96, 128, 23)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
$InputWorkSheet = GUICtrlCreateInput("", 168, 96, 121, 27, BitOR($GUI_SS_DEFAULT_INPUT, $ES_NUMBER))
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
$Input1 = GUICtrlCreateInput("", 168, 24, 121, 27, BitOR($GUI_SS_DEFAULT_INPUT, $ES_NUMBER))
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
GUICtrlSetState(-1, $GUI_HIDE)
$Labprogress = GUICtrlCreateLabel("正在处理：第1/m个文件", 64, 184, 205, 23)
GUICtrlSetFont(-1, 14, 400, 0, "宋体")
GUICtrlSetState(-1, $GUI_HIDE)
GUISetState(@SW_SHOW, $FormMain)
#EndRegion ### END Koda GUI section ###
While 1
Sleep(100)
WEnd
Func CLOSEButton() ;点击关闭按钮
Exit
EndFunc ;==>CLOSEButton
Func btnCls() ;点击重置按钮
GUICtrlSetState($chBox, $GUI_UNCHECKED)
GUICtrlSetState($Input1, $GUI_HIDE)
GUICtrlSetData($Input1, "")
GUICtrlSetData($InputWorkSheet, "")
EndFunc ;==>btnCls
Func chBox() ;点击选择框
If _IsChecked($chBox) Then
GUICtrlSetState($Input1, $GUI_SHOW)
Else
GUICtrlSetState($Input1, $GUI_HIDE)
EndIf
EndFunc ;==>chBox
Func _IsChecked($idControlID) ;是否选中复选框
Return BitAND(GUICtrlRead($idControlID), $GUI_CHECKED) = $GUI_CHECKED
EndFunc ;==>_IsChecked
Func btnRun() ;点击运行按钮
If _IsChecked($chBox) Then
$biaotou = Number(GUICtrlRead($Input1))
Else
$biaotou = 0
EndIf
$j = Number(GUICtrlRead($InputWorkSheet)) ;第几张工作表
If $j = 0 Then
MsgBox(0, "参数错误", "输入的参数错误，请检查！")
ElseIf $j > 0 And $biaotou >= 0 Then
$filePath = FileOpenDialog("请选择要处理的EXCEL文件", @WorkingDir & "", "Excel文件 (*.xls;*.xlsx)", $FD_FILEMUSTEXIST + $FD_MULTISELECT)
If @error Then
MsgBox(0, "文件选择错误", "未选择文件")
Else
$path = _dirFilePath($filePath)
Local Const $sFileLogPath = _WinAPI_GetTempFileName(@TempDir) ;获取系统临时文件目录
Local $hFileLogOpen = FileOpen($sFileLogPath, $FO_CREATEPATH + $FO_OVERWRITE) ;目录不存在则创建，覆盖模式
Global $oExcelApp = ObjCreate("Excel.Application") ;创建Excel对象
$oExcelApp.DisplayAlerts = False
$oExcelApp.visible = True
$oExcelApp.visible = False
$oFinalWorkBook = $oExcelApp.Workbooks.add()
$k = 1 ;汇总工作表行数计数器
GUICtrlSetData($Progress1, 0) ;初始化进度条
GUICtrlSetData($Labprogress, "")
GUICtrlSetState($Labprogress, $GUI_SHOW) ;初始化不显示进度文字
For $i = 1 To $path[0]
$oExcelWorkBook = $oExcelApp.Workbooks.Open($path[$i])
If $j <= $oExcelWorkBook.Sheets.count Then ;输入的工作表所在位置未超出范围
$excelRows = $oExcelWorkBook.Sheets($j).UsedRange.Rows.Count ;获得工作簿中要处理的工作表行数
If $excelRows > $biaotou Then ;行数大于表头
If $biaotou > 0 And $i = 1 Then ;有表头且是第1个文件时将表头一起复制到汇总文件
;将第1行 到 $excelRows行 复制到新的工作表
$oExcelWorkBook.Sheets($j).Rows(1 & ":" & $excelRows).Copy($oFinalWorkBook.Sheets(1).Rows($k))
$k = $k + $excelRows
$oExcelWorkBook.close
GUICtrlSetData($Progress1, $i / $path[0] * 100)
GUICtrlSetData($Labprogress, "正在处理：第" & $i & "/" & $path[0] & "个文件")
FileWriteLine($hFileLogOpen, "已复制汇总文件 " & $path[$i] & " 第1:" & $excelRows & "行")
Else
;将$biaotou+1行 到 $excelRows行 复制到新的工作表
$oExcelWorkBook.Sheets($j).Rows($biaotou + 1 & ":" & $excelRows).Copy($oFinalWorkBook.Sheets(1).Rows($k))
$k = $k + $excelRows - $biaotou
$oExcelWorkBook.close
GUICtrlSetData($Progress1, $i / $path[0] * 100)
GUICtrlSetData($Labprogress, "正在处理：第" & $i & "/" & $path[0] & "个文件")
FileWriteLine($hFileLogOpen, "已复制汇总文件 " & $path[$i] & " 第" & $biaotou + 1 & ":" & $excelRows & "行")
EndIf
Else
FileWriteLine($hFileLogOpen, "复制汇总文件 " & $path[$i] & "失败")
EndIf
Else
MsgBox(0, "", "在文件" & $path[$i] & "中第" & $j & "张工作表不存在")
FileWriteLine($hFileLogOpen, "复制汇总文件 " & $path[$i] & "失败")
EndIf
Next
GUICtrlSetData($Labprogress, "所有文件已汇总完成")
$savePath = FileSaveDialog("保存汇总EXCEL文件", @WorkingDir & "", "Excel文件 (*.xls;*.xlsx)", $FD_PATHMUSTEXIST + $FD_PROMPTOVERWRITE)
If $savePath = "" Then
GUICtrlSetData($Labprogress, "汇总文件未保存")
Else
$oFinalWorkBook.saveas($savePath)
GUICtrlSetData($Labprogress, "汇总文件已保存")
FileMove($sFileLogPath, _dirLogPath($savePath))
EndIf
FileClose($hFileLogOpen)
$oFinalWorkBook.close
$oExcelApp.quit
EndIf
EndIf
EndFunc ;==>btnRun
Func _dirFilePath($strFilePath) ;返回选择的文件数量和多个文件的路径
$str_long = StringLen($strFilePath) ;字符串长度
$f_instr = StringInStr($strFilePath, "|") ;获取第1个"|"在字符串中出现的位置
$fileDir = StringLeft($strFilePath, $f_instr - 1) ;截取文件所在目录位置
$fileNames = StringRight($strFilePath, $str_long - $f_instr) ;截取多个文件的名字
$fileName = StringSplit($fileNames, "|")
If $fileName[0] > 1 Then
For $i = 1 To $fileName[0]
$fileName[$i] = $fileDir & "" & $fileName[$i]
Next
EndIf
Return $fileName
EndFunc ;==>_dirFilePath
Func _dirLogPath($strFilePath)
$str_long = StringLen($strFilePath) ;字符串长度
$f_instr = StringInStr($strFilePath, "", -1) ;获取最后1个""在字符串中出现的位置
$f_dian = StringInStr($strFilePath, ".", -1) ;获取"."在字符串中出现的位置
$fileDir = StringLeft($strFilePath, $f_instr) ;截取目录位置
$logName = StringMid($strFilePath, $f_instr + 1, $f_dian - $f_instr - 1)
$logName = $fileDir & $logName & "_日志文件.txt"
Return $logName
EndFunc ;==>_dirLogPath
|手机版|小黑屋|AUTOIT CN
( 鲁ICP备19019924号-1 )谷歌
百度
发表于 2016-7-10 14:14:15
发表于 2016-7-11 14:24:26