RubyExcel class. Useful?

I’ve managed to create a (relatively) stable data-processing class which
behaves a bit like Excel, but with some of Ruby’s awesome syntax styles.
I’ve used it on a few projects now so I’m happy with how it works,
although I’m always trying to improve it.

The 2 files are intended to be placed in the same directory. I might
break it down further in a later build.

This was written to be documented with “yard” if that makes the random
layout easier on the eyes.

The intention is to help simplify scripts which have to sift through
table data, by allowing the user to work with a variety of ways to
access
and loop though the data. The class is built around the assumption that
it is dealing with a 2D array of data with headers in the first row,
which it generally tries to avoid touching while looping through the
data. The
indexing is 1-based to mimic Excel’s API.

There are multiple ways to reference the data. You can do this by Row,
Column, Cell indices, Ranges, and individual addresses using [].

I’ve found this a happy medium between the slowness of Excel’s clunky
VBA, and the frustration of having to constantly move Ruby’s Array
indices around to avoid affecting the headers.

If I can get rid of the bugs in this and add enough useful functionality
then I might publish it as a gem for anyone who regularly has to deal
with tables (HTML in my case) and wants a simple tool to do it with.

Any comments, advice, requests or constructive criticism are welcome!

Special thanks to Robert K. for providing the impetus to get this
started.

Oops, I had a broken line in there.

This:
ruby_XL.send( self.class.to_sym, self.index ,ruby_XL )

Doesn’t cause an error anymore if changed to this:
ruby_XL.send( self.class.name[/row|column/i].downcase.to_sym, self.index
)

Are there tests that demonstrate the use of this?

Thanks. One thing that strikes me, looking through the code, is that
Column
and Row are very similar. It wouldn’t surprise me if you could pull a
lot
more logic up into the base class. I also have the vague feeling that
this
could generally be done with a lot less code. But talk is cheap, so take
that with a grain of salt. What are you using this for? It’s certainly
an
interesting exercise, if nothing else.

Good point Andrew. Writing a thorough demo is a good way to
systematically test every method as well.
I just tried to demo some of the newer stuff I added and I’ve already
found a couple of bugs where I have class methods referenced rather than
the mix-in methods I changed to recently. I’ll run a thorough series of
tests, build up some examples, and then try again.

This covers some of the basics anyway:

irb(main):002:0> re = RubyExcel.new.test
=> RubyExcel => columns: 4, rows: 6, values: 24
irb(main):003:0> puts re
a b c d
e f g h
i j k l
m n o p
q r s t
u v w x
=> nil
irb(main):004:0> re[‘B2’] = ‘z’
=> “z”
irb(main):005:0> re.column_by_header(‘c’).map! { |el| el.next }
=> Class: RubyExcel::Column, Index: C
irb(main):006:0> puts re
a b d d
e z h h
i j l l
m n p p
q r t t
u v x x
=> nil
irb(main):007:0> re.delete_column(‘D’)
=> RubyExcel => columns: 3, rows: 6, values: 18
irb(main):008:0> re.cell(2,3).value = ‘z’
=> “z”
irb(main):009:0> puts re
a b d
e z z
i j l
m n p
q r t
u v x
=> nil
irb(main):010:0> re += [ %w(1 2 3) ]
=> RubyExcel => columns: 3, rows: 7, values: 21
irb(main):011:0> puts re
a b d
e z z
i j l
m n p
q r t
u v x
1 2 3
=> nil
irb(main):012:0> re.filter!(‘b’) { |el| el =~ /[a-r]/ }
=> RubyExcel => columns: 3, rows: 4, values: 12
irb(main):013:0> puts re
a b d
i j l
m n p
q r t
=> nil
irb(main):014:0> re.row(2)[‘A’] = ‘q’
=> “q”
irb(main):015:0> re.summarise ‘a’
=> {“q”=>2, “m”=>1}
irb(main):016:0> puts re
a b d
q j l
m n p
q r t
=> nil
irb(main):017:0> puts re.uniq! ‘a’
a b d
q j l
m n p
=> nil

Thanks for the feedback. I’d certainly like to make the code concise and
efficient. I’m trying to refactor regularly but I also add new options
and variations whenever I think of them, so it’s a bit of a struggle in
both directions.

I’ve tried to use the “Write Once / DRY” approach, which is why I’ve
started to put certain shared methods into the Helper module, but I’m
still a n00b at this so I’m sure I’m making parts needlessly
complicated.

Thanks for the name suggestions. I picked the name “RubyExcel” out of
the air so I’m open to changing it. The reason I went with that name was
because I’m trying to make something with the best of Excel’s and Ruby’s
APIs together.

I’ll have to learn how to use github now :slight_smile: I’ll weed out the bugs that
I’ve noticed and then figure out how to upload there.

I wrote this because I work with browser-based reports which appear in
HTML tables. I then have to extract the data into excel. This is a tool
to handle the data in the interim, and to make all of the changes to the
data in the memory, using an API which allows me to easily. read back
what it’s doing.
I’ll add more detail on usage cases later.

Thanks for the advice, guys. I’ll act on this and come back with some
updates.

Looks nice! Could you toss it up on github?

Also I’d recommend calling it something more generic like
RubySpreadsheet or Tabular, since it is neither interfacing with excel
nor reading excel files.

martin

On Fri, Mar 8, 2013 at 4:49 PM, Joel P. [email protected]
wrote:

I’ve managed to create a (relatively) stable data-processing class which
behaves a bit like Excel, but with some of Ruby’s awesome syntax styles.
I’ve used it on a few projects now so I’m happy with how it works,
although I’m always trying to improve it.

Good, I see you’re making progress!

One thing I noticed on quick glancing is that you are overusing string
interpolation.

self =~ “#{s}” → self =~ s.to_s

if “#{ val }”.empty? → if val.to_s.empty?

In []= you are evaluating the same condition twice.

I also share Andrew’s feeling that you might get away with less code.
Somehow it feels like you put too much into the class. For example,
methods #strip! and #upcase! deal with individual cell manipulations.
They may make sense as convenience methods but would first try to
focus on the core functionality of the spreadsheet: inserting and
removing rows and columns. And method #test does not belong there this
should go into a test class.

Another thing that stroke me odd is that you assume there will always
be one header row. IMHO that is a too specific assumption. If any I
would only put this in a subclass or a wrapper class.

Finally I believe you are working too much with Arrays. For example
in method #+ instances of Row, Column and RubyExcel are converted to
Arrays before they are appended. Since these classes belong to this
library you know how their internals look and you should have a better
way to transfer their data over to this sheet. Same for #- and
probably others as well.

Kind regards

robert

Thanks for your input, Robert.

I did wonder whether I should convert the underlying dataset into an
Array rather than using a Hash, since spreadsheets are “structured” and
I find the easiest way to manipulate the structure is with Array
methods. Enumerable seems to be Array-based as well, and I’m still
rather hazy on when to override methods like “map”, or when to rely on
the methods already available through “each”.
Still, I’ve learned a lot about Hashes while writing this code, so even
if I do abandon their use for the main data storage I’ll still find good
use for them elsewhere.

My reasoning behind the prevalence of headers is simply that if you
wanted data without headers you’d just use arrays rather than this
class. One of the big things I find helpful with this is that code is
much more readable if I can reference a header rather than an index.
I’m not sure what a test class or wrapper class is. I’ll look them up.

I see your point about to_s. I suppose I should differentiate between
using interpolation for multiple variables and to_s for single cases.

The +, -, and << methods are recent additions; mostly because I only
just learned that you can define these. I’m sure there are multiple ways
to write these; my first attempt was very poor in performance… and my
thinking was to avoid re-inventing the wheel by using the Array methods
written by someone much smarter than me :slight_smile:

I hadn’t realised that I was evaluating “empty?” twice in “[]=”. That
was a performance-increasing experiment I’d tried to avoid recalculating
the dimensions after every write operation. It made quite a difference
at the time and I hadn’t looked into simplifying it yet.

All in all, this still needs a lot of work to make it useful; but now
that I have a better feel of what the weaknesses and strengths are, I
hope to improve on this starting point and eventually build something
genuinely useful to others as well as myself.

I’d be particularly interested on the question of Array vs Hash for the
internals. Hash is great because of the simplicity of addresses and its
efficient way of coping with blank space, but Arrays can keep their
“form” much more effectively and already support things like sorting,
rows, and columns.

Wow! Lots of good advice, as usual :slight_smile: It’ll take me a while to process
all this so I’ll just get on with it.

Incidentally, I hadn’t seen this before:

It looks similar in many ways to what I’m trying to build. I’ll have to
see what I can learn from it as well.

On Sat, Mar 9, 2013 at 4:31 PM, Joel P. [email protected]
wrote:

Thanks for your input, Robert.

You’re welcome!

I did wonder whether I should convert the underlying dataset into an
Array rather than using a Hash, since spreadsheets are “structured” and
I find the easiest way to manipulate the structure is with Array
methods.

It does not really matter what you do. You could even use a hybrid
approach where you start with an Array based storage and exchange it
with a Hash based storage once sparseness is too large (for your
particular measure of “too large”). If you follow the layered
approach (see at end) you could have two implementations of the plain
data store which have the exact same API but one uses a Hash
internally and the other on an Array…

Enumerable seems to be Array-based as well, and I’m still

No, not at all. Enumerable is just a module which relies solely on
the existence of a method #each. It’s as simple as

irb(main):001:0> class X
irb(main):002:1> def each; yield 1; self end
irb(main):003:1> include Enumerable
irb(main):004:1> end
=> X
irb(main):005:0> x = X.new
=> #<X:0x802ec310>
irb(main):006:0> x.to_a
=> [1]
irb(main):007:0> x.select {|n| n.odd?}
=> [1]
irb(main):008:0> x.select {|n| n.even?}
=> []
irb(main):009:0> x.find {|n| n > 0}
=> 1

Or a simplistic integer range:

irb(main):014:0> class IntRange
irb(main):015:1> include Enumerable
irb(main):016:1> def initialize(a, b)
irb(main):017:2> @low, @high = [Integer(a), Integer(b)].sort
irb(main):018:2> end
irb(main):019:1> def each
irb(main):020:2> n = @low
irb(main):021:2> while n < @high
irb(main):022:3> yield n
irb(main):023:3> n += 1
irb(main):024:3> end
irb(main):025:2> self
irb(main):026:2> end
irb(main):027:1> end
=> nil
irb(main):028:0> ir = IntRange.new 3, 8
=> #<IntRange:0x80280048 @low=3, @high=8>
irb(main):029:0> ir.to_a
=> [3, 4, 5, 6, 7]
irb(main):030:0> ir.each {|x| p x}
3
4
5
6
7
=> #<IntRange:0x80280048 @low=3, @high=8>

No Arrays around. :slight_smile:

rather hazy on when to override methods like “map”, or when to rely on
the methods already available through “each”.

You should normally not override Array methods. You generally
shouldn’t inherit from Array either. Those core classes are best used
through delegation.

Still, I’ve learned a lot about Hashes while writing this code, so even
if I do abandon their use for the main data storage I’ll still find good
use for them elsewhere.

That’s good! I am glad you see it that way. Others might view these
exercises as useless detours - but they underestimate the learning
effects which come from that. You certainly learned a lot more than
by placing questions for every detailed question that occurred to you
as a few other members of the community seem to have chosen to do
recently.

My reasoning behind the prevalence of headers is simply that if you
wanted data without headers you’d just use arrays rather than this
class. One of the big things I find helpful with this is that code is
much more readable if I can reference a header rather than an index.

Then I would at least make the number of header rows and header
columns a property of the individual instance - and not as a constant
in the implementation. Still, I believe that with introduction of the
concept of “headers” in this class you may make things too complex too
fast.

I’m not sure what a test class or wrapper class is. I’ll look them up.

A test class would be a class implementing unit tests. A wrapper
class simply wraps around your class RubyExcel in much the same way
that RubyExcel wraps a Hash. In other words: it presents a different
abstraction. It’s a general approach in software engineering to
create several layers of abstractions which makes it easier to deal
with only few aspects on one layer. The ISO 7 layer model is a famous
representative of that approach.
http://www.technology-training.co.uk/understandingtheiso7layermodel_10.php

I see your point about to_s. I suppose I should differentiate between
using interpolation for multiple variables and to_s for single cases.

+1

The +, -, and << methods are recent additions; mostly because I only
just learned that you can define these. I’m sure there are multiple ways
to write these;

I wrote about numeric operators in Ruby a while back:
http://blog.rubybestpractices.com/posts/rklemme/019-Complete_Numeric_Class.html

my first attempt was very poor in performance… and my
thinking was to avoid re-inventing the wheel by using the Array methods
written by someone much smarter than me :slight_smile:

… which is perfectly understandable and OK. In this case the
conversion to an Array based structure might burn a lot of memory
though.

All in all, this still needs a lot of work to make it useful; but now
that I have a better feel of what the weaknesses and strengths are, I
hope to improve on this starting point and eventually build something
genuinely useful to others as well as myself.

That’s a good approach.

I’d be particularly interested on the question of Array vs Hash for the
internals. Hash is great because of the simplicity of addresses and its
efficient way of coping with blank space, but Arrays can keep their
“form” much more effectively and already support things like sorting,
rows, and columns.

In the end it does not matter that much what you use internally for
representation. The important bit is to use the proper API to your
storage to allow for a consistent view of the model and easy of use.
For the moment I’d stick with Hash but it may make sense to use a
layered approach: split class RubyExcel into (at least) two where one
is only responsible for providing a consistent API to your data with
the minimal operations needed to make it work. Use that internally as
storage and put everything else like header handling, those
convenience methods mentioned or reading from file and writing to file
in the wrapper class. That way you get a clean separation of
concerns: you have a proper abstraction of the storage and you get a
second layer which adds all the whistles and bells you need to work
efficiently with it (like treading header rows and columns
differently).

Kind regards

robert

Ok… having looked over what I want to accomplish with this I’ve
decided to go with the following:

Completely rewrite using Arrays as primary internal storage (there’s
always the option to swap with or include Hash later). I rarely deal
with large gaps in my data so this seems the best approach for now.

Rebuild the internal classes for the different functions (data storage,
sections, element handling, accessing, etc.) within a class which
controls the API. I want to try and separate the different types of
logic and make this easier to work on.

Plan the layout before writing any code this time, and rewrite as
idiomatically as possible rather than copy-pasting the older code.

This is going to take some time, due to my other work. Still, once it’s
complete it should improve the speed with which I can can write related
code.
Apologies to anyone who wanted to play around with this anytime soon,
but I’d rather do it right than do it quickly.

Aha! I have learned how to use Github.

My hideous-looking, still under-construction code is here:

I’ve split it into something more resembling an Excel workbook, and
tried to reduce the redundancy of address-based code by adding a module
called Address.

I’ll keep hammering bits onto it, cutting away the dross, and testing it
until it turns into a reasonably serviceable tool.

As always, any advice is welcome!

On Wed, Mar 13, 2013 at 4:45 PM, Joel P. [email protected]
wrote:

Aha! I have learned how to use Github.

+1

As always, any advice is welcome!
This doesn’t look good:
def range=( first_cell, last_cell=nil, val )

Calls will look like this

sheet.range= 1, 2, 3

There is no visual separation between coordinates and the value like
there is with []=.

I always find constructs like this suspicious:

def cell( row, col )
Element.new( self, indices_to_address( row, col ) )
end

The purpose of a constructor is to construct something and return it
so the caller can do something with it. If you do not do that
everybody is left wondering what happens to the newly created
instance. If you need it only temporary then another method name
would be better. Or change it so that not Element stores itself
somewhere in “self” but rather “self” itself.

Kind regards

robert

Robert K. wrote in post #1101459:

This doesn’t look good:
def range=( first_cell, last_cell=nil, val )
There is no visual separation between coordinates and the value like
there is with []=.

Thanks for spotting that, I wasn’t sure how to use it. I’ve completely
removed that method; range now returns an object and range.value can get
and set its content.

I always find constructs like this suspicious:

def cell( row, col )
Element.new( self, indices_to_address( row, col ) )
end

The purpose of a constructor is to construct something and return it
so the caller can do something with it. If you do not do that
everybody is left wondering what happens to the newly created
instance. If you need it only temporary then another method name
would be better. Or change it so that not Element stores itself
somewhere in “self” but rather “self” itself.

I’m not really sure what you mean by this. My intention is that cell and
cells are like alises for range, but you can select by 1-based indices
(again, copying excel). Range is also capable of referring to more than
1 cell, and can take cells as arguments. This allow for purely numerical
selection of any area, I’ve allowed columns to be selected numerically
as well.

irb(main):053:0> s.cell(1,1)
=> RubyExcel::Sheet::Element: A1
irb(main):054:0> s.cell(1,1).value
=> “A1”

On Sat, Mar 9, 2013 at 10:05 PM, Joel P. [email protected]
wrote:

Wow! Lots of good advice, as usual :slight_smile: It’ll take me a while to process
all this so I’ll just get on with it.

I have attached the skeleton of a class for spreadsheet storage to
illustrate what I was saying earlier. Note for example that this
class does not have textual cell indexes. I thought it might be
easier to use int ranges when focusing on the basic manipulations
which will move cells. Depending on storage chosen that may be tricky
enough already.

A next step could be to create a subclass which allows for
CellReferences to register with the class and have them automatically
update on successful move operations. I do not know whether you need
that but when trying to recreate a spreadsheet application in Ruby
that might be an interesting feature to have.

As step towards the user your spreadsheet class could wrap the storage
class and translate indexes back and forth so the user of your class
would only see addresses like “A4” instead of [0, 3].

Incidentally, I hadn’t seen this before:
Class: CSV::Table (Ruby 1.9.3)
It looks similar in many ways to what I’m trying to build. I’ll have to
see what I can learn from it as well.

Yes, but keep in mind that CSV has a different mental model: in CSV a
row is typically a record, so all the data in a row belongs together.
In a spread sheet both dimensions are much more equal. With CSV you
won’t do operations like inserting a rectangular area somewhere in
between while that could be quite common with spread sheets.

Kind regards

robert

On Wed, Mar 13, 2013 at 6:18 PM, Joel P. [email protected]
wrote:

Robert K. wrote in post #1101459:

would be better. Or change it so that not Element stores itself
somewhere in “self” but rather “self” itself.

I’m not really sure what you mean by this. My intention is that cell and
cells are like alises for range, but you can select by 1-based indices
(again, copying excel). Range is also capable of referring to more than
1 cell, and can take cells as arguments. This allow for purely numerical
selection of any area, I’ve allowed columns to be selected numerically
as well.

It’s about the constructor (Element.new) - not addressing methods.
Bottom line: don’t abuse constructors for doing algorithmic things.
Constructors are for constructing something so it is in a consistent
state and can be worked with. Placing the complete logic in a
constructor will prevent flexible use of classes.

Cheers

robert

On Wed, Mar 13, 2013 at 8:50 PM, Joel P. [email protected]
wrote:

Maybe I’m just being stupid, but I’m having trouble understanding your
meaning. Do you mean that I shouldn’t always use an “Element” class, and
just work directly with the data?

No, I was being stupid. Somehow I overlooked that the whole point of
the method was to return the Element instance. Just forget it. Sorry
for the noise.

Cheers

robert

Maybe I’m just being stupid, but I’m having trouble understanding your
meaning. Do you mean that I shouldn’t always use an “Element” class, and
just work directly with the data?

So, for instance, “cell” and “[]” could reference the data directly,
whereas “range” could be an object to be used in different ways?

Rare honesty, admirable :slight_smile: