Hallo Leute,
ich bin relativ neu in Autoit und versuche mich gerade daran aus verschiedenen Quellen (txt, xls) daten zu ziehen und sie zu einem excelprotokoll zusammenzufügen und auszuwerten.
ansich klappt das ganze recht gut ich habe nur 2 probleme.
1. problem: ich lese ein array ein und benutze die werte dann später in meinem code, nur leider ist ein wert in diesem array plötzlich "leer".
Wie man sieht mache ich das mit einer einfachen for-schleife. die Msgbox hatte ich einfach mal zum testen. in der werden mir alle daten die ich haben möchte korrekt angezeigt.
außerhalb der schleife fehlt mir aber plötzlich $WertArray[7], mit "1-6" kann ich ohne probleme arbeiten.
$PAR[1] = "BM-Nummer"
;Stüli
$PAR[2] = "Stückliste"
;Steller
$PAR[3] = "E-Steller"
;Chipsatz
$PAR[4] = "Chip"
;Minflow Soll Kg/h
$PAR[5] = "Minflow in Kg/h"
;Minflow Soll Volt
$PAR[6] = "Minflow in Volt"
;Maxflow Soll Volt
$PAR[7] = "Maxflow Soll Volt"
;Minflow Kg/h Toleranz
$PAR[8] = "Minflow Toleranz"
Global $WertArray[9]
$openfile = FileOpen($file,0)
for $s = 1 to 8
for $i = 1 to _FileCountLines($file)
$readlineroh = FileReadLine($openfile,$i)
$readline = StringTrimLeft($readlineroh, StringInStr($readlineroh,"=", -1))
if StringInStr($readlineroh,$PAR[$s]) Then
$WertArray[$s] = $readline
;MsgBox(64,"",$WertArray[$s])
endif
next
next
Alles anzeigen
Problem Nr.2
Das erstellen des Protokolls dauert zu lange.
Natürlich programmiere ich nach bestem wissen und gewissen, allerdings geht das sicher noch um einiges besser und kompakter.
Ich poste einfach mal meinen ganzen Code, vielleicht findet ja jemand die Zeit um mir effektive tipps zu geben.
Vielen dank schon mal im Voraus.
#include <File.au3>
#include <Excel.au3>
#include <Array.au3>
#include <Date.au3>
#include <MsgBoxConstants.au3>
;WinActivate("PG588")
;WinWaitActive("PG588")
Local $oExcel = _Excel_Open(@SW_HIDE);@SW_HIDE optionales verstecken
Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Küstermann-Messung_Ohne_Event19\Küstermann-Messung_Ohne_Event19\Temporäre Minflow-Werte.csv")
$UsedRows = $oExcel.ActiveSheet.UsedRange.Rows.Count
Global $ExcelArray[$UsedRows+1]
Global $ExcelArray2[$UsedRows+1]
Global $ExcelArray3[$UsedRows+1]
Global $ExcelArray4[$UsedRows+1]
Global $ExcelArray5[$UsedRows+1]
Global $ExcelArray6[$UsedRows+1]
Global $ExcelArray7[$UsedRows+1]
for $rowcount = 1 to $UsedRows
;Volt
$ExcelArray[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "B"&$rowcount)
;Umgebungsdruck
$ExcelArray2[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "H"&$rowcount)
;Temp vor ATL
$ExcelArray3[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "I"&$rowcount)
;Temp nach ATL
$ExcelArray4[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "J"&$rowcount)
;Messdruck
$ExcelArray5[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "K"&$rowcount)
;Durchfluss
$ExcelArray6[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "L"&$rowcount)
;Streuung
$ExcelArray7[$rowcount] = _Excel_RangeRead($oWorkbook, Default, "M"&$rowcount)
next
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel, False, True)
$hWnd = WinGetHandle("PG588", "")
;Anzahl der Messungen
;$Anzahl = controlgettext($hWnd,"","[NAME:MeasCountTextBox]")
$Anzahl = 5
;######## Stüli/BM-Nummer etc aus TXT laden
$file= "C:\Küstermann-Messung_Ohne_Event19\Küstermann-Messung_Ohne_Event19\config.ini"
local $PAR[9] ;Parameter Array
;BM-Nummer
$PAR[1] = "BM-Nummer"
;Stüli
$PAR[2] = "Stückliste"
;Steller
$PAR[3] = "E-Steller"
;Chipsatz
$PAR[4] = "Chip"
;Minflow Soll Kg/h
$PAR[5] = "Minflow in Kg/h"
;Minflow Soll Volt
$PAR[6] = "Minflow in Volt"
;Maxflow Soll Volt
$PAR[7] = "Maxflow Soll Volt"
;Minflow Kg/h Toleranz
$PAR[8] = "Minflow Toleranz"
Global $WertArray[9]
$openfile = FileOpen($file,0)
for $s = 1 to 8
for $i = 1 to _FileCountLines($file)
$readlineroh = FileReadLine($openfile,$i)
$readline = StringTrimLeft($readlineroh, StringInStr($readlineroh,"=", -1))
if StringInStr($readlineroh,$PAR[$s]) Then
$WertArray[$s] = $readline
;MsgBox(64,"",$WertArray[$s])
endif
next
next
;Excel+Workbook öffnen
Local $oExcel = _Excel_Open(@SW_HIDE);@SW_HIDE optionales verstecken
Local $oWorkbook = _Excel_BookNew($oExcel, 2)
;Sheet Namen ändern
$_NewSheetName = "BM-Nummer Minflow Auswertung"
$oExcel.ActiveSheet.Name = $_NewSheetName
;Excel Spalten Anpassen (Breite)
$oExcel.Columns("A:A").ColumnWidth = "21"
;Konstanten in Excel schreiben
$date = @MDAY & "." & @MON & "." & @YEAR & " / " & @HOUR & ":" & @MIN
;Kopf
$oExcel.Range("A4:C7").Borders.LineStyle = 1
$oExcel.Range("A4:C7").Interior.ColorIndex = 24 ; setze Farbe
$oExcel.Range("B4:C4").Merge
$oExcel.Range("B5:C5").Merge
$oExcel.Range("B6:C6").Merge
$oExcel.Range("B7:C7").Merge
;Kopf Werte
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Stückliste" ,"A4")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$WertArray[2] ,"B4")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"BM-Nummer" ,"A5")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$WertArray[1] ,"B5")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"E-Steller" ,"A6")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$WertArray[3] ,"B6")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Chipsatz" ,"A7")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$WertArray[4] ,"B7")
;Body
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Messdaten" ,"A9")
$oExcel.Range("A9") .Font.Bold = TRUE
$oExcel.Range("A9").Borders.LineStyle = 1
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$date ,"E4")
$oExcel.Range("E4:F4").Merge
$oExcel.Range("E4:F4").Borders.LineStyle = 1
$oExcel.Range("E4:F4").Interior.ColorIndex = 24
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Messung Nr." ,"A10")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Umgebungsdruck in mBar" ,"A11")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Temp. vor ATL in °C" ,"A12")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Temp. nach ATL in °C" ,"A13")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Minflow in Volt / LSB" ,"A15")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Durchfluss in Kg/h" ,"A16")
;Überschrift und Design
$oExcel.ActiveSheet.Rows(2).Rowheight = 25
$oExcel.Range("A2:F2").Select
With $oExcel.Selection
.MergeCells = true
.Font.size= 17
.Font.Bold = TRUE
.Borders.LineStyle = 1
.Interior.ColorIndex = 24
.HorizontalAlignment = 7
EndWith
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Minflow Datenerfassung und Auswertung" ,"A2")
;Fuß Design
;Arrays übertragen
for $i = 2 to $Anzahl + 1
Local $iCol = $i
Local $sLetter = _Excel_ColumnToLetter($iCol)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$i -1 ,$sLetter & 10)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.3f\n", $ExcelArray[$i-1]), $sLetter &15)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.1f\n", $ExcelArray2[$i-1]), $sLetter &11)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.1f\n", $ExcelArray3[$i-1]), $sLetter &12)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.1f\n", $ExcelArray4[$i-1]), $sLetter &13)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.2f\n", $ExcelArray6[$i-1]), $sLetter &16)
next
;Durchschnitt Volt Array
Local $averageVolt
For $n = 1 To $rowcount -1
$averageVolt += $ExcelArray[$n]
Next
$averageVolt = $averageVolt / ($rowcount -1)
;Durchschnitt Massenstrom Array
Local $averageMass
For $n = 1 To $rowcount -1
$averageMass += $ExcelArray6[$n]
Next
$averageMass = $averageMass / ($rowcount -1)
$oExcel.Range("A1:Z20").CurrentRegion
With $oExcel.Selection
.HorizontalAlignment = 7
EndWith
$oExcel.Range("A10:" & $sLetter &13).Select
With $oExcel.Selection
.Borders.LineStyle = 1
.Interior.ColorIndex = 43
EndWith
$oExcel.Range("A15:" & $sLetter &16).Select
With $oExcel.Selection
.Borders.LineStyle = 1
.Interior.ColorIndex = 27
EndWith
Local $iCol2 = $Anzahl + 3
Local $sLetter2 = _Excel_ColumnToLetter($iCol2)
;Konstanten aus TXT/PG übernehmen
Local $iCol1 = $Anzahl + 2
Local $sLetter1 = _Excel_ColumnToLetter($iCol1)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Soll" ,$sLetter1 & 14)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $WertArray[6] ,$sLetter1 & 15)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $WertArray[5] ,$sLetter1 & 16)
$oExcel.Range($sLetter1&14&":"&$sLetter2&16).Select
With $oExcel.Selection
.Borders.LineStyle = 1
.Font.Bold = TRUE
endwith
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Ist" ,$sLetter2 & 14)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.3f\n", $averageVolt) ,$sLetter2 & 15)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.2f\n", $averageMass) ,$sLetter2 & 16)
;Bewertung
$IstVolt = _Excel_Rangeread($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.3f\n", $averageVolt) ,$sLetter2 & 15)
$SollVolt = _Excel_Rangeread($oWorkbook, $oWorkbook.Activesheet, $WertArray[6] ,$sLetter1 & 15)
$IstMass = _Excel_Rangeread($oWorkbook, $oWorkbook.Activesheet,StringFormat("%.2f\n", $averageMass) ,$sLetter2 & 16)
$SollMass = _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $WertArray[5] ,$sLetter1 & 16)
$VoltMax = $SollVolt + 0.005
$VoltMin = $SollVolt - 0.005
$MassMax = $SollMass + $WertArray[7]
$MassMin = $SollMass - $WertArray[7]
MsgBox(64,"",$WertArray[7])
if $IstVolt > $VoltMin and $IstVolt <$VoltMax then
$oExcel.Range("E19:F21").Select
With $oExcel.Selection
.Merge = true
.Interior.ColorIndex = 4
EndWith
Else
$oExcel.Range("E19:F21").Select
With $oExcel.Selection
.Merge = true
.Interior.ColorIndex = 3
EndWith
endif
if $IstMass > $MassMin and $IstMass <$MassMax then
$oExcel.Range("E19:F21").Select
With $oExcel.Selection
.Merge = true
.Interior.ColorIndex = 4
EndWith
Else
$oExcel.Range("E19:F21").Select
With $oExcel.Selection
.Merge = true
.Interior.ColorIndex = 3
EndWith
endif
$oExcel.Range("E18:F18").Merge
$oExcel.Range("E18:F21").Borders.linestyle = 1
$oExcel.Range("E18:F18").Select
With $oExcel.Selection
.Merge = true
.HorizontalAlignment = 7
.Font.Bold = TRUE
EndWith
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,"Bewertung" ,"E18")
Local $oExcel = _Excel_Open()
exit
Alles anzeigen