Excel Automatic hierarchical group

Post your working scripts, libraries and tools for AHK v1.1 and older
sixtyone
Posts: 6
Joined: 30 Jun 2018, 00:59

Excel Automatic hierarchical group

Post by sixtyone » 24 Sep 2021, 11:02

Code: Select all

;hotkey: #j
SetBatchLines,-1
#SingleInstance Force
#NoEnv
SetWorkingDir %A_ScriptDir%
Process, Priority,, High
Menu, Tray, NoStandard 
Menu, tray, Add,about,About	
Menu, tray, add, End,fjexit
Menu, Tray, Default,about
;#IfWinActive,ahk_exe excel.exe

#j::
CStart=0
excel :=Excel_Get22()
if !IsObject(excel)		
{
	MsgBox,NO Excel Obj!
	return
}
gui,Destroy
Gui Add, Radio, x15 y5 w140 h23 Checked  vxinghao, By *
Gui Add, Radio, x15 y40 w140 h23 vxuhao, By serial number
Gui Add, Radio, x15 y75 w140 h23 vfeikong, By non empty cell
Gui Add, Radio, x15 y110 w140 h23 vqita, Other
Gui Add, Edit, x15 y145 w140 h23 vfjedit,[\x{4e00}-\x{9fa5}]
Gui Add, Button, x160 y15 w90 h23 gfjcancle ,Cancel
Gui Add, Button, x160 y75 w90 h23 gfjclear ,Clear
Gui Add, Button, x160 y135 w90 h23 gfjact ,Act
Gui +AlwaysOnTop -MinimizeBox -MaximizeBox +ToolWindow
Gui Show,,Excel Grouping v1.0
Return

fjcancle:
excel=
Gui,Destroy
return

fjclear:
Excel.Rows.ClearOutline
return

About:
about=
(
hotkey: win + j
)
MsgBox,%about%
return

fjact:	
Level=1
gui,Submit,NoHide
Fjmode :=xinghao ? "xinghao" 
			: xuhao ? "xuhao"
			: feikong ? "feikong"
			: qita ? "qita"
			:""
if RegExMatch(Excel.selection.address,":\$[0-9]+")   
{
	Excel.ScreenUpdating :=1
Gui +OwnDialogs
MsgBox,You cannot select the whole row area. Please select the column to be graded.
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,Please enter the start line,Please enter the start line
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,Something went wrong! Maybe it's a matter of constituency or mode!
		return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,completed!
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,Something went wrong! Maybe it's a matter of constituency or mode!
		return
	}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,completed!
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,Something went wrong! Maybe it's a matter of constituency or mode!
		return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,completed!
return	
}

if Fjmode=qita
{
	if !fjedit
	{
		excel.ScreenUpdating := -1	
		Gui +OwnDialogs
		MsgBox,The expression is empty!!
		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,Something went wrong! Maybe it's a matter of constituency or mode!
		return
}
excel.ScreenUpdating := -1
Gui +OwnDialogs
MsgBox,completed!
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
}
[Mod edit: [code][/code] tags added.]
image.png
image.png (78.33 KiB) Viewed 473 times
image.png
image.png (99.83 KiB) Viewed 473 times
image.png
image.png (92.4 KiB) Viewed 473 times
Last edited by sixtyone on 26 Sep 2021, 02:31, edited 2 times in total.

ozzii
Posts: 481
Joined: 30 Oct 2013, 06:04

Re: Excel Automatic hierarchical group

Post by ozzii » 25 Sep 2021, 03:30

A little explanation/gif/snapshot for not having to read all the code for knowing wat is the purpose/how to use it?

Post Reply

Return to “Scripts and Functions (v1)”