Возможно, вы создали файлы CSV в своих приложениях на Rails на основе некоторых данных. Затем эти файлы можно преобразовать в формат Excel, чтобы применить некоторые стили, форматирование, добавить графики и т. Д. Однако не будет ли удобнее автоматизировать все эти задачи и генерировать файлы .xlsx? С драгоценным камнем axlsx, созданным Рэнди Морганом, это вполне возможно!
Этот драгоценный камень предоставляет огромный список функций, позволяющих вам делать практически все, начиная от добавления простых правил форматирования и заканчивая генерацией графиков и настройкой параметров печати. Много разных примеров можно найти на GitHub . Более того, доступны другие дополнительные драгоценные камни:
- axlsx_rails — предоставляет шаблоны Rails для axlsx. Действительно хорошее решение, которое мы будем использовать в этой статье.
- acts_as_axlsx — специальный миксин ActiveRecord, оснащающий ваши модели методом
to_xlsx - activeadmin-xlsx — плагин для ActiveAdmin , который упоминался в статье SitePoint об ActiveAdmin.
Сегодня мы рассмотрим, как создавать и настраивать файлы Excel в приложениях Rails с помощью гема axlsx.
Исходный код доступен на GitHub .
Создание приложения
Для этой демонстрации я буду использовать Rails 5 (хотя он все еще находится в бета-версии), но axlsx_rails работает как с Rails 3, так и 4. Сам Axlsx работает с Ruby 1.9.2 и выше.
Давайте назовем наше демо-приложение «ExcelNinja»:
$ rails new ExcelNinja -T
Мы будем хранить некоторые образцы данных в базе данных, а затем сгенерируем файлы Excel на их основе, поэтому добавим новую модель Product :
$ rails g model Product title:string price:decimal $ rake db:migrate
Я собираюсь воспользоваться seed.rb, чтобы заполнить эту новую таблицу примерами данных. Однако я также хочу, чтобы мои продукты выглядели более или менее реалистично, поэтому давайте добавим жемчужину. Он может генерировать практически все, от цен на продукты до названий книг и псевдо-ИТ-фраз.
Gemfile
[...] gem 'faker' [...]
Бегать
$ bundle install
и подправить файл seed.rb :
дБ / seeds.rb
30.times do Product.create({title: Faker::Commerce.product_name, price: Faker::Commerce.price}) end
Теперь заполните таблицу:
$ rake db:seed
Хорошо, последнее, что нужно сделать, это настроить некоторые маршруты, создать контроллер и просмотреть.
конфиг / routes.rb
[...] resources :products, only: [:index] root 'products#index' [...]
products_controller.rb
class ProductsController < ApplicationController def index @products = Product.order('created_at DESC') end end
просмотров / продукция / index.html.erb
<h1>List of products</h1>
Я не буду ничего перечислять в этом представлении — вместо этого мы предоставим пользователям ссылку для загрузки отформатированного файла Excel, поэтому перейдите к следующему разделу.
Создание файла Excel
Добавьте новый драгоценный камень в свой Gemfile :
Gemfile
[...] gem 'axlsx_rails' [...]
и беги
$ bundle install
Имея этот драгоценный камень, вы можете ввести новый формат ответа, например:
products_controller.rb
[...] def index @products = Product.order('created_at DESC') respond_to do |format| format.html format.xlsx end end [...]
Представьте новую ссылку в соответствующем формате:
просмотров / продукция / index.html.erb
<h1>List of products</h1> <%= link_to 'Download as .xlsx', products_path(format: :xlsx) %>
Теперь создайте новый шаблон с именем index.xlsx.axlsx (да, имя расширения является сложным, поэтому убедитесь, что вы набрали его правильно). Это будет обычный Ruby-файл с инструкциями для генерации .xlsx . Внутри представления xlsx_package локальная переменная xlsx_package :
просмотров / продукция / index.xlsx.axlsx
wb = xlsx_package.workbook
Таким образом, мы просто создаем новую книгу Excel. Каждая рабочая книга состоит из листов, поэтому давайте добавим один:
просмотров / продукция / index.xlsx.axlsx
wb = xlsx_package.workbook wb.add_worksheet(name: "Products") do |sheet| end
Внутри этого блока вы определяете содержимое этого листа:
просмотров / продукция / index.xlsx.axlsx
wb = xlsx_package.workbook wb.add_worksheet(name: "Products") do |sheet| @products.each do |product| sheet.add_row [product.title, product.price] end end
Таким образом, с помощью add_row мы создаем новую строку, которая содержит название продукта и цену. name: "Products" устанавливает заголовок листа.
Загрузите сервер, перейдите на корневую страницу и нажмите ссылку «Скачать как .xlsx». Это было просто, не так ли?
Чтобы переименовать сгенерированный файл, используйте следующий код:
products_controller.rb
[...] respond_to do |format| format.html format.xlsx { response.headers['Content-Disposition'] = 'attachment; filename="all_products.xlsx"' } end [...]
Кроме того, вы можете использовать метод render с различными параметрами:
render xlsx: 'products', template: 'my/template', filename: "my_products.xlsx", disposition: 'inline', xlsx_created_at: 3.days.ago, xlsx_author: "Elmer Fudd"
Более того, файлы Excel можно создавать внутри почтовых программ — читайте больше здесь .
стайлинг
В настоящее время наш лист выглядит немного скучно, поэтому давайте добавим немного цвета фона для цен. Для этого инициализируйте новую переменную со стилями внутри блока:
index.xlsx.axlsx
wb = xlsx_package.workbook wb.styles do |style| highlight_cell = style.add_style(bg_color: "EFC376") wb.add_worksheet(name: "Products") do |sheet| @products.each do |product| sheet.add_row [product.title, product.price], style: [nil, highlight_cell] end end end
Используя метод add_style , мы вводим правила стиля, которые впоследствии могут быть применены к одной или нескольким ячейкам. При вызове add_row мы просто передаем опцию :style которая принимает стили для каждой ячейки. Пока я не хочу стилизовать первый столбец (который содержит название продукта), я просто указываю nil в качестве элемента первого массива. Второй элемент содержит наш собственный стиль, который устанавливает оранжевый фон для цены продукта.
Существует гораздо больше стилей, которые можно установить (см. Примеры для получения дополнительной информации). Например, давайте добавим тонкую границу для цены и центрируем содержимое по горизонтали:
index.xlsx.axlsx
[...] highlight_cell = style.add_style(bg_color: "EFC376", border: Axlsx::STYLE_THIN_BORDER, alignment: { horizontal: :center }) [...]
Вы можете легко переопределить стили для любой ячейки:
sheet.rows.last.cells.first.u = :single # Or sheet.rows.last.cells[0].u = :single
По сути, строки и ячейки организованы как элементы массива. Метод u подчеркивает содержимое ячейки (по какой-то причине это не работает в LibreOffice — найдите здесь другие известные проблемы).
Форматирование
Хорошо, теперь я также хочу отобразить дату создания для каждого продукта. Измените add_row метода add_row :
index.xlsx.axlsx
sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell]
Однако, как вы помните, столбец created_at хранит информацию в довольно уродливом и непривлекательном формате. Как насчет добавления правила форматирования для этой ячейки? Это также можно сделать с помощью стилей:
date_cell = style.add_style(format_code: "yyyy-mm-dd", border: Axlsx::STYLE_THIN_BORDER)
Я также добавил тонкую границу, как мы сделали для цен. Стили и правила форматирования могут быть смешаны вместе без каких-либо проблем.
Теперь примените новые правила стиля:
index.xlsx.axlsx
sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell, date_cell]
Также добавим правила форматирования цен:
index.xlsx.axlsx
highlight_cell = style.add_style(bg_color: "EFC376", border: Axlsx::STYLE_THIN_BORDER, alignment: { horizontal: :center }, :format_code => '#.##')
Вместо этого вы можете использовать один из предопределенных форматов:
highlight_cell = style.add_style(bg_color: "EFC376", border: Axlsx::STYLE_THIN_BORDER, alignment: { horizontal: :center }, num_fmt: 8)
Добавление гиперссылок
Вы можете добавить гиперссылку на любую ячейку, используя метод add_hyperlink и add_hyperlink ссылку на нужную ячейку. Предположим, мы хотим, чтобы названия продуктов приводили к show действию ProductsController . Давайте быстро добавим новый метод, представление и маршрут:
конфиг / routes.rb
[...] resources :products, only: [:index, :show] [...]
products_controller.rb
[...] def show @product = Product.find(params[:id]) end [...]
просмотров / продукция / show.html.erb
<h1><%= @product.title %></h1> <p>Price: <%= @product.price %></p>
Теперь добавьте гиперссылку:
index.xlsx.axlsx
[...] @products.each do |product| sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell, date_cell] sheet.add_hyperlink location: url_for(action: "show", id: product.id, only_path: false), ref: sheet.rows.last.cells.first [...] end [...]
Здесь есть пара вещей, на которые стоит обратить внимание. Метод url_for генерирует правильную ссылку. Однако мы должны установить для параметра :only_path значение false , в противном случае этот метод сгенерирует относительный путь, который явно неверен. Опция :ref указывает адрес гиперссылки. Мы уже узнали, что ячейки могут быть доступны так же, как элементы массива. Пока мы вызываем add_hyperlink внутри цикла, sheet.rows.last всегда будет указывать на вновь добавленную строку, а sheet.rows.last.cells.first , в свою очередь, указывает на ячейку с названием продукта.
Гиперссылки могут также указывать на другие ячейки и листы. Предположим, вы добавили еще один лист в свою книгу:
wb.add_worksheet(name: 'Another Sheet') do |sheet| sheet.add_row ['a cell'] end
Вы хотите, чтобы ссылки указывали на ячейку B2 этого нового листа:
sheet.add_hyperlink location: "'Another Sheet'!B2", ref: sheet.rows.last.cells.first, target: :sheet
Просто укажите имя листа, номер ячейки и установите для параметра :target значение :sheet .
Добавление формул
Вы можете легко добавить формулы Excel с помощью Axlsx. Предположим, мы хотим рассчитать общую стоимость всех наших продуктов. Конечно, это можно сделать непосредственно внутри файла index.xlsx.axlsx, но это не совсем оптимально. Вместо этого давайте использовать функцию SUM .
Добавьте новую строку кода сразу после цикла:
index.xlsx.axlsx
[...] @products.each do |product| [...] end sheet.add_row ['Total', "=SUM(B1:B#{@products.length})"] [...]
Мы не знаем, сколько продуктов будет храниться в базе данных, поэтому используйте интерполяцию для получения правильного номера строки.
Добавление диаграмм
Axlsx поддерживает все основные типы графиков: линейчатые, линейные, круговые и точечные. Для этой демонстрации давайте создадим трехмерную гистограмму, иллюстрирующую цену продукта. Используйте метод add_chart :
index.xlsx.axlsx
[...] sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N40") do |chart| end [...]
:start_at указывает верхний левый угол графика, тогда как :end_at — нижний правый. Если в вашей базе данных много продуктов, эти значения должны быть соответствующим образом изменены — в противном случае диаграмма будет слишком маленькой. Вы также можете ввести простую формулу, такую как эта:
index.xlsx.axlsx
sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart| end
Внутри блока просто опишите диаграмму, добавив ряд данных, предоставив метки и правила стиля:
index.xlsx.axlsx
[...] sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart| chart.add_series data: sheet["B1:B#{@products.length}"], labels: sheet["A1:A#{@products.length}"], title: "Products by price", colors: @products.map { "00FF00" } end [...]
-
:dataговорят, где взять данные диаграммы. -
:labelsуказывает, куда загружать метки для каждой серии. -
:titleуказывает заголовок самой диаграммы. -
:colorsуказывает, какой цвет использовать для каждой серии.
Я обнаружил, что для LibreOffice вы должны предоставить цвета для всех серий, иначе они не будут отображаться вообще.
Вы также можете легко скрыть линии сетки:
index.xlsx.axlsx
sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart| chart.add_series data: sheet["B1:B#{@products.length}"], labels: sheet["A1:A#{@products.length}"], title: "Products by price", colors: @products.map { "00FF00" } chart.valAxis.gridlines = false chart.catAxis.gridlines = false end
По-видимому, эти параметры не имеют никакого эффекта в LibreOffice, так как он ни в коем случае не отображает линии сетки.
Вывод
В этой статье мы обсудили гем axlsx, который позволяет легко создавать файлы Excel. Мы также воспользовались преимуществами axlsx_rails для интеграции с Rails. Как я уже сказал, с помощью Axlsx вы можете сделать гораздо больше: объединить столбцы, добавить условное форматирование, добавить верхние и нижние колонтитулы, скрыть или защитить листы и т. Д. Многие примеры использования можно найти в файле примеров . Драгоценный камень также задокументирован с YARD, поэтому вы можете следовать инструкциям здесь, чтобы иметь возможность прочитать его.
К сожалению, axlsx не идеален и имеет некоторые известные проблемы взаимодействия с LibreOffice и Google Docs (на самом деле мы наткнулись на некоторые из них в этой демонстрации). Список этих вопросов можно найти здесь .
Тем не менее, эти проблемы довольно незначительны, и в целом Axlsx — действительно отличное решение, и я рекомендую попробовать его. Не стесняйтесь оставлять свои вопросы в комментариях, если что-то не работает для вас. Спасибо, что остаетесь со мной и до скорой встречи!