kbosompem / bb-excel Goto Github PK

clojure.lang.ExceptionInfo: clojure.lang.ExceptionInfo in Interceptor  - Could not find sheet with name or index
equal 'test'! Sheet does not exist. {:execution-id 2, :stage :enter, :interceptor nil, :exception-type :clojure.l
ang.ExceptionInfo, :exception #error {
 :cause "Could not find sheet with name or index equal 'test'! Sheet does not exist."
 :data {}
 :via
 [{:type clojure.lang.ExceptionInfo
   :message "Could not find sheet with name or index equal 'test'! Sheet does not exist."
   :data {}
   :at [bb_excel.util$throw_ex invokeStatic "util.clj" 27]}]

source:

(defn ->map
  "converts excel (xlsx and xls) tab/sheet name to map"
  [path sheet-name]
  (map #(dissoc % :_r)
       (excel/get-sheet
        path sheet-name {:hdr true :fxn keyword})))

(comment
  (->map "excel-sample.xls" "test"))

excel-sample.xls

I propose some improvements that will allow the library to be used in HTTP services, and not just in granny scripts. Let me know if you're interested so I can get them finished.
I add draft PR for this proposal

Let's look at the following example

(get-sheet (io/file "/Users/flocktory/Code/bb-excel/test/data/simple.xlsx") 1)
=>
({:_r 1, :A "FirstName", :B "LastName", :C "DateOfBirth", :D "Show", :E "Votes"}
 {:_r 2, :A "Jack", :B "Bean", :C "04/20/1979", :D "Breaking Bad", :E "1325"}
 {:_r 3, :A "Mary", :B "Smith", :C "05/15/1991", :D "House M.D", :E "435"}
 {:_r 4, :A "Todd", :B "Green", :C "12/31/1999", :D "La Femme Nikita", :E "80"})

This is data from example table without opts. Let's set start-row(row) to 3 and rename columns with data from the first row

(get-sheet (io/file "/Users/flocktory/Code/bb-excel/test/data/simple.xlsx") 1 {:row 3 :hdr true})
=>
({:_r 1, "Mary" "FirstName", "Smith" "LastName", "05/15/1991" "DateOfBirth", "House M.D" "Show", "435" "Votes"}
 {:_r 2, "Mary" "Jack", "Smith" "Bean", "05/15/1991" "04/20/1979", "House M.D" "Breaking Bad", "435" "1325"}
 {:_r 4, "Mary" "Todd", "Smith" "Green", "05/15/1991" "12/31/1999", "House M.D" "La Femme Nikita", "435" "80"})

Some questions about happen above?

1. Are the headlines we received correct? We started from the third line. Did we want to get its values as headers? Or would we like to get the values from the first row as headers? Or would we like to control this behavior through a parameter? Like :hdr :top-sheet-row || :top-range-row || :numeric-representation ;; or maybe even numeric, like we can do in excel itself
2. Here I’m almost sure that we are dealing with unexpected behavior, but I’ll still clarify whether this is so. Should we have gotten lines 1 and 2 in the example above? Or if the first line is line 3, then then we only deal with lines 3 and above? An exception is possible in the form of taking the value for the header from the first row in the sheet.
3. Is it really necessary to limit the :rows(Number of rows to extract) parameter to 10000 values? Maybe change it to 1048576 which represent maximum row count xlsx format supported? When I first encountered that I only got 10,000 records, it was unexpected for me.
4. If the :row parameter was specified, were :rows meant to be counted from :row or from the top row in the table?

in some cases I don't need to know the indicating (line) of excel, we could have an option to remove the key :_r

example:

(print-table
   (get-sheet "test/data/simple.xlsx" "Shows" 
              {:line false
               :hdr true
               :fxn bb-excel.cli/skeyword}))

:line by default is true (keeps the :_r column), by setting it to false it dissoc in :_r

| :rank |        :tv_show |
+-------+-----------------|
|   1.0 |   Sesame Street |
|   2.0 | La Femme Nikita |
|   3.0 |       House M.D |
|   4.0 |    Breaking Bad |