ExVEx - Excel from Elixir

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

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

Thanks.

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

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?

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?

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.

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,

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.