Как связать эксель с сайтом

Обновлено: 28.03.2024

В этом приеме описывается три способа получить данные, содержащиеся в веб-странице: вставить статическую копию информации; создать обновляемую ссылку на сайт; открыть страницу непосредственно в Excel.

Вставка статической информации

Один из способов получить данные из веб-страницы на лист — просто выделить текст в браузере, нажать Ctrl+C, чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал — в комплекте с настройками форматирования, гиперссылками и графикой.

Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная ► Буфер обмена ► Вставить, можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная ► Буфер обмена ► Вставить ► Специальная вставка, а затем пробовать различные варианты вставки.

Вставка обновляемой информации

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

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

Рис. 176.2. Данные, импортированные из веб-страницы

Рис. 176.2. Данные, импортированные из веб-страницы

По умолчанию импортированные данные — это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.

Открытие веб-страницы напрямую

Еще один способ получить данные веб-страницы на лист — открыть URL-адрес напрямую, с помощью команды Файл ► Открыть. Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть. Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.

Не секрет, что 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 может быть применим и для решения других задач, требующих интерактива и сложных пользовательских интерфейсов при работе с числовыми и табличными данными.

Всем привет. Встала такая проблема: на сайте есть форма с несколькими select и одним input в который клиент вводит тираж заказа. Цены на продукцию лежат в файле excel .xlsx . При различных select, цены разные. Как мне из файла excel брать динамически цены ? К примеру клиент выбирает select визитки и вводит количество 2. Мне нужно взять цену визитки( а она зависит еще и от тиража) и умножить на 2. Потом захочет произвести расчет другой продукции. Пока только расчет.

Менеджер знает только excel, перебить в mysql в связи с большим количеством таблиц в excel и их сложность не реально.

Нужно каким-то образом брать значение из нужно ячейки ?

Спасибо за ответы.

KorsaR-ZN

Конвертируйте все xls в csv, дальше на js конвертируйте csv в массив и считываете нужную Вам колонку по индексу.

Либо маленький серверный скрипт, который по запросу к нему вернет из нужного файла значение нужной колонки, там можно и CSV и XLS ридеры найти и без всяких баз :)

RazerVG

Вот только одна проблема в одном excel есть несколько таблиц. А cvs для каждой таблицы создает файл, у меня их 3. Такой вариант не подходит. Я нашел в интернете библиотеку js-xlsx. Для него необходим NodeJS. Я делаю так сказать подсистему для сайта, который сделан на 1С: Битрикс. Возможно ли установка на него NodeJS ?

RazerVG

То есть я хотел написать сайт у меня на хостинге и node.js и js-xlsx не мой вариант, так как для этой связки необходим минимум VDS/VPS.
Мне предложили сделать БД и написать для менеджера возможность синхронизировать excel, к примеру если изменяться цены. И AJAX в зависимости от select выбирать цену.
Что думайте по поводу такого варианта ? Еще есть проблемка в том, что значения нужно выбирать, как бы перекрестным способом, то есть цена зависит и от столбца и от строки. Я даже не представляю, как в таком случае делать структуру.

KorsaR-ZN

RazerVG: Ну самый правильный вариант это конечно база, т.к у этого варианта самая быстрая скорость обработки запроса.

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

Если скажем в файле, строки это типы (визитки и т.д), столбцы это размеры, я перекрест их - это цена.
То Вам нужно будет три таблицы, первая для хранения типа, вторая для хранения размера и третья для организации связи с ценой (typeId, sizeId, price). Тогда для получения цены вам нужно будет запросить из третей таблицы по условию typeId=A AND sizeId=B.

KorsaR-ZN

RazerVG: А почему вариант с тремя csv файлами не подходит?. Если там все три таблицы однородные, тогда их можно склеить в один файл. Или в чем там проблема?)

RazerVG

KorsaR-ZN: Просто я делаю это задание для фирмы и менеджеры в связи с часто меняющимися ценами будут переделывать excel файл. И потом им нужно каждый раз экспортировать в три файла вообщем это долго и не удобно. Таблицы тоже везде разные где то, как я писал цена выбирается по типу таблицы умножения на старых тетрадках ( самый простой пример ). Где то просто два столбца цена и стоимость.

KorsaR-ZN

RazerVG: пришли те мне на почту (есть в пофиле) примеры разных этих прайсов, у меня сегодня будет свободное время, я что-нибудь по думаю. Просто не видя структуры, сложно что-то посоветовать.

master2016

Без написания excel-скрипта для периодического выброса (скажем, раз в минуту) данных из таблиц в csv не обойдетесь. Хотя и это очень не лучший вариант интеграции.

Не уверен, что об этом писали здесь и считаю своим долгом поделиться этим хаком.

Иногда приходит менеджер и говорит: — Хочу эту таблицу видеть на сайте.
И уходит.
Вырисовывается два стандартных пути:
— либо через некую админку в редакторе ваять таблицу и заполнять ее значениями;
— либо ручками сразу в html и также наполнять значениями.
И ладно, если там 3х5 ячеек, а если больше.

Столкнувшись с подобным несколько лет назад я открыл для себя метод, базирующийся на использовании Windows Live Writer (софт написания и отправки постов в некоторые блоггерские платформы).

Все просто. Скопированную таблицу вставляем в WLW специальной вставкой с сохранением формата

image

затем переходим на нижнюю вкладку «Source» в WLW и копируем из поля весь огромный код

image

Получается так или почти так, как в оригинале. Времени занимает секунды, если не считать одноразовых затрат на прикрепление WLW к какой-нибудь блого-учетке.
Пример тут codepen

раз уж карма и рейтинг упали, допишу тут для всех, кто предлагает всевозможный save as из excel.

1. сохранить как «Веб страница в одном файле». Вариант "… веб страница" рассматривать не буду, т.к. он создает папку с четырьмя файлами.

Хотите поделиться файлом из OneDrive? Прочтите эту статью.

Поскольку внедренные Excel в Интернете книги теперь включены в SharePoint Online, вы можете использовать их на своей веб-странице или в блоге, где другие пользователи смогут просматривать их и работать с ними, не входя в SharePoint Online. Вы можете получить URL-адрес книги, размещенной в Microsoft 365, и внедрить ее непосредственно в блог или на веб-страницу, задав значение атрибута SRC элемента .

Следуйте инструкциям ниже, чтобы встраить SharePoint Online- Excel в Интернете бизнес-аналитики в свою веб-страницу или блог.

Настройка разрешений для книги

Перед внедрением книги в блог или на веб-страницу убедитесь, что доступ к ней предоставлен всем, кому нужно. Например, если вы ведете общедоступный блог, нужно поделиться книгой со всеми пользователями, включая тех, кто не является сотрудниками вашей организации. Вот как предоставить общий доступ к книге:

Меню

В окне Пригласить пользователей выберите Все и Могут просматривать.

Диалоговое окно

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

Флажок Требовать вход должен быть в любом случае снят, чтобы был возможен анонимный доступ.

Иногда администраторы SharePoint блокируют анонимный доступ, удаляя поле Требовать вход и присваивая этому параметру значение "Истина". В этом случае обратитесь к своему администратору.

Внедренную книгу с Excel в Интернете нельзя редактировать, но люди с нужными разрешениями могут открыть внедренную книгу в Excel, где они смогут редактировать данные.

Получение URL-адреса общедоступной книги

В меню Дополнительно (…) рядом с книгой выберите гостевую ссылку.

Гостевая ссылка в меню

Выделите URL-адрес гостевой ссылки, скопируйте его и вставьте в Блокнот.

Чтобы использовать внедренную книгу, измените URL-адрес следующим образом:

Чтобы книга выводилась как внедренная, добавьте в конце URL-адреса параметр action=embedview.

Чтобы в книге можно было использовать фильтры и сводные таблицы, добавьте параметр wdAllowInteractivity=True.

Для использования функций бизнес-аналитики, таких как визуализации Power View, сводные таблицы и срезы на основе модели данных, добавьте строку &wdbipreview=True.

Конечный URL-адрес должен выглядеть примерно так:

Настройка дополнительных параметров

Дополнительные параметры URL-адреса позволяют изменить способ вывода внедренной книги.

Вот пример URL-адреса с дополнительными параметрами:

Скрывает ярлычки, которые отображаются внизу всех листов в книге.

Позволяет сортировать и фильтровать данные в таблицах и сводных таблицах книги.

Выводит определенный элемент. Если книга содержит диаграмму, таблицу, сводную таблицу или именованный диапазон и на веб-странице необходимо отобразить только этот элемент, его можно указать с помощью параметра Item. Сведения об именованных диапазонах и именованных элементах в книгах Excel см. в статье Определение и использование имен в формулах.

Определяет, какая ячейка будет активной (выбранной) во внедренной книге при открытии веб-страницы. Ячейку можно указать путем ссылки на нее (например, A1) или по имени.

С помощью этого параметра также можно определить активный лист, выбрав ячейку на нем. Если вы хотите сделать активным лист Power View, задайте для этого параметра значение A1, хотя на листе Power View нет сетки.

Если активная ячейка не задана, будут показано последнее сохраненное представление.

Выводится последнее сохраненное представление.

Скрывает линии сетки на листе.

Скрывает заголовки строк и столбцов.

Настройка размера фрейма

Вы можете изменить размер фрейма, в котором книга выводится на веб-странице, с помощью стандартных свойств HTML. Найдите значения ширины и высоты (в пикселях) внутри тега iframe и измените их, чтобы получить нужный размер.

width="201" height="173" frameborder="0" scrolling="no"

Проверка опубликованной книги

Убедитесь, что опубликованная вами внедренная книга доступна для анонимных пользователей. Вот на что стоит обратить внимание:

Для полной поддержки Power View требуется Silverlight. В HTML5 некоторые возможности Power View могут быть ограничены.

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

Откройте браузер в частном режиме. Например, щелкните Начать просмотр InPrivate в Internet Explorer или Новое окно в режиме инкогнито в Chrome.

Читайте также: