This is my CSV with the cells separated by a | for your convenience:
Code: Select all
5.95 | Name A
3.85 | Name A
59.50 | Name B
329 | Name C
39.1 | Name C
393 | Name C
Code: Select all
4.9 | Name A
59.50 | Name B
253.7 | Name C
Code: Select all
5.95 | Name A
3.85 | Name A
59.50 | Name B
329 | Name C
39.1 | Name C
393 | Name C
Code: Select all
4.9 | Name A
59.50 | Name B
253.7 | Name C
Code: Select all
Loop 6
{
Counter := 0
FileReadLine, Output, FileName, %Counter%
if ( ; Cell: B2 = Cell: B3)
sum := (Cell: A2 + Cell: A3) / 2
FileAppend, %sum%, FileName ; to Cell: A2
; delete row 3
Counter := Counter - 1
}
Code: Select all
#NoEnv
#Warn ; Enable warnings to assist with detecting common errors.
#SingleInstance force
#Persistent
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
;---------------------------------------------------------------------------------
; 1. Create a blank worksheet.
; Init - CreateUnoService
oSM := ComObjCreate("com.sun.star.ServiceManager") ; This line is mandatory with AHK for OOo API
oDesk := oSM.createInstance("com.sun.star.frame.Desktop") ; Create the first and most important service
; Set Open parameters
Array := ComObjArray(VT_VARIANT:=12, 2)
Array[1] := MakePropertyValue(oSM, "Hidden", ComObject(0xB,true))
oDoc := oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array) ; open a file with .loadComponentFromURL()
;---------------------------------------------------------------------------------
; Create a table in the blank spreadsheet
; SheetName = Blad1
SheetName = Sheet1
CellWriteByPos(SheetName, 0, 0, "The Title of the Table!", 2)
Loop 10 ; Loop i
{ i := A_Index - 1
i1 := A_Index
CellWriteByPos(SheetName, i, 1, "Column" i1, 2)
Loop 10 ; Loop j
{ j := A_Index - 1
j1 := i + j
j2 := j + 2
CellWriteByPos(SheetName, i, j2, j1, 1)
}
}
;---------------------------------------------------------------------------------
ExitApp
; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Function .: Write a value in a cell position
CellWriteByPos(Page, Col, Row, Info, Type)
{ Global
oSheet := oDoc.getSheets().getByName(Page) ; Get the sheet by its name
oCell := oSheet.getCellByPosition(Col, Row) ; Get a Cell by its position
If Type = 1
oCell.setValue(Info) ; Set the value of the cell as number
If Type = 2
oCell.setString(Info) ; Set the value of the cell as string
If Type = 3
oCell.setFormula(Info) ; Set the value of the cell as formula
Return
}
; Function .: MakePropertyValue!
MakePropertyValue(poSM, cName, uValue)
{ oPropertyValue := Object()
oPropertyValue := poSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name := cName
oPropertyValue.Value := uValue
Return oPropertyValue
}
; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Code: Select all
CSV =
(Join`r`n
5.95,Name A
3.85,Name A
59.50,Name B
329,Name C
39.1,Name C
393,Name C
)
Names := {}
Loop, Parse, CSV, `n, `r ; Put all the CSV data into an array
{
Cells := StrSplit(A_LoopField, "`,")
if Names.HasKey(Cells.2)
Names[Cells.2].Push(Cells.1)
else
Names[Cells.2,1] := Cells.1
}
for Name, Values in Names ; Calculate Averages from Array data
{
X := 0
for Index, Value in Values
X += Value
Display .= Name "`t" RegExReplace(X / Index, "\.0*$|(\.\d*?)0*$", "$1") "`n" ; RegEx is to Remove Trailing Zeros
}
MsgBox % Display
Users browsing this forum: No registered users and 144 guests