|
Можно ли заставить функцию Mnior'а по созданию Excel xml 2003 файла работать с #tmp?
#39859716
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Вот всем известная (на этом форуме) функция по выводу содержимого табличной функции/view/таблицы в виде Excel-xml-2003:
Автоматизация вывода отчетов в файлы xls (Excel)
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.
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
Вопрос:
А можно ли ее заставить работать с временной таблицей?
Переписать каким то таким образом:
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.
Create table #tmp (id int, com nvarchar(255), dt date)
insert into #tmp
Values (1, 'Два', Cast('20190101' as date))
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 tempdb.sys.columns C
WHERE C.[object_id] = object_id('tempdb.dbo.#tmp')
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 ",#tmp," 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=""",out,""">
<Table>
",(/Style/text())[1],"
<Row>",(/Name/text())[1],"</Row>
{/Table/Row}
</Table>
</Worksheet>
</Workbook>''))")','NVarChar(max)')
Как обозначить здесь: FROM ",#tmp," WITH(NoLock) - что выборка идет из временной таблицы??? |
|
|
