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

Откуда: Казань
Сообщений: 123
Всем добрый вечер.

Поиском находится масса информации, но можно спросить, какие сейчас моды?)

Раз в секунду (лучше чаще) нужно в открытой книге Excel обновлять таблицу.

На текущий момент находил варианты:
1. Настроенный QueryTable обновлять в самом Excel циклическим макросом
2. Рулить Excel из SQL через вызовы sp_OA*

SSIS пока не топтал, не коннектится почему-то

Что из этого более похоже будет на DDE вывод или может быть есть DDE, которого я не нашел? )

Приклад в итоге будет перевезен в SQL, но пока для ускорения нужен костыль вот такой.
Подскажите плз предпочтительный вариант.
29 апр 19, 23:37    [21875027]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33231
Блог
dklim.kzn,

Зависит от...
Может у вас этот эксель будет открыт все это время. А может закрыт. А может у вас там пару сотен тысяч строк...
29 апр 19, 23:58    [21875036]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Критик
dklim.kzn,

Зависит от...
Может у вас этот эксель будет открыт все это время. А может закрыт. А может у вас там пару сотен тысяч строк...


Эксель открыт всё время. Там на Calculate макрос работает.
Строк немного, до 100 точно, и недлинные.
В общем-то можно и по sp_OA*
И даже одно значение кидать
Потом в экселе разобрать прямо формулами на листе или типа того

Но если во время не упрусь - то предпочел бы табличку выливать апдейтом или типа того
Фоновое обновление QueryTable в 1 минуту минимум - не устраивает
30 апр 19, 00:06    [21875041]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
SSIS запустил, пакет создался и отработал
Это он такой неторопливый в SMSS ?
Если вызывать в процедуре - сможет чаще секунды прокручиваться?
30 апр 19, 00:09    [21875043]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
Зачем в этой схеме эксель с какой-то там логикой на макросах, совершенно не понятно.
30 апр 19, 00:10    [21875045]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Гавриленко Сергей Алексеевич
Зачем в этой схеме эксель с какой-то там логикой на макросах, совершенно не понятно.


абсолютно незачем
и потом эксель будет исключен
но пока нет времени всё переписывать
а эксель с работающими макросами есть

так что мне просто надо ему докинуть поток данных из sql
30 апр 19, 00:22    [21875049]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
SSIS не пишет в открытую книгу, печалька
а из закрытой данные не выцепляются сами
видимо, через SSIS не стоит
30 апр 19, 00:25    [21875052]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
Исключите эксель сразу, не майтесь фигней.
30 апр 19, 00:27    [21875055]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Гавриленко Сергей Алексеевич
Исключите эксель сразу, не майтесь фигней.


Костылизм не только лишь непобедим, но и полезен в количествах не более двух.

UPDATE OPENROWSET
(           
            'Microsoft.ACE.OLEDB.12.0',
            'EXCEL 12.0; database=C:\X\x.xls',
            'SELECT * FROM [S1$]'
)
set a=3

выполняется в среднем за 0,1с, что в целом радует
но таки предполагает циклический макрос в основной книге в варианте подсасывать туда из закрытой быстро массивом
но всё-же быстрее будет просто брать соединением из основной

а это уже недокостыль и чревато переходом временного варианта в постоянный)))
30 апр 19, 00:50    [21875063]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
Каждый волен упарываться по-своему.

Сообщение было отредактировано: 30 апр 19, 00:52
30 апр 19, 00:52    [21875064]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Гавриленко Сергей Алексеевич
Каждый волен упарываться по-своему.


Ой, да

Sqlclr вот еще как замена sp_oa*
30 апр 19, 01:18    [21875069]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
redsunrise
Member

Откуда: kazan
Сообщений: 16
dklim.kzn,

А почему не сделать подключение к таблице sql server прямо из excel, там есть такой функционал, и обновляйте таблицу либо при открытии файла либо по кнопке обновить, если только такое решение вам подходит.
30 апр 19, 11:33    [21875363]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
VBA:
Public Sub GetData
  Dim cn As ADODB.Connection, rs As ADODB.Recordset
  Dim sql As String
  Dim cnt As Variant, idx As Integer
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Provider=SQLOLEDB;Data Source=ServerIPOrName;Initial Catalog=DatabaseName;Trusted_connection=yes;"
  cn.Open
  sql = "SELECT * FROM dbo.myTable"
  Set rs = New ADODB.Recordset
  rs.Open sql, cn
  myWorksheet.Range("A1").CopyFromRecordset rs
End Sub
30 апр 19, 12:39    [21875444]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
redsunrise
dklim.kzn,

А почему не сделать подключение к таблице sql server прямо из excel, там есть такой функционал, и обновляйте таблицу либо при открытии файла либо по кнопке обновить, если только такое решение вам подходит.


да, видимо, так и придется, но я было бы круто из sql )))

на самом деле в основном для интереса
но сложно, забью, пожалуй

объект создается, а файл не создает, пишет места нет)
хотя учетка уже в админы включена и т.п.

была еще мысль действительно из макроса экселя сообщить серверу телефончик, чтобы тот звонил
то есть одним запросом сообщить свой токен для доступа
чтобы сервер далее сам писал в эту же книгу по этому токену через sp_OAMethod сразу как соберется
иначе возникают вопросы синхронизации и т.п.
ну не сильно критично, но было бы прикольнее


однако, я так и не выудил ole токен, нужный для sp_OAMethod

Public Declare PtrSafe Function GetWindowThreadProcessId Lib "User32" (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As LongPtr

Sub a()
  Dim ThreadID As LongPtr
  Dim ProcIdXL As Long

  ProcIdXL = 0
  ThreadID = GetWindowThreadProcessId(GetObject(, "Excel.Application").hwnd, ProcIdXL)

End Sub


с полученным значением SQL сделать ничего не может
видимо, у него свои токены или типа того
в запущенном экселе значение получается 17680, например, а когда SQL сам по sp_OACreate делает - 16711422
1 май 19, 11:28    [21876207]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Руслан Дамирович,

спасибо, это всё можно примерно и через sp_OA*

однако, если книга открыта - то ошибка открытия
если закрыта - то толку нет
она обновляется, а связанные ячейки в открытой книге - нет, даже при пересчете по F9
и, конечно, речи не идет про "автоматически"
а в макросе всё же проще напрямую данные получать, чем из соседней книги обновление запрашивать

хотя само по себе время выполнения и радует, менее 0,1с для одного значения
но как всегда - только для презентаций

UPDATE OPENROWSET
(           
            'Microsoft.ACE.OLEDB.16.0',
            'EXCEL 12.0; database=C:\_dir\_file.xls',
            'SELECT * FROM [S1$]'
)
set a=5
1 май 19, 11:57    [21876219]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
Synoptic
Member

Откуда:
Сообщений: 141
Чёт не пойму в чём проблема?
1. Через ADO/DAO получаем данные от сервера, размещаем на листе.
2. Обновляем лист.
3. Пауза
И так по кругу.
1 май 19, 12:24    [21876241]     Ответить | Цитировать Сообщить модератору
 Re: Трансляция/высокочастотное обновление таблицы из MSSQL в Excel  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Synoptic
Чёт не пойму в чём проблема?
1. Через ADO/DAO получаем данные от сервера, размещаем на листе.
2. Обновляем лист.
3. Пауза
И так по кругу.


это всё vb в самом ёкселе
а была мысль в него писать внешне из sql по ole
но утухла)

так то вроде и п.2 не нужен в списке
1 май 19, 13:57    [21876326]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить