in

Ative at Work

Agile software development

Ative at Work

Tweetable Code - Introducing Docjure for Succintly Reading and Writing Spreadsheets

How would you like to be able to SMS or tweet your Excel data-crunching code?

With Docjure, it is possible:

(->> (load-workbook "spreadsheet.xlsx")
     (select-sheet "Price List")
     (select-columns {:A :name, :B :price}))

This reads the A and B columns from the Price List sheet in the spreadsheet.xlsx file into a list of maps where the a column has key :name and the B column has key :price.

I believe that is pretty good for code small enough to fit in an SMS.

Exporting your business data to spreadsheets for further analysis is just as easy:

;; Create a spreadsheet and save it
(let [wb (create-workbook "Price List"
                          [["Name" "Price"]
                           ["Foo Widget" 100]
                           ["Bar Widget" 200]])
      sheet (select-sheet "Price List" wb)
      header-row (first (row-seq sheet))]
  (do
    (set-row-style! header-row (create-cell-style! wb {:background :yellow,
                                                       :font {:bold true}}))
    (save-workbook! "spreadsheet.xlsx" wb)))

In just a few lines of code and you have exported the price list to a worksheet, complete with a yellow-background bold header line for extra style points.

In our business applications, bridging to Excel has provided huge benefits:

  • Users love it - having their data in Excel enables them to do much more than a static report that can only be changed by a software developer.
  • Developers love it - It eliminates a lot of tedious code for generating bespoke reports as we can easily export data into an Excel report template.
  • Project managers love it - using spreadsheets provides an easy to understand data exchange format and the flexibility to change reporting features quickly late in the project.
  • Sponsors love it - the project saves a lot of time and reduces training cost by leveraging an application the users already know.

As an inspiration, here are some of the things we have used it for:

  • Use Excel to calculate currency trading strategies - the traders calculate their currency trading strategies in Excel then import it into the trading system. They benefit from the flexibility of a powerful tool they already know and the trading system takes care of the technical details of the actual trades.
  • Exporting to Excel for bespoke analysis - would you like to check your currency trading record? Export all the information to Excel and the traders can slice-and-dice it easily with little or no technical assitance. This is much easier than setting up a reporting database for them to link to.
  • Using Excel sheets for content management to facilitate translation - translating an application to all the languages of an international enterprise was easy. Rather than using complex technical XML-files we made Excel sheets the data format for the application strings and had the subsidiaries add their translations to that. Then we put an adapter on the application to convert back and forth to XML-config files and RESX files used internally in the web and Silverlight parts of the application. The translators had a great experience, they had a familiar tool with spell checking and it reduced waste in the translation process by presenting a well-known easily accessible format.
  • Exporting to Excel for reporting - set up a spreadsheet template with a data sheet and some reports based on that, then populate it with data from your application. This allows the users to easily change or add reports with no change to the software application itself, thus dramatically reducing the turn-around time for new reports. 

We believe this is so great that we just have to share it:

Therefore, Docjure is free and open-source.

It is written in Clojure for the JVM. Get it at our GitHub page here: http://github.com/ative/docjure and be sure to follow our updates on Twitter: @ativedk (http://twitter.com/ativedk)

Comments

No Comments

About Martin Jul

Building better software faster is Martin's mission. He is a partner in Ative, and helps development teams implement lean/agile software development and improve their craftmanship teaching hands-on development practises such as iterative design and test-first. He is known to be a hardliner on quality and likes to get things done-done.
© Ative Consulting ApS