Как улучшить процесс создания/сохранения файлов XLS. / Microsoft Office

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Как улучшить процесс создания/сохранения файлов XLS.

21 сообщений из 21, страница 1 из 1

Как улучшить процесс создания/сохранения файлов XLS.

#37765924

Catyara

Гость

Добрый день.

У меня есть необходимость создавать и сохранять большое количество(около 500) файлов XLS. Для этого был написан следующий макрос:
Private Sub MakeReportXLS(tData() As tWB_Data, Payee As String)
Dim wb As Workbook
Dim i As Integer
Dim rRow As Double
Dim r As Range

On Error Resume Next

Application.ScreenUpdating = False

Set wb = Workbooks.Add

wb.Sheets("Sheet1").Range("A1").CurrentRegion.Delete Shift:=xlUp
Set r = wb.Sheets("Sheet1").Range("A1")
'*******************Format Sheet***********************
заполнение листа информацией
'*******************Format Sheet***********************
форматирование листа
'*************************************************************
wb.Sheets("Sheet1").Name = "Data"
Application.DisplayAlerts = False
wb.Sheets("Sheet2").Delete
wb.Sheets("Sheet3").Delete
wb.SaveAs Filename:=ThisWorkbook.Path & "\Reports\" & Payee & " SW Report " & _
Format(Date, "ddmmyyyy") & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = True

wb.Close
Set wb = Nothing

Application.ScreenUpdating = True
End Sub

Проблема состоит в том, что в процессе выполнения скорость создания/выполнения файлов падает, причем очень ощутимо.
Пожалуйста, подскажите мои недочеты и возможные пути устранения этой проблемы.

...

Рейтинг:

0 / 0

23.04.2012, 11:32

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37765965

Hugo121

Гость

Catyara,

Мне кажется, что тормоза накапливаются в тех нам невидимых частях.
Но попробуйте - я чуть исправил код на свой вкус:

Код: vbnet

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.

Option Explicit

Private Sub MakeReportXLS(tData() As tWB_Data, Payee As String)
Dim wb As Workbook
Dim i As Long 'на всякий случай
Dim rRow As Double 'почему строка вдруг double?
Dim r As Range

On Error Resume Next

Application.ScreenUpdating = False

Set wb = Workbooks.Add(xlWBATWorksheet)

'wb.Sheets("Sheet1").Range("A1").CurrentRegion.Delete Shift:=xlUp 'зачем чистить чистый лист?
Set r = wb.Sheets(1).Range("A1") 'универсальнее не обращаться по имени - оно может вдруг быть другим
'*******************Format Sheet***********************
'заполнение листа информацией
'*******************Format Sheet***********************
'форматирование листа
'*************************************************************
wb.Sheets(1).Name = "Data"
Application.DisplayAlerts = False
'wb.Sheets("Sheet2").Delete 'лишнее
'wb.Sheets("Sheet3").Delete 'лишнее
wb.SaveAs Filename:=ThisWorkbook.Path & "\Reports\" & Payee & " SW Report " & _
Format(Date, "ddmmyyyy") & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = True

wb.Close 0
Set wb = Nothing

Application.ScreenUpdating = True
End Sub

...

Рейтинг:

0 / 0

23.04.2012, 11:59

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37765982

Catyara

Гость

Hugo121Catyara,

Мне кажется, что тормоза накапливаются в тех нам невидимых частях.

[/src]

Ок, тогда привожу код целиком:

Код: vbnet

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.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.

Private Sub MakeReportXLS(tData() As tWB_Data, Payee As String)
Dim wb         As Workbook
Dim i          As Integer
Dim rRow       As Double
Dim r          As Range

On Error Resume Next

Application.ScreenUpdating = False

Set wb = Workbooks.Add

wb.Sheets("Sheet1").Range("A1").CurrentRegion.Delete Shift:=xlUp
Set r = wb.Sheets("Sheet1").Range("A1")

r.Offset(0, 0).Value = "Поле 1"
r.Offset(0, 1).Value = "Поле 2"
r.Offset(0, 2).Value = "Поле 3"
r.Offset(0, 3).Value = "Поле 4"
r.Offset(0, 4).Value = "Поле 5"
r.Offset(0, 5).Value = "Поле 6"
r.Offset(0, 6).Value = "Поле 7"
r.Offset(0, 7).Value = "Поле 8"
r.Offset(0, 8).Value = "Поле 9"
r.Offset(0, 9).Value = "Поле 10"
r.Offset(0, 10).Value = "Поле 11"
r.Offset(0, 11).Value = "Поле 12"

rRow = 0

For i = 1 To UBound(tData)
    If tData(i).Flag = "Flag 1" Or tData(i).Flag = "Flag 2" Then
        rRow = rRow + 1
        r.Offset(rRow, 0).Value = tData(i).Value_1
        r.Offset(rRow, 1).Value = tData(i).Value_2
        r.Offset(rRow, 2).Value = tData(i).Value_3
        r.Offset(rRow, 3).Value = tData(i).Value_4
        r.Offset(rRow, 4).Value = tData(i).Value_5
        r.Offset(rRow, 5).Value = tData(i).Value_6
        r.Offset(rRow, 6).Value = tData(i).Value_7
        r.Offset(rRow, 7).Value = tData(i).Value_8
        r.Offset(rRow, 8).Value = tData(i).Value_9
        r.Offset(rRow, 9).Value = tData(i).Value_10
        r.Offset(rRow, 10).Value = tData(i).Value_11
        Select Case tData(i).Flag
            Case "Flag 1"
                r.Offset(rRow, 11).Value = "Указан флаг №1"
            Case "Flag 2"
                r.Offset(rRow, 11).Value = "Указан флаг №2"
        End Select
    End If
Next i

'*******************Format Sheet***********************
Set r = r.CurrentRegion
With r
    .Borders.LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Columns(3).Borders(xlEdgeRight).Weight = xlMedium
    For i = .Rows.count To 1 Step -2
        .Rows(i).Interior.Color = RGB(255, 255, 153)
        .Rows(i - 1).Interior.Color = RGB(219, 229, 241)
    Next i
    .Rows(1).Interior.Color = RGB(178, 178, 178)
    .Columns(6).NumberFormat = "0"
    .Columns(4).NumberFormat = "0"
    .EntireColumn.AutoFit
End With
'*************************************************************
wb.Sheets("Sheet1").Name = "Data"
Application.DisplayAlerts = False
wb.Sheets("Sheet2").Delete
wb.Sheets("Sheet3").Delete
wb.SaveAs Filename:=ThisWorkbook.Path & "\Reports\" & Payee & " SW Report " & _
                    Format(Date, "ddmmyyyy") & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = True

wb.Close
Set wb = Nothing

Application.ScreenUpdating = True
End Sub

...

Рейтинг:

0 / 0

23.04.2012, 12:11

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37765986

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

Попробуйте оценить время выполнения частей кода
Проще всего для этого - поместить debug.print Now() в начале процедуры и после каждого интересующего куска кода

Так вы увидите на чём именно тормозит выполнение.
Сказать что-то ещё затруднительно, т.к. ничего неизвестно о размерах файлов, способах наполнения и т.д.

...

Рейтинг:

0 / 0

23.04.2012, 12:14

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37765997

Catyara

Гость

ElenHimПопробуйте оценить время выполнения частей кода

Сама процедура отрабатывает довольно быстро. Дело в том, что проблемы начинаются после создания некоторого кол-ва файлов.
После этого начинает тормозить именно на этапе

Код: vbnet

1.
2.

wb.SaveAs Filename:=ThisWorkbook.Path & "\Reports\" & Payee & " SW Report " & _
                    Format(Date, "ddmmyyyy") & ".xls", FileFormat:=xlExcel8

...

Рейтинг:

0 / 0

23.04.2012, 12:19

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766003

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

А, теперь ясно.

вы вставляете данные поячеечно. Гораздо быстрее сразу диапазон вставлять.
Ну и форматируете вы тоже в циклах (когда цвет задаёте) . Это тоже можно без цикла сделать

...

Рейтинг:

0 / 0

23.04.2012, 12:21

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766009

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

Ну а размер файла какой получается? Если мегабайт 200 - тогда и удивляться нечего

...

Рейтинг:

0 / 0

23.04.2012, 12:22

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766018

Catyara

Гость

ElenHimСказать что-то ещё затруднительно, т.к. ничего неизвестно о размерах файлов, способах наполнения и т.д.

В переменной tData() As tWB_Data передается массив с максимальным кол-вом записей около 2000. Следовательно и в файле заполняется 2000 строк(максимум, где-то и 1-2).

...

Рейтинг:

0 / 0

23.04.2012, 12:24

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766024

Catyara

Гость

ElenHimНу а размер файла какой получается? Если мегабайт 200 - тогда и удивляться нечего

Средний размер файлов 25 KB.

...

Рейтинг:

0 / 0

23.04.2012, 12:25

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766035

скукотища

Гость

> ElenHim
> Ну и форматируете вы тоже в циклах (когда цвет задаёте) . Это тоже можно без цикла сделать

Каким образом?

...

Рейтинг:

0 / 0

23.04.2012, 12:30

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766052

Hugo121

Гость

Я бы массив делал двумерным, сразу в массиве выставлял все эти флаги, затем одним действием вываливал массив на лист.
Так не будет работы с ячейками, что долго.
С форматироанием сложнее, но тоже можно как-то сгруппировать диапазон и покрасить одним действием.
Можно через union, а может кто оптимальнее подскажет, что-то сразу ничего на ум не приходит...

...

Рейтинг:

0 / 0

23.04.2012, 12:37

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766061

Hugo121

Гость

Кстати, красит уже в 2 раза быстрее так:

Код: vbnet

1.
2.
3.
4.
5.
6.

With r
.Rows().Interior.Color = RGB(219, 229, 241)
    For i = .Rows.Count To 1 Step -2
        .Rows(i).Interior.Color = RGB(255, 255, 153)
    Next i
End With

...

Рейтинг:

0 / 0

23.04.2012, 12:43

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766144

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

скукотища,

на скорую руку, например, так(вложение)

Можно ещё быстрее/эффективнее. Но теперь, когда я указал направление, думаю, и сами догадаетесь как.

...

Рейтинг:

0 / 0

23.04.2012, 13:28

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766209

Hugo121

Гость

ElenHim,

Это не по-спортивному :)
Но т.к. во всех случаях процесс занимает доли секунды на имеющиеся 2000 строк - это не решает проблему...

...

Рейтинг:

0 / 0

23.04.2012, 13:55

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766239

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

Hugo121,

Странные у вас критерии по-спортивному/не по-спортивному.
Это был ответ на конкретный вопрос - как без циклов. Тут уж неважно, 2000 строк или 200000 (понятно, что формула из примера будет считаться дольше и Intersect соответственно, но они тут для примера .SpecialCells, можно без них).

И да, ещё есть .PasteSpecial, но область применения .SpecialCells, имхо, гораздо шире.

Мораль очень проста - чем лучше владеешь своим предметом, тем больше возможностей.

...

Рейтинг:

0 / 0

23.04.2012, 14:12

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766271

Hugo121

Гость

ElenHim, я думаю, что писать на лист критерий отбора не всегда возможно. Да и свободного столбца может и не быть.
Я кстати тоже думал в этом направлении...

...

Рейтинг:

0 / 0

23.04.2012, 14:26

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766324

ElenHim

Участник

Откуда: Москва

Сообщения: 197

Рейтинг: 0 / 0

Hugo121Да и свободного столбца может и не быть.

Это результат бездарного проектирования

Hugo121писать на лист критерий отбора не всегда возможно

С этим можно согласится. Но опять, же зависит от конечных целей. Обычно недостаточно чтобы работало.
Нужно непременно чтобы работало быстро, правильно (красиво, с определённым функционалом и т.д.)

Возвращаясь к теме, 0,5k файлов по 2k строк - это 1kk строк. Они ведь откуда-то берутся?Если из БД, то и раскидывать их по файлам
быстрее из самой БД. Если не из БД, тогда у вас всё очень грустно.

...

Рейтинг:

0 / 0

23.04.2012, 14:48

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766327

Catyara

Гость

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

...

Рейтинг:

0 / 0

23.04.2012, 14:51

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766467

Игорь Горбонос

Участник

Откуда: Днепропетровск

Сообщения: 4 416

Рейтинг: 0 / 0

> Автор: Catyara
> Спасибо, большое за эти улучшения. Но к сожалению основную проблему это не решает.

А кто такой

Код: vbnet

tData() As tWB_Data

?
Может он не освобождается и на каждую книгу создается новый? Тогда получается что 11 столбцов умножить на 2000 строк и
умножить на 4 байта(для лонга) и умножить на 500 книг получим внушительную цифру.

Можете выложить полный код формирования отчетов?

Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

23.04.2012, 15:51

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766781

скукотища

Гость

> ElenHim
> на скорую руку, например, так(вложение)

Красиво.

...

Рейтинг:

0 / 0

23.04.2012, 17:48

| Ответить | Цитировать | Написать

Как улучшить процесс создания/сохранения файлов XLS.

#37766794

скукотища

Гость

Catyara,
попробуйте закомментировать заполнение и форматирование листа. Оставьте только создание (и сохранение книги).
Посмотрите, сохранится ли тенденция "затормаживания".

Код: vbnet

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

Private Sub MakeReportXLS_test(tData() As tWB_Data, Payee As String)
Dim wb         As Workbook
Dim i          As Integer
Dim rRow       As Double
Dim r          As Range

' On Error Resume Next

Application.ScreenUpdating = False

Set wb = Workbooks.Add

' во втором тесте закомментировать и следующую строку
wb.SaveAs Filename:=ThisWorkbook.Path & "\Reports\" & Payee & " SW Report " & _
                    Format(Date, "ddmmyyyy") & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = True

wb.Close 0
Set wb = Nothing

Application.ScreenUpdating = True
End Sub

Немного может ускорить заполнение не новой книги, а подготовленного шаблона (цвета строк, границы, форматы ячеек).

...

Рейтинг:

0 / 0

23.04.2012, 17:54

| Ответить | Цитировать | Написать

21 сообщений из 21, страница 1 из 1

Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Как улучшить процесс создания/сохранения файлов XLS.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=61&msg=37766327&tid=2175784]:	0ms
get settings:	4ms
get forum list:	13ms
check forum access:	2ms
check topic access:	2ms
track hit:	38ms
get topic data:	10ms
get forum data:	2ms
get page messages:	54ms
get tp. blocked users:	1ms
others:	213ms

total:	339ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы