Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Автоматизация вывода отчетов в файлы xls (Excel)  [new]
papa1
Member

Откуда:
Сообщений: 70
SQL 2005.
Задача: автоматизировать создание отчетов в xls формате.
В день 40-90 файлов - ручной копипаст занимает 2 часа.
Шапки отчетов не трогать (цветастые и форматные). Шапка занимает первую строку листа xls.
В файле xls по 3-4 отчета - каждый на своем листе.

Вопрос : Integration Services ?
Ps: как не трогать форматирование шапок....
18 ноя 09, 15:07    [7945645]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Report Server
18 ноя 09, 15:10    [7945663]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=713043
18 ноя 09, 15:17    [7945729]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
автор
как не трогать форматирование шапок


если заголовки в одной строке, то проблем вообще не будет
если заголовки многострочные, то можно под ними определить "именованный диапозон" (присвоить имя) и пихать в него, тогда заголовки не испортятся


I Have Nine Lives You Have One Only
THINK!
18 ноя 09, 15:27    [7945811]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
papa1
Member

Откуда:
Сообщений: 70
Хотелось чтобы интерфейс к "хозяйству" был через хранимую процедуру с примерно таким набором параметров
...
(
  @Query varchar(max) /* запрос, который формирует набор для отчета*/
  , @File varchar(255) /* файл xls */
  , @Sheet varchar(40) /* лист xls файла*/
)


Идея : 1 раз написать и при добавлении новых отчетов не париться...
18 ноя 09, 15:27    [7945814]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
papa1
Member

Откуда:
Сообщений: 70
Спасибо за советы , пойду штудировать Report Service.
18 ноя 09, 15:30    [7945839]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
iap
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=713043


Можем... один из сотни отчётов (см. вложение)

К сообщению приложен файл. Размер - 0Kb
18 ноя 09, 15:35    [7945883]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Сказать "в Excel" - ничего не сказать. Форматов то много.
Например, можно воспользоваться форматом Excel XML spreadsheet. И фигарить какой вам нужно файл, с каким вам нужно оформлением. Хотите, могу скинуть набросок генератора файлов, а вы уже подправите под ваши нужды (шапки, шаблоны ...). И никаких компонентов - нативный SQL.
18 ноя 09, 21:23    [7947523]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
papa1
Member

Откуда:
Сообщений: 70
Mnior,

Спасибо за помощь. Но нужен именно xls формат Excel'я, нужен для обмена с другой организацией, где есть только тетки с офисом. Они разбирают отчеты в Excel и в случае чего ссылаются на входную корреспондецию. Поэтому не нужен никакой другой промежуточный транспорт.
19 ноя 09, 14:53    [7951247]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
_интеpесующийся
Guest
Mnior,
будьте добры, поделитесь с общественностью наброском генератора файлов.
По первому впечатлению, генерация Excel XML spreadsheet - очень интересная тема.
19 ноя 09, 17:05    [7952484]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Процедура сохранение в файл тут на форуме кажись была представлена неоднократно.
А вот для отсылки письмом прямиком (@attach_query_result_as_file) как прикреплённым файлом - шаблон нужно поменять. Там ограничение на длину строки, так что каждый row отдельной строкой приходится генерить, (нарезать по {/Table/Row}) - это неплохо для микро-отчётов. А для крупного экспорта палюбому зипать.
20 ноя 09, 20:35    [7958842]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
_интеpесующийся
Guest
Mnior,

спасибо, будем экспериментировать.
21 ноя 09, 11:46    [7959599]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
YarmakIN
Member

Откуда: Уфа
Сообщений: 321
Mnior

DECLARE	@Query NVarChar(max)
SET	@Query = dbo.fnExcelQuery('<ViewName>')
PRINT @Query
EXEC (@Query)	-- Сами решайте куды запрос зафигачивать

Уважаемый 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]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
YarmakIN
Уважаемый 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'

А с экзеком не умею пока что.
Уважаемый YarmakIN, будьте пожайлуста внимательным. @Query содержит сам запрос. А EXEC(@Query) возвращает результат запроса - уже готовый текст файла.

Для утилиты 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).

YarmakIN
И еще: можно ли функцию dbo.fnExcelQuery доработать так, чтоб ей передать процедуру, возвращающую набор нужных мне данных?
А вы сами подумайте. Процедура это чёрный ящик, она как обезьяна с гранатой - захочет вернёт пять результатов запросов, захочет три других, а захочет дропанёт всё в чёртовой матери. Процедура не для запросов, для запросов есть представления и функции. Функция может сделать любой запрос, в ней также можно писать такие глупости как WHILE и IF - только я вам это не советую. Процедурное мышление потихоньку уходит в прошлое, не отставайте.

Если вы хотите, чтоб Excel файл генерировался на лету в RunTime без заранее известных деклараций, то это можно сделать только на клиенте (допустим в CLR). Например, на .Net после получения очередного SqlReader-а, прочитать его схему, наваять шапку и вперёд читать до упора в потоке. Такое делал, но не для Excel, а для Delhi-йского XML-я, строк кода не много, кажись даже меньше, чем в программе конвертации DBF <-> XML.
24 дек 09, 22:50    [8116044]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
YarmakIN
Member

Откуда: Уфа
Сообщений: 321
Спасибо огромное за разъяснения!!!

Mnior
Уважаемый YarmakIN, будьте пожайлуста внимательным. @Query содержит сам запрос. А EXEC(@Query) возвращает результат запроса - уже готовый текст файла.

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

Mnior

Но это не самое главное. Вы, как я вижу из примера, запрос к bcp не генерируете динамически, а пишете явно один раз. Поэтому, выполните один раз функцию dbo.fnExcelQuery и результат как текст вставляйте где вам надо.

Здесь я привел пример "не динамический", т.к. именно с ним я эксперементировал, чтоб понять, как это работает. А вообще моя мысль генерировать шапку именно динамически, я по этому поводу задавал вопрос в форуме asp.net. Смысл в том, что есть страница на сайте, где юзер настраивает параметры - и получает некие данные, которые потом надо скинуть в эксель. Так вот мысль была на основе выбранных юзером параметров сгенерировать шапку документа (в частности, закинуть в нее период формируемого отчета), а точнее шаблон xslt, а затем в этот шаблон уже скинуть сами данные. Я этим занимаюсь впервые, поэтому много вопросов и непоняток. Но ваш пост на многое пролил свет, спасибо еще раз :)

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

В принципе, я тоже так думал, но на всякий случай уточнил.

Mnior
Если вы хотите, чтоб Excel файл генерировался на лету в RunTime без заранее известных деклараций, то это можно сделать только на клиенте (допустим в CLR). Например, на .Net после получения очередного SqlReader-а, прочитать его схему, наваять шапку и вперёд читать до упора в потоке. Такое делал, но не для Excel, а для Delhi-йского XML-я, строк кода не много, кажись даже меньше, чем в программе конвертации DBF <-> XML.

А нельзя ли как то глянуть пример такого "чтения схемы"? Может на почту, чтоб тут не засорять эфир.

Я еще раз уточняю, какая у меня сложность - именно динамически генерировать шапку документа в шаблоне, т.е. простановка дат и прочей лабуды.
25 дек 09, 07:36    [8116472]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
aleks2
Guest
Я, канешно, понимаю, что XML и прочие радости - это круто...

НО! Чем не устраивает ВСТРОЕННЫЙ импорт данных Excel?

По виду ваших отчетов не скажешь, шо там что-то невероятное надо...
25 дек 09, 08:28    [8116555]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
YarmakIN
Member

Откуда: Уфа
Сообщений: 321
встроенный куда?
25 дек 09, 08:32    [8116563]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
aleks2
Guest
YarmakIN
встроенный куда?


В Excel, вестимо.
25 дек 09, 08:43    [8116595]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
YarmakIN
Member

Откуда: Уфа
Сообщений: 321
тем, что пользователь работает на сайте - там он формирует нужные данные. а потом их пуляет в эксель
25 дек 09, 08:53    [8116625]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
aleks2
Guest
YarmakIN
тем, что пользователь работает на сайте - там он формирует нужные данные. а потом их пуляет в эксель


Хе-хе... Ну и чо?

papa1

Задача: автоматизировать создание отчетов в xls формате.
В день 40-90 файлов - ручной копипаст занимает 2 часа.


Уже сайт сделали? Учите теперь OLE Automation... или ReportingService.
25 дек 09, 09:02    [8116658]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
YarmakIN
Member

Откуда: Уфа
Сообщений: 321
а в чем преимущество OLE Automation перед XML?

Я посчитал, что OLE Automation неудобен, т.к. каждый отчет нужно программировать, любое изменение отчета надо будет лезсть в код. В то время как с XML достаточно сгенерировать шаблон и работать с ним (менять стили, добавлять надписи и т.д.)
25 дек 09, 09:43    [8116832]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
aleks2
Guest
YarmakIN
а в чем преимущество OLE Automation перед XML?

Я посчитал, что OLE Automation неудобен, т.к. каждый отчет нужно программировать, любое изменение отчета надо будет лезсть в код. В то время как с XML достаточно сгенерировать шаблон и работать с ним (менять стили, добавлять надписи и т.д.)


Дык и с OLE Automation надо только сгененрировать шаблон - excel-файл.
Дык в чем преимущество XML?

Есть подозрение, что ваять шаблоны в Excel-е попроще будет.

Ну да воня ваша...
25 дек 09, 10:55    [8117356]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить