matthewdowney / excel-clj Goto Github PK

Write Excel docs & PDFs with Clojure data, from higher level abstractions (tree, table) or via a manual grid specification.

License: Eclipse Public License 1.0

Clojure 100.00%

excel-clj's People

Contributors

Stargazers

Watchers

Forkers

nosco plumpmath luposlip szetobo hermann-p keanedp jonathanjansen vimfun

excel-clj's Issues

No methods for reading data from excel?

First of all, thank you for providing a library that is well designed and easy to follow even if you have to dig into the source code.

Now, I haven't seem any methods to read data from excel. At least for me, that is essential, since when automating excel workflows we usually retrieve the data from the excel sheets themselves.

Having to use different libraries for reading and writing is a little annoying.

Column width does not adjust for columns after J column

I did look at the source code, but I did not find any obvious bug.

Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.

Hello,

Cool project! I'm just starting to check it out, but came across a nasty issue in my setup. May not even be related to excel-clj, but I've played around with other packages and this is the first time I've seen it. If you know that this is misplaced and know where I should put it to prevent other newbs like me from banging their head against the wall like I did, I'm all ears.

I tried the Tables example and got a Java error as summarized in the issue title and in more detail below. I "fixed" it by doing "xhost +local:" from the command line as suggested in this link, and I have a couple of theories about what that means, but really I'm just a monkey miming in a mirror here.

So this Github issue boils down to, is this an issue with dependencies related to excel-clj, and if so, should there be a note in the documentation to warn new users, or should that note go elsewhere (and if so, where)? If the latter, I'm happy to repost in the appropriate location.

Thanks for any guidance!

I'm using:

Practicalli Spacemacs on Manjaro Linux 22.0.0 (Gnome desktop, which uses Wayland windowing system)
Leiningen 2.9.10 on Java 17.0.5 OpenJDK 64-Bit Server VM
Clojure CLI version 1.11.1.1149

From a fresh "lein new app v2", my project.clj, core.clj, and the error message follow.

project.clj:

(defproject v2 "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "EPL-2.0 OR GPL-2.0-or-later WITH Classpath-exception-2.0"
            :url "https://www.eclipse.org/legal/epl-2.0/"}
  :dependencies [[org.clojure/clojure "1.11.1"]
                 [org.clojars.mjdowney/excel-clj "2.1.0"]]
  :main ^:skip-aot v2.core
  :target-path "target/%s"
  :profiles {:uberjar {:aot :all
                       :jvm-opts ["-Dclojure.compiler.direct-linking=true"]}})

core.clj:

(ns v2.core
  (:require [excel-clj.core :as excel])
  (:gen-class))

(def table-data
  [{"Date" #inst"2018-01-01" "% Return" 0.05M "USD" 1500.5005M}
   {"Date" #inst"2018-02-01" "% Return" 0.04M "USD" 1300.20M}
   {"Date" #inst"2018-03-01" "% Return" 0.07M "USD" 2100.66666666M}])

(let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
          ;; Convert the table to a grid with the table-grid function.
      workbook {"My Generated Sheet" (excel/table-grid table-data)}]
  (excel/write! workbook "resources/excel-clj-test.xlsx"))

Error message:

v2.core> (let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
               ;; Convert the table to a grid with the table-grid function.
               workbook {"My Generated Sheet" (excel/table-grid table-data)}]
           (excel/write! workbook "resources/excel-clj-test.xlsx"))
Execution error (AWTError) at sun.awt.X11GraphicsEnvironment/initDisplay (X11GraphicsEnvironment.java:-2).
Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.
ERROR: Unhandled REPL handler exception processing message {:op stacktrace, :nrepl.middleware.print/stream? 1, :nrepl.middleware.print/print cider.nrepl.pprint/puget-pprint, :nrepl.middleware.print/quota 1048576, :nrepl.middleware.print/buffer-size 4096, :nrepl.middleware.print/options {:width 80}, :session 797900c2-0e71-4496-acf1-9bcf7e88a4a3, :id 62}
java.util.concurrent.ExecutionException: java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11GraphicsEnvironment
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
	at clojure.core$deref_future.invokeStatic(core.clj:2317)
	at clojure.core$future_call$reify__8544.deref(core.clj:7041)
	at clojure.core$deref.invokeStatic(core.clj:2337)
	at clojure.core$pmap$step__8557$fn__8561.invoke(core.clj:7092)
	at clojure.lang.LazySeq.sval(LazySeq.java:42)
	at clojure.lang.LazySeq.seq(LazySeq.java:51)
	at clojure.lang.LazySeq.first(LazySeq.java:73)
	at clojure.lang.RT.first(RT.java:692)
	at clojure.core$first__5449.invokeStatic(core.clj:55)
	at clojure.core$first__5449.invoke(core.clj:55)
	at cider.nrepl.middleware.stacktrace$flag_duplicates.invokeStatic(stacktrace.clj:198)
	at cider.nrepl.middleware.stacktrace$flag_duplicates.invoke(stacktrace.clj:194)
	at cider.nrepl.middleware.stacktrace$analyze_stacktrace.invokeStatic(stacktrace.clj:219)
	at cider.nrepl.middleware.stacktrace$analyze_stacktrace.invoke(stacktrace.clj:213)
	at cider.nrepl.middleware.stacktrace$analyze_cause.invokeStatic(stacktrace.clj:323)
	at cider.nrepl.middleware.stacktrace$analyze_cause.invoke(stacktrace.clj:314)
	at cider.nrepl.middleware.stacktrace$analyze_causes$fn__10531.invoke(stacktrace.clj:348)
	at clojure.core$map$fn__5931$fn__5932.invoke(core.clj:2759)
	at clojure.core$take_while$fn__5982$fn__5983.invoke(core.clj:2918)
	at clojure.lang.Iterate.reduce(Iterate.java:81)
	at clojure.core$transduce.invokeStatic(core.clj:6946)
	at clojure.core$into.invokeStatic(core.clj:6962)
	at clojure.core$into.invoke(core.clj:6950)
	at cider.nrepl.middleware.stacktrace$analyze_causes.invokeStatic(stacktrace.clj:347)
	at cider.nrepl.middleware.stacktrace$analyze_causes.invoke(stacktrace.clj:339)
	at cider.nrepl.middleware.stacktrace$handle_stacktrace.invokeStatic(stacktrace.clj:356)
	at cider.nrepl.middleware.stacktrace$handle_stacktrace.invoke(stacktrace.clj:353)
	at clojure.lang.Var.invoke(Var.java:388)
	at cider.nrepl$wrap_stacktrace$fn__6878.invoke(nrepl.clj:432)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.interruptible_eval$interruptible_eval$fn__5543.invoke(interruptible_eval.clj:154)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_inspect$fn__6814.invoke(nrepl.clj:227)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_out$fn__6838.invoke(nrepl.clj:334)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_clojuredocs$fn__6936.invoke(nrepl.clj:529)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_complete$fn__6770.invoke(nrepl.clj:143)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_undef$fn__6910.invoke(nrepl.clj:493)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_info$fn__6804.invoke(nrepl.clj:208)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_debug$fn__6780.invoke(nrepl.clj:163)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_xref$fn__6928.invoke(nrepl.clj:514)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at v2.core$eval7016$fn__7017$fn__7019.invoke(form-init6770048005804239773.clj:1)
	at cider.nrepl$wrap_apropos$fn__6754.invoke(nrepl.clj:129)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.session$add_stdin$fn__5661.invoke(session.clj:379)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_refresh$fn__6854.invoke(nrepl.clj:380)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_resource$fn__6862.invoke(nrepl.clj:406)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_enlighten$fn__6788.invoke(nrepl.clj:190)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_test$fn__6886.invoke(nrepl.clj:442)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_classpath$fn__6762.invoke(nrepl.clj:137)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.load_file$wrap_load_file$fn__6085.invoke(load_file.clj:81)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.caught$wrap_caught$fn__5476.invoke(caught.clj:97)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_tracker$fn__6902.invoke(nrepl.clj:482)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.sideloader$wrap_sideloader$fn__6166.invoke(sideloader.clj:108)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_spec$fn__6870.invoke(nrepl.clj:416)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.dynamic_loader$wrap_dynamic_loader$fn__5729.invoke(dynamic_loader.clj:98)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_ns$fn__6830.invoke(nrepl.clj:300)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at cider.nrepl$wrap_content_type$fn__6738.invoke(nrepl.clj:107)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.session$session$fn__5646.invoke(session.clj:325)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.middleware.print$wrap_print$fn__5443.invoke(print.clj:234)
	at nrepl.middleware$wrap_conj_descriptor$fn__5207.invoke(middleware.clj:16)
	at nrepl.server$default_handler$fn__6214.invoke(server.clj:141)
	at nrepl.server$handle_STAR_.invokeStatic(server.clj:24)
	at nrepl.server$handle_STAR_.invoke(server.clj:21)
	at nrepl.server$handle$fn__6182.invoke(server.clj:41)
	at clojure.core$binding_conveyor_fn$fn__5823.invoke(core.clj:2047)
	at clojure.lang.AFn.call(AFn.java:18)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11GraphicsEnvironment
	at java.base/java.lang.Class.forName0(Native Method)
	at java.base/java.lang.Class.forName(Class.java:467)
	at clojure.lang.RT.classForName(RT.java:2209)
	at clojure.lang.RT.classForName(RT.java:2218)
	at clojure.lang.Compiler.maybeResolveIn(Compiler.java:7455)
	at clojure.core$ns_resolve.invokeStatic(core.clj:4371)
	at clojure.core$ns_resolve.invokeStatic(core.clj:4360)
	at clojure.core$ns_resolve.invoke(core.clj:4360)
	at cider.nrepl.inlined_deps.orchard.v0v10v0.orchard.java$resolve_class$fn__10018.invoke(java.clj:301)
	at cider.nrepl.inlined_deps.orchard.v0v10v0.orchard.java$resolve_class.invokeStatic(java.clj:301)
	at cider.nrepl.inlined_deps.orchard.v0v10v0.orchard.java$resolve_class.invoke(java.clj:295)
	at cider.nrepl.inlined_deps.orchard.v0v10v0.orchard.java$resolve_symbol.invokeStatic(java.clj:335)
	at cider.nrepl.inlined_deps.orchard.v0v10v0.orchard.java$resolve_symbol.invoke(java.clj:321)
	at cider.nrepl.middleware.stacktrace$analyze_fn.invokeStatic(stacktrace.clj:82)
	at cider.nrepl.middleware.stacktrace$analyze_fn.invoke(stacktrace.clj:61)
	at clojure.core$comp$fn__5876.invoke(core.clj:2586)
	at clojure.core$comp$fn__5876.invoke(core.clj:2586)
	at clojure.core$comp$fn__5876.invoke(core.clj:2586)
	at cider.nrepl.middleware.stacktrace$analyze_frame.invokeStatic(stacktrace.clj:211)
	at cider.nrepl.middleware.stacktrace$analyze_frame.invoke(stacktrace.clj:207)
	at clojure.co

Feedback Requested - Version 2.0.0-SNAPSHOT

Hello everybody,

My working prototype of version 2 is just about ready to go and I wanted to take this chance to collect feedback. I mostly use this library at work, and version 2 is certainly a huge improvement for my needs, but I want to try to accommodate anybody else using this library.

The big changes are improving performance and un-complicating the styling and data representation of cells.

Trying it out

Version 2 is available on the develop branch along with a fully updated readme (https://github.com/matthewdowney/excel-clj/tree/develop). It is also on Clojars.

If anybody has feedback on the design, finds things that are not backwards compatible that they wish were, or otherwise wants to contribute to shaping the eventual version 2, feel free to comment in this thread, open issues, or submit pull requests. I'm probably going to leave this open for a couple of weeks, and if there's no feedback at that point, merge v2 as-is.

Changes

There's a poi.clj file with a Writer-style abstraction that the rest of the framework uses — by setting it in streaming mode (the new default), the performance of writing large spreadsheets (100k rows or more) increases by an order of magnitude.
Working with cells and grids is easier.
- Cells are embellished to include style & dimension data with the helpers in cell.clj, which make it easier to merge in styles or change the dimension of a cell without knowing if it is a plain value or already embellished.
- Core now includes transpose and juxtapose helpers to transpose grids & stick them side by side, respectively. (Sticking them together vertically can be done with concator into.)

Table formatting is easier. The old table function is replaced by table-grid, which produces a grid of [[cell]]. Tables are [column->value], and table-grid respects any values or whole rows that are embellished with via cell.clj. E.g.:

(require '[excel-clj.cell :as cell])

(def table-data
  (let [highlight {:fill-pattern :solid-foreground
                   :fill-foreground-color :yellow}]
    [{"Date" #inst"2020-01-01" "Value" 42}
     ;; Highlight the entire row
     (cell/style {"Date" #inst"2020-02-01" "Value" "123"} highlight)
     ;; Highlight just the date
     {"Date" (cell/style #inst"2020-03-01" highlight) "Value" :abc}]))

This makes it easier to style and format tables, since it doesn't have to be done in the constructor for table anymore.

The tree format that tree.clj is simpler, and so is the namespace. The new format looks like

{:category {:subcategory {:label 123 :label2 456}}}

instead of

[:category [[:subcategory {:label 123 :label2 456}]]]

Even though tree and table are replaced by tree-grid and table-grid, the old functions continue to work (and tree still works with the old tree format).
In addition to write!, there is an append! function which replaces only specific sheets within a preexisting workbook and writes the result to a new file. This can be used for some kinds of templates, where the entire template lives in one sheet and uses formulas to pull in raw data from the sheet that append! replaces.

Best,
Matt

issue with an ordered hash map

Thanks for creating this library. It makes it much simpler to create a spreadsheet. My issue was if the initial map is long, the resulting spreadsheet mixes up the order of the entries from what was provided. I think this is a problem with trying to insist on an order in a hash table, but I am not sure. Specifically, I tried the following sample for a tree (see below).
When it is opened in my Libreoffice spreadsheet, the column headings are not in the order of 2010 to 2031, but, seemingly randomly arranged, e.g. column list: 2030, 2011, 2031,2023, 2028 etc.

In any event your work has been helpful.
Blake Hurt (4bhurt at gmail.com)

(def mock-balance-sheet
  ["balance sheet"
   [["Assets"
     [["Current Assets"
       [["Deposits"
         {2010 14146.445249629207,
          2011 15191.131989554618,
          2012 16056.606701961136,
          2013 15752.586376407999,
          2014 15442.207255633548,
          2015 13133.928102771752,
          2016 11719.329564240645,
          2017 12596.978559237672,
          2018 12325.148904772941,
          2019 11363.87928459968,
          2021 10927.0,
          2022 11374.34375,
          2023 11825.625,
          2024 12280.84375,
          2025 12740.0,
          2026 12971.0546875,
          2027 13203.09375,
          2028 13436.1171875,
          2029 13436.1171875,
          2030 13436.1171875,
          2031 13436.1171875}]
        ["Cash" {2018 100M, 2017 85M}]
        ["Accounts Receivable" {2018 5M, 2017 45M}]]
       ["Investments" {2018 100M, 2017 10M}]
       ["Other" {2018 12M, 2017 8M}]]]]
    ["Liabilities & Stockholders' Equity"
     [["Liabilities"
       [["Current Liabilities"
         [["Notes payable" {2018 5M, 2017 8M}]
          ["Accounts payable" {2018 10M, 2017 10M}]]]
        ["Long-term liabilities" {2018 100M, 2017 50M}]]]
      ["Equity"
       [["Common Stock" {2018 102M, 2017 80M}]]]]]]])

Caused by: java.lang.RuntimeException: No such var: enc/nested-merge, compiling:(excel_clj/cell.clj:26:20)

Triggered when I require excel-clj.core

Example of how to use POI for Sheets

The README.md helps me with how to format cells 👍

An example of manipulating Sheets would be fantastic because it is unclear how to approach this best.

E.g. I would love to use setColumnWidth because some of the cell content is very long, but I don't want the column to be equally long.

Cannot import library on a server without X11

Hi! Thanks for making this. It's the only Clojure excel library I could find that is based on a recent version of POI.

Unfortunately, when I try to import the library on a server, I get an exception about X11 not being present, which I think is caused by the PDF stuff in there. I'll dig around to see if I can make a PR that pushes those to a separate namespace, so that I don't have to import them if I don't need them.

Thanks again!

i'm using wsl

hi thanks.
I try first example. problem below~

(let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
;; Convert the table to a grid with the table function.
workbook {"My Generated Sheet" (excel/table table-data)}]
(excel/quick-open workbook))
Fontconfig error: Cannot load default config file

(process:28549): Gtk-WARNING **: 08:45:29.014: Locale not supported by C library.
Using the fallback 'C' locale.
Execution error (IOException) at sun.awt.X11.XDesktopPeer/launch (XDesktopPeer.java:124).
Failed to show URI:file:/tmp/generated-sheet16968759586452060480.xlsx

Thanks! good day~*

symbol inst-ms (excel_clj/poi.clj:321:22) cannot be resolved

Hello there,
I'm trying to use your library but I get this error:
#error {
:cause Unable to resolve symbol: inst-ms in this context
:via
[{:type clojure.lang.Compiler$CompilerException
:message java.lang.RuntimeException: Unable to resolve symbol: inst-ms in this context, compiling:(excel_clj/poi.clj:321:22)
:at [clojure.lang.Compiler analyze Compiler.java 6688]}
{:type java.lang.RuntimeException
:message Unable to resolve symbol: inst-ms in this context
:at [clojure.lang.Util runtimeException Util.java 221]}]

Can you fix it please?

Periodic issue when generating large Excel files (parallelization?)

Hi there!

First of all - I think your approach to generating Excel from Clojure is great and more flexible than the alternatives I've tried.

When using version 1.2.0:

There seem to be a periodic issue when generating the exact same files based on the exact same data with the exact same code: Sometimes styles/cells are messed up, and placed incorrectly in the sheet. This means I always have to double check the file, and re-generate it if something looks wrong.

When using version 1.1.2:

The issue doesn't seem to be there.

Conclusion?

My initial thought is that the cause might be the parallelization feature of 1.2.0, which perhaps doesn't always put the results correctly together?

Best,
Henrik

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.