How to export xlsx file using elixlsx

Phoenix Forum Questions / Help
,
1

Hello everyone, I am trying to export a xlsx file, and I find a library elixlsx. I find the example here Phoenix/Elixir — Export data to XLSX , but it is too old and I can’t find any other example. I don’t know what should I do because phoenix 1.7 remove view. Can anyone share a example how to use it

2

I find your question a bit confusing.
Do you want to render an Excel file or allow it to be downloaded?
These are two very different things.
Could you please clarify?

3

Phoenix did pull out the specific modules around Phoenix.View, but you can do both:

  • Pull in phoenix_view as a dependency and continue like before
  • Use the plain function based view layer phoenix generates since 1.7 with distinct modules per format
1 Like
4

generate a excel file and download it

5

I find your answer and finally convert it to phoenix 1.7, thank you :grinning:How to convert this simple view to a Phoenix 1.7 view?
here’s my solution:

defmodule ExcelWeb.PostController do
  use ExcelWeb, :controller

  def index(conn, _params) do
    posts=[
      %{id: 1, name: "first", title: "first", content: "first"},
      %{id: 2, name: "second", title: "second", content: "second"},
      %{id: 3, name: "third", title: "third", content: "third"}
    ]

    conn
    |> put_resp_content_type("text/xlsx")
    |> put_resp_header("content-disposition", "attachment; filename=posts_report.xlsx")
    |> render("report.xlsx", %{posts: posts})
  end
end

defmodule ExcelWeb.PostXLSX do
  alias Elixlsx.{Workbook, Sheet}

  @header [
    "ID",
    "Name",
    "Title",
    "Content"
  ]

  def report(posts) do
    report_generator(posts)
    |> Elixlsx.write_to_memory("report.xlsx")
    |> elem(1)
    |> elem(1)
  end

  def report_generator(%{posts: 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

and remember add the format in Myapp_web.ex

formats: [:html, :json, :xlsx]
2 Likes
6

Also check out Exceed, which makes it easy to generate an Excel spreadsheet as a stream which helps when your document has a lot of data, so that you can start sending the document immediately.

Exceed is a high-level stream-oriented library for generating Excel files, useful when generating spreadsheets from data sets large enough that they may exceed available memory—or the available memory that one wants to dedicate to building spreadsheets.

4 Likes