Excel ftp как пользоваться
В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.
Работаем с файлами MS Excel на Python
Для работы с Excel файлами из Python мне известны 2 варианта:
Использование библиотек
Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.
Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:
Теперь давайте посмотрим, как считать значения из нужных ячеек:
Как видно чтение данных не составляет труда. Теперь запишем их в другой файл. Для этого создам новый excel файл с новой рабочей книгой:
Запишем в новый файл полученные ранее данные и сохраним изменения:
Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).
Чтобы успешно работать с форматом xlsx, понадобится библиотека openpyxl. Для демонстрации ее работы проделаем действия, которые были показаны для предыдущих библиотек.
Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:
Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:
Отличие от прошлых библиотек в том, что openpyxl дает возможность отображаться к ячейкам и последовательностям через их имена, что довольно удобно и понятно при чтении программы.
Теперь посмотрим как нам произвести запись и сохранить данные:
Из примера видно, что запись, тоже производится довольно легко. Кроме того, в коде выше, можно заметить, что openpyxl кроме имен ячеек может работать и с их индексами.
К недостаткам данной библиотеки можно отнести, то что, как и в предыдущем примере, нет возможности сохранить изменения без создания новой книги.
Как было показано выше, для более менее полноценной работы с excel файлами, в данном случае, нужно 4 библиотеки, и это не всегда удобно. Кроме этого, возможно нужен будет доступ к VBA (допустим для какой-либо последующей обработки) и с помощью этих библиотек его не получить.
Однако, работа с этими библиотеками достаточно проста и удобна для быстрого создания Excel файлов их форматирования, но если Вам надо больше возможностей, то следующий подпункт для Вас.
Работа с com-объектом
В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.
Проиллюстрируем это на той же задаче, что и предыдущие примеры.
Для начала загрузим нужную библиотеку и создадим COM объект.
Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:
Давайте получим значение первой ячейки и последовательности:
Как можно заметить, мы оперируем здесь функциями чистого VBA. Это очень удобно если у вас есть написанные макросы и вы хотите использовать их при работе с Python при минимальных затратах на переделку кода.
Посмотрим, как можно произвести запись полученных значений:
Из примера видно, что данные операции тоже довольно просто реализовываются. Кроме этого, можно заметить, что изменения мы сохранили в той же книге, которую открыли для чтения, что достаточно удобно.
Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.
На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.
Вызываем функции Python из MS Excel
Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?
Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.
Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:
На вход ей подается список, состоящий из списков, это одно из условий, которое должно выполняться для работы данной функции в Excel.
Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.
Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.
Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:
Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:
Итак, что же происходит в данной функции?
Для начала, с помощью PyModule , мы подключаем нужный модуль. Для этого в качестве параметров ей передается имя модуля без расширения, и путь до папки в которой он находится. На выходе работы PyModule мы получаем объект для работы с модулем.
Затем, с помощью PyCall , вызываем нужную нам функцию из указанного модуля. В качестве параметров PyCall получает следующее:
- Объект модуля, полученный на предыдущем шаге
- Имя вызываемой функции
- Параметры, передаваемые функции (передаются в виде списка)
Теперь, чтобы убедиться в работоспособности нашей связки, вызовем нашу свежеиспеченую функцию на листе в Excel:
Как видно из рисунка все отработало правильно.
Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub'e автора доступна новая версия.
Заключение
В качестве заключения, надо отметить, примеры в данной статье самые простые и для более глубоко изучения данных методов, я рекомендую обратиться к
документации по нужным пакетам.
Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlsxwriter для генерации excel файлов или xlwings, который может работать с Excel файлами «на лету», а также же PyXLL, который выполняет аналогичные функции ExcelPython.
Кроме этого в статье я попытался несколько обобщить разборасанный по сети материал, т.к. такие вопросы часто фигурируют на форумах и думаю некоторым будет полезно иметь, такую «шпаргалку» под рукой.
Как именно использовать эти функции - можете посмотреть в коде надстройки для отправки файлов Excel на FTP сервер
В чем недостаток этого способа - так это в необходимости обеспечения совместимости кода с различными платформами.
В частности, чтобы код с функциями API работал и в Office 2010, и в 64-битной Windows, необходимо заметно увеличить объём кода. А, поскольку описание этих функций из wininet.dll и без того занимает много места (а универсальный код вообще займёт сотню строк), да и надо ещё и разбираться во всех этих функциях, т.к. в разных версиях Windows возможны различия в способе вызова функций из wininet.dll, и были созданы аналоги этих функций для работы с FTP, не использующие WinAPI
Сподвигла меня на это решение необходимость реализации средств обновления надстроек Excel, где необходимо было реализовать функционал отправки файлов Excel на FTP сервер, причем так, чтобы это стабильно работало на всех компьютерах. Попутно, кстати, родилась и функция загрузки файла с сервера без использования WinAPI, которая тоже вошла в состав данного инструментария.
Основу предлагаемого мной решения составляет модуль класса FTPcommander , который предоставляет вам следующие функции:
- Function DownloadFile(ByVal FtpFolder$, ByVal FtpFilename$, ByVal LocalPath$) As Boolean
(Функция скачивает файл с FTP-сервера с именем FtpFilename$ из папки FtpFolder$. Скачанный файл сохраняется на компьютере под именем (и по пути) LocalPath$. Функция возвращает TRUE, если загрузка файла завершилась успешно) - Function UploadFile(ByVal LocalPath$, Optional ByVal FtpFolder$, Optional ByVal FtpFilename$ = "") As String
(Функция загружает файл LocalPath$ по FTP на сервер в папку FtpFolder$. Если задан параметр FtpFilename$, отправленный файл получает имя FtpFilename$. Функция возвращает ссылку на файл, если закачка файла завершилась успешно) - Function CreateNewFolder(ByVal FtpFolder$) As Boolean
(Функция создаёт папку FtpFolder$ на FTP сервере. Возвращает TRUE, если папка была успешно создана, или существовала ранее) - Function DeleteFile(ByVal FtpFolder$, ByVal FtpFilename$) As Boolean
(Функция создаёт папку FtpFolder$ на FTP сервере. Возвращает TRUE, если папка была успешно создана, или существовала ранее) - Function DownloadFileFromURL(ByVal URL$, ByVal LocalPath$) As Boolean
(Функция скачивает файл по ссылке URL$, и сохраняет его по пути LocalPath$. Возвращает TRUE, если файл был успешно загружен) - Функция GetLastError возвращает информацию об ошибке, если некая функция была завершена некорректно (возвратила FALSE)
Как использовать модуль класса FTPcommander для работы с файлами по FTP:
1) Откройте прикреплённый к статье файл Excel, и мышом перетащите модуль класса FTPcommander в свой файл
2) Пропишите настройки FTP аккаунта в специальной функции, или сохраните их в реестре Windows
(подробнее об этом - ниже)
3) В своём макросе создаёте экземпляр класса FTPcommander, и используете его методы для работы с файлами
Создать экземпляр класса вам поможет следующий код: Dim FTP As New FTPcommander
Примеры макросов отправки и скачивания файлов доступны в прикреплённом файле (в модуле mod_TestFTP):
Вот один из примеров использования класса FTPcommander:
Как видите, всего 2 строки кода, - и ваш файл оказался загружен на FTP сервер.
Ещё один пример использования:
Есть 2 способа задать настройки FTP аккаунта для использования объектом FTPcommander:
1 способ - один раз запустить макрос следующего вида:
Этот макрос запишет все необходимые настройки в реестр Windows, и впоследствии будет брать их оттуда
Преимущество этого способа: один раз запустили макрос, потом удалили его, - и можно нигде в коде не прописывать секретные данные FTP аккаунта
Недостаток этого способа: при хранении настроек в реестре, возможен доступ только к одному FTP серверу
2 способ - для инициализации объекта FTPcommander использовать специальные функции с настройками:
Пример использования функции:
Преимущества этого способа: возможно работать с несколькими FTP серверами одновременно, конфиденциальные данные (настройки FTP аккаунта) не хранятся в открытом виде в реестре
Недостаток этого способа: настройки FTP аккаунта хранятся в коде VBA - если файл попадёт постороннему человеку, он легко сможет добраться до этих настроек (как известно, любые пароли на VBA ломаются за секунду)
Комментарии
Вы вводите недопустимый IP адрес, оттого и ошибка.
IP адрес:порт
Подскажите, почему адрес сервера с портом для доступа не работает?
например завожу в поле сервер 121.121455.86:2121 при проверке пишет ошибка, при удалении порта начинает проверять, но естественно ошибка, так как требуется порт.
Виталий, спасибо за отзыв
По поводу NAT, — ну, не знаю, не было у меня никогда проблем с ним. Все компы, на которых я использовал этот код, были за NAT (за роутером). Вот только без файрвола (ибо зачем создавать себе проблемы)
Т.е. если это обычный комп с интернетом, всё будет работать.
А если это организация, где есть сисадмины, которые постоянно там что-то настраивают, защищают, используют хитрые файрволы и т.п., - то да, потом фиг разберешься, что почему не работает.
Ещё раз здравствуйте! Хотел бы поделиться результатами испытаний на различных компьютерах данного этого скрипта и класса.
Вкратце, всё удручающе плохо, но не потому что автор что-то не учёл или допустил ошибку. Тут как раз наоборот, код логически блестящен, а класс построен безукоризненно на мой взгляд дилетанта.
Проблема заключается совсем другом: утилита ftp, встроенная в систему Windows, крайне капризна в плане работы через различные файрволлы с NAT, которыми изобилуют локальные подсети как у частных лиц, так и организациях. В некоторых случаях, эту утилиту (ftp) не удалось заставить работать должным образом даже при деятельном содействии нескольких системных администраторов разных корпоративных подсетей.
Решение было подсказано "внезапно" работающей программой командной строки на моём компьютере с Windows 10 (это вендорская сборка ОС от бренда, имя не указываю, чтобы не сочли за рекламу). Её имя - cURL, которая чаще привычна пользователям *NIX операционных систем. Чаще всего, cURL на Windows не предустановлен. Вкратце, основной функционал:
С использованием этой утилиты: все пляски с бубнами по поводу NAT, открытием портов, раздачей прав в политиках и проброски портов через шлюзы - сразу закончились!
Установка cURL на Windows любой версии проста и легко ищется по запросу "cURL Windows", я её устанавливал на компьютеры даже в условиях ограниченной учетной записи (т.е., без прав Администратора).
Мне пришлось модифицировать представленный Игорем код в соответствии с задачами моего конкретного случая, и полагаю, это послужит толчком для остальных пытливых пользователей этого замечательного сайта. Код публиковать не буду нигде по многим соображениям, просто подсказал путь принципиального решения проблемы.
Ещё раз спасибо Игорю - автору, программисту и администратору этого замечательного проекта!
Прежде всего хотелось бы поблагодарить Администратора этого замечательного сайта за этот скрипт. Спасибо Игорь, вы делаете хорошее дело!
Заметил, что некоторые пользователи жалуются на то, что при закачке и скачивании файла они получают файл с нулевой длиной.
С ровно такой же проблемой пришлось столкнуться и мне. При этом способ, подсказанный в обсуждении - добавить процесс Excel.exe в исключения для брандмауэра - мне не помог.
Пришлось копнуть немного глубже.
Как оказалось, при отключении брандмауэра, весь функционал класса работает вполне нормально.
Не буду рассказывать долго из-за чего это произошло и как.
Мне помогло добавление правила в исходящие и входящие соединения для файла ftp.exe , который обычно находится в папке Windows/system32/. Правило разрешает все соединения этого файла по протоколу TCP по всем портам. Для справки: у меня Windows 10 Home, которая в мае 2018 обновилась до версии 1803.
Доработки бесплатных макросов, либо консультации по их использованию, - только на платной основе.
ЭЭх, блин, я поищу конечно в нете перебор, ибо сам могу только на Js сейчас написать перебор, на VBA - к сожалению слишком вскользь сталкивался. А так - я конечно понимаю что вам это не прям надо - но мб скинули бы вариант?Или через заказ только?
Да, можно
Получаете список файлов, в цикле перебираете их, проверяя на совпадение с нужными расширениями.
Если совпадение есть, - скачиваете очередной файл
Здравствуйте. подскажите - можно ли как-то сделать загрузку всех файлов из папки по опр. шаблону (типа как по формату, *.jpg/*.ico и т.д.)
В файле с использованием WinAPI прописаны условия для разных версий VBA. А в случае с разными ОС (32 и 64 бит) всё идентично?
День добрый!
Возникла проблема при загрузке файла с фтп. если попытаться сохранить файл в корневой каталог например c:\1.xlsm то он его просто туда не записывает не выдавая никакой ошибке о доступе и т.д. там просто не появляется файла.
А вот если указать путь c:\users\public\1.xlsm то все нормально сохраняется
Здравствуйте, Иван.
Да, можно
Код под ваш случай писать лень, - в комментах его уже дважды писал. (могу под заказ сделать макрос)
Сначала к одному подключаетесь и отправляете файл, потом точно также к другому подключаетесь и отправляете
Можно 2 отдельных макроса сделать (отличающихся только настройками доступа), можно в один макрос объединить.
Здравствуйте, Игорь!
Подскажите, пожалуйста, можно ли с помощью вашего решения загружать файлы одновременно на два разных сервера? Можно ли в настройках задать два фтп? Если да, то подскажите, пожалуйста, каким образом? Где это задать?
Да, как всегда, спешишь и только все усложняешь сам себе )) Благодарю, так все работает. Отправил так же небольшую благодарность за помощь на Я.кошелек.
Заметил маленький недочет, если интересно.
В настройках сайт прописываю так:
.BaseURL = "http://site.ru/"
загрузку файла запускаю так:
link$ = FTP.UploadFile(filename$, "/wp-content/uploads" )
или так
link$ = FTP.UploadFile(filename$, "wp-content/uploads" )
файл в любом случае закачивается норм, а вот в окне отладки результат всегда пишется с пропущенным слэшем:
"Загруженный файл доступен по ссылке: http://site.ru/wp-contentuploads/111.txt"
Андрей, надо быть внимательнее :)
Из примера, где вы взяли код, вы потеряли самую первую строку кода:
Но дело не в этом. Сейчас скачал ваш пример с WinAPI, вставил в НЕГО новую процедуру test() - код ее ниже я писал - запустил и получил ровно ту же ошибку объекта.
Так что я явно что то не так запускаю. Взгляните плиз на код моей процедуры запуска еще разок, как будет время.
Надо из файла Excel в примере перетащить в Access ВСЁ - все 4 модуля класса, форму, и модуль
Тогда всё должно заработать
Вы написали "поставить какие-то галочки", а их там тьма. ) Ну не суть.
Теперь на самую первую строку моей процедуры ругается "Требуется объект", то есть "New FTPsettings" не может выполнить.
В каком классе этот объект задается? Не найду..
Форму я никак не запускал и не сохранял через нее данные, если что. Ведь это наверно не нужно, раз я руками в процедуре прописываю доступы?
Андрей, я же написал, что нужно сделать.
Надо в Tools - References (в редакторе VBA) поставить галочку для библиотеки Microsoft XML v.6.0
(версия может быть и 3.0. или 4.0. - какая найдется)
И ошибка эта исчезнет.
Создал новый файл Access, из вашего примера по ссылке с WinAPI экспортировал в него все что было внутри.
Создал для теста новую процедуру по примеру из коммента:
Здравствуйте, Андрей.
Код без WinAPI использовать не советую, - он не везде работает.
Новая версия (на WinAPI) в плане настроек - аналогичная старой версии, почти все также
Почитайте комменты, - буквально 3 комментами ниже написано, как задавать настройки в коде.
По Access - возможно, надо в Tools - References поставить какие-то галочки (я на access код не тестировал, - но, по идее, все должно работать без серьезных переделок)
Здравствуйте.
Спасибо за код что вы пишите.
Столкнулся с такой же проблемой что и Сергей - при использовании модуля без WinAPI на одном компе все проходит нормально, а тот же код на другом компе пишет пустой файл на FTP.
В результате хочу попробовать модуль с использование WinAPI, но там все в разы сложнее понять. Никак даже не могу найти где мне прописать настройки. В этой версии модуля их нельзя прописать в коде VBA?
И второй вопрос, попробовал использовать этот модуль для VBA Access, но при запуске тестового примера выдает какую-то ошибку связанную с DOM. Может ли модуль на основе WinAPI работать из Access 2010?
Здравствуйте!
А с помощью вашей надстройки возможно получить список файлов с контрольными суммами md5 из определенной папки ftp-сервера, не скачивая эти файлы?
Спасибо огромное, все работает! Правда пришлось так же заменить пять слов на Object )
Вебсервисы? Не, не слышали.
Третьего дня нашей компании довелось организовывать потоковую синхронизацию данных с производственным комплексом «Хэ». IT отдел заказчика настаивал на использовании FTP сервера, другие способы обмена яростно отвергались.
Выгрузка данных в формат xlsx.
Ничто так не радует, как горе у соседа.
Хорошо помню, как утром, по приходу в офис, мне улыбался коллега и не без доли злорадствия рапортовал о выходе новой версии Mozilla Firefox. А все потому, что задача экспорта данных в excel в наших программных продуктах была разрешена с помощью браузерного плагина, который использовал написанную на visual-C библиотеку. После смены политики безопасности приложений FireFox, мне предстояло при каждой смене версии, в лучшем случае, скачивать новую SDK и пересобирать DLL-ку (и плагин в целом), в худшем – переписывать сишный код для DLL-ки и JS парсер страницы). Про необходимость обновления плагина у пользователей и вспоминать не хочется.
- Берём (формируем) html таблицу с данными
- Сохраняем таблицу в текстовый файл, меняем расширение на xls
- Открываем файл, не забываем жмакнуть «да» на табличке с предупреждением и … вуаля! Эксель прекрасно распознал нашу таблицу. При необходимости ячейкам можно передавать css стили и указывать формат данных.
Ячейке, к примеру, можно задать размер шрифта — Анна Антонова , а можно указать числовой тип формата:
Полезные советы
Сокращения
Наверняка не все знают, что в COS существуют сокращенные версии операторов, вроде этих:
Set = s
Do = d
Write = w
Kill = k
Quit = q
…
Сокращения в большинстве случаев отражены в документации примерно так:
Типизация
Пример:
$CLASSNAME в SQL
Редкий, но вполне реальный случай – при построении запроса необходимо вывести имя класса (Аналог $CLASSNAME в SQL), для этих целей можно использовать скрытое поле, которое присуще всем классам — x__classname.
Пример:
Есть 2 класса A и B, которые наследуются от общего предка Letters Extends %Persistent. Включим в выборку поле x__classname.
Версия Cache — Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2012.2.1 (Build 705U) Wed Oct 24 2012 14:32:01 EDT.
Не секрет, что Excel довольно мощный инструмент для работы с числовыми табличными данными. Однако средства, которые предоставляет Microsoft для интеграции с ним, далеки от идеала. В частности, сложно интегрировать в Excel современные пользовательские интерфейсы. Нам нужно было дать пользователям Excel возможность работать с довольно насыщенным и функциональным интерфейсом. Мы пошли несколько другим путем, который в итоге показал хороший результат. В этой статье я расскажу, как можно организовать интерактивное взаимодействие Excel c веб-приложением на Angular и расшить Excel практически любым функционалом, который реализуем в современном веб-приложении.
Итак, меня зовут Михаил и я CTO в Exerica. Одна из проблем которые мы решаем — облегчение работы финансовых аналитиков с числовыми данными. Обычно они работают как с исходными документами финансовой и статистической отчетности, так и каким-либо инструментом для создания и поддержания аналитических моделей. Так сложилось, что 99% аналитиков работают в Microsoft Excel и делают там довольно сложные вещи. Поэтому перевести их с Excel на другие решения не эффективно и практически невозможно. Объективно, «облачные» сервисы электронных таблиц до функционала Excel пока не дотягивают. Но в современном мире инструменты должны быть удобны и соответствовать ожиданиям пользователей: открываться по клику мышки, иметь удобный поиск. А реализация в виде разных несвязанных приложений будет довольно далека от ожиданий пользователя.
То с чем работает аналитик выглядит примерно так:
Основные данные тут — это числовые «финансовые показатели», например, доход за 1 квартал 2020 года. Для простоты буду называть их дальше просто «числами». Как видно, почти никакой связи между числами в документе и в аналитической модели нет, все только в голове у аналитика. И работа с заполнением и поддержанием модели занимает часы поиска и перебивания чисел из документа в таблицы, а потом поиск ошибок ввода. Вместе этого мы хотели бы предложить пользователю привычные инструменты: «перетаскивание», вставка через клипборд и так далее, а также быстрый просмотр исходных данных.
Что у нас уже было
Связываем данные
Кроме UDF наш addin реализует ribbon (панель инструментов) с настройками и некоторыми полезными функциями по работе с данными.
Добавляем интерактивность
Вставляем данные в Excel
В нашем SPA мы подсвечиваем все числа, которые обнаружила система. Пользователь может выделять их, навигировать по ним и т.п. Для вставки данных мы реализовали 3 механизма, чтобы закрыть различные варианты использования:
- Перетаскивание (drag-and-drop)
- Автоматическая вставка по клику в SPA
- Копирование и вставка через клипборд
Эти способы хороши, когда пользователю нужно вставлять в свою модель по одному числу, но если надо перенести целую таблицу или ее часть, необходим другой механизм. Наиболее привычным для пользователей представляется копирование через клипборд. Однако этот способ оказался сложнее первых двух. Дело в том, что для удобства вставляемые данные должны быть представлены в нативном для Excel формате — OpenXML Spreadsheet. Наиболее просто это реализуется используя объектную модель Excel, то есть из addin’а. Поэтому процесс формирования клипборда у нас выглядит так:
- Пользователь выделяет область с числами в SPA
- Массив выделенных чисел передается на Notification Service
- Notification Service передает его в addin
- Addin формирует OpenXML и вставляет его в клипборд
- Пользователь может вставить данные из клипборда в любое место любой Excel-таблицы.
Несмотря на то, что данные проделывают довольно долгий путь, благодаря SignalR и RTD происходит это довольно быстро и абстрагированно от пользователя.
Распространяем данные
После того, как пользователь выбрал начальные данные для своей модели, их надо «распространить» все периоды (года, полугодия и кварталы), которые представляют интерес. Для этих целей одним из параметров нашей UDF является дата (период) данного числа (вспоминаем: «доход за 1 квартал 2020 года»). В Excel существует нативный механизм «распространения» формул, который позволяет заполнить ячейки той же формулой с учетом ссылок, заданных в параметрах. То есть вместо конкретной даты в формулу вставлена ссылка на нее, а далее пользователь «распространяет» ее на другие периоды, при этом в таблицу автоматически загружаются «те же» числа из других периодов.
А что это там за число?
Теперь у пользователя есть модель на несколько сотен строк и несколько десятков столбцов. И у него может возникнуть вопрос, что же там за число в ячейке L123? Чтобы получить ответ, у нас ему достаточно кликнуть на эту ячейку и в нашем SPA откроется тот самый отчет, на той самой странице, где записано кликнутое число, а число в отчете будет выделено. Вот так:
А если это не просто одно число из отчета, а результат некоторых вычислений на числах, взятых из отчета, то мы подсветим все числа, входящие в вычисляемое в Excel выражение. При этом не происходит загрузки всего приложения и подгрузки всех необходимых данных, как в случае перехода по ссылке.
В качестве заключения
Вот такая, на мой взгляд, нестандартная реализация взаимодействия между Excel и веб-приложением получилась довольно удобной для пользователя. Благодаря использованию Excel порог входа для пользователей из целевой аудитории довольно низкий. При этом мы получаем также всю мощь Excel по работе с числовыми данными. Сами данные всегда остаются связанными с источником, а также связанными во времени. Для целевых клиентов нет необходимости встраивать в бизнес-процесс работы с данными абсолютно новые внешние системы. Наше решение встраивается как дополнительный «инструмент» внутри Excel, что является стандартом де-факто для провайдеров финансовых данных.
Подобный архитектурный подход к интеграции веб-приложений с Microsoft Excel может быть применим и для решения других задач, требующих интерактива и сложных пользовательских интерфейсов при работе с числовыми и табличными данными.
Программирование Excel vba для загрузки ftp и открытия автоматической работы
0x00 фон
В последнее время из-за эпидемии было скучно сидеть дома, поэтому я сделал несколько макросов на Excel.
Excel использует язык vba для реализации макросов. Хотя я не изучил его, но общий код можно понять, я сделал небольшой скрипт для открытия Excel для автоматического запуска макросов и загрузки файлов на локальный ftp-сервер.
Конфигурация 0x01
Во-первых, вам нужно открыть макросы Excel, в начале excel- «параметры-» центр доверия- «настройки макросов» включить все макросы, как показано ниже.
После настройки макроса вы можете приступить к программированию макроса. Чтобы
В параметрах средства разработки выберите Visual Basic, чтобы войти в интерфейс программирования макросов.
После входа в интерфейс программирования дважды щелкните Thisbook слева, чтобы войти в модуль программирования текущего рабочего листа.
Наконец, установите автоматический запуск макроса и автоматически запускайте макрос при открытии Excel. Выберите открыть на вкладке справа
На этом настройка Excel завершена.
0x02 vba реализует загрузку по ftp
Я долго искал в Интернете, чтобы найти его. Вначале я собираюсь использовать способ доступа к dos, а затем постепенно передаю команду. Но обнаружено, что excel не просто передает строку в команду dos для запуска, а напрямую вызывает команду dos для выполнения, что делает невозможным передачу одного символа в команду dos. Если вы запустите команду aaa, она сразу вернет «недопустимую команду».
Но все же запишите метод вызова командной строки, на случай, если он пригодится в будущем =. знак равно
Код очень прост, просто вызовите встроенную функцию интерфейса напрямую. Передняя часть представляет собой строковую команду, а задняя 1 представляет собой черное окно запущенной команды dos. 0 означает, что она не выполняется (это хорошо!)
Однако этот метод не поддерживает использование команд 1 && в dos. Я не знаю почему.
Ниже приведен метод, который я нашел.
Излишне говорить, что первые три параметра
localftp - это каталог локального хранилища
ftpfile - это путь и имя файла на ftp-сервере, который нужно получить
cfgfile - текущий файл конфигурации, серия наборов команд, которые необходимо выполнить
Читайте также: