MS Excel 与 AHK

COM Object: Excel.Sheet

Purpose: Hold MS Excel Data - represents a MS Excel workbook

System Requirements: MS Excel

Documentation Link: WorkBook Object

Other Links: Excel Constants, Sort Method

Code Example:

;// Excel Constants
xlMaximized := -4137
xlAsc := xlYes := 1

;// Create WorkBook
XLBook := ComObjCreate("Excel.Sheet")
XLSht := XLBook.ActiveSheet

;// Maximize the WorkBook Control
XLBook.Windows(1).WindowState := xlMaximized
XLBook.Application.Visible := true

;// Fill in Data & Sort
for cell in XLSht.Range("A1:C10")
   if (cell.row = 1)
      cell.value := "Header " [color=brown]A_Index
   else {
      Random, num, 0, 1
      cell.value := num
   }

;// Sort Data
MsgBox, Sorting by Column 1, then 2, then 3...
XLSht.Cells.Sort(   XLSht.Columns(1), xlAsc
                  , XLSht.Columns(2), ComObjMissing(), xlAsc
                  , XLSht.Columns(3), xlAsc
                  , xlYes )

;// Save WorkBook
MsgBox, 4, , The Workbook will close once the object is released - would you like to save it to the scirpt directory?
IfMsgBox, Yes
   XLBook.SaveAs(A_ScriptDir "\Excel.Sheet Example.xls")

Note - the Excel.Sheet object will open in the Active Excel Process (accessed by ComObjActive) - or will create a new Excel Process - and it will close when the pointer is released.

COM Object: Excel.Application
Purpose: Perform calculations, analyse information and visualise data in spreadsheets.
System Requirements: Microsoft Office Excel application
Documentation Link: <!-- m -->http://msdn.microsof...ry ... 12).aspx<!-- m -->
Other Links:
Basic Code Example:

oExcel := ComObjCreate("Excel.Application") ; create Excel Application object
oExcel.Workbooks.Add ; create a new workbook (oWorkbook := oExcel.Workbooks.Add)

oExcel.Range("A1").Value := 3 ; set cell A1 to 3
oExcel.Range("A2").Value := 7 ; set cell A2 to 7
oExcel.Range("A3").Formula := "=SUM(A1:A2)" ; set formula for cell A3 to SUM(A1:A2)

oExcel.Range("A1:A3").Interior.ColorIndex := 19 ; fill range of cells from A1 to A3 with color number 19
oExcel.Range("A3").Borders(8).LineStyle := 1 ; set top border line style for cell A3 (xlEdgeTop = 8, xlContinuous = 1)
oExcel.Range("A3").Borders(8).Weight := 2 ; set top border weight for cell A3 (xlThin = 2)
oExcel.Range("A3").Font.Bold := 1 ; set bold font for cell A3

A1 := oExcel.Range("A1").Value ; get value from cell A1, and store it in A1 variable
oExcel.Range("A4").Select ; select A4 cell
oExcel.Visible := 1 ; make Excel Application visible
MsgBox % A1 "`n" oExcel.Range("A2").Value ; check. You can use Round() function to round numbers to the nearest integer
ExitApp

How to access Workbook without opening it?

FilePath := "C:\Book1.xlsx" ; example path
oWorkbook := ComObjGet(FilePath) ; access Workbook object
MsgBox % oWorkbook.Sheets(1).Range("A1").Value ; get value from A1 cell in first sheet

How to access active Excel Application object?
Use oExcel := Excel_Get(), not oExcel := ComObjActive("Excel.Application"). More info here.
How to access active Workbook?

try
oWorkbook := Excel_Get().ActiveWorkbook ; try to access active Workbook object
catch
return ; case when Excel doesn't exist, or it exists but there is no active workbook. Just Return or Exit or ExitApp.

; if there is active workbook, code continues execution...
oWorkbook.ActiveSheet.Range("B2").Value := "B2" ; set value of B2 cell in active sheet to "B2"

How to access Excel object from Workbook object?

oExcel := oWorkbook.Application ; returns Excel application object that owns Workbook object

xl := ComObjActive("Excel.Application")
for cell in xl.range("Q4:Q200")
{
	case := cell.value
	if case between 0.07 and 1
		Cell.Interior.ColorIndex := 3 ; Red

	else if case between 0.065 and 0.07
		Cell.Interior.ColorIndex := 46 ; Orange

	else if case between 0.06 and 0.065
		Cell.Interior.ColorIndex := 45 ; Light Orange

	else if case between 0.055 and 0.06
		Cell.Interior.ColorIndex := 44 ; Gold

	else if case between 0.05 and 0.055
		Cell.Interior.ColorIndex := 40 ; Tan

	else if case between 0.0475 and 0.05
		Cell.Interior.ColorIndex := 36 ; Light Yellow

	Else
		Cell.Interior.ColorIndex := -4142 ; Default
}
return

xl := ComObjActive("Excel.Application")
loop 4
	xl.cells(A_Index, 1).value := "A", xl.cells(A_Index, 2).value := A_Index
loop 2
	xl.cells(A_Index+4, 1).value := "B", xl.cells(A_Index+4, 2).value := A_Index+4
; the above is to create sample data

Array := {}

Loop {
	if !(xl.cells(A_Index, 1).value)
		break
	Name := xl.cells(A_Index, 1).Text	
	Product := xl.cells(A_Index, 2).Text
	Array[Product] := Name	
}
return

!1::	; press alt+1 to Enter a name
InputBox, MyName, Question, Please Enter Name
MyProducts := ""
for Product, Name  in Array
	MyProducts .= (Name = MyName) ? "> " Product "`n" : ""
MsgBox % """" MyName """ Has `n" MyProducts
return

Another example:

;打开一个Excel实例名字叫test，内有三个Sheet

Values =

oExcel := ComObjActive("Excel.Application") ; 创建Excel 对象

sheet1 := oExcel.WorkBooks("test").Worksheets("sheet1") ;定义源Sheet和 excel.Worksheets[1] 是一样的。

sheet2 := oExcel.WorkBooks("test").Worksheets("sheet2")

index := 1

For k in sheet1.Range("A1:A500")

{

if (k.Value = "")

break

strIndex := "B"index  ;字符串与数字级连就是字符串啦，试了大半天！！！

;MsgBox , strIndex

sheet2.Range[strIndex] := k.Value

Values .= k.Value ", "

index ++

}

MsgBox, % Values

SArr := sheet1.Range("A1:C10").value

MsgBox % SArr.MaxIndex(1) ; total rows

MsgBox % SArr.MaxIndex(2) ; total columns

Loop % SArr.MaxIndex(1)

{

CurRowNum := A_Index

Loop % SArr.MaxIndex(2)

MsgBox % SArr[CurRowNum, A_Index]

}