Phoenix/Elixir — Export data to XLSX

In this tutorial, we create phoenix application with Post model whitсh is going to be exported like a xlsx. First of all install Phoenix project (i use elixir v1.4.0, phoenix v1.2.1, psql (PostgreSQL) 9.6.1) and create phoenix project:

mix phoenix.new posts_xlsx

Before moving on, configure your database in config/dev.exs and run:

$ mix ecto.create
The database for PostsXlsx.Repo has been created
$ mix phoenix.server

After running phoenix server we can open URL http://localhost:4000 and see starting page:

As we had our phoenix project we need to create Post table where we can store our posts content and show it in our application. Elixir provides Mix build tools and Phoenix has currently a number of built-in Phoenix-specific and ecto-specific mix tasks. Mix task mix phoenix.gen.html the generated resource will contain:

  • a schema in web/models
  • a view in web/views
  • a controller in web/controllers
  • a migration file for the repository
  • default CRUD templates in web/templates
  • test files for generated model and controller

Generates a Phoenix resource:

mix phoenix.gen.html Post posts name:string title:string content:text

Add the resource to your browser scope in web/router.ex:

scope "/", PostsXlsx do
pipe_through :browser
  get "/", PageController, :index
resources "/posts", PostController
end

Remember to update your repository by running migrations:

$ mix ecto.migrate

Open page http://localhost:4000/posts and add some posts:

Create scope folders:

$ mkdir web/controllers/exports/
$ mkdir web/view/exports/

Create controller and view that responsible for export generation:

touch web/controllers/exports/post_controller.ex
touch web/view/exports/post_view.ex

For creating xlsx export we going to use elixlsx lib, add it to mix.exs file:

defp deps do
[{:phoenix, “~> 1.2.1”},
{:phoenix_pubsub, “~> 1.0”},
{:phoenix_ecto, “~> 3.0”},
{:postgrex, “>= 0.0.0”},
{:phoenix_html, “~> 2.6”},
{:phoenix_live_reload, “~> 1.0”, only: :dev},
{:gettext, “~> 0.11”},
{:cowboy, “~> 1.0”},
{:elixlsx, “~> 0.1.1”}]
end

Add code to web/controller/exports/post_controller.ex

defmodule PostsXlsx.Exports.PostController do
use PostsXlsx.Web, :controller
  alias PostsXlsx.Post
  def index(conn, _params) do
posts =
Post
|> limit(10_000)
|> Repo.all
    conn
|> put_resp_content_type(“text/xlsx”)
|> put_resp_header(“content-disposition”, “attachment; filename=posts_report”)
|> render(“report.xlsx”, %{posts: posts})
end
end

And for web/views/reports/post_view.ex:

defmodule PostsXlsx.Exports.PostView do
use PostsXlsx.Web, :view
alias Elixlsx.{Workbook, Sheet}
@header [
“ID”,
“Name”,
“Title”,
“Content”
]
def render(“report.xlsx”, %{posts: posts}) do
report_generator(posts)
|> Elixlsx.write_to_memory(“report.xlsx”)
|> elem(1)
|> elem(1)
end
def report_generator(posts) do
rows = posts |> Enum.map(&(row(&1)))
%Workbook{sheets: [%Sheet{name: “Posts”, rows: [@header] ++ rows}]}
end
def row(post) do
[
post.id,
post.name,
post.title,
post.content
]
end
end

Also, we need to create the route for our report controller, open file web/router.ex and add scope:

scope “/exports”, as: :exports, alias: PostsXlsx.Exports do
pipe_through :browser
  resources “/posts”, PostController, only: [:index]
end

You can check your routes by mix task:

$ mix phoenix.routes
page_path  GET     /               PostsXlsx.PageController :index
post_path GET /posts PostsXlsx.PostController :index
post_path GET /posts/:id/edit PostsXlsx.PostController :edit
post_path GET /posts/new PostsXlsx.PostController :new
post_path GET /posts/:id PostsXlsx.PostController :show
post_path POST /posts PostsXlsx.PostController :create
post_path PATCH /posts/:id PostsXlsx.PostController :update
PUT /posts/:id PostsXlsx.PostController :update
post_path DELETE /posts/:id PostsXlsx.PostController :delete
exports_post_path GET /exports/posts PostsXlsx.Exports.PostController :index

Phoenix generates path helper for us exports_post_path. We need to create a button for export our post xlsx report. Open file web/template/post/index.html.eex and add code:

<h2>Listing posts</h2>
<ul class="nav pull-right">
<li>
<%= link "Export XLSX", to: exports_post_path(
@conn, :index), class: "btn btn-default" %>
</li>
</ul>

<table class="table">
<thead>
<tr>
<th>Name</th>
<th>Title</th>
<th>Content</th>
<th></th>
</tr>
</thead>
<tbody>
<%= for post <- @posts do %>
<tr>
<td><%= post.name %></td>
<td><%= post.title %></td>
<td><%= post.content %></td>
<td class="text-right">
<%= link "Show", to: post_path(@conn, :show, post), class: "btn btn-default btn-xs" %>
<%= link "Edit", to: post_path(@conn, :edit, post), class: "btn btn-default btn-xs" %>
<%= link "Delete", to: post_path(@conn, :delete, post), method: :delete, data: [confirm: "Are you sure?"], class: "btn btn-danger btn-xs" %>
</td>
</tr>
<% end %>
</tbody>
</table>
<%= link "New post", to: post_path(@conn, :new) %>

If you press the button, phoenix application generate for you xlsx report with Posts data.

P.S. Example Post XLSX Phoenix application code available by link.