分类 办公软件 下的文章

补料申请单需要手动选生产订单号和行号，效率低，操作不方便，特写这个小工具，批量填充，曲线救国。
捕获.PNG

;**** 参数创建于 AutoIt3Wrapper_GUI ****
; *** Start added by AutoIt3Wrapper ***
#include <WinAPIConstants.au3>
; *** End added by AutoIt3Wrapper ***
#Region
#AutoIt3Wrapper_Icon=msra.ico
#AutoIt3Wrapper_Outfile_x64=U8补料申请单批量处理.exe
#AutoIt3Wrapper_UseUpx=y
#AutoIt3Wrapper_Res_Comment=U8补料申请单批量处理
#AutoIt3Wrapper_Res_Description=U8补料申请单批量处理
#AutoIt3Wrapper_Res_Fileversion=3.2.0.0
#AutoIt3Wrapper_Res_ProductName=U8补料申请单批量处理
#AutoIt3Wrapper_Res_ProductVersion=2.0
#AutoIt3Wrapper_Res_LegalCopyright=HSIAOYANG CHEN
#AutoIt3Wrapper_Res_SaveSource=y
#AutoIt3Wrapper_Res_Language=2052
#AutoIt3Wrapper_Res_requestedExecutionLevel=None
#EndRegion


#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <AutoItConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>

#include <Misc.au3>
If _Singleton("SHOYO", 1) = 0 Then ;禁止多个程序同时运行
    ;MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running")
    Exit
EndIf

main()

;将设置的参数从注册表读取出来使用
Func init()

    Global $pn, $pny, $num, $origx, $origy, $high, $wide, $line, $stop
    RegEnumVal("HKEY_CURRENT_USER\SOFTWARE\SHOYO", 1)
    If @error = 0 Then
        GUICtrlSetData($idFile1, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "pn"))
        GUICtrlSetData($idFile2, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "pny"))
        GUICtrlSetData($idFile3, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "num"))
        GUICtrlSetData($idFile4, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "origx"))
        GUICtrlSetData($idFile5, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "high"))
        GUICtrlSetData($idFile6, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "origy"))
        GUICtrlSetData($idFile7, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "wide"))
        GUICtrlSetData($idFile8, RegRead("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "line"))
    EndIf

EndFunc   ;==>init

;鼠标操作核心算法
Func start()
    Local $hWnd = WinGetHandle("[CLASS:ThunderRT6FormDC]")  ; 获取U8窗口句柄

    If $hWnd Then

        ProcessSetPriority("EnterprisePortal.exe", $PROCESS_ABOVENORMAL);提高U8响应优先级

        ;Mod($num,10)  一组10次，求余数
        ;Int($num/10)  一组10次，求操作趟数

        BlockInput(1) ;禁止外设输入
        ;MouseMove($origx , $origy,100)
        ;MouseClick ( "left" , $origx , $origy, 1, 50 )
        ;Opt("MouseClickDelay", 500) ;10 milliseconds
        $tmp = $num ;tmp临时变量，作为$num和$i的储存
        GUICtrlSetColor($idFile8l, 0x0000FF)
        GUICtrlSetColor($idFile3l, 0x00FF00)
        GUICtrlSetColor($idBtn,0xFF0000)
        GUICtrlSetData($idFile3l, "剩余行数：")
        GUICtrlSetData($idBtn, "进行中...")
        GUICtrlSetData($idFile8l, "执行次数：")
        For $j = 0 To Int($num / 10)
            For $i = 0 To ($j = Int($num / 10) ? Mod($num, 10) - 1 : 9)
                MouseClick("left", $origx, $origy + $i * $high, 1, 30)
                Send($pn)
                MouseClick("left", $origx + $wide, $origy + $i * $high, 1, 30)
                Send($pny)
                $line += 1
                GUICtrlSetData($idFile8, $line)
                $tmp -= 1
                GUICtrlSetData($idFile3, $tmp)
                Sleep(10) ;休眠，暂停线程，给主程序处理esc事件提供时间
                If $stop = 1 Then
                    $tmp = $i
                    $i = ($j = Int($num / 10) ? Mod($num, 10) - 1 : 9)

                EndIf
            Next
            If $stop = 1 Then
                $i = $tmp + 1
            EndIf
            If Not ($j* 10 = $num) Then
                MouseClick("left", $origx - $wide / 2, $origy + ($i-1) * $high, 1, 30)
            EndIf

            Sleep(10) ;休眠，暂停线程，给主程序处理esc事件提供时间

            If ($tmp > 0) And ($stop = 0) Then

                Sleep(500 + 22000 * ($line / 1000)) ;数据行数越多，系统延迟越大，加长等待时间

                For $i = 0 To ((($j = Int($num / 10)) Or (($j+1) * 10 = $num)) ? -1 : 9) ;最尾页的处理：最后一页不进行翻页，整数页也不翻页处理。
                    MouseWheel($MOUSE_WHEEL_DOWN, 1)
                    Sleep(500 + 250 * ($line / 1000))
                Next

            EndIf
            If $stop = 1 Then
                $j = Int($num / 10)
            EndIf
        Next
        GUICtrlSetColor($idFile8l, 0x000000)
        GUICtrlSetData($idFile3l, "填充行数：")
        GUICtrlSetColor($idFile3l, 0x000000)
        GUICtrlSetData($idBtn,"开始填充")
        GUICtrlSetColor($idBtn,0x000000)
        BlockInput(0) ;允许外设输入
        ProcessSetPriority("EnterprisePortal.exe", $PROCESS_NORMAL);恢复默认U8响应优先级
    Else
        MsgBox($MB_ICONERROR, "", "未找到U8补料申请单窗口！", 10)
    EndIf

EndFunc   ;==>start

;绘制图形窗口
Func main()

    GUICreate("U8补料申请单批量处理", 320, 200, @DesktopWidth / 2 + 200, @DesktopHeight / 2 - 100, -1, BitOR($WS_EX_ACCEPTFILES, $WS_EX_TOPMOST))
    GUISetIcon(@SystemDir & "\System32\msra.exe", 0)
    GUICtrlCreateLabel("生产订单号：", 10, 15, 100)     ;x=10,y=15,长度100
    Global $idFile1 = GUICtrlCreateInput("2024040003", 100, 10, 200, 20)     ;x=100,y=10,长度200,高度20
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    GUICtrlCreateLabel("生产订单行号：", 10, 15 + 30, 90)
    Global $idFile2 = GUICtrlCreateInput("1", 100, 40, 50, 20, $ES_NUMBER)     ;限制数字类型
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 4, 1)      ;限制字符最大长度4,最小1,
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    Global $idFile3l = GUICtrlCreateLabel("填充行数：", 180, 15 + 30, 70)
    Global $idFile3 = GUICtrlCreateInput("10", 250, 40, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 3, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    GUICtrlCreateLabel("原点坐标X：", 10, 15 + 30 + 30, 90)
    Global $idFile4 = GUICtrlCreateInput("500", 100, 70, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 4, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    GUICtrlCreateLabel("行高：", 180, 15 + 30 + 30, 70)
    Global $idFile5 = GUICtrlCreateInput("25", 250, 70, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 2, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    GUICtrlCreateLabel("原点坐标Y：", 10, 15 + 30 + 30 + 30, 90)
    Global $idFile6 = GUICtrlCreateInput("320", 100, 100, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 4, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    GUICtrlCreateLabel("行宽：", 180, 15 + 30 + 30 + 30, 70)
    Global $idFile7 = GUICtrlCreateInput("100", 250, 100, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 3, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)
    Global $idBtn = GUICtrlCreateButton("开始填充", 110, 140, 100, 40)
    ;GUICtrlSetFont(-1,16)
    GUICtrlCreateLabel("X/Y轴坐标：", 10, 15 + 30 + 30 + 30 + 30, 90)
    ;GUICtrlSetFont(-1,13)
    Global $Label1 = GUICtrlCreateLabel("0/0", 10, 15 + 30 + 30 + 30 + 30 + 20, 90)
    GUICtrlSetFont($Label1, 12)

    Global $idFile8l = GUICtrlCreateLabel("执行次数：", 245, 15 + 30 + 30 + 30 + 30, 100)
    Global $idFile8 = GUICtrlCreateInput("1", 250, 15 + 30 + 30 + 30 + 30 + 20, 50, 20, $ES_NUMBER)
    GUICtrlSetStyle(-1, $SS_RIGHT)
    GUICtrlSetLimit(-1, 4, 1)
    GUICtrlSetState(-1, $GUI_DROPACCEPTED)

    GUISetState(@SW_SHOW)

    init()

    HotKeySet("{Esc}", "_Stop")
    Global $_whand = DllCallbackRegister("_Mouse_Events_Handler", "int", "int;ptr;ptr")
    Local $_whmod = _WinAPI_GetModuleHandle(0)
    Global $_whook = _WinAPI_SetWindowsHookEx($WH_MOUSE_LL, DllCallbackGetPtr($_whand), $_whmod)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "pn", "REG_SZ", GUICtrlRead($idFile1))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "pny", "REG_SZ", GUICtrlRead($idFile2))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "num", "REG_SZ", GUICtrlRead($idFile3))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "origx", "REG_SZ", GUICtrlRead($idFile4))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "high", "REG_SZ", GUICtrlRead($idFile5))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "origy", "REG_SZ", GUICtrlRead($idFile6))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "wide", "REG_SZ", GUICtrlRead($idFile7))
                RegWrite("HKEY_CURRENT_USER\SOFTWARE\SHOYO", "line", "REG_SZ", GUICtrlRead($idFile8))
                Sleep(100)
                ExitLoop
            Case $idBtn
                $pn = GUICtrlRead($idFile1)
                $pny = GUICtrlRead($idFile2)
                $num = GUICtrlRead($idFile3)
                $origx = GUICtrlRead($idFile4)
                $high = GUICtrlRead($idFile5)
                $origy = GUICtrlRead($idFile6)
                $wide = GUICtrlRead($idFile7)
                $line = GUICtrlRead($idFile8)
                $stop = 0
                If MsgBox($MB_YESNO, "🐇执行信息核对", "生产订单号：" & $pn & @CRLF & "生产订单行号：" & $pny & @CRLF & "填充行数：" & $num & @CRLF & "原点坐标X/Y：" & $origx & "/" & $origy & @CRLF & "行高/行宽：" & $high & "/" & $wide & @CRLF & @CRLF & "按ESC键，可中断运行!" & @CRLF) < 7 Then
                    start()
                EndIf

        EndSwitch
    WEnd

EndFunc   ;==>main

Func _Mouse_Events_Handler($wnCode, $wwParam, $wlParam)
    Switch $wwParam
        Case $WM_MOUSEMOVE         ;
            Local $aPos = MouseGetPos()
            Local $iX = $aPos[0]
            Local $iY = $aPos[1]

            GUICtrlSetData($Label1, $iX & '/' & $iY & @CRLF)
            ;ConsoleWrite($iX & '/' & $iY & @CRLF)
    EndSwitch
EndFunc   ;==>_Mouse_Events_Handler


Func _Stop()
    $stop = 1
EndFunc   ;==>_Stop

下载地址：U8补料申请单批量处理.zip

如果有很多= A1 + B1格式的文本字符串，则需要将这些文本字符串转换为真实公式并在工作表中计算它们的值，不幸的是，没有直接的方法可以在Excel中解决。 但是，在这里我可以为您介绍一些有趣的技巧。
以下简短的VBA代码可以帮助您解决将文本转换为公式的问题，具体步骤如下：
1.按住 ALT + F11 键，然后打开 Microsoft Visual Basic for Applications窗口.
2.点击 插页 > 模块，然后将以下代码粘贴到 模块窗口.
VBA代码：将文本字符串转换为公式

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

3.保存此代码并返回到您的工作表，输入此公式 = Eval（C1） 放入空白单元格（C1 包含要转换为公式的文本字符串单元格），请参见屏幕截图：
doc-convert-text--to-formula1.png
4.然后按 输入 键，然后选择单元格D1，将填充手柄拖到要应用此公式的范围。 看截图：
doc-convert-text--to-formula2.png

范例字符串转公式.xlsm

转载地址：https://zh-cn.extendoffice.com/documents/excel/1683-excel-convert-text-string-to-formula.html

这个一个简单的EXCEL透视表处理工具，批量将透视表数据转换成完整的记录行，方便数据的二次使用。

微信图片_20220819162216.png

基于vba宏编写，打开运行，需要启用宏，才能正常工作。

微信图片_20220819164549.png

处理代码如下：

Sub copy_Click()
'
' copy_Click 宏
'
' 快捷键: Ctrl+r
'
'定义 起始和结束单元变量
Dim start_range, end_range As String

'对起始和结束单元单元格进行赋值，取A4和B4的值
start_range = Range("A4").Value
end_range = Range("B4").Value

'定义 4个行列偏移量，相对于A1单元
Dim c1, r1, c2, r2 As Long
c1 = Range(start_range).Column - 1
r1 = Range(start_range).Row - 1
c2 = Range(end_range).Column - 1
r2 = Range(end_range).Row - 1

'行循环，从起始行到结束行
For i = r1 To r2

    '列循环，从起始列到结束列
    For j = c1 To c2
    
         '当本行单元格内容为空，且上行单元格不为空时，将上行单元格赋值到本行单元格
         If Range("A1").Offset(i, j) = "" And Range("A1").Offset(i - 1, j) <> "" Then
         
        '    Range("A1").Offset(i, j).Select
            Range("A1").Offset(i, j) = Range("A1").Offset(i - 1, j)
         End If
         
    '相当于 j++
    Next j
    
'相当于 i++
Next i

'操作完成输出提示，对话框
MsgBox ("已整理完毕！")

End Sub

范例下载