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

Откуда:
Сообщений: 566
Добрый день!
Встала задача сохранять данные таблицы в файл Excel, причем в определенном месте таблицы хранятся ссылки.
Циклично в программе это реализовано, но объем большой, и выгружается долго.
А есть ли возможность средствами SQL в поле ячейки Excel добавлять гиперссылку из определенного поля таблицы SQL Server?
Для выгрузки данных использую :
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database={{NAMEFILE}}','SELECT * FROM [Лист1$]') select * from {{TABLE}}
Спасибо за советы.
17 окт 13, 16:14    [14986435]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
SQL Server ничего не знает про гиперссылки. Для него значение в поле - это набор байт. Делайте не экспорт в Excel, а импорт из SQL Server Excelом и форматируйте нужные Вам поля макросом.

Сообщение было отредактировано: 17 окт 13, 16:18
17 окт 13, 16:17    [14986463]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
user89
Member

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

можно сделать select из таблицы, а на выходе получить строку в формате HTML. Она легко откроется в Excel-e. Ниже пример, который содержит гиперссылки. Скорость работы for xml path('') высокая.
+ Строку @xls скопируйте в пустой текстовый файл. Этому файлу дать расширение XLS
set nocount on

declare @xls varchar(max)
declare @t table (a varchar(max), h varchar(max))
insert @t(a,h) values ('Строка 1', 'https://www.sql.ru/'), ('Строка 2', 'http://google.ru/'), ('Строка 1', null)
--select * from @t

select @xls = '<html><head>' +char(13)+char(10)+
'<style type="text/css">' +
'table {' +char(13)+char(10)+
'  font-family:Arial,sans-serif;' +char(13)+char(10)+
'  font-size:10pt;' +char(13)+char(10)+
'  border-collapse:collapse;' +char(13)+char(10)+
'}' +char(13)+char(10)+
'td {' +char(13)+char(10)+
'  border:0.5pt solid #b9c0d4;' +char(13)+char(10)+
'}' +char(13)+char(10)+
'</style></head>' +char(13)+char(10)+
'<body><table widht=2000 cellpadding=3 cellspacing=0>' +char(13)+char(10)+
(
  select '<tr><td>' + isnull(a,'') + '</td><td>' + isnull('<a href="' + h + '">' + h + '</a>','') + '</td></tr>' +char(13)+char(10)
  from @t
  for xml path(''), type
).value('text()[1]','varchar(max)')
+ '</table></body></html>'

print @xls

Если такой способ заинтересует, то я могу дать примеры, как в HTML для Excel сделать альбомную ориентацию страницы, поля для печати и т.д. Там несложно.
17 окт 13, 17:32    [14987073]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89,
Спасибо, хороший способ. По Вашем примеру у меня получилось.
Но как сделать, чтобы значение и гиперссылка были в одной ячейке (аналог в Excel =ГИПЕРССЫЛКА(Адрес;Имя))...я в html не силен.
18 окт 13, 10:35    [14996147]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89,
Спасибо...сам уже разобрался. Все зарабатало!!!
18 окт 13, 10:42    [14996214]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89,
А Вы не подскажите, может существует обратный способ загрузки данных из Excel в SQL через HTML с раскидыванием Ссылки и Имени в разные поля?
18 окт 13, 11:15    [14996502]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
user89
Member

Откуда:
Сообщений: 2083
Lexx_SQL,
+ содержимое файла d:\t.xls
<html><head>
<style type="text/css">table {
  font-family:Arial,sans-serif;
  font-size:10pt;
  border-collapse:collapse;
}
td {
  border:0.5pt solid #b9c0d4;
}
</style></head>
<body><table widht=2000 cellpadding=3 cellspacing=0>
<tr><td>Строка 1</td><td><a href="https://www.sql.ru/">Хороший сайт</a></td></tr>
<tr><td>Строка 2</td><td><a href="http://google.ru/">Поисковик</a></td></tr>
<tr><td>Строка 3</td><td></td></tr>
</table></body></html>

Считываем файл d:\t.xls сначала в переменную @b, потом ищем блок c данными <tr><td>...</td></tr>
set nocount on

declare @x xml, @b varchar(max)
select @b = BulkColumn from openrowset(bulk 'd:\t.xls', single_blob) as x
select @x = rtrim(ltrim(substring(@b, charindex('<tr',@b), charindex('</table',@b)-charindex('<tr',@b))))

select n.value('td[1]', 'varchar(max)') [f1],
n.value('(td/a)[1]/@href', 'varchar(max)') [f2],
n.value('(td/a/text())[1]', 'varchar(max)') [f3]
from @x.nodes('/tr') t(n)
Результат:
f1f2f3
Строка 1https://www.sql.ru/Хороший сайт
Строка 2http://google.ru/Поисковик
Строка 3NULLNULL

