ExVEx - Excel from Elixir

News Announcing
,
1

Excel from, or for Elixir.

There are a couple of existing Excel packages. Some read Excel, others write Excel. Some may read and write excel. This one is the only one (I know of) that reads, writes, and edits Excel files, preserving formatting.

9 Likes
2

This is awesome! Finally we can read/write Excel from a single package!

3

Let me know if you run into any issues, or raise an issue on github.

Thanks.

4

Great stuff. I was searching for this type of software back when made BPMN projects.

5

I see pivot tables in your future plans but what about plain old boring tables?

Would you consider a name that is more enjoyable to type than ExVEx?

2 Likes
6

Sorry, I had made ex_v_ex quite a while ago while working on some work projects. Forgot to provide table functionality, as we were not using tables, just opening/parsing/updating existing files that had none.

Please check now, and let me know if you have any issues.
I’ve tested with the following:

cat tmp/excel_check/gen.exs

out = "tmp/excel_check"

seed = fn ->
{:ok, book} = ExVEx.new()
rows = [["Region", "Qty", "Amount"], ["North", 2, 10.5], ["South", 3, 20.0], ["East", 1, 5.25]]

Enum.reduce(Enum.with_index(rows, 1), book, fn {row, r}, acc ->
  Enum.reduce(Enum.with_index(row, 1), acc, fn {v, c}, acc2 ->
    {:ok, acc2} = ExVEx.put_cell(acc2, "Sheet1", {r, c}, v)
    acc2
    end)
  end)
end

{:ok, a} = ExVEx.add_table(seed.(), "Sheet1", "A1:C4", name: "Sales")
:ok = ExVEx.save(a, Path.join(out, "1_new_table.xlsx"))

{:ok, b} = ExVEx.put_table_totals_row(a, "Sales", Region: {:label, "Total"}, Qty: :count, Amount: :sum)
{:ok, b} = ExVEx.put_cell(b, "Sheet1", "E1", {:formula, "SUM(Sales[Amount])"})
:ok = ExVEx.save(b, Path.join(out, "2_totals_row.xlsx"))

{:ok, c} = ExVEx.append_table_rows(b, "Sales", [["West", 4, 1.0], %{"Region" => "Central", "Amount" => 2.0}])
:ok = ExVEx.save(c, Path.join(out, "3_append_rows.xlsx"))

{:ok, d} = ExVEx.rename_table_column(c, "Sales", "Amount", "Value")
{:ok, d} = ExVEx.rename_table(d, "Sales", "Revenue")
:ok = ExVEx.save(d, Path.join(out, "4_renamed.xlsx"))

{:ok, e} = ExVEx.insert_column(d, "Sheet1", 2)
{:ok, e} = ExVEx.insert_row(e, "Sheet1", 3)
:ok = ExVEx.save(e, Path.join(out, "5_insert_row_and_column.xlsx"))

{:ok, f} = ExVEx.put_table_style(e, "Revenue", style: "TableStyleLight9", show_row_stripes: false, show_first_column: true)
{:ok, f} = ExVEx.resize_table(f, "Revenue", "A1:E10")
:ok = ExVEx.save(f, Path.join(out, "6_style_and_resize.xlsx"))

{:ok, g} = ExVEx.add_sheet(f, "Other")
{:ok, g} = ExVEx.put_cell(g, "Other", "A1", "Key")
{:ok, g} = ExVEx.put_cell(g, "Other", "A2", 1)
{:ok, g} = ExVEx.add_table(g, "Other", "A1:A2", name: "Keys", style: nil)
{:ok, g} = ExVEx.put_cell(g, "Other", "C1", {:formula, "SUM(Revenue[Value])"})
:ok = ExVEx.save(g, Path.join(out, "7_second_sheet_table.xlsx"))

{:ok, h} = ExVEx.remove_table_totals_row(g, "Revenue")
{:ok, h} = ExVEx.remove_table(h, "Keys")
:ok = ExVEx.save(h, Path.join(out, "8_remove_totals_and_table.xlsx"))

{:ok, i} = ExVEx.open("test/fixtures/with_table.xlsx")
{:ok, i} = ExVEx.insert_row(i, "Sales", 3, 2)
{:ok, i} = ExVEx.append_table_rows(i, "Orders", [["West", 4, 2.0, nil]])
:ok = ExVEx.save(i, Path.join(out, "9_fixture_edited.xlsx"))

{:ok, j} = ExVEx.open("test/fixtures/with_table.xlsx")
{:ok, j} = ExVEx.delete_row(j, "Sales", 2, 3)
:ok = ExVEx.save(j, Path.join(out, "10_fixture_all_data_deleted.xlsx"))

By the way, on the name. What do you think of ExCell, or Spreadex? Something else?

7

That was quick! I’ll have a play with the table api.

Re: name. I am in the you-don’t-need-to-put-ex-in-your-libary-name camp. Short-ish name-cased names are my preference. Xlsx, Xcell, Sheets, Linen, Mud, Bark, Chook etc.

8

I don’t get Chook/Bark/Mud
I like Linen, and Xcell and ExCell are similar.
Sheets (vs Linen) may give the impression of Google Sheets somehow… Linen is kinda cool though,

9

Finally, we don’t have to run to Python to work with Excel! Nice work, @jjenkins!

I’m with @cmo on this the name needs to change.

I’m in favour of SpreadEx. It sounds nice and is clear. ExCell sounds and seems too similar to Excel. But again, it’s up to you.

10

Sorry for the delay.
I’ve changed the name to Linen. Was going to use SpreadEx, but there was a gambling app with the same name, so decided to avoid that name.

1 Like