Aus einer großen Exceltabelle was finden. _Excel_RangeFind zu langsam!

Romanow

Guten Tag liebe Autoit Gemeinde,

ich bin auf folgendes Problem gestoßen, vielleicht stand schon jemand von euch vor dem selben Problem.

Das Programm sucht aus einer Exceltabelle (aktuell 8100 Zeilen lang und 4 Breit Bereich A1:D8100)
In der ersten Spalte steht die ID, der Bereich in dem gesucht wird lautet A1:A10000 (Nicht A8100 weil die Tabelle pro Woche um 150-300 Zeilen länger wird)

$idLPN_NR = Zu Suchender String - ca 14 chars lang +/- 3

Local $aResult = _Excel_RangeFind($oWorkbook, $idLPN_NR,"A1:A10000",$xlValues)

Ich benötige die Information z.B
A10
A1796
A:242
Eine ID kann mehrfach in dieser Exceltabelle vorhanden sein!

Daraus mache ich
A10:D10
A1796:D1796
A3242:D3242
Um mir die benötigten Informationen anzeigen zu lassen. Das geht super schnell.

Das dauert aktuell leider 7 Sekunden, ich benötige das aber in unter 1 Sekunde

Hat jemand eine Idee wie das schneller gehen könnte?

Ich liebäugle gerade mit ClipPut und ClipGet mit StringCompare was zu basteln....

Ich verwende die Excel.au3 der AutoIt Version : 3.3.15.0 mit zwei Anpassungen

$aResult[$iIndex][1] =$oMatch.Name.Name

Macht folgenden Fehler
\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
Geändert in -> $aResult[$iIndex][1] = 0;$oMatch.Name.Name

$aResult[$iIndex][5] = $oMatch.Comment.Text

Macht folgenden Fehler
\AutoIt3\Include\Excel.au3" (660) : ==> The requested action with this object has failed.:

Geändert in -> $aResult[$iIndex][5] = 0;$oMatch.Comment.Text

Code

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
	If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
	If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
	If $iLookIn = Default Then $iLookIn = $xlValues
	If $iLookAt = Default Then $iLookAt = $xlPart
	If $bMatchcase = Default Then $bMatchcase = False
	Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
	If $vRange = Default Then
		$bSearchWorkbook = True
		$oSheet = $oWorkbook.Sheets(1)
		$vRange = $oSheet.UsedRange
	ElseIf IsString($vRange) Then
		$vRange = $oWorkbook.Activesheet.Range($vRange)
		If @error Then Return SetError(3, @error, 0)
	EndIf
	Local $aResult[100][6], $iIndex = 0, $iIndexSheets = 1
	While 1
		$oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
		If @error Then Return SetError(4, @error, 0)
		If IsObj($oMatch) Then
			$sFirst = $oMatch.Address
			While 1
				$aResult[$iIndex][0] = $oMatch.Worksheet.Name
				$aResult[$iIndex][1] = 0;$oMatch.Name.Name
				$aResult[$iIndex][2] = $oMatch.Address
				$aResult[$iIndex][3] = $oMatch.Value
				$aResult[$iIndex][4] = $oMatch.Formula
				$aResult[$iIndex][5] = 0;$oMatch.Comment.Text
				$iIndex = $iIndex + 1
				If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
				$oMatch = $vRange.Findnext($oMatch)
				If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
			WEnd
		EndIf
		If Not $bSearchWorkbook Then ExitLoop
		$iIndexSheets = $iIndexSheets + 1
		$sFirst = ""
		$oSheet = $oWorkbook.Sheets($iIndexSheets)
		If @error Then ExitLoop
		$vRange = $oSheet.UsedRange
	WEnd
	ReDim $aResult[$iIndex][6]
	Return $aResult
EndFunc   ;==>_Excel_RangeFind

Alles anzeigen

**water**

Viel interessanter wäre Dein Script. Denn ich glaube nicht, dass _Excel_RangeFind 7 Sekunden braucht.
Außerdem: Welche Version von Excel verwendest Du?

Romanow

Ich darf hier keine xlsx hochladen, deshalb die zip mit der Excel & Script

Hab das Programm auf das wesentlich zusammengestrichen.

Ich verwende Excel 2003 & 2013

AutoIt

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <StringConstants.au3>
#include <File.au3>
#include <GuiButton.au3>
#include <WindowsConstants.au3>
#include <GUIConstants.au3>
#include <Misc.au3>
#include <Sound.au3>
#include <Array.au3>
#include <Excel.au3>






local $sDir = @ScriptDir & "\ID_LIST.xlsx"




GUICreate("Scanner", @DesktopWidth*0.9, @DesktopHeight*0.9, $WS_EX_ACCEPTFILES)


GUICtrlCreateLabel("LPN Nummer:", 5, 80, 350, 60)
GUICtrlSetFont(-1,32)


Local $idLPN = GUICtrlCreateEdit("LPN000000000000", 350, 80, 400, 60,BitOR($ES_WANTRETURN,$ES_AUTOVSCROLL)) ; Muss ein Edit sein damit die Entererkennung funktioniert!
GUICtrlSetFont(-1,32)
Local $idBtn = GUICtrlCreateButton("SUCHEN", 800, 80, 200, 60)
GUICtrlSetFont(-1,32)
Local $diBeschreibung = GUICtrlCreateLabel("", 5, 180, (@DesktopWidth*0.9)-180, (@DesktopHeight*0.9)-5)
GUICtrlSetFont(-1, 56, 400, 0, "Calibri", 5)




; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sDir)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & $sDir & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


GUISetState(@SW_SHOW)


While 1
	Switch GUIGetMsg()
		Case $GUI_EVENT_CLOSE
			_Excel_Close($oExcel)
			ExitLoop
		Case $idBtn
			Suchen()
	EndSwitch
WEnd


func Suchen()
	$idLPN_NR = GUICtrlRead($idLPN)
	Local $aResult = _Excel_RangeFind($oWorkbook, $idLPN_NR,"A1:A10000",$xlValues)
	local $sResult_1_Text
	Local $aResult_1


	_ArrayDisplay($aResult) ;<- DEBUG
 	for $i = 0 to UBound($aResult)-1
		$aResult_Text_1 = StringReplace($aResult[$i][2],"A","D")
		$aResult_1 = _Excel_RangeRead($oWorkbook, Default, $aResult[$i][2] & ":" & $aResult_Text_1, 1)
		$sResult_1_Text		= $sResult_1_Text &"Beschreibung " & $i+1 & " "& _ArrayToString($aResult_1) & @CRLF
	next


	GUICtrlSetData($diBeschreibung,$sResult_1_Text)
EndFunc

Alles anzeigen

**water**

Lass Dir mal anzeigen, wo das Skript die Zeit liegen lässt. Ich vermute die Schleife mit RangeRead.

Code

func Suchen()
	$idLPN_NR = GUICtrlRead($idLPN)
	Local $hTimer = TimerInit()
	Local $aResult = _Excel_RangeFind($oWorkbook, $idLPN_NR,"A1:A10000",$xlValues)
	ConsoleWrite("Rangefind: " & TimerDiff($hTimer) & @CRLF)
	local $sResult_1_Text
	Local $aResult_1
	_ArrayDisplay($aResult) ;<- DEBUG
	$hTmer = TimerInit()
 	for $i = 0 to UBound($aResult)-1
		$aResult_Text_1 = StringReplace($aResult[$i][2],"A","D")
		$aResult_1 = _Excel_RangeRead($oWorkbook, Default, $aResult[$i][2] & ":" & $aResult_Text_1, 1)
		$sResult_1_Text		= $sResult_1_Text &"Beschreibung " & $i+1 & " "& _ArrayToString($aResult_1) & @CRLF
	next
	ConsoleWrite("RangeRead: " & TimerDiff($hTimer) & @CRLF)
	GUICtrlSetData($diBeschreibung,$sResult_1_Text)
EndFunc

Alles anzeigen

Romanow

10000 Zeilen
Local $aResult = _Excel_RangeFind($oWorkbook, $idLPN_NR,"A1:A10000",$xlValues)
Rangefind: 9316.23515462834
RangeRead: 180.090862213931

1000 Zeilen
Local $aResult = _Excel_RangeFind($oWorkbook, $idLPN_NR,"A1:A1000",$xlValues)
Rangefind: 1192.39494928926
RangeRead: 62.661755432355

**water**

Es liegt an der Excel-Datei.
Ich habe nur die Werte in eine andere Excel_Datei kopiert und schon läuft das Ding in weniger als 1/4 Sekunde durch.
Habe aber noch keine Idee warum!

Romanow

Danke, auf die Idee wäre ich niemals gekommen!

Jetzt ist das Programm ca 155000 mal schneller xD

Neue Zeiten
Rangefind: 59.1207894321974
RangeRead: 231.070919493418

Romanow

@water

Ich habe den Fehler in der Exceltabelle gefunden.

Es wurde bereits mit Excel 2013 die gleichen IDs farbig Markiert.
Mit STRG+A wählt man die Tabelle aus mit Rechtsklick auf Schnellanalyse und da wurde "Doppelte Werte" ausgewählt, das muss rückgängig gemacht werden.
Es handelt sich um keine normale Formatierung, diese lässt sich nicht über die Formatierung löschen.

**water**

Danke für die Rückmeldung. Seltsam, dass sich eine Formatierung auf die Suchgeschwindigkeit auswirkt.