Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 3 4 вперед Ctrl→ все |
papa1 Member Откуда: Сообщений: 70 |
SQL 2005. Задача: автоматизировать создание отчетов в xls формате. В день 40-90 файлов - ручной копипаст занимает 2 часа. Шапки отчетов не трогать (цветастые и форматные). Шапка занимает первую строку листа xls. В файле xls по 3-4 отчета - каждый на своем листе. Вопрос : Integration Services ? Ps: как не трогать форматирование шапок.... |
18 ноя 09, 15:07 [7945645] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Report Server |
18 ноя 09, 15:10 [7945663] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47045 |
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=713043 ![]() |
18 ноя 09, 15:17 [7945729] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3015 |
если заголовки в одной строке, то проблем вообще не будет если заголовки многострочные, то можно под ними определить "именованный диапозон" (присвоить имя) и пихать в него, тогда заголовки не испортятся I Have Nine Lives You Have One Only THINK! |
||
18 ноя 09, 15:27 [7945811] Ответить | Цитировать Сообщить модератору |
papa1 Member Откуда: Сообщений: 70 |
Хотелось чтобы интерфейс к "хозяйству" был через хранимую процедуру с примерно таким набором параметров... ( @Query varchar(max) /* запрос, который формирует набор для отчета*/ , @File varchar(255) /* файл xls */ , @Sheet varchar(40) /* лист xls файла*/ ) Идея : 1 раз написать и при добавлении новых отчетов не париться... |
18 ноя 09, 15:27 [7945814] Ответить | Цитировать Сообщить модератору |
papa1 Member Откуда: Сообщений: 70 |
Спасибо за советы , пойду штудировать Report Service. |
18 ноя 09, 15:30 [7945839] Ответить | Цитировать Сообщить модератору |
vanoman Member Откуда: Нижний Новгород Сообщений: 845 |
Можем... один из сотни отчётов (см. вложение) К сообщению приложен файл. Размер - 0Kb |
||
18 ноя 09, 15:35 [7945883] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Сказать "в Excel" - ничего не сказать. Форматов то много. Например, можно воспользоваться форматом Excel XML spreadsheet. И фигарить какой вам нужно файл, с каким вам нужно оформлением. Хотите, могу скинуть набросок генератора файлов, а вы уже подправите под ваши нужды (шапки, шаблоны ...). И никаких компонентов - нативный SQL. |
18 ноя 09, 21:23 [7947523] Ответить | Цитировать Сообщить модератору |
papa1 Member Откуда: Сообщений: 70 |
Mnior, Спасибо за помощь. Но нужен именно xls формат Excel'я, нужен для обмена с другой организацией, где есть только тетки с офисом. Они разбирают отчеты в Excel и в случае чего ссылаются на входную корреспондецию. Поэтому не нужен никакой другой промежуточный транспорт. |
19 ноя 09, 14:53 [7951247] Ответить | Цитировать Сообщить модератору |
_интеpесующийся
Guest |
Mnior, будьте добры, поделитесь с общественностью наброском генератора файлов. По первому впечатлению, генерация Excel XML spreadsheet - очень интересная тема. |
19 ноя 09, 17:05 [7952484] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Функция создаёт шаблон запроса на основе декларации (представления/функции/таблицы):CREATE FUNCTION [dbo].[fnExcelQuery] ( @Object SysName ) RETURNS NVarChar(max) AS BEGIN RETURN ( SELECT ( SELECT CASE WHEN C.column_id = 1 THEN ' ' ELSE ' ,' END + 'NULL AS [text()],' + CASE WHEN C.system_type_id IN (48,52,56,59,60,62,106,108,122,127) THEN '''Number'' ' WHEN C.system_type_id IN (58,61) THEN '''DateTime''' WHEN C.system_type_id = 104 THEN '''Boolean'' ' ELSE '''String'' ' END + 'AS [Cell/Data/@s:Type],' + QuoteName(C.name) + Space(Max(Len(C.name))OVER() - Len(C.name)) + ' AS [Cell/Data/text()]' AS [Row/text()] , IsNull('<Column s:StyleID="' + CASE WHEN C.system_type_id IN (48,52,56,59,62,106,108,127) THEN 'Number' WHEN C.system_type_id IN (60,122) THEN 'Currency' WHEN C.system_type_id IN (58,61) THEN 'DateTime' WHEN C.system_type_id = 104 THEN 'Boolean' END + '" />','<Column />') AS [Style/text()] ,'<Cell><Data s:Type="String">' + C.name + '</Data></Cell>' AS [Name/text()] FROM sys.columns C WHERE C.[object_id] = Object_ID(@Object) ORDER BY C.column_id FOR XML Path(''),Type).query('(<Row>{/Row/text()}</Row>,<Style>{/Style/text()}</Style>,<Name>{/Name/text()}</Name>)').value('fn:concat(" ;WITH XMLNAMESPACES(Default ''urn:schemas-microsoft-com:office:spreadsheet'',''urn:schemas-microsoft-com:office:spreadsheet'' AS s) SELECT N''<?xml version=""1.0"" encoding=""UTF-16""?><?mso-application progid=""Excel.Sheet""?>'' + Convert(NVarChar(max),IsNull(( SELECT ",(/Row/text())[1]," FROM ",sql:variable("@Object")," WITH(NoLock) FOR XML Path(''Row''),Root(''Table''),Type),'''').query('' <Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:s=""urn:schemas-microsoft-com:office:spreadsheet""> <Styles> <Style s:ID=""Boolean""><NumberFormat s:Format=""True/False"" /></Style> <Style s:ID=""Currency""><NumberFormat s:Format=""Currency"" /></Style> <Style s:ID=""Number""><NumberFormat s:Format=""General Number"" /></Style> <Style s:ID=""DateTime""><NumberFormat s:Format=""General Date"" /></Style> </Styles> <Worksheet s:Name=""",sql:variable("@Object"),"""> <Table> ",(/Style/text())[1]," <Row>",(/Name/text())[1],"</Row> {/Table/Row} </Table> </Worksheet> </Workbook>''))")','NVarChar(max)') ) END GOВозможно нуна подпилить этот черновик. Использование: DECLARE @Query NVarChar(max) SET @Query = dbo.fnExcelQuery('<ViewName>') PRINT @Query EXEC (@Query) -- Сами решайте куды запрос зафигачиватьЕго не обязательно каждый раз генерить, если набор отчётных представлений (таблиц) фиксированный - получили шаблон, поправили и поставили куда надо. Всё зависит от степени автоматизации системы. У меня варианты посложнее и ещё некоторые оформительские элементы срезал. Можно сэкономить на названии стилей. |
20 ноя 09, 17:28 [7958157] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Процедура сохранение в файл тут на форуме кажись была представлена неоднократно. А вот для отсылки письмом прямиком (@attach_query_result_as_file) как прикреплённым файлом - шаблон нужно поменять. Там ограничение на длину строки, так что каждый row отдельной строкой приходится генерить, (нарезать по {/Table/Row}) - это неплохо для микро-отчётов. А для крупного экспорта палюбому зипать. |
20 ноя 09, 20:35 [7958842] Ответить | Цитировать Сообщить модератору |
_интеpесующийся
Guest |
Mnior, спасибо, будем экспериментировать. |
21 ноя 09, 11:46 [7959599] Ответить | Цитировать Сообщить модератору |
AklexK Member Откуда: Москва Сообщений: 37 |
У нас автоматизировано с помощью Genrep (http://genrep.net/adodb.html) созданы шаблоны отчетов далее простым планировщиком запускается (можно через Job CmdExec) строчка вида dorep.exe "sab\price_ms.sab" "17" После этого в папочке шаблона формируется price_ms.rtf (или xml) |
1 дек 09, 11:58 [8001206] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
Уважаемый Mnior! подскажи плз, как результат EXEC (@Query)засунуть в файл. Результат запроса я в файл сохранять умею, например, вот так: DECLARE @result int DECLARE @cmd varchar( 150) SET @cmd = 'bcp "SELECT i.Doc_Date, i.Doc_Var FROM ReportsDB.Rpt.t_Income i" queryout "C:\1.txt" -w -C 1251 -r -T' EXEC @result = master..xp_cmdshell @cmd, no_output IF (@result = 0) PRINT 'Success' ELSE PRINT 'Failure' А с экзеком не умею пока что. И еще: можно ли функцию dbo.fnExcelQuery доработать так, чтоб ей передать процедуру, возвращающую набор нужных мне данных? И, если можно, опубликуй, плз, любой пример с оформленной шапкой (может на эл.почту?), т.к. я тоже решаю сейчас подобную задачу. Заранее спасибо! |
||
24 дек 09, 12:20 [8112354] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Для утилиты bcp в режиме queryout, для командной строки запрос придётся хорошенько примять. Подход мне не нравиться, но работает: DECLARE @Result Int ,@Command VarChar(4000) SET @Command = 'bcp "' + Replace(Replace(dbo.fnExcelQuery('<ViewName>'),'"',''''''),' ','') + '" queryout "C:\1.txt" -T -q -w -r -t' EXEC @Result = master.dbo.xp_CmdShell @Command IF (@Result = 0) PRINT 'Success'; ELSE PRINT 'Failure';Как вы понимаете в командной строке запрос не должен содержать двойные кавычки, т.к. они являются терминатором запроса, аналогично с переводом каретки. Но это не самое главное. Вы, как я вижу из примера, запрос к bcp не генерируете динамически, а пишете явно один раз. Поэтому, выполните один раз функцию dbo.fnExcelQuery и результат как текст вставляйте где вам надо. Если запрос генерируется динамически, вам придётся допилить функцию под себя. Хотябы добавить подстановку условий отбора (WHERE).
Если вы хотите, чтоб Excel файл генерировался на лету в RunTime без заранее известных деклараций, то это можно сделать только на клиенте (допустим в CLR). Например, на .Net после получения очередного SqlReader-а, прочитать его схему, наваять шапку и вперёд читать до упора в потоке. Такое делал, но не для Excel, а для Delhi-йского XML-я, строк кода не много, кажись даже меньше, чем в программе конвертации DBF <-> XML. ![]() |
||||
24 дек 09, 22:50 [8116044] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
Спасибо огромное за разъяснения!!!
Да я это понял, просто не знал, каким образом в файл это дело запихнуть.
Здесь я привел пример "не динамический", т.к. именно с ним я эксперементировал, чтоб понять, как это работает. А вообще моя мысль генерировать шапку именно динамически, я по этому поводу задавал вопрос в форуме asp.net. Смысл в том, что есть страница на сайте, где юзер настраивает параметры - и получает некие данные, которые потом надо скинуть в эксель. Так вот мысль была на основе выбранных юзером параметров сгенерировать шапку документа (в частности, закинуть в нее период формируемого отчета), а точнее шаблон xslt, а затем в этот шаблон уже скинуть сами данные. Я этим занимаюсь впервые, поэтому много вопросов и непоняток. Но ваш пост на многое пролил свет, спасибо еще раз :)
В принципе, я тоже так думал, но на всякий случай уточнил.
А нельзя ли как то глянуть пример такого "чтения схемы"? Может на почту, чтоб тут не засорять эфир. Я еще раз уточняю, какая у меня сложность - именно динамически генерировать шапку документа в шаблоне, т.е. простановка дат и прочей лабуды. |
||||||||
25 дек 09, 07:36 [8116472] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Я, канешно, понимаю, что XML и прочие радости - это круто... НО! Чем не устраивает ВСТРОЕННЫЙ импорт данных Excel? По виду ваших отчетов не скажешь, шо там что-то невероятное надо... |
25 дек 09, 08:28 [8116555] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
встроенный куда? |
25 дек 09, 08:32 [8116563] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
В Excel, вестимо. |
||
25 дек 09, 08:43 [8116595] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
тем, что пользователь работает на сайте - там он формирует нужные данные. а потом их пуляет в эксель |
25 дек 09, 08:53 [8116625] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Хе-хе... Ну и чо?
Уже сайт сделали? Учите теперь OLE Automation... или ReportingService. |
||||
25 дек 09, 09:02 [8116658] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
а в чем преимущество OLE Automation перед XML? Я посчитал, что OLE Automation неудобен, т.к. каждый отчет нужно программировать, любое изменение отчета надо будет лезсть в код. В то время как с XML достаточно сгенерировать шаблон и работать с ним (менять стили, добавлять надписи и т.д.) |
25 дек 09, 09:43 [8116832] Ответить | Цитировать Сообщить модератору |
YarmakIN Member Откуда: Уфа Сообщений: 321 |
Mnior, что я делаю неправильно:DECLARE @Result Int ,@Command VarChar(4000) SET @Command = 'bcp "' + Replace(Replace(dbo.fnExcelQuery('ReportsDB.RptBuh.f_Commission_GetRest()'),'"',''''''),' ','') + '" queryout "C:\1.xml" -T -q -w -r -t' EXEC @Result = master.dbo.xp_CmdShell @Command IF (@Result = 0) PRINT 'Success'; ELSE PRINT 'Failure'; CREATE FUNCTION [RptBuh].f_Commission_GetRest () RETURNS TABLE AS RETURN( SELECT r.WayBill_Var AS [№ накладной], r.WayBill_Date AS [Дата накладной], r.WeightOper_Flo AS [Количество, тн] FROM Rpt.t_RestResource r ) |
25 дек 09, 09:48 [8116870] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Дык и с OLE Automation надо только сгененрировать шаблон - excel-файл. Дык в чем преимущество XML? Есть подозрение, что ваять шаблоны в Excel-е попроще будет. Ну да воня ваша... |
||
25 дек 09, 10:55 [8117356] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 3 4 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |