Список форумов BOSSForum.RU - Форум. БОСС-Кадровик
Независимый форум, посвященный системе БОСС-Кадровик
и всему, что с ней связано
 FAQFAQ   ПоискПоиск   ПользователиПользователи   ГруппыГруппы   РегистрацияРегистрация 
 ПрофильПрофиль   Войти и проверить личные сообщенияВойти и проверить личные сообщения   ВходВход 

Отчет в Excel с большим количеством строк и форматированием+

 
Начать новую тему   Ответить на тему    Список форумов BOSSForum.RU - Форум. БОСС-Кадровик -> Общие вопросы
Предыдущая тема :: Следующая тема  
Автор Сообщение
Константин



Зарегистрирован: 02.05.2012
Сообщения: 39
Откуда: Красноярск
СообщениеДобавлено: Пт Май 04, 2012 10:15    Заголовок сообщения: Отчет в Excel с большим количеством строк и форматированием+ Ответить с цитатой
Сделали по заказу отчет для компании по формированию свода премий за выбранный период для выбранных структур. В отчете выводится ~9000 строк, при этом есть заголовки структур предприятия, есть депримированные сотрудники (выделяются красным цветом) и разнообразные промежуточные итоги. Отчет работает правильно, но вот скорость очень низкая, т.к. идет построчный вывод и форматирование.

Вывод в Excel через ADODB удовлетворительный по скорости, но вот с форматированием в процессе никак, только после вывода в Excel можно применить его на все выведенные данные.


Есть ещё способы создания отчетов с удовлетворительной скоростью и возможность форматировать "на лету" ?

Последний раз редактировалось: Константин (Сб Май 12, 2012 17:33), всего редактировалось 1 раз
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Spartak



Зарегистрирован: 18.03.2010
Сообщения: 185
СообщениеДобавлено: Сб Май 05, 2012 15:12    Заголовок сообщения: Ответить с цитатой
Попробуйте посмотрите отчет в зарплате Документы - Ведомости - Начислений на выплаты, он кажется имеет довольно сложное форматирование...
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
VladSPb



Зарегистрирован: 27.04.2012
Сообщения: 17
Откуда: St.Petersburg
СообщениеДобавлено: Пн Май 07, 2012 13:24    Заголовок сообщения: Ответить с цитатой
Самый быстрый вывод - это если в z-процедуре в запросе получать не набор полей, а 1 поле, содержащее конкатенацию всех полей (через спецразделитель). А в шаблоне д.б. макрос, разбирающий данные по столбцам. Тогла скорость вывода увеличится в разы, если не на порядки. Одно "но": при необходимости корректировки отчета - корректировка усложнится, т.к. надо будет ковырять и макрос... Sad
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Отправить e-mail
Константин



Зарегистрирован: 02.05.2012
Сообщения: 39
Откуда: Красноярск
СообщениеДобавлено: Пт Май 11, 2012 19:54    Заголовок сообщения: Ответить с цитатой
Отчеты на Z-языке вроде быстрей, но так:

Код:

local Query_ as string;
ADODB.Connection as AutoCon;
ADODB.Recordset as AutoSet;
ADODB.Recordset as AutoSet2;
AutoCon.Open('@@xUtil{dsq}');
AutoSet.CursorLocation = 2;
CON Формирование отчета "Список работников"...;
let Query_='select
pp.Num_Tab,
cc.Full_Name,
ap.Name_appoint,
stt.Struct_Name,
t1.name,
''=СУММ(1+3+5)''
from
user_standart_recipients t0
join people pp on t0.people_id=pp.pId
join card cc on pp.Auto_Card=cc.Auto_Card
join pr_current prc on pp.pId=prc.pId and GETDATE() between prc.Date_trans and prc.Date_depart
join Appointments ap on ap.Code_appoint=prc.Code_Appoint
join user_standart_pts t1 on t0.standart_id=t1.id
join StructS stt on stt.Struct_Code =prc.Code_struct_name
join Cells ccc on ccc.Cell_item = prc.Cell_item
order by t1.pt,t0.standart_id,stt.sort,prc.poz,ccc.number';

AutoSet.Open(:Query_, :AutoCon, 3, 1, 1);
execute xOCX {Excel.Application as Excel};

AutoSet.Open(:Query_, :AutoCon, 100, 100, 1);
execute xOCX {Excel.Application as Excel};


local vb.Record=&AutoSet;
local vb.Excel=&Excel;
vb
{
Excel.ScreenUpdating = 0
Excel.EnableEvents = 0
Dim WorkBook
Set WorkBook=Excel.Workbooks.Add()
WorkBook.Worksheets(1).Cells(1,1) = "Таб"
WorkBook.Worksheets(1).Cells(2,1).CopyFromRecordset Record
WorkBook.Worksheets(1).Cells(1,2) = "ФИО"
WorkBook.Worksheets(1).Cells(2,2).CopyFromRecordset Record
WorkBook.Worksheets(1).Cells(1,3) = "Cnhernehf"
WorkBook.Worksheets(1).Cells(2,3).CopyFromRecordset Record
WorkBook.Worksheets(1).Rows(1).RowHeight = 30
WorkBook.Worksheets(1).Columns(1).ColumnWidth = 25
rem Меняем тип строчки на формулу
WorkBook.Worksheets(1).Columns(6).Select
rem WorkBook.Worksheets(1).Selection.Value = WorkBook.Selection.FormulaLocal
WorkBook.Worksheets(1).Columns(6).FormulaLocal = WorkBook.Worksheets(1).Columns(6).Value
Excel.ScreenUpdating = 1
Excel.EnableEvents = 1
};
AutoSet.Close;
CON;

execute xOCX{
Excel.Visible=True;
};

execute xOcxFree(AutoSet);
execute xOcxFree(Excel);
execute xOcxFree(AutoCon);


Скорость самая подходящая, только вот не могу разобраться как вывести в одну таблицу результат нескольких последовательныз запросов. Может кто сталкивался?
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Константин



Зарегистрирован: 02.05.2012
Сообщения: 39
Откуда: Красноярск
СообщениеДобавлено: Сб Май 12, 2012 09:30    Заголовок сообщения: Ответить с цитатой
(исправлено) По мотивам данного форума нашел следующее:
Код:


--Быстрый вывод в Excel двух запросов, в каждом по 126000 записей


Local {_strQry; _Title; _Title1;};


Select short_name
into :Struct_name_
from setup where id_firm = 1;

Let {
_Title = 'asdqwe123';
};


Let {
_Title1 = Struct_name_[''] + ' Период:' +'_dateask_month[м]' + ' _dateask_year';
};

Alias _strQry := select
"Подразделение" = FNAme
,"ПФ(страх)" = h1
,"ПФ(накоп)" = h2
,"ПФ(всего)" = h4
,"ФСС" = h5
,"ТФОМС" = h6
,"ФФОМС" = h7
,"Травматизм" = h8
,"НДФЛ" = st
from user_rep
;

-- Параметры коннекта
Free _dsn,_db,_uid,_pwd,_is_ntuser,_ConnStr;
Local{_dsn;_db;_uid;_pwd;_is_ntuser;_ConnStr;};

Let _dsn = '@@xUtil{DSN}';
select db_name() into :_db;
select suser_sname() into :_uid;
Let _Pwd = '@@xUtil{pwd}';
select Count(*)
into :_is_ntuser
from master.dbo.syslogins
where loginname = suser_sname()
and (isntname =1 OR isntgroup = 1 OR isntuser = 1);
if _is_ntuser['] > 0 then
{Let _ConnStr := 'ODBC;DSN=' + _dsn[''] + ';UID=' + _uid[''] + ';PWD=;Database=' + _db[''];}
else {Let _ConnStr := 'ODBC;DSN=' + _dsn[''] + ';UID=' + _uid[''] + '; PWD=' + _pwd[''] + ';Database=' + _db['']};
Free _dsn,_db,_uid,_pwd,_is_ntuser;



-- Выгрузка данных
VB.FREE;
VB{
Dim ConnStr
Dim strQry
Dim TitleTxt
Dim TitleTxt1

Dim Flag
};

Let VB.ConnStr := _ConnStr[""];
Let VB.strQry := _strQry[""];
Let VB.TitleTxt := _Title[""];
Let VB.TitleTxt1 := _Title1[""];

Alias VB.Flag == 0;


VB {
Flag = 0

Dim cc
Dim lc
Dim Excel
Dim Sh

   Err.Clear
   On Error Resume Next
   Set Excel = GetObject(,"Excel.Application")

   If Err.Number <> 0 Then
      Err.Clear
      Set Excel = CreateObject("Excel.Application")
   end if
   On Error Goto 0


Excel.Visible = True
rem Excel.Workbooks.Add
   Set Sh = GetObject("", "Excel.sheet")
   Set Sh = Sh.sheets(1)
Sh.PageSetup.Orientation = 2
Sh.PageSetup.Zoom = False
Sh.PageSetup.FitToPagesWide = 1
Sh.PageSetup.FitToPagesTall = 1


With Sh.QueryTables.Add(ConnStr, Sh.Range("A3"))
.Name = "ExportData"
.CommandText = strQry
.FieldNames = True
rem .RowNumbers = false
rem .FillAdjacentFormulas = False
rem .PreserveFormatting = True
rem .RefreshOnFileOpen = False
rem .BackgroundQuery = False
rem .RefreshStyle = 1
rem .SavePassword = False
rem .SaveData = True
rem .AdjustColumnWidth = True
rem .RefreshPeriod = 0
rem .EnableEditing = False
rem .PreserveColumnInfo = True
.Refresh
End With

With Sh.QueryTables.Add(ConnStr, Sh.Range("L3"))
.Name = "ExportData"
.CommandText = strQry
.FieldNames = True
rem .RowNumbers = false
rem .FillAdjacentFormulas = False
rem .PreserveFormatting = True
rem .RefreshOnFileOpen = False
rem .BackgroundQuery = False
rem .RefreshStyle = 1
rem .SavePassword = False
rem .SaveData = True
rem .AdjustColumnWidth = True
rem .RefreshPeriod = 0
rem .EnableEditing = False
rem .PreserveColumnInfo = True
.Refresh
End With


cc = Sh.UsedRange.Columns.Count
While cc = 0
cc = Sh.UsedRange.Columns.Count
wend

if Sh.UsedRange.Rows.Count > 1 then
Sh.UsedRange.Select
Excel.Selection.Borders(5).LineStyle = -4142
Excel.Selection.Borders(6).LineStyle = -4142
With Excel.Selection.Borders(7)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With Excel.Selection.Borders(8)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With Excel.Selection.Borders(9)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With Excel.Selection.Borders(10)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With Excel.Selection.Borders(11)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With Excel.Selection.Borders(12)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
End If 'if Sh.UsedRange.Rows.Count
cc = Sh.UsedRange.Columns.Count

Sh.Range(Sh.Cells(3, 1), Sh.Cells(3, cc)).Select
With Excel.Selection.Interior
.ColorIndex = 15
.Pattern = 1
End With
With Excel.Selection
.HorizontalAlignment = -4108
.VerticalAlignment = -4160
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Excel.Selection.WrapText = True
Excel.Selection.ColumnWidth = 10
Excel.Columns("A:A").ColumnWidth = 40
Excel.Columns("B:B").ColumnWidth = 14
Excel.Columns("C:C").ColumnWidth = 14
Excel.Columns("D:D").ColumnWidth = 14
Excel.Columns("E:G").ColumnWidth = 14
Excel.Columns("H:H").ColumnWidth = 14
Excel.Columns("I:I").ColumnWidth = 14
Excel.Columns("E:H").WrapText = True
Excel.Columns("A:H").VerticalAlignment = -4160


Sh.Range("A1").Value = TitleTxt
Sh.Range("A1").Select
Sh.Range(Sh.Cells(1, 1), Sh.Cells(1, cc)).Select

Excel.Selection.Font.Bold = True
Excel.Selection.Font.Size = Excel.Selection.Font.Size

Sh.Range("A2").Value = TitleTxt1
Sh.Range("A2").Select
Excel.Selection.Font.Bold = True
Excel.Selection.Font.Size = Excel.Selection.Font.Size

Sh.Range(Sh.Cells(4, 3), Sh.Cells(99, 9)).Select

Excel.Selection.NumberFormat = "#,##0.00"

Sh.Range("A2").Select

Set Excel = Nothing 'Добавил, после найденного решения

Flag = 1
};--VB

VB.FREE;
free *;
EndFn:


В одну таблицу выводится два запроса с достаточно большим кол-вом строк, но минус в том что после выполнения скрипта и закрытия сформированного документа в памяти остается висеть процесс Excel. Не могу понять какую переменную надо освободить, что бы Excel не висел после закрытия.

Последний раз редактировалось: Константин (Сб Май 12, 2012 10:23), всего редактировалось 1 раз
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Mike
Большой шоколадный орден
Большой шоколадный орден


Зарегистрирован: 07.04.2011
Сообщения: 255
СообщениеДобавлено: Сб Май 12, 2012 09:45    Заголовок сообщения: Ответить с цитатой
execute xOCXfree(*);
ан нет, чтот и у меня висеть продолжает...
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Константин



Зарегистрирован: 02.05.2012
Сообщения: 39
Откуда: Красноярск
СообщениеДобавлено: Сб Май 12, 2012 09:51    Заголовок сообщения: Ответить с цитатой
Mike не помогает. Пробовал на Express версии босса, Excel 2010

Решение найдено:
в конце блока VB надо добавить:
Код:
Set Excel = Nothing
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Константин



Зарегистрирован: 02.05.2012
Сообщения: 39
Откуда: Красноярск
СообщениеДобавлено: Сб Май 12, 2012 13:41    Заголовок сообщения: Ответить с цитатой
Сделал такой пример, демонстрирующий subj

Код:


--Подготовим тестовые данные
   erroroff;
   DROP TABLE tmp_20120412_1;
   DROP TABLE tmp_20120412_2;
   erroron;

   create table tmp_20120412_1 (str_code int, str_name nvarchar(50));
   create table tmp_20120412_2 (id int, fio_name nvarchar(50), str_code int);

   insert into tmp_20120412_1 (str_code,str_name) VALUES ('1','Дирекция');
   insert into tmp_20120412_1 (str_code,str_name) VALUES ('2','Инженеры');
   insert into tmp_20120412_1 (str_code,str_name) VALUES ('3','Рабочие');

   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('1','Вася А','1');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('2','Вася Б','1');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('3','Петя А','2');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('4','Петя Б','2');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('5','Миша А','3');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('6','Миша Б','3');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('7','Жора А','3');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('8','Жора Б','3');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('9','Боря А','3');
   insert into tmp_20120412_2 (id,fio_name,str_code) VALUES ('10','Боря Б','3');


-- Параметры коннекта
   Free _dsn,_db,_uid,_pwd,_is_ntuser,_ConnStr;
   Local {_dsn;_db;_uid;_pwd;_is_ntuser;_ConnStr;};

   Let _dsn = '@@xUtil{DSN}';
   select db_name() into :_db;
   select suser_sname() into :_uid;
   Let _Pwd = '@@xUtil{pwd}';
   select Count(*) into :_is_ntuser from master.dbo.syslogins where loginname = suser_sname() and (isntname =1 OR isntgroup = 1 OR isntuser = 1);
   if _is_ntuser['] > 0
   then {Let _ConnStr := 'ODBC;DSN=' + _dsn[''] + ';UID=' + _uid[''] + ';PWD=;Database=' + _db[''];}
   else {Let _ConnStr := 'ODBC;DSN=' + _dsn[''] + ';UID=' + _uid[''] + '; PWD=' + _pwd[''] + ';Database=' + _db['']};
   Free _dsn,_db,_uid,_pwd,_is_ntuser;


-- Начало вывода результата в Excel
   VB.FREE;
   VB{
      Dim ConnStr
      Dim str_query
      Dim Flag
   };

   Let VB.ConnStr := _ConnStr[""];
   Alias VB.Flag == 0;

   VB {
      Flag = 0

      Dim cc
      Dim lc
      Dim Excel
      Dim Sh
      Dim CurPosY
      Dim SeekY
      Dim st_name

      Err.Clear
      On Error Resume Next
      Set Excel = GetObject(,"Excel.Application")

      If Err.Number <> 0 Then
         Err.Clear
         Set Excel = CreateObject("Excel.Application")
      end if
      On Error Goto 0

   
      Excel.Visible = True
      rem Excel.Workbooks.Add
      Set Sh = GetObject("", "Excel.sheet")
      Set Sh = Sh.sheets(1)
      Sh.PageSetup.Orientation = 2
      rem Sh.PageSetup.Zoom = False
      rem Sh.PageSetup.FitToPagesWide = 1
      rem Sh.PageSetup.FitToPagesTall = 1
      Excel.ActiveWindow.WindowState = -4137 ' на весь жкран развернуть
   };


   stable @structs select str_code,str_name from tmp_20120412_1;
   
   let vb.CurPosY=1; --Положение курсора в таблице
   let vb.SeekY=0;  -- Кол-во записей возвращаемых запросом

   --Перебираем структуру предприятия
   scan @structs
   execute {
      alias astr_query = select ROW_NUMBER() OVER(ORDER BY id),fio_name,str_code from tmp_20120412_2 where str_code=@structs:1; -- запрос получения списка сотрудников в подразделении
      let vb.str_query := astr_query[""]; --передаем параметр в объект VB
      let vb.st_name := @structs:2;  -- передаем в VB наименвоание подразделения

      --для смещения вниз необходимо знать кол-во записей в запросе
      stable @lenquery astr_query;
      let vb.seeky := @lenquery:[x];
      destroy @lenquery;
      
      --добавляем в таблицу точки вывода запроса, сами запросы
      VB {
         'добавляем некое подобие шапки, прежде объединив их
         sh.Range(sh.Cells(CurPosY,1), sh.Cells(CurPosY,3)).MergeCells = True      
         sh.Cells(CurPosY,1) = st_name
         CurPosY = CurPosY + 1

         With Sh.QueryTables.Add(ConnStr, Sh.Range(sh.cells(CurPosY,1),sh.cells(CurPosY+seeky,3)))
         .Name = "ExportData"
         .CommandText = str_query
         .FieldNames = false 'не выводить наименовая полей
         .RefreshStyle = 0 'не смещать данные при добавлении в область ранее добавленных данных, если 1 то будет смещать
         .Refresh
         End With

         CurPosY = CurPosY + seeky 'сдвигаем курсор таблицы на размер добавляемых данных
      
      };
   };
   destroy @structs;

   VB {
      flag=1
      set Excel = nothing
   
   };--VB

   VB.FREE;



На исходной задачи опробовал, скорость формирования отчета приемлемая.

joy!
_________________
return @@tMonth[%m]
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Показать сообщения:   
Начать новую тему   Ответить на тему    Список форумов BOSSForum.RU - Форум. БОСС-Кадровик -> Общие вопросы Часовой пояс: GMT + 4
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах


Pоwerеd by рhpВB © 2001, 2005 рhpВB Grouр
Русская поддержка phрВB
Rambler's Top100 Рейтинг@Mail.ru Список форумов BOSSForum.RU