З.Ы. Я в XML-запросах не очень силен...
18 окт 13, 12:45    [14997477]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89,
Спасибо...
18 окт 13, 12:59    [14997631]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89,
извиняюсь за беспокойство. Я уперся в проблему выгрузки полученной строки в файл.
Делаю запросом:
SET @cmd='sqlcmd -S SBS -E -h -1 -Q "Select '+@var+'" -o F:\ZImp_1.txt -u -W -s '
EXEC @result = ZImport..xp_cmdshell @cmd, no_output

Но ничего не выгружается. Хотя если подставить какой нибудь обычный запрос(например, Select * from Table) , то все нормально.
Как Вы выгружали строку в файл?
Спасибо.
21 окт 13, 09:35    [15006353]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Glory
Member

Откуда:
Сообщений: 104751
Lexx_SQL
Но ничего не выгружается

А вы прежде, чем выполнять динамически собранную строку, не пробовали вывести на то, что в этой строке получилось ?
И зачем no_output ? Чтобы сообщений не видеть ?

Сообщение было отредактировано: 21 окт 13, 09:43
21 окт 13, 09:41    [15006379]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Glory
Member

Откуда:
Сообщений: 104751
И что за ZImport..xp_cmdshell ? У вас какая то своя, не системная xp_cmdshell ?
21 окт 13, 09:42    [15006383]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
Glory,
Вот результаты.

Select @var
<html><head>  <style type="text/css">table {    font-family:Arial,sans-serif;    font-size:10pt;    border-collapse:collapse;  }  td {    border:0.5pt solid #b9c0d4;  }  </style></head>  <body><table widht=2000 cellpadding=3 cellspacing=0>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr> 
... 

Select @cmd
sqlcmd -S SBS -E -h -1 -Q "Select <html><head>  <style type="text/css">table {    font-family:Arial,sans-serif;    font-size:10pt;    border-collapse:collapse;  }  td {    border:0.5pt solid #b9c0d4;  }  </style></head>  <body><table widht=2000 cellpadding=3 cellspacing=0>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr></tr>  <tr><
...

Output
Msg 105, Level 15, State 1, Server SBS, Line 1
Unclosed quotation mark after the character string 'Select '<html><head>
'.
Msg 102, Level 15, State 1, Server SBS, Line 1
Incorrect syntax near 'Select '<html><head>
'.
NULL

Длина переменной @var =390873 , @cmd =390933, строка обрезалась.
Переделал
DECLARE @cmd varchar(max)
....
...
Set @cmd='sqlcmd -S SBS -E -h -1 -Q "Select '''+@var+'''" -o F:\ZImp_1.txt -u -W '
EXEC xp_cmdshell @cmd


Теперь ошибка
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.


Насколько я понимаю, необходима командная строка:
command_string -тип varchar(8000) или nvarchar(4000)
А у меня намного больше. Что делать?
21 окт 13, 10:10    [15006510]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
user89
Member

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

no_output - убрать из запроса.
Вы, допустим, уже получили переменную @var, которая представляет собой HTML-текст. Потом эту переменную надо сохранить во временную таблицу, но с двумя "решетками" ##, иначе динамический SQL не прокатит.
Попробуйте такой вариант
if object_id('tempdb..##res_tmp_email') is not null drop table ##res_tmp_email
select @var [b] into ##res_tmp_email
select @FileName = 'c:\Temp\MyFile.xls'

select @cmd = 'bcp "select b from ##res_tmp_email" queryout "' + @FileName + '" -c -T -C1251'
exec master.dbo.xp_cmdshell @cmd

if object_id('tempdb..##res_tmp_email') is not null drop table ##res_tmp_email
21 окт 13, 10:10    [15006513]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Glory
Member

Откуда:
Сообщений: 104751
Lexx_SQL
Вот результаты.

Select @var

И как вы себе представляете выполнение запроса

Select <html><head> <style type="text/css">table { font-family:Arial,sans-serif; font-size:10pt; border-collapse:collapse; } td { border:0.5pt solid #b9c0d4; } </style></head> <body><table widht=2000 cellpadding=3 cellspacing=0> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr></tr> <tr><
21 окт 13, 10:14    [15006522]     Ответить | Цитировать Сообщить модератору
 Re: Выгрузка в Excel гиперссылок  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
user89, спасибо. Получилось.
Очень конечно необычный способ выгрузки данных в xls.
Огромное спасибо за помощь.
21 окт 13, 10:37    [15006618]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить