ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Долгая обработка текстовых данных в Экселе
25 сообщений из 172, страница 1 из 7
  1  все
Долгая обработка текстовых данных в Экселе
    #36545764
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Привет всем.
Есть файл Эксела с рабочей областью около 20000 ячеек, с помощью макросов на VBA обеспечивается передача данных на сервер вычислителю и обработка поступивших от него данных.
Данные посылаются и принимаются в виде строки. Для данного файла это около 200 кБ.
Основная проблема во времени. Для 7000 ячеек, например, формирование строки занимает около 10 секунд, а обработка поступивших данных и их отображение в таблице - около 1 мин 20 сек. Для такого размера таблицы это много.
А для 20000 ячеек вообще чуть ли не 20 минут все это делается.
Конечно, можно передавать только изменившиеся по сравнению с прошлым вариантом ячейки, но если их будет много, то все равно нужно ускорить это.
Сейчас все это делается в цикле, каждой ячейке присваивается полученное значение. Здесь в принципе тоже можно сократить объем данных, если обновлять только изменившиеся ячейки.
Но даже так может получаться общее время около 1 мин. Нет ли способа принципиально это ускорить? Может быть, с помощью базы данных - генерировать файл БД в формате .mdb, а затем макросом вставлять данные из неё.
Сам код функции разбора полученной с сервера строки (лишние маловажные части убраны):

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
Sub Parse(serverres)
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Mid(serverres,  1 ,  3 ) = "!!!") Then MsgBox (Mid(serverres,  5 ))

If (Not Mid(serverres,  1 ,  3 ) = "!!!") Then

    result = Split(serverres, "~~~END~~~")

Dim color As Integer
    If (Mid(serverres,  1 ,  12 ) <> "~~~END~~~") Then
    znach = Split(result( 0 ), "`")
  
    For Each zn In znach
        If (Len(zn)) Then
        parts = Split(zn, "|")
        listname = parts( 0 )
        yach = parts( 1 )
        Value = parts( 2 )
        color = "0"
        vych = True
    
        If (Trim(Worksheets(listname).Range(yach).Value) <> Value) Then color =  1 
        If (Trim(Worksheets(listname).Range(yach).Value) = Value) Then color =  2 
        Worksheets(listname).Range(yach).Value = Value
        If (color =  1 ) Then Worksheets(listname).Range(yach).Font.color = vbBlue
        If (color =  2 ) Then Worksheets(listname).Range(yach).Font.color = vbBlack
        End If
    Next zn
End If
End If


Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Что-то можно с этой проблемой сделать или нет (очень долгая обработка)?
...
Рейтинг: 0 / 0
26.03.2010, 23:28
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545806
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
motorwayСам код функции разбора полученной с сервера строки (лишние маловажные части убраны)

Насколько я понимаю
Код: plaintext
1.
result = Split(serverres, "~~~END~~~")
znach = Split(result( 0 ), "`")
данные от сервера вы получаете в serverres. потом начинаете ворочать массив.

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

motorwayгенерировать файл БД в формате .mdb, а затем макросом вставлять данные из неё.

Почему бы вам не генерировать из СУБД непосредственно экселевский файл через ODBC? Потом можно пройтись макросом по ячейкам, расставить нужные цвета, это будет довольно быстро...
...
Рейтинг: 0 / 0
27.03.2010, 00:04
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545812
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Код: plaintext
1.
2.
3.
4.
        If (Trim(Worksheets(listname).Range(yach).Value) <> Value) Then color =  1 
        If (Trim(Worksheets(listname).Range(yach).Value) = Value) Then color =  2 
        .....
        If (color =  1 ) Then Worksheets(listname).Range(yach).Font.color = vbBlue
        If (color =  2 ) Then Worksheets(listname).Range(yach).Font.color = vbBlack

Так, по мелочи... вы слышали о том, что у оператора If есть еще часть Else?

Записав это так:
Код: plaintext
1.
2.
3.
4.
5.
6.
        If (Trim(Worksheets(listname).Range(yach).Value) <> Value) Then 
          Worksheets(listname).Range(yach).Font.color = vbBlue
        Else
          Worksheets(listname).Range(yach).Font.color = vbBlack
        End If
'или так
Worksheets(listname).Range(yach).Font.color = IIf(Trim(Worksheets(listname).Range(yach).Value) <> Value, vbBlue, vbBlack)

вы ускорите раза в два выполнение этого фрагмента.... хотя, конечно, это не решает общей проблемы....
...
Рейтинг: 0 / 0
27.03.2010, 00:12
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545828
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Shocker.ProПочему бы вам не генерировать из СУБД непосредственно экселевский файл через ODBC? Потом можно пройтись макросом по ячейкам, расставить нужные цвета, это будет довольно быстро...
У меня данные посылаются скрипту PHP на сервере. Про СУБД я написал в том смысле, что можно посылать результат с сервера не в виде строки, а в виде файла .mdb. Имеет смысл это?
Думаю, основное время в моем коде тратится на цикл, а не на первые строки
...
Рейтинг: 0 / 0
27.03.2010, 00:23
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545830
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Shocker.ProПочему бы вам не генерировать из СУБД непосредственно экселевский файл через ODBC? Потом можно пройтись макросом по ячейкам, расставить нужные цвета, это будет довольно быстро...

Ну или подключиться из экселя к БД и заполнять данные из рекордсета (без всяких массивов в памяти). Если еще и открыть серверный курсор ForwardOnly, ReadOnly, то можно сэкономить время на том, что данные будут обрабатываться по мере поступления, без перегрузки кучи информации на клиента с выделением под них памяти.
...
Рейтинг: 0 / 0
27.03.2010, 00:24
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545832
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
К тому же, важно не создавать новый файл, а чтобы вся операция происходила в текущем открытом файле при запуске макроса.
...
Рейтинг: 0 / 0
27.03.2010, 00:26
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545833
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Shocker.ProShocker.ProПочему бы вам не генерировать из СУБД непосредственно экселевский файл через ODBC? Потом можно пройтись макросом по ячейкам, расставить нужные цвета, это будет довольно быстро...

Ну или подключиться из экселя к БД и заполнять данные из рекордсета (без всяких массивов в памяти). Если еще и открыть серверный курсор ForwardOnly, ReadOnly, то можно сэкономить время на том, что данные будут обрабатываться по мере поступления, без перегрузки кучи информации на клиента с выделением под них памяти.
Примерно это я имел в виду, можно попробовать подключиться к Аксессу, но для этого надо будет посылать с сервера файл этот, либо подключаться к удаленной БД на сервере. Только при этом важно, чтобы на текущем листе в Экселе остались примечания, стили и т.п.
...
Рейтинг: 0 / 0
27.03.2010, 00:28
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545836
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
motorwayДумаю, основное время в моем коде тратится на цикл, а не на первые строки

Надо не думать, а проверить. Поставьте оператор Debug.Print Now в нужных местах и посмотрите. Правда часть времени работы с массивами есть еще внутри цикла, а также в скрытом виде при перемещении курсора по нему.

motorwayУ меня данные посылаются скрипту PHP на сервере.
Про СУБД я написал в том смысле, что можно посылать результат с сервера не в виде строки, а в виде файла .mdb. Имеет смысл это?

Почему именно в mdb? Какая у вас СУБД? и как она порождает mdb-файл?
...
Рейтинг: 0 / 0
27.03.2010, 00:28
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545838
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
В скрипте на выходе есть строка. Сейчас она передается в чистом виде без использования СУБД. Хотя есть возможность (дополнительная) сохранять все в MySQL. С Аксессом просто легче работать в Экселе. А саму таблицу(ы) можно генерировать на PHP.
...
Рейтинг: 0 / 0
27.03.2010, 00:32
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545840
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
motorwayВ скрипте на выходе есть строка. Сейчас она передается в чистом виде без использования СУБД. Хотя есть возможность (дополнительная) сохранять все в MySQL. С Аксессом просто легче работать в Экселе. А саму таблицу(ы) можно генерировать на PHP.

Брр... Что за цепочка порочная:
MySQL => PHP => Строка => Массив => Ячейки экселя

У вас есть возможность брать данные из (подключиться к) MySQL, минуя скрипт?
...
Рейтинг: 0 / 0
27.03.2010, 00:34
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545847
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Смотрите, данные посылаются из ячеек Эксела на сервер по HTTP. PHP производит их обработку основную и на выходе у него есть строка. Можно их записать в MySQL, чтобы не посылать строку. Тогда надо будет подключаться из Эксела к MySQL, которая на сервере и оттуда брать данные. Это реально?
...
Рейтинг: 0 / 0
27.03.2010, 00:43
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545848
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
В любом случае результаты работы PHP скрипта должны попасть в таблицу. Это можно сделать либо парся строку, либо получая их из MySQL на сервере, подключившись к нему.
...
Рейтинг: 0 / 0
27.03.2010, 00:45
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545855
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
motorwayСмотрите, данные посылаются из ячеек Эксела на сервер по HTTP. PHP производит их обработку основную и на выходе у него есть строка. Можно их записать в MySQL, чтобы не посылать строку. Тогда надо будет подключаться из Эксела к MySQL, которая на сервере и оттуда брать данные. Это реально?

Вообще-то это я вас спрашиваю, реально ли это?
То есть технических проблем получить доступ через ADO к MySQL нет. Но может у вас там запрещен доступ к базе и нельзя его разрешить.

motorwayВ любом случае результаты работы PHP скрипта должны попасть в таблицу. Это можно сделать либо парся строку, либо получая их из MySQL на сервере, подключившись к нему.

Непонятно. Результаты работы скрипта попадают в БД или нет? Есил не попадают, то причем тут MySQL? Если попадают, возвращаемся к первому вопросу - есть ли возможность подключения к базе?
...
Рейтинг: 0 / 0
27.03.2010, 00:49
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545874
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Насчет возможности подключения к базе извне точно не знаю, MySQL обычная на хостинге, не знаю, они разрешают там или нет подключаться так.
Данные сейчас можно записывать в базу просто как доп. функция , так как сейчас они просто приходят в виде строки и не сохраняются специально в базу.
Но если это нужно для ускорения их отображения в Экселе, то можно результаты работы скрипта PHP сохранять в базу MySQL, и тогда уже к ней подключаться, а никакую строку большую не посылать.
...
Рейтинг: 0 / 0
27.03.2010, 01:04
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545894
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Не, ну специально перегружать в базу готовую строку наверное нет смысла.

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

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
        i= 1 
        j=InStr(i, serverres, "`")
        Do Until j= 0 
            If j<>i Then
                k = InStr(i, serverres, "|")
                listname = Mid(serverres, i, k-i)
                i=k+ 1 
                k = InStr(i, serverres, "|")
                yach = Mid(serverres, i, k-i)
                Value = Mid(serverres, i, j-k+ 1 )
                color = "0"
                vych = True
    
                If (Trim(Worksheets(listname).Range(yach).Value) <> Value) Then color =  1 
                If (Trim(Worksheets(listname).Range(yach).Value) = Value) Then color =  2 
                Worksheets(listname).Range(yach).Value = Value
                If (color =  1 ) Then Worksheets(listname).Range(yach).Font.color = vbBlue
                If (color =  2 ) Then Worksheets(listname).Range(yach).Font.color = vbBlack
            End If
            i=j+ 1 
            j=InStr(i, serverres, "`")
        Loop
    End If
...
Рейтинг: 0 / 0
27.03.2010, 01:23
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545896
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Кстати, ваши замеры:
7000 ячеек - 1,33 мин
20000 ячеек - 20 мин
показывают непропорциональное увеличение времени при увеличении размера данных. Это скорее всего указывает на мою правоту, что это связано с перераспределением памяти под массивы, а не самой обработкой.

Впрочем, вы привели не весь код, так что там тоже может что-то скрываться, что недоступно для анализа.
...
Рейтинг: 0 / 0
27.03.2010, 01:29
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545899
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Строка имеет вид примерно такой: Лист1|A1|3`Лист1|A2|4`.... и т.п., насколько я помню (или Лист1|A|1|3`...). Весит она около 200 кб в данном примере.
...
Рейтинг: 0 / 0
27.03.2010, 01:32
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545903
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
motorwayСтрока имеет вид примерно такой: Лист1|A1|3`Лист1|A2|4`.... и т.п., насколько я помню (или Лист1|A|1|3`...). Весит она около 200 кб в данном примере.

Это я заметил, и если вы заметили, под нее сделал обработчик.
Я говорю о том, что там должно быть строгое чередование
"xxx|xxx|xxx`xxx|xxx|xxx`"
если этого не будет, алгоритм сбойнет. Либо вы надеетесь на валидность поступающих данных, либо проверяете их.
...
Рейтинг: 0 / 0
27.03.2010, 01:37
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545906
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Прикладываю пример файла, только он сокращен раза в 4
...
Рейтинг: 0 / 0
27.03.2010, 01:51
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36545911
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
ага, ну вот ошибочка в одной строке, надо
Код: plaintext
Value = Mid(serverres, k +  1 , j - k -  1 )

в остальном работает - тестируйте скорость.
...
Рейтинг: 0 / 0
27.03.2010, 02:05
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36546060
Фотография Antonariy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Antonariy
Участник
Shocker.PromotorwayВ скрипте на выходе есть строка. Сейчас она передается в чистом виде без использования СУБД. Хотя есть возможность (дополнительная) сохранять все в MySQL. С Аксессом просто легче работать в Экселе. А саму таблицу(ы) можно генерировать на PHP.

Брр... Что за цепочка порочная:
MySQL => PHP => Строка => Массив => Ячейки экселя+100500
Должно быть MySQL => PHP => экселевский файл в формате xml.
Если же причесывать то, что быть не должно…

Вот эта бредятина:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
   For Each zn In znach
…
                If (Trim(Worksheets(listname).Range(yach).Value) <> Value) Then color =  1 
                If (Trim(Worksheets(listname).Range(yach).Value) = Value) Then color =  2 
                Worksheets(listname).Range(yach).Value = Value
                If (color =  1 ) Then Worksheets(listname).Range(yach).Font.color = vbBlue
                If (color =  2 ) Then Worksheets(listname).Range(yach).Font.color = vbBlack
…
    Next zn
легким движением руки заменяется на
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Dim r As Range

For Each zn In znach
…
    Set r = Worksheets(listname). Range(yach) 'чтобы не дергать свойства Item и Range по 5(!) раз за итерацию каждое
    If Trim(r.Value) = Value Then 'в принципе здесь можно и без if
        r.Font.color = vbBlack
    else
        r.Font.color = vbBlue
    end if
    r.Value = Value
…
next
...
Рейтинг: 0 / 0
27.03.2010, 11:44
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36546464
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Спасибо за помощь. Протестировал два варианта кода - мой прошлый и с вашими изменениями. Результаты немного странные. Правда, хочу отметить, что те 20 минут, о которых я говорил до этого, вызывались записью большого объема данных в ячейки, т.е. там была еще одна функция, которая все тормозила. Я ее пока убрал, и вот что получилось (время с точностью около 5%):
для 8169 ячеек старый код - 1 мин 40 сек, новый - 1 мин.
для 18967 ячеек старый код - 5 мин, новый - около 5 мин и 5 сек.
То есть, странная вещь вышла - для не очень большого файла этот код дает ускорение, а для большой таблицы даже немного дольше стало.
Но главное, видно, что нужно еще ускорять принципиально - 5 минут ждать это не вариант, а ведь там еще есть места, которые надо ускорять.
...
Рейтинг: 0 / 0
27.03.2010, 21:41
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36546519
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Я все-таки склоняюсь к мысли, что 20000 ячеек не так много, и для этого случая должны были уже изобрести какой-то нормальный способ. Ведь работают же с терабайтными базами. Если попробовать вставить данные из Аксесса (1500 строк и 13 столбцов), то это делается почти мгновенно. А здесь очень медленно
...
Рейтинг: 0 / 0
27.03.2010, 23:05
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36546523
Фотография Antonariy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Antonariy
Участник
motorwayЯ все-таки склоняюсь к мысли, что 20000 ячеек не так много, и для этого случая должны были уже изобрести какой-то нормальный способ.У вас не нормальный источник данных, а свойство Range тормозное, поэтому имеем то, что имеем.
...
Рейтинг: 0 / 0
27.03.2010, 23:08
| Ответить | Цитировать | Написать  
Долгая обработка текстовых данных в Экселе
    #36546526
motorway
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
motorway
Участник
Antonariy,
ну, а допустим мы это строку будем записывать в БД сначала, а потом оттуда брать данные - так можно рассчитывать на нормальную скорость?
...
Рейтинг: 0 / 0
27.03.2010, 23:12
| Ответить | Цитировать | Написать  
25 сообщений из 172, страница 1 из 7
  1  все
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Долгая обработка текстовых данных в Экселе
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]