Unable to load the cells which are blank

[LakshmiSwathi97]

Once you stream read and get your workbook, the columns which are blank are not considered which is resulting in columns mismatch while reading the cell values everytime

[pjfanning]

Do you have a test case that proves this?

[gs-offcl]

@LakshmiSwathi97 Do you have working code on this issue?

[margintop15px]

Hello guys! I have the same issue. Here is my sample code (I'm using it from Clojure code but it shouldn't affect the actual functionality)

(ns xsls-test.core
  (:require
   [clojure.java.io :as io])
  (:import
   [com.monitorjbl.xlsx StreamingReader]
   [org.apache.poi.ss.usermodel Cell CellType]))

(defn cell-data [^Cell cell]
  (let [cell-type (.getCellType cell)]
    (cond
      (= cell-type CellType/NUMERIC) (.getNumericCellValue cell)
      (= cell-type CellType/BOOLEAN) (.getBooleanCellValue cell)
      :otherwise (.getStringCellValue cell))))

(let [stream   (io/input-stream (io/resource "trialling_sergey_change.xlsx"))
      workbook (-> (StreamingReader/builder)
                   (.rowCacheSize 100)
                   (.bufferSize 4096)
                   (.open stream))
      sheet    (first workbook)]
  (->> (seq sheet)
       (mapv (fn [row]
               (let [cells (seq row)]
                 (mapv cell-data cells))))))

Result I'm getting out of it

[["person" "role'" "depart-ment"]
 ["harbs" "head" "devOps" "kkmjksnkja"]
 ["ridders" "product"]
 ["pascal" "head" "afsaaa" "product"]
 ["Dos" "legend" "engineering"]]

As you can see the number of columns is different for each row. And here is a screenshot of how the actual file looks like

Project setup

(defproject xsls-test "0.1.0-SNAPSHOT"

  :dependencies [[org.clojure/clojure "1.11.1"]
                 [com.monitorjbl/xlsx-streamer "2.2.0"]
                 [commons-io "2.8.0"]]

  :repl-options {:init-ns xsls-test.core})

[margintop15px]

I can provide an actual XLSX file if it helps.

[pjfanning]

@margintop15px this project is not maintained but if you want to submit a test case to my fork, I will have a look. https://github.com/pjfanning/excel-streaming-reader