Что нового

Обработка Excel2007 не сохраненного листа >65535 строк с максимальной скоростью

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Добрый день.
Я недавно начал разбираться с AutoIt, написал скрипт, но меня не устраивает скорость его работы. Наверняка можно использовать какие-нибудь библиотеки или еще что-нибудь, что улучшило бы быстродействие скрипта. Надеюсь получить от вас, более опытных пользователей AutoIt, советы по доработке моего скрипта.

Код:
$_FilePath='C:\!copy\'
$_FileStart='1.xlsx'
$_FileBuy='Buy.txt'
$_FileSell='Sell.txt'

$R=1; номер строки перед нужнымы данными
$StartDate="20111216"
$Buys=0
$Sells=0

GUICreate("Запись в файл", 300, 150)
GUICtrlCreateLabel($_FilePath&$_FileBuy, 30, 10)
GUICtrlCreateLabel($_FilePath&$_FileSell, 30, 30)
$stop_button = GUICtrlCreateButton("Стоп", 50, 100, 100)
GUISetState(@SW_SHOW)
$Begin=TimerInit()
$AllTime=TimerInit()
$Flag=0

While 1
	$msg = GUIGetMsg()
	Select
		Case $msg = $stop_button
			$Stoptime=TimerDiff($AllTime)
			FileWriteLine($_FilePath&$_FileBuy,$Stoptime)
			FileWriteLine($_FilePath&$_FileSell,$Stoptime)
			Exit
	ExitLoop
	EndSelect

$R=$R+1
;If TimerDiff($Begin)>5000 Then
;	Exit
;EndIf
$obj1 = ObjGet($_FilePath&$_FileStart)
$L1=$obj1.WorkSheets("1").Cells($R, 2).Value ;ячейка даты
$L2=$obj1.WorkSheets("1").Cells($R, 3).Value ;ячейка времени
$L3=$obj1.WorkSheets("1").Cells($R, 4).Value ; кол-во
$L4=$obj1.WorkSheets("1").Cells($R, 5).Value ; операция

$D=StringRight($L1,4)&StringMid($L1,4,2)&StringLeft($L1,2) ;дата к формату YYYYMMDD
$T=StringRegExpReplace($L2,":","")
$TT=StringLeft($T,4) ; время к формату HHMMSS

IF $D+0<>$StartDate+0 and $D<>"" Then
	ContinueLoop
EndIf

IF $L1<>"" Then
	IF $Flag=0 Then
		$TS=$TT
		$Flag=1
		$Buys=0
		$Sells=0
	EndIf
	If $TS=$TT then
		If $L4="Купля" Then
			$Buys=$Buys+$L3
		EndIf
		If $L4="Продажа" Then
			$Sells=$Sells+$L3
		EndIf
	Else
		$LBuy=$D&","&$TS&",0,0,0,0,"&$Buys ; формат вывода в строку покупок
		$LSell=$D&","&$TS&",0,0,0,0,"&$Sells ; формат вывода в строку продаж

		FileWriteLine($_FilePath&$_FileBuy,$LBuy)
		FileWriteLine($_FilePath&$_FileBuy,(TimerDiff($Begin)/1000)/60)

		FileWriteLine($_FilePath&$_FileSell,$LSell)
		FileWriteLine($_FilePath&$_FileSell,(TimerDiff($Begin)/1000)/60)

		$Begin=TimerInit()
		$TS=$TT
		If $L4="Купля" Then
			$Buys=$L3
			$Sells=0
		EndIf
		If $L4="Продажа" Then
			$Sells=$L3
			$Buys=0
		EndIf
	EndIf
Else
	FileWriteLine($_FilePath&$_FileBuy,TimerDiff($AllTime))
	FileWriteLine($_FilePath&$_FileSell,TimerDiff($AllTime))
	Exit
EndIf
	Sleep(100) ;пауза, чтобы не грузить процессор.

WEnd


Данный скрипт служит для того, чтобы обрабатывать постоянно дополняющийся файл через DDE экспорт.
Файл, в который будут записываться данные может быть с любым расширением. Видел в хэлпе отдельные функции для работы с ini, потому предположил, что есть отдельные методы, возможно, более скоростные. Для меня расширение не критично. Кроме того, этот в этот скрипт добавлены временные отсечки, это только для разработки. В конечном варианте их не будет.
 

vovsla

Осваивающий
Сообщения
553
Репутация
26
Re: Повысить скорость работы скрипта

Пару лет назад я написал реляционную СУБД для склада, хоть я и использовал разные ini файлы для хранения различных данных, все равно по мере роста базы она начинала все сильнее тормозить. По моему лучше для базы использовать SQLite, в AutoIt есть библиотеки для работы с этим движком. Справка можно найти в стандартной справке AutoIt, User Defined Function Reference --> SQLite Management, но по самому SQLite лучше найти какой-нить учебник, мне хорошо помог учебник МАРТИН ГРУБЕР Понимание SQL. Для просмотра базы при отладке очень помогает SQLiteManager - программа для просмотра и редактирования *.db файлов. Сейчас я работаю над новой базой и она уже на SQLite, при запросе на вывод таблицы в SQLiteManager, 1591 запись выгружается за 0,003 секунды.
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Vovsla, добрый день!
Спасибо, что отозвались на мой вопрос.
Скажите, я правильно Вас понял, что используя SQLite, я смогу сделать то же самое с более высокой скоростью?
У меня на входе - таблица Excel. Только она. В неё добавляются данные по DDE. Другого источника у меня нет и, скорее всего не будет. На выходе - текстовик, любой, главное формат записей внутри.
В итоге этот скрипт обрабатывает около 100 тыс. строк приблизительно часа 3-4... Но в реале нужно, чтобы строк 300 обрабатывалось в минуту минимум. Ну и, конечно чем быстрее тем лучше.
Ваш вариант поможет мне решить проблему?
Я не программист, с SQL не работал никогда..
На сколько я понимаю, чтобы работать с базой *.db её нужно будет сначала создать. Теоретически есть у меня вывод ODBC, но, по отзывам, он слишком тормозной. То есть остается DDE в экзель. Получается, этот экзелевский файл нужно будет сначала преобразовывать в *.db, а потом с ним работать? А преобразовывать можно будет через COM объект, потому что файл не сохраняется и только так его можно увидеть. Это реально все сделать, при этом скорость обработки возрастет?
 

madmasles

Модератор
Глобальный модератор
Сообщения
7 790
Репутация
2 319
Re: Повысить скорость работы скрипта

ViktorSPB,
А можно увидеть пример Excel-файла и пример файлов-результатов?
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Да, конечно, пожалуйста. Только тут Exсel-евский источник порезанный. Минимальный весит 2.5Мб в архиве. Максимум пока около 800 тыс. строк. Данные приходят по мере поступления и обрабатываются согласно алгоритму. Сжимаются в минутные интервалы.
 

madmasles

Модератор
Глобальный модератор
Сообщения
7 790
Репутация
2 319
Re: Повысить скорость работы скрипта

ViktorSPB,
Попробуйте так, должно быть быстрее.
Код:
#include <Excel.au3>
;#include <Array.au3>

$iBuy = 0
$sBuy = 'Купля'
$sFileExcel = @ScriptDir & '\1.xls';поменяйте расширение, я на 2003 проверял.
$sFileTemp = @ScriptDir & '\Temp.txt'
$sFileBuy = @ScriptDir & '\1_Buy.txt'
$sTime = ''
$sTimeOld = ''
If FileExists($sFileTemp) Then FileDelete($sFileTemp)
$o_Excel = _ExcelBookOpen($sFileExcel, 0)
If @error Then
	MsgBox(16, 'Error', '_ExcelBookOpen')
	Exit
EndIf
_ExcelBookSaveAs($o_Excel, @ScriptDir & '\Temp', 'txt')
If @error Then
	MsgBox(16, 'Error', 'Error')
EndIf
_ExcelBookClose($o_Excel, 0)

$sText = FileRead($sFileTemp)
$aBuy = StringRegExp($sText, '(?m)^(.*?' & $sBuy & '.*?)$', 3)
If @error Then
	MsgBox(16, 'Error', 'StringRegExp')
	Exit
EndIf
$sText = ''
;_ArrayDisplay($aBuy)
For $i = 0 To UBound($aBuy) - 1
	$iBuy += StringRegExpReplace($aBuy[$i], '.*\s(\d+)\s' & $sBuy, '$1');если я правильно понял, как это считается
	$sTime = StringRegExpReplace($aBuy[$i], '.*\s(\d{2}):(\d{2}).*', '$1$2,0,0,0,0,')
	If $sTimeOld <> $sTime Then
		$sTimeOld = $sTime
		$sText &= StringRegExpReplace($aBuy[$i], '.*(\d{2})\.(\d{2})\.(\d{4})\s.*', '$3$2$1,') & $sTimeOld & $iBuy & @CRLF
	EndIf
Next
$hFile = FileOpen($sFileBuy, 2)
FileWrite($hFile, StringTrimRight($sText, 2))
FileClose($hFile)
FileDelete($sFileTemp)
Код поправил (про минуты забыл). :smile:
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

madmasles, сходу получилось быстрее. Спасибо!
Теперь я его подправлю, ну доведу до конца и проверить теперь смогу только в понедельник, сейчас экспорта нет. Я пробовал через библиотеку _Excel работать. Дело в том, что через эти функции файл не виден. Его надо сохранить, сделать, так сказать, снимок. Потом обрабатывать. Кроме того, мне принципиально Excel2007. Ибо проглотить 800тыс строк на листе, боюсь, 2003 не сможет..
Сейчас переделаю с учетом того, что скрипт надо будет гнать в цикле, данные сравнивать с прошлым кадром и дописывать в файл. Ну и алгоритм пересчета подправлю, почти правильно Вы посчитали)
В целом, на много круче получилось у Вас! :ok:
Лишь бы заработало в бою)
 

madmasles

Модератор
Глобальный модератор
Сообщения
7 790
Репутация
2 319
Re: Повысить скорость работы скрипта

ViktorSPB [?]
Я пробовал через библиотеку _Excel работать. Дело в том, что через эти функции файл не виден.
Посмотрите функцию _ExcelBookAttach().
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Прочитал Help. Спасибо. Должно заработать. Сейчас разбираю Ваш скрипт. Попробовал просто прогнать на своем файле, полной его версии.. Я в шоке! Махом все посчиталось! Супер! Ладно, продолжаю работать)
 

vovsla

Осваивающий
Сообщения
553
Репутация
26
Re: Повысить скорость работы скрипта

Сравнил скорости занесения данных в файл, записывал значения от 1 до 100000, функция FileWriteLine самая быстрая.
Результаты в миллисекундах
IniWrite 854484.828242744
FileWriteLine 245146.140526275
Запись в SQL 14375189.6851097
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Vovsla, понял Вас, но сейчас пришли к тому, чтобы записывать файл целеком. Но буду иметь ввиду на будущее. Спасибо большое.

madmasles, помогите, пожалуйста, разобраться, не пойму, что происходит.
Этот скрипт заносит в $sTime первую строку, например,
0959pp,0,0,0,0,
Это логично
Код:
$sTime = StringRegExpReplace($aBuy[$i], '.*\s(\d{2}):(\d{2}).*','$1$2'&'pp,0,0,0,0,');

а этот
Код:
$sTime = StringRegExpReplace($aBuy[$i], '.*\s(\d{2}):(\d{2}).*','$1$2'&'00,0,0,0,0,');

подтирает 59 и в переменной
09,0,0,0,0,
Как так?
А мне нужно, чтобы время было в формате HHMMSS, то есть 095900
Ну чтобы всегда дописывались 00.
Что я не так делаю?
 

madmasles

Модератор
Глобальный модератор
Сообщения
7 790
Репутация
2 319
Re: Повысить скорость работы скрипта

Vovsla [?]
Сравнил скорости занесения данных в файл, записывал значения от 1 до 100000
Как?

ViktorSPB,
Код:
$sString = '190	16.12.2011	10:00:04	105	Купля'

$sTime = StringRegExpReplace($sString, '.*\s(\d{2}):(\d{2}).*','$1${2}00,0,0,0,0,')
MsgBox(64, 'Info', $sTime)

Почитайте эту тему.
 

beliy

Продвинутый
Сообщения
372
Репутация
72
Re: Повысить скорость работы скрипта

мне принципиально Excel2007. Ибо проглотить 800тыс строк на листе, боюсь, 2003 не сможет..
Функция _Excel нормально работает с Excel2007
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

beliy, добрый день!
А вот не скажите. С сохраненным листом проблем не возникает, однако, когда используется _ExcelBookAttach, при "сохранении как" вываливается ошибка. Почитал хэлп, подправил исходную библиотеку _Excel, пока результат нулевой.. Разбираюсь. Попробуйте сохранить в текстовик файл, вызванный как COM-объект.
[box title=TitleBox]
C:\Program Files\AutoIt3\Include\Excel2007.au3 (374) : ==> The requested action with this object has failed.:
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook^ ERROR
[/box]
Причем, если использую _ExcelBookOpen, никаких проблем и править ничего не надо было, все проходило замечательно.
 

WSWR

AutoIT Гуру
Сообщения
941
Репутация
361
Re: Повысить скорость работы скрипта

ViktorSPB
А вам принципиально квик? Можно посмотреть в сторону альтернативных путей доступа - Альфа-Директ, Алор, Транзак-коннектор от Финама, Нетинвестор, от Айти-Инвеста терминалы и т.д.?
Там более совершенные способы получения данных.
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Здравствуйте, WSWR.
В данном случае почти все готово. Работает хорошо НО с сохраненным листом через _ExcelBookOpen. Никак не могу победить, чтобы работа шла с COM объектом Excel. Я не сомневаюсь, что квик - эээ... не очень надежный и совершенный терминал, скажем так. Но пока я прописал в нем на Qpile экспорт всего, что мне нужно, и прицепить осталось только эти данные. Сейчас стоит задача не робота построить, а обеспечить сбор данных. Тут не в глюках квика дело. Я же открываю СОХРАНЕННЫЙ даже лист Excel, обращаюсь к нему через COM, вижу, что временный файл появился, то есть объект в памяти, но операция SaveAs не хочет работать корректно.
Есть и другие терминалы, безусловно, но сейчас на них время тратить не могу, нужно добить этот вопрос и дальше будет видно. Все по порядку) Что касается вообще использования квика, то можем в частной беседе обсудить его возможности. А они есть.
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Форумчане, прошу вашей помощи. Не могу заставить _ExcelBookSaveAs сохранить файл в .txt с Excel2007.
Несколько часов поиска и никаких результатов..
Метод в библиотеке подправил, но поправка, как я понимаю, для сохранения в других форматах...
Нюанс в том, что лист нужно вызвать через объект, т.е. через _ExcelBookAttach
ошибка:
[box title=ERROR]
==> The requested action with this object has failed.:
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook^ ERROR
[/box]
Если вызываю через _ExcelBookOpen все работает.
Уже MSDN смотрел на соответствие метода в библиотеке, уже вручную прописывал каждый параметр в самой библиотеке, думал, может метод стал отличаться.. Никак не получается.
На всякий случай у меня VisualStudio 2010
 

madmasles

Модератор
Глобальный модератор
Сообщения
7 790
Репутация
2 319
Re: Повысить скорость работы скрипта

ViktorSPB,
А в формате csv тоже не сохраняет?
 
Автор
V

ViktorSPB

Новичок
Сообщения
109
Репутация
0
Re: Повысить скорость работы скрипта

Тоже. Попробовал.


Добавлено:
Сообщение автоматически объединено:

На всякий случай, чтобы было меньше сомнений, привожу код
Код:
$o_Excel = _ExcelBookAttach($sFileExcel,'FilePath');
;$o_Excel = _ExcelBookOpen($sFileExcel, 0);


_ExcelBookSaveAs($o_Excel, 'C:\STORAGE\'&$Instrument&'temp', 'txt');

Уже и файл сохранен, и когда открывается временный файл, при обращении, размеры их совпадают при обоих методах вызова, предполагаю, что открывают правильно..
 

vovsla

Осваивающий
Сообщения
553
Репутация
26
Re: Повысить скорость работы скрипта

Цитата
Сравнил скорости занесения данных в файл, записывал значения от 1 до 100000
Как?
Я сравнивал абстрактные данные

Код:
#include <SQLite.au3>


Global $IniForTest="IniForTest.ini", $TxtForTest="TxtForTest.txt"

LogWrite("Старт внесения 100К записей в ini файл")
$timer = TimerInit()
For $Num=1 To 100000
IniWrite($IniForTest, $Num, $Num, $Num)
Next
$timer2=TimerDiff($timer)
LogWrite("запись в ini завершена "&$timer2)



LogWrite("Старт внесения 100К записей в txt файл")
$timer = TimerInit()
For $Num=1 To 100000
FileWriteLine($TxtForTest, $Num)
Next
$timer2=TimerDiff($timer)
LogWrite("запись в txt завершена "&$timer2)



_SQLite_Startup()
_SQLite_Open("DBTest.db")

_SQLite_Exec(-1,"Create table DBTest (Numdata, Data);")

LogWrite("Старт внесения 100К записей SQL")
$timer = TimerInit()
For $Num=1 To 100000
_SQLite_Exec(-1,"Insert into DBTest values ('"&$Num&"', '"&$Num&"' );" )
Next
$timer2=TimerDiff($timer)
LogWrite("запись в SQL завершена "&$timer2)

_SQLite_Close()
_SQLite_Shutdown()



Func LogWrite($Text)
FileWrite("!AutoLog.txt", $Text)
$LongOfString=80-StringLen($Text)
Do
FileWrite("!AutoLog.txt", " ")
$LongOfString=$LongOfString-1
Until $LongOfString=0
FileWrite("!AutoLog.txt", @HOUR&":"&@MIN&":"&@SEC&":"&@MSEC&@CRLF)
EndFunc



Добавлено:
Сообщение автоматически объединено:


Попробуйте сохранять через макрос, что-то сейчас не соображу какой код должен получится.
Вот пример для выделения границ ячеек.

Код:
#include <Excel.au3>    
	$oExcel = _ExcelBookOpen("c:\tst.xls")
MakeBorderOfCell($oExcel, "A1:A10")
		
Func MakeBorderOfCell($oExcel, $Range="A1:A1")
$oSel = $oExcel.ActiveWorkBook.ActiveSheet.Range($Range)
With $oSel.Borders($xlEdgeLeft)
    .Weight = $xlThin
EndWith
With $oSel.Borders($xlEdgeTop)
    .Weight = $xlThin
EndWith
With $oSel.Borders($xlEdgeBottom)
    .Weight = $xlThin
EndWith
With $oSel.Borders($xlEdgeRight)
    .Weight = $xlThin
EndWith
With $oSel.Borders($xlInsideVertical)
    .Weight = $xlThin
EndWith
With $oSel.Borders($xlInsideHorizontal)
    .Weight = $xlThin
EndWith
EndFunc


макрос в Exel на сохранение следующий
ActiveWorkbook.SaveAs Filename:="C:\1.txt", _
FileFormat:=xlUnicodeText, CreateBackup:=False
но что-то в автоит пока перенести не получается...
 
Верх