Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как из MSSQL создать Excel-файл?  [new]
-SWAN-
Member

Откуда: Оделся, обулся - и на работу вернулся!)
Сообщений: 207
О ГУРУ MSSQL, Здравствуйте!
Подскажите, как из MSSQL создать Excel-файл?
Заполнить уже имеющийся Excel-файл я могу, но для этого файл уже должен быть создан:
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 8.0;Database=\\WDMYCLOUD\Public\обмен\tmpLebed\testLEBED.xlsx;', 
    'SELECT * FROM [Лист1$]') select [name] from [FB].[dbo].[Brand]


Спасибо заранее за любую помощь!
24 июл 19, 10:59    [21933305]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
-SWAN-,

Существует - использование CLR процедур, ReportingServices (рисуете отчет) или Integration Services (C# скрипт для рыбы + выгрузка потоками).
24 июл 19, 13:58    [21933553]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
-SWAN-
Member

Откуда: Оделся, обулся - и на работу вернулся!)
Сообщений: 207
нашел вот такой вариант одновременного создания и заполнения Excel (правда только .xls , с .xlsx не получается):
declare @sql varchar(8000), @file_name varchar(100)
set @file_name='\\WDMYCLOUD\Public\обмен\tmpLebed\MYNEWLEBEDTEST.xls'

set @sql='exec master..xp_cmdshell ''bcp "select * from dbp.[dbo].[tblCDEK_CitiesFORTEST] " queryout "' + @file_name +'" -C ACP -T -c'''
exec(@sql)

ещё минус этого варианта - не выводятся наименования столбцов (может есть способ создания названия столбцов?)
24 июл 19, 14:13    [21933566]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
-SWAN-,

первая ссылка в гугле

https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file
24 июл 19, 14:14    [21933568]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
-SWAN-
Member

Откуда: Оделся, обулся - и на работу вернулся!)
Сообщений: 207
TaPaK, спасибо за ссылку. Как раз копаю её уже час)))

Но тут есть минус - я создаю эту процедуру в одной БД, а вытаскиваю данные из другой - в этом случае названия столбцов отсутствуют.
24 июл 19, 14:22    [21933576]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Почему не сделать через SSRS? Клепаете rdl + подписка с выгрузкой на FTP
24 июл 19, 16:01    [21933682]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
Sergey Syrovatchenko,

народ предпочитает нестабильные решения и с дырами в безопасности.
24 июл 19, 16:51    [21933727]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
uaggster
Member

Откуда:
Сообщений: 827
Для простых выгрузок - используйте excel xml 2003.
По форуму бегала вполне рабочая процедура, генерирующая xml файл в формате exel xml 2003.
С т.з. пользователя - разницы никакой. Также по двум кликам экселям открывается.

+
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------
-- Usage:
-- 
-- Declare @ret int
-- exec @ret = [dbo].[OutToExcelFile] 'dbo.test', '', 'D:\MMSSQL\test.xml'
-- Select @ret
-- 
-- Параметры:
-- @object - table, view или функция, возвращающая табличное значение
-- @query - дополнительный отбор, должен выглядеть как 'Where ... and ...'
-- @path - Имя файла и путь. Папка может быть сетевой, но SQLSERVER должен иметь в нее разрешения. 
---------------------------------------

CREATE proc [dbo].[OutToExcelFile]
	 @object sysname
	,@query nvarchar(4000)
	,@path sysname
as
Begin
	set nocount on;
	set xact_abort on;
	
	DECLARE	@Result Int
	Declare @Command nvarchar(4000)

	SET	 @Command = 'bcp "' 
	+ Replace(Replace(dbo.fnExcelQuery(@object, @query),'"',''''''),'
',' ') -- должно быть именно так, это перевод строки!			
	+ '" queryout "' + @path + '" -S . -T -q -w'

	--print @Command
	EXEC @Result = master.dbo.xp_CmdShell @Command, no_output
		
	IF (@Result = 0) return 0
		ELSE return -1 -- была ошибка
End
GO

CREATE FUNCTION [dbo].[fnExcelQuery] (
	 @Object	SysName
	,@Query		NVarChar(max)
) RETURNS NVarChar(max) AS BEGIN RETURN ((
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">' + RePlace((SELECT C.name AS [*] FOR XML Path('')),'''','&apos;') + '</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"),sql:variable("@Query"),"
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],"   &lt;Row&gt;
   ",(/Name/text())[1],"   &lt;/Row&gt;
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
) END
GO


Авторство не моё, на этом же форуме нашел.
Автору - спасибо!

Сообщение было отредактировано: 25 июл 19, 12:32
25 июл 19, 08:32    [21934014]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5957
uaggster
По форуму бегала вполне рабочая процедура, генерирующая xml файл в формате exel xml 2003.

Всегда удивляло желание сделать что угодно непременно средствами сервера - отправить XmlHttpRequest, что-то сделать в AD, или вот как здесь - эксель сгенерить. Зачем нужен этот хардкор с динамическим XQuery, когда вполне можно задействовать CLR-процедуру, или внешнюю программу, которая в лаконичном и удобоваримом виде сделает то же самое с помощью нормально читаемого XSL-шаблона (или с помощью всего предложенного выше).
25 июл 19, 10:12    [21934068]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
Сон Веры Павловны,

объясняется просто - всё от недостатка образования.
25 июл 19, 12:09    [21934217]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
-SWAN-
Member

Откуда: Оделся, обулся - и на работу вернулся!)
Сообщений: 207
Владислав Колосов
Sergey Syrovatchenko,

народ предпочитает нестабильные решения и с дырами в безопасности.


Объясните, пожалуйста, где же в том коде, что я нашел, дыра безопасности? И почему решение нестабильно?

У меня задача изначально стояла такая - автоматически из MSSQL (задание по расписанию) генерить и отправлять письма с вложениями (Excel-файлы) с данными из Сайта и MSSQL и нашего клиента MS Access.
Можете предложить другой вариант решения?
25 июл 19, 13:04    [21934280]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33682
Блог
-SWAN-
У меня задача изначально стояла такая - автоматически из MSSQL (задание по расписанию) генерить и отправлять письма с вложениями (Excel-файлы) с данными из Сайта и MSSQL и нашего клиента MS Access.
Можете предложить другой вариант решения?


Джоб и SSIS-пакет
25 июл 19, 13:16    [21934300]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
court
Member

Откуда:
Сообщений: 2018
-SWAN-
У меня задача изначально стояла такая - автоматически из MSSQL (задание по расписанию) генерить и отправлять письма с вложениями (Excel-файлы) с данными из Сайта и MSSQL и нашего клиента MS Access.
Можете предложить другой вариант решения?

SSRS-отчет и подписка

в виде бонуса получишь возможность разных экселевских "бантиков" в отчете, в плане шрифтов, фонов и т.д.
25 июл 19, 13:23    [21934314]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
-SWAN-
Member

Откуда: Оделся, обулся - и на работу вернулся!)
Сообщений: 207
Джоб и SSIS-пакет
SSRS-отчет и подписка
...
Данными средствами возможно "вытянуть данные" с сайта (MySQL) и одновременно запустить пару-тройку процедур в MSSQL?
А после этого сформировать письмо на Email Получателя (с вложениями) с определенного почтового адреса?
25 июл 19, 13:42    [21934340]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
-SWAN-
Джоб и SSIS-пакет
SSRS-отчет и подписка
...
Данными средствами возможно "вытянуть данные" с сайта (MySQL) и одновременно запустить пару-тройку процедур в MSSQL?
А после этого сформировать письмо на Email Получателя (с вложениями) с определенного почтового адреса?
SSIS-пакетом можно.

Но, конечно, всё зависит от конкретной задачи.

1) Вариант от uaggster очень хорош, быстрый, стабильный и надёжный, но не позволяет конструировать документы по шаблонам.

2) Решение на SSIS более трудоёмко, громоздко, требует более квалифицированного программиста, то есть дороже. Медленее вносить изменения. Медленнее работает (ИМХО). Ну и сложнее (существенно дороже) в эксплуатации.
Но зато с шаблонами, а это очень ценно.

3) Вариант с OPENROWSET, как любой вариант с работающим из MSSQL Эксель-провайдером, ненадёжный и требует плясок с драйверами (притом на сервере!). Но зато три строки кода. Но заставить работать эти три строки кода дано не каждому.
Вот одно из обсуждений: https://stackoverflow.com/questions/909933/sql-server-export-to-excel-with-openrowset
, и там такой код (провайдер, как видите, другой):
DECLARE @myfile varchar(800)

SET @myfile = 'C:\template.xls'

EXEC ('
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=' + @myfile + ';'', 
''SELECT * FROM [SheetName$]'') 
select * from myTable
')
25 июл 19, 16:49    [21934604]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
zindur
Member

Откуда:
Сообщений: 86
правда не совсем "Excel" но можно сохранить как CSV файл - правда надо немножко поправить скрипт

25 июл 19, 18:46    [21934717]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2704
xlsx это зазипованный xml с которым SQL Server прекрасно работает
(так-же как и наличие возможности вызова внешних команд/скриптов, cmd PowerShell и пр.),
так что рассыпай ворох граблей, изобретай велосипед и начинай там ездить сколько угодно.
хоть как советуют выше - сначала в CSV а потом конвертация в xlsx
25 июл 19, 19:10    [21934735]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
просто копируйте пустой файл на место его и открывайте
26 июл 19, 03:59    [21934942]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
zindur
Member

Откуда:
Сообщений: 86
zindur
правда не совсем "Excel" но можно сохранить как CSV файл - правда надо немножко поправить скрипт

DumpDataFromTable


почемуто линк потерялся по пути

DumpDataFromTable
26 июл 19, 17:33    [21935639]     Ответить | Цитировать Сообщить модератору
 Re: Как из MSSQL создать Excel-файл?  [new]
uaggster
Member

Откуда:
Сообщений: 827
Сон Веры Павловны
uaggster
По форуму бегала вполне рабочая процедура, генерирующая xml файл в формате exel xml 2003.

Всегда удивляло желание сделать что угодно непременно средствами сервера - отправить XmlHttpRequest, что-то сделать в AD, или вот как здесь - эксель сгенерить. Зачем нужен этот хардкор с динамическим XQuery, когда вполне можно задействовать CLR-процедуру, или внешнюю программу, которая в лаконичном и удобоваримом виде сделает то же самое с помощью нормально читаемого XSL-шаблона (или с помощью всего предложенного выше).

Ну, во-первых, хардкор с динамическим sql - там только с вызовом bcp из текста хранимой процедуры.
Если на сервере взведено Filestream, то ничего этого не нужно, можно создавать файлы прямо в filetable одним запросом.
А во вторых - чем сборка "выходного" xml, сделанная таким образом хуже, и малочитабельней чем xslt преобразование, которое, к тому же, выполняется черте где?
29 июл 19, 11:04    [21936659]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить