EXCEL自动分级显示v1.0
;Excel自动分级显示v1.0
;热键win+j
;作者：sixtyone 576642385
;2020.4.29
#SingleInstance Force
#NoEnv
SetWorkingDir %A_ScriptDir%
Process, Priority,, High
Menu, Tray, NoStandard
Menu, tray, Add,关于,About
Menu, tray, add, 退出,fjexit
Menu, Tray, Default,关于
Gui,Tj: Destroy
Gui,Tj: Add, ActiveX, x5 y5 w800 h500 vtjwb, Shell.Explorer
SetTimer,Tjzxrs,-100
#IfWinActive,ahk_exe excel.exe
#j::
CStart=0
excel :=Excel_Get22()
gui,Destroy
Gui Add, Radio, x15 y5 w140 h23 Checked vxinghao, 按*号分级显示
Gui Add, Radio, x15 y40 w140 h23 vxuhao, 按序号分级显示
Gui Add, Radio, x15 y75 w140 h23 vfeikong, 按非空单元格分1级
Gui Add, Radio, x15 y110 w140 h23 vqita, 其他正则1级
Gui Add, Edit, x15 y145 w140 h23 vfjedit,[\x{4e00}-\x{9fa5}]
Gui Add, Button, x160 y15 w90 h23 gfjcancle ,取消
Gui Add, Button, x160 y75 w90 h23 gfjclear ,清除分级显示
Gui Add, Button, x160 y135 w90 h23 gfjact ,刷新分级显示
Gui +AlwaysOnTop -MinimizeBox -MaximizeBox +ToolWindow
Gui Show,,Excel自动分级显示v1.0
Return
fjcancle:
excel=
Gui,Destroy
return
fjclear:
Excel.Rows.ClearOutline
return
About:
关于=
(
Excel自动分级显示v1.0
热键: win + j
by sixtyone 2020.4.29
)
MsgBox,%关于%
return
fjact:
Level=1
gui,Submit,NoHide
Fjmode :=xinghao ? "xinghao"
: xuhao ? "xuhao"
: feikong ? "feikong"
: qita ? "qita"
:""
if RegExMatch(Excel.selection.address,":\$[0-9]+")
{
Gui +OwnDialogs
MsgBox,不能选择整行区域,请选择需分级的列...
return
}
if RegExMatch(Excel.selection.address,"(?<![A-Z0-9])\$[A-Z0-9]+:\$[A-Z0-9]+")
{
ExLastCellAddress :=xlFindLastCell22(Excel)
if !ExLastCellAddress
return
NewSelection :=Excel.Intersect(Excel.selection,Excel.Range("$A$1:"ExLastCellAddress))
CStart=1
}
else
{
NewSelection :=Excel.Intersect(Excel.selection,Excel.selection)
}
excel.ActiveSheet.Outline.SummaryRow :=0
if CStart=1
{
Gui +OwnDialogs
InputBox,rStart,请输入开始行,需输入开始行
rStart--
rEnd :=Excel.Range(ExLastCellAddress).row
CStart=0
}
else
{
rStart :=NewSelection.Rows(1).Row - 1
rEnd :=NewSelection.Rows(NewSelection.Rows.Count).Row
}
CC :=NewSelection.column
NewSelection.Rows.ClearOutline
;Excel.Rows.ClearOutline
excel.ScreenUpdating := 0
if Fjmode=xinghao
{
try{
loop,% rEnd - rStart
{
If RegExMatch(excel.Cells(rStart + A_Index, CC).text,"\*+")
{
ji:= StrSplit(excel.Cells(rStart + A_Index, CC).text, "*")
Level := ji.MaxIndex()
if level=2
Excel.Rows(rStart + A_Index + 1).OutlineLevel := 1
else
Excel.Rows(rStart + A_Index).OutlineLevel := Level -1
continue
}
else
{
Excel.Rows(rStart + A_Index ).OutlineLevel := Level
}
Sleep,20
}
}
catch{
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,出错了!也许是选区或模式的问题!
return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,分级显示完成!
return
}
if Fjmode=xuhao
{
try{
loop,% rEnd - rStart
{
If RegExMatch(excel.Cells(rStart + A_Index, CC).text,"\d{1,2}\.{0,1}")
{
ji:= StrSplit(excel.Cells(rStart + A_Index, CC).text, ".")
Level := ji.MaxIndex()
if level=1
Excel.Rows(rStart + A_Index + 1).OutlineLevel := 1
else
Excel.Rows(rStart + A_Index).OutlineLevel := Level
continue
}
else
{
Excel.Rows(rStart + A_Index ).OutlineLevel := Level + 1
}
Sleep,20
}
}
catch{
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,出错了!也许是选区或模式的问题!
return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,分级显示完成!
return
}
if Fjmode=feikong
{
try{
loop,% rEnd - rStart
{
If !RegExMatch(excel.Cells(rStart + A_Index, CC).text,"^$")
{
Excel.Rows(rStart + A_Index + 1).OutlineLevel := 1
continue
}
else
{
Excel.Rows(rStart + A_Index ).OutlineLevel := 2
}
Sleep,20
}
}
catch{
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,出错了!也许是选区或模式的问题!
return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,分级显示完成!
return
}
if Fjmode=qita
{
if !fjedit
{
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,表达式为空,请重新输入!!
return
}
try{
loop,% rEnd - rStart
{
If RegExMatch(excel.Cells(rStart + A_Index, CC).text,fjedit)
{
Excel.Rows(rStart + A_Index + 1).OutlineLevel := 1
continue
}
else
{
Excel.Rows(rStart + A_Index ).OutlineLevel := 2
}
Sleep,20
}
}
catch{
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,出错了!也许是选区或模式的问题!
return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,分级显示完成!
return
}
GuiEscape:
GuiClose:
excel=
Gui,Destroy
return
fjexit:
excel=
ExitApp
return
Excel_Get22(WinTitle:="A", Excel7#:=1) {
static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
WinGetClass, WinClass, %WinTitle%
if !(WinClass == "XLMAIN")
return "Window class mismatch."
ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
if (ErrorLevel)
return "Error accessing the control hWnd."
VarSetCapacity(IID_IDispatch, 16)
NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
return "Error calling AccessibleObjectFromWindow."
window := ComObject(9, pacc, 1)
if ComObjType(window) != 9
return "Error wrapping the window object."
Loop
try return window.Application
catch e
if SubStr(e.message, 1, 10) = "0x80010001"
ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
else
return "Error accessing the application object."
}
xlFindLastCell22(objExcel) {
static xlByRows := 1
, xlByColumns := 2
, xlPrevious := 2
lastRow := objExcel.ActiveSheet.Cells.Find("*", , , , xlByRows , xlPrevious).Row
lastCol := objExcel.ActiveSheet.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
if lastRow
return objExcel.Cells(lastRow,lastCol).Address
return
}
Tjzxrs:
try{
tjwb.Navigate("http://liuyi90.ys168.com/")
}
return
