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